如今數(shù)據(jù)庫(kù)得到了廣泛的應(yīng)用，關(guān)于數(shù)據(jù)庫(kù)名大家可以修改，下文中將為大家講解修改數(shù)據(jù)庫(kù)名的方法。

1.用oracle自帶的工具nid改數(shù)據(jù)庫(kù)名

在本例中，假設(shè)原來的數(shù)據(jù)庫(kù)名為test,要改成testdb,原實(shí)例名(service_name,instance_name)test,要改成testdb.

nid是自帶的工具，在oracle_home/bin目錄中.以下方法假設(shè)登陸到數(shù)據(jù)庫(kù)本機(jī)做。

1.1 sqlplus "sys/password as sysdba"

1.2 sql>shutdown immediate --先停掉數(shù)據(jù)庫(kù)

1.3 sql>startup mount --nid需要在mount狀態(tài)下才能做。

1.4sql>host nid target=sys/password dbname=new_dbname --nid是一個(gè)在操作系統(tǒng)下執(zhí)行的命令，在sqlplus環(huán)境中用host 去調(diào)用操作系統(tǒng)環(huán)境下的命令。命令中，當(dāng)你想將數(shù)據(jù)庫(kù)名字改為TESTDB時(shí)，應(yīng)寫成dbname=testdb

1.5執(zhí)行后程序會(huì)詢問：

Change database ID and database name TEST to TESTDB? (Y/[N]) =>y

敲y確定。

之后程序會(huì)對(duì)控制文件，數(shù)據(jù)文件進(jìn)行修改：

Control File C:ORACLEORADATATESTCONTROL01.CTL - modified

Control File C:ORACLEORADATATESTCONTROL02.CTL - modified

Control File C:ORACLEORADATATESTCONTROL03.CTL - modified

Datafile C:ORACLEORADATATEST YSTEM01.DBF - dbid changed, wrote new name

Datafile C:ORACLEORADATATESTUNDOTBS01.DBF - dbid changed, wrote new nam

Datafile C:ORACLEORADATATESTINDX01.DBF - dbid changed, wrote new name

Datafile C:ORACLEORADATATESTTOOLS01.DBF - dbid changed, wrote new name

Datafile C:ORACLEORADATATESTUSERS01.DBF - dbid changed, wrote new name

Datafile D:DATAFILEPORMALS_SPA.DBF - dbid changed, wrote new name

Datafile C:ORACLEORADATATESTOEM_REPOSITORY.DBF - dbid changed, wrote ne

name

Datafile D:DATAFILEPORMALS_SPA_01.DBF - dbid changed, wrote new name

Datafile C:ORACLEORADATATESTTEMP01.DBF - dbid changed, wrote new name

Control File C:ORACLEORADATATESTCONTROL01.CTL - dbid changed, wrote new

name

Control File C:ORACLEORADATATESTCONTROL02.CTL - dbid changed, wrote new

name

Control File C:ORACLEORADATATESTCONTROL03.CTL - dbid changed, wrote new

name

Database name changed to TESTDB.

Modify parameter file and generate a new password file before restarting.

Database ID for database TESTDB changed to 2321050327.

All previous backups and archived redo logs for this database are unusable.

Shut down database and open with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

1.6 停庫(kù)再到mount狀態(tài)下改db_name:

sql>shutdown immediate;

sql>startup mount

sql>alter system set db_name=testdb scope=spfile;

sql>shutdown immediate;

1.7 重新創(chuàng)建sys的password文件：

passwd文件通常放在oracle_home/database目錄下，文件命名形式為PWDsid.ora，sid為實(shí)例名(Service_name)，如當(dāng)前的數(shù)據(jù)庫(kù)名及service_name為test,則passwd文件為PWDtest.ora

sql>host orapwd file=c:oracleora92databasepwdtest.ora password=123456 entries=5

要注意一下，此時(shí)雖然數(shù)據(jù)庫(kù)名已經(jīng)改成testdb了，但instance_name還是test，所以，passwd文件必須跟以前一樣。否則會(huì)出錯(cuò)。

1.8 開啟數(shù)據(jù)庫(kù)：（要open resetlogs)

sql>startup mount

sql>alter database open resetlogs;

1.9 檢查：

sql>select dbid,name from v$database;

2.改好數(shù)據(jù)庫(kù)名后，接著改instance_name

2.1如果是windows系統(tǒng)，要先把所有的oracle服務(wù)先關(guān)閉，否則會(huì)出錯(cuò)。

2.2先將原來的service_name刪除:

在操作系統(tǒng)中，進(jìn)入cmd，

oradim -delete -sid test

2.3創(chuàng)建密碼文件

orapwd file=c:/oracle/ora92/database/pwdtestdb.ora password=....entries=

2.4創(chuàng)建一個(gè)新的sid，也就是你想改名的sid：

oradim -new -sid testdb -intpwd 密碼 -startmode a -pfile c:oracleora92databaseinittestdb.ora

當(dāng)數(shù)據(jù)庫(kù)啟動(dòng)時(shí)，會(huì)在database目錄中找spfile，如果spfile不在就找initSID.ora這個(gè)文件來頂。所以，可以把pfile直接創(chuàng)建在這里。方便數(shù)據(jù)庫(kù)的啟動(dòng)。

2.5 進(jìn)入oracle并創(chuàng)建spfile：

c: set oracle_sid=testdb

c:sqlplus "sys/password as sysdba"

sql>create spfile from pile='c:oracleora92databaseinittestdb.ora';

2.6 reload listener：

c:lsnrctl reload

2.7 open resetlogs:

改數(shù)據(jù)庫(kù)名字精簡(jiǎn)版

1.用oracle自帶的工具nid改數(shù)據(jù)庫(kù)名

1.1 sqlplus "sys/zhuzerp as sysdba"

1.2 sql>shutdown immediate

1.3 sql>startup mount

1.4 sql>host nid target=sys/zhuzerp dbname=ORCLERP

1.5 Change database ID and database name zhuzerp to ORCLERP? (Y/[N]) =>y

1.6 sql>shutdown immediate

sql>startup mount

sql>alter system set db_name=ORCLERP scope=spfile;

sql>shutdown immediate

1.7 重新創(chuàng)建sys的password文件

host orapwd file=D:\Oracle\database\PWDzhuzerp.ora password=orclerp entries=5

1.8 sql>startup mount

sql>alter database open resetlogs;

1.9 sql>select dbid,name from v$database;

2.更改instance_name

2.1 net stop oracleservicezhuzerp

2.2 oradim -delete -sid zhuzerp

2.3 orapwd file=D:\Oracle\database\PWDORCLERP.ora password=orclerp entries=5

2.4 修改文件中的db_name和instance_name

D:\Oracle\admin\test\pfile\init.ora.8252009155525

2.5 oradim -new -sid ORCLERP -intpwd orclerp -startmode a -pfile D:\Oracle\admin\zhuzerp\pfile\init.ora

2.6 set oracle_sid=orclerp

2.7 sqlplus "sys/orclerp as sysdba"

2.8 create spfile from pfile='D:\Oracle\admin\zhuzerp\pfile\init.ora';

2.9 lsnrctl reload

相信大家通過上文的學(xué)習(xí)之后，對(duì)于如何修改數(shù)據(jù)庫(kù)名已經(jīng)有所了解，以后在工作中遇到類似問題就能夠輕松解決了，希望上文中涉及到內(nèi)容對(duì)待大家能夠有所幫助。