oracle10g資料庫創建
⑴ 在Oracle 10g中創建資料庫的方法有哪些它們分別由哪些優點和缺點
一般分為圖形化界面(OUI)和silent模式。
圖形界面比較直觀,你能看到各個component如何設置,缺點是要開圖形化界面,而且對於客制化的部分需要寫腳本
silent模式主要使用資料庫創建腳本,不需要人工過多干涉,只要把腳本完善即可。並且可以在建庫同時把客制化部分加進來。缺點是需要熟悉腳本設置
⑵ Oracle Database 10g Express Edition 如何創建一個資料庫
按照步驟走,挺靈活的。但是要在選擇資料庫模版時注意一下,有三個選項,選擇第二項可以定義資料庫的目錄,然後在自己創建主目錄下分別創建自己的子目錄,例如我,有REDOLOGS、ARCHIVELOGS、DATAFILES、CONTROLFILES等目錄,同時也要為FLASHRECOVERY指定目錄,完全可以自定義。在最後一步,就是顯示所有文件目錄的頁面上可以定義。但是要注意,10G的DBCA好像有些BUG,我曾經出現創建不成功的情況。但步驟應該是這樣的。包括資料庫的NLS類型,建議在創建時(如果是中文系統)定義為AMERICAN_AMERICA.WE8IS08859P1.因為用默認中文在CMD下會有顯示問題。當然也可以嘗試用CREATE DATABASE腳本創建,也比較靈活。更詳細的答案: http://www..com/s?bs=Oracle+Database+10g+Express+Edition+%C8%E7%BA%CE%B4%B4%BD%A8%D2%BB%B8%F6%CA%FD%BE%DD%BF%E2&f=8&wd=Oracle10g%C8%E7%BA%CE%B4%B4%BD%A8%D2%BB%B8%F6%CA%FD%BE%DD%BF%E2
⑶ 在Oracle 10g中創建資料庫的方法有哪些它們分別由哪些優點和缺點
創建表語法:
create
table
表名(
欄位名1
欄位類型,
欄位名2
欄位類型,
.....
欄位名n
欄位類型
);
案例:創建學生表,包括欄位
學號,姓名,性別,年齡,生日,電話
sql>
create
table
student(
2
no
varchar2(10),
3
name
varchar2(20),
4
sex
char(2),
5
age
number(3),
6
birthday
date,
7
tel
varchar2(20)
8
);
*
desc
表名;//查詢表的結構
//採用復製表的方式創建表
create
table
emp2
as
select
*
from
emp;
//瘋狂復製法
insert
into
emp2
select
*
from
emp2;
常採用第一種:直接創建表
⑷ oracle資料庫10g安裝步驟
運行解壓縮出的 Oracle 10g 安裝程序。打開oracle10g的安裝目錄,找到setup.exe,雙擊即可進入安裝狀態
⑸ 如何手工創建一個Oracle 10g資料庫
1.首先要有一個參數文件,這個我利用了歷史的參數文件
[oracle@dbstatsvr dbstat]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.2.0 - Proction on Thu Sep 17 18:21:20 2009
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1778384896 bytes
Fixed Size 1261332 bytes
Variable Size 301990124 bytes
Database Buffers 1459617792 bytes
Redo Buffers 15515648 bytes
2.創建腳本,參考其他資料庫,編寫一個
SQL> create database dbstat
2 logfile group 1 ('/data1/dbstat/redo1.log') size 50M,
3 group 2 ('/data1/dbstat/redo2.log') size 50M,
4 group 3 ('/data1/dbstat/redo3.log') size 50M
5 character set ZHS16GBK
6 national character set utf8
7 datafile '/data1/dbstat/system.dbf'
8 size 500M autoextend on next 10M maxsize unlimited extent management local
12 sysaux datafile '/data1/dbstat/sysaux.dbf'
13 size 500M autoextend on next 10M maxsize unlimited
17 undo tablespace undotbs1
18 datafile '/data1/dbstat/undo.dbf' size 100M
20 default temporary tablespace temp
21 tempfile '/data1/dbstat/temp.dbf' size 100M;
Database created.
3.在後台跑一跑其他腳本:
[oracle@dbstatsvr dbstat]$ nohup sqlplus "/ as sysdba" @?/rdbms/admin/catalog.sql &
[1] 5399
[oracle@dbstatsvr dbstat]$ nohup: appending output to `nohup.out'
[oracle@dbstatsvr dbstat]$
[1]+ Stopped nohup sqlplus "/ as sysdba" @?/rdbms/admin/catalog.sql
[oracle@dbstatsvr dbstat]$
[oracle@dbstatsvr dbstat]$ nohup sqlplus "/ as sysdba" @?/rdbms/admin/catproc.sql &
[2] 5402
[oracle@dbstatsvr dbstat]$ nohup: appending output to `nohup.out'
這樣就基本上有了一個資料庫的雛形了。
4.還有一個腳本要運行
這是資料庫用普通用戶登錄會出現一個錯誤,提示以SYSTEM運行PUPBLD.SQL腳本:
SQL> connect sms/sms
Error accessing PRODUCT_USER_PROFILE
Warning: Proct user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
這個腳本會創建一個表,用於限制用戶執行某些特定的SQL*PLUS命令:
SQL> @?/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
DATE_VALUE FROM PRODUCT_USER_PROFILE
*
ERROR at line 3:
ORA-00942: table or view does not exist
DROP TABLE PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
DROP TABLE PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist
View created.
Grant succeeded.
DROP PUBLIC SYNONYM PRODUCT_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
Synonym created.
DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
SQL> connect sms/sms
Connected.
⑹ 一般的應用系統在生產環境下Oracle10g資料庫的創建全過程
1、安裝oracle軟體,直接點下一步就行了,oracle安裝完了以後
2、然後安裝資料庫,默認orcl,也可以改成別的名字,最好把用戶名密碼記下來別搞忘了
3、登陸資料庫,使用資料庫工具創建表空間,設置一個最小值,讓它自動增長就可以了,最大值就不用設置了,因為到時候你不能估計你資料庫到底有多大
4、創建一個草雞用戶,然後把表空間分配給這個用戶,以後操作資料庫就方便了,怎麼分配許可權就看你系統怎麼使用了
5、備份和使用很簡單的,就cmd裡面使用imp命令就可以對資料庫進行導出了,導入也差不多
⑺ oracle10G怎麼里怎麼創建多個資料庫
一個資料庫創建多個實例
1. 先要關閉資料庫(進程和內存關閉)
[Oracle@oracle_2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Proction on TueNov 12 20:34:53 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release10.2.0.1.0 - Proction
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
2. 設置環境變數
[oracle@oracle_2 ~]$ vi .bash_profile
此時環境變數已經設置好了
3. 創建目錄
i. 創建相關文件
此時我們可以看見已經有了相關目錄,那是我們之前創建的實例ORCL的相關目錄
創建目錄是採用了批量創建目錄{a,b,c,u}mp
[oracle@oracle_2 ~]$ cd $ORACLE_BASE
[oracle@oracle_2 oracle]$ ls
admin flash_recovery_area oradata oraInventory proct
[oracle@oracle_2 oracle]$
[oracle@oracle_2 oracle]$ mkdir -p admin/ORA10G/{a,b,c,u}mp
[oracle@oracle_2 oracle]$ ls
admin flash_recovery_area oradata oraInventory proct
[oracle@oracle_2 oracle]$ cd admin/
[oracle@oracle_2 admin]$ ls
ORA10G ORCL
[oracle@oracle_2 admin]$ cd ORA10G/
[oracle@oracle_2 ORA10G]$ ls
amp bmp cmp ump
[oracle@oracle_2 ORA10G]$
[oracle@oracle_2 ORA10G]$ ls
amp bmp cmp ump
[oracle@oracle_2 ORA10G]$ cd ..
[oracle@oracle_2 admin]$ ls
ORA10G ORCL
[oracle@oracle_2 admin]$ cd ..
[oracle@oracle_2 oracle]$ ls
admin flash_recovery_area oradata oraInventory proct
[oracle@oracle_2 oracle]$ mkdir -p oradata/ORA10G
[oracle@oracle_2 oracle]$
ii. 創建密碼文件
創建密碼文件需要到ORACLE_HOME/dbs目錄下創建
[oracle@oracle_2 10.2.0]$ cd $ORACLE_HOME/dbs
[oracle@oracle_2 dbs]$ ls
hc_ORCL.dat initdw.ora init.ora lkORCL orapwORCL spfileORCL.ora
[oracle@oracle_2 dbs]$ orapwd
Usage: orapwd file=<fname>password=<password> entries=<users> force=<y/n>
where
file -name of password file (mand),
password- password for SYS (mand),
entries -maximum number of distinct DBA and force - whether to overwrite existing file (opt),
OPERs (opt),
There areno spaces around the equal-to (=) character.
[oracle@oracle_2 dbs]$ orapwd file=orapwORA10Gpassword=oracle entries=30
[oracle@oracle_2 dbs]$ ls
hc_ORCL.dat init.ora orapwORA10G spfileORCL.ora
initdw.ora lkORCL orapwORCL
[oracle@oracle_2 dbs]$
[oracle@oracle_2 dbs]$ cat init.ora |grep -v^#|grep -v ^$ >initORA10G.ora
[oracle@oracle_2 dbs]$ ls
hc_ORCL.dat init.ora lkORCL orapwORCL
initdw.ora initORA10G.ora orapwORA10G spfileORCL.ora
[oracle@oracle_2 dbs]$
[oracle@oracle_2 dbs]$ vi initORA10G.ora
將內容改的和下面的一樣
db_name=ORA10G
db_files = 80 # SMALL
db_file_multiblock_read_count = 8 #SMALL
log_checkpoint_interval = 10000
processes = 50 # SMALL
parallel_max_servers = 5 #SMALL
log_buffer = 32768 # SMALL
max_mp_file_size = 10240 # limit trace file size to 5 Meg each
global_names = false
control_files =(/u01/app/oracle/oradata/ORA10G/ora_control1.ctl,/u01/app/oracle/oradata/ORA10G/ora_control2.ctl)
sga_max_size=300m
sga_target=300m
4. 啟動實例為ORA10G的資料庫
[oracle@oracle_2 dbs]$ export $ORACLE_SID=ORA10G
[oracle@oracle_2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Proction on TueNov 12 21:08:55 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ORA10G>
SYS@ORA10G>create spfile from pfile;
File created.
SYS@ORA10G>startup nomount;
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 96470416 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
SYS@ORA10G>
SYS@ORA10G>show parameter undo
NAME TYPE VALUE
------------------------------------ -----------------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string
SYS@ORA10G>
修改參數,由於undo_management參數為靜態參數,所以需要加上scope=spfile
SYS@ORA10G>alter system set undo_management=autoscope=spfile;
System altered.
SYS@ORA10G>show parameter undo
NAME TYPE VALUE
------------------------------------ -----------------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string
SYS@ORA10G>shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
此時發現並沒有更改,是由於靜態參數需要重啟才有效
SYS@ORA10G>startup nomount;
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 96470416 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes
SYS@ORA10G>
此時只是改了spfile的參數還需要改pfile的參數
SYS@ORA10G>create pfile from spfile;
File created.
SYS@ORA10G>
5. 多個實例的切換
i. 實例為ORCL啟動資料庫
[oracle@oracle_2 dbs]$ export ORACLE_SID=ORCL
[oracle@oracle_2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Proction on TueNov 12 21:19:19 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ORCL>
ii. 實例為ORCL10G啟動資料庫
SYS@ORCL>exit
Disconnected
[oracle@oracle_2 dbs]$ export ORACLE_SID=ORA10G
[oracle@oracle_2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Proction on TueNov 12 21:24:54 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release10.2.0.1.0 - Proction
With the Partitioning, OLAP and Data Mining options
SYS@ORA10G>
⑻ 、oracle10g怎麼創建資料庫
方法1.
開始/程序->Oracle - OraDb10g_home1->配置和移植工具->Database Configuration Assistant
一步一步走下去,最後生成腳本即可,這樣的腳本可以多次使用,每次只需要根據需求,修改相關參數即可
方法2、
直接寫sql語句,create database dtabase_name這個比較麻煩,而且容易出問題!
⑼ oracle 10g在安裝時讓創建啟動資料庫是什麼意思
oracle 10g在安裝時讓創建啟動資料庫的意思是在安裝oracle軟體過程中由安裝程序為你創建一個資料庫實例。
具體解釋如下:
1、如果選擇此選項,則安裝程序會為你安裝一個資料庫實例,並將此實例運行,即OPEN狀態,你可以在系統運行的本地服務中查看到此服務。
2、如果不選擇此選項,則安裝程序會為你安裝一個資料庫實例,則安裝程序只為你安裝oracle軟體。你可以在oracle程序的菜單中,配置和遷移工具部分,選擇database configuration assistant進行手動安裝自己的資料庫實例。