複製數據庫步驟

複製DB步驟:

1.     Exp源庫資料(準備源庫):

select * from nls_database_parameters where parameter='NLS_CHARACTERSET'(檢查語言集)

export ORACLE_SID=CMS722

export NLS_LANG=American_America.ZHT16BIG5

exp cms/cms file=/oradata/ cms.dmp rows=n log=/oradata/cms_exp.logcompress=n(rows=n代表不導出數據,否則就帶)

2.     創建Tablespace:

CREATE TABLESPACE CMS_D DATAFILE '/oradata/ cms230_exp /CMS_D01.DBF'SIZE 100M REUSE AUTOEXTEND ON NEXT 200M MAXSIZE 8000M EXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO;--for table

CREATE TABLESPACE CMS_X DATAFILE '/oradata/ cms230_exp /CMS_X01.DBF'SIZE 100M REUSE AUTOEXTEND ON NEXT 200M MAXSIZE 8000M EXTENT MANAGEMENT LOCALSEGMENT SPACE MANAGEMENT AUTO;--for index

3.     創建帳號user(cms):

CREATE USER cms

IDENTIFIED BY VALUES 'cms'

DEFAULT TABLESPACE CMS_D

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

GRANT CONNECT TO cms;

ALTER USER cms DEFAULT ROLE ALL;

GRANT CREATE TABLE TO cms;

GRANT CREATE VIEW TO cms;

GRANT CREATE JOB TO cms;

GRANT CREATE TRIGGER TO cms;

GRANT CREATE PROCEDURE TO cms;

GRANT CREATE DATABASE LINK TO cms;

GRANT CREATE SYNONYM TO cms;

創建完畢後需要手動重設user密碼

對於Oracle11g:

密碼永不過期:

SELECT *　FROM dba_profiles　WHERE PROFILE = 'DEFAULT' AND resource_name= 'PASSWORD_LIFE_TIME';

ALTER PROFILE DEFAULT LIMITPASSWORD_LIFE_TIME UNLIMITED;

密碼區分大小寫：

SELECT *　FROM v$parameter　WHERE NAME LIKE '%case%';

ALTER SYSTEM SET sec_case_sensitive_logon = FALSE;

4.     Imp源庫:

export ORACLE_SID=CMS230

export NLS_LANG=American_America.ZHT16BIG5

imp cms/cms buffer=1048576 file=/oradata/cms230_exp/cms.dmp  ignore=y commit=y full=ylog=/oradata/cms230_exp/cms_imp.log

對於同一DB中需要Copy Schema創建新的帳號,需要修改table和index的tablespace,具體如下:

1.     修改表的空間

Alter table table_name move tablespace tablespace_name;

查詢當前用戶下的所有表:

Select ‘alter table ‘||table_name||’ move tablespace tablespacename;’

From user_all_tables;

2.     修改表的索引的空間

Alter index index_name rebuild tablespace tablespacename;

查詢當前用戶下的所有索引:

Select ‘alter index ‘||index_name||’ rebuild tablespace tablespacename;’from user_indexes;

可以使用腳本執行查詢的結果,這樣就可以批量處理.