複製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;
可以使用腳本執行查詢的結果,這樣就可以批量處理.