oracle数据新建过程

新创建数据库,需要创建表空间，创建user，从旧库迁移一些数据。

1、创建表空间
#!/bin/sh

. ~/.bash_profile

ts_src=USER_TS

ts_file1=/data1/oradata/easyliao_${ym}_001.dbf
ts_file2=/data1/oradata/easyliao_${ym}_002.dbf

sqlplus -s system/123456 << EOF set hea off --alter system set db_16k_cache_size=32m; --alter tablespace $ts_src rename to $ts_dst; --CREATE bigfile TABLESPACE $ts_src DATAFILE '$ts_file1' SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED; -- CREATE TABLESPACE $ts_src DATAFILE '$ts_file1' SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED BLOCKSIZE 16K; alter TABLESPACE $ts_src add DATAFILE '$ts_file2' SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED; quit; EOF 2、创建user ts_src=USER_TS sqlplus -s '/as sysdba' << EOF set hea off create user authuser identified by development default tablespace $ts_src; grant dba,resource,connect to user; create user devuser identified by development default tablespace $ts_src; grant dba,resource,connect to devuser; select USERNAME,default_tablespace from dba_users where account_status='OPEN'; EOF 3、create dir . ~/.bash_profile today=$(date +%F-%H%M) sqlplus -s '/as sysdba' << EOF set hea off --set linesize 200 col OWNER for a20 col DIRECTORY_name for a20 col DIRECTORY_PATH for a50 create or replace directory expdp_dir1 as '/home/oracle/backup'; grant read,write on directory expdp_dir1 to devuser; grant read,write on directory expdp_dir1 to authuser; grant read,write on directory expdp_dir1 to public; select * from dba_directories; EOF 4、用expdp备份oldb . ~/.bash_profile user=test today=$(date +%F-%H%M) bakfile=expdp_${user}_${today}.dmp logfile=expdp_${user}_${today}.log expdp ${user}/123456 dumpfile=$bakfile DIRECTORY=expdp_dir1 PARALLEL=2 logfile=$logfile COMPRESSION=all \ tables=USER,\ CID 5、scp -P22 root@olddb:/home/oracle/1.zip . 6、impdb导入newdb . ~/.bash_profile today=$(date +%F) bakfile=11.dmp logfile=impdp_${today}.log cd /home/oracle/backup impdp devuser/111111 directory=expdp_dir1 dumpfile=$bakfile logfile=$logfile remap_schema=aaa:dev TABLE_EXISTS_ACTION=REPLACE remap_tablespace=TS1:TS2 transform=segment_attributes:n 7、ORA-39112: Dependent object type COMMENT skipped 利用参数 TRANSFORM=segment_attributes:n 去掉表空间和存储子句，加上这个参数后，remap_tablesapce参数就会失效，就会倒进用户默认的表空间，

相关日志