--创建表空间
create tablespace mytbs logging datafile '/u02/oradata/tbsdata/mytbs\mytbs01.dbf' size 400m AUTOEXTEND ON NEXT 10M MAXSIZE 20480m;
alter tablespace mytbs add datafile '/u02/oradata/tbsdata/mytbs\mytbs02.dbf' size 400m AUTOEXTEND ON NEXT 10M MAXSIZE 20480m;
alter tablespace mytbs add datafile '/u02/oradata/tbsdata/mytbs\mytbs03.dbf' size 400m AUTOEXTEND ON NEXT 10M MAXSIZE 20480m;
alter tablespace mytbs add datafile '/u02/oradata/tbsdata/mytbs\mytbs04.dbf' size 400m AUTOEXTEND ON NEXT 10M MAXSIZE 20480m;
alter tablespace mytbs add datafile '/u02/oradata/tbsdata/mytbs\mytbs05.dbf' size 400m AUTOEXTEND ON NEXT 10M MAXSIZE 20480m;
create tablespace mytbs logging
datafile '/home/oracle/data/tbs/mydata1.dbf' SIZE 5120M,
'/home/oracle/data/tbs/mydata2.dbf' SIZE 5120M,
'/home/oracle/data/tbs/mydata3.dbf' SIZE 5120M,
'/home/oracle/data/tbs/mydata4.dbf' SIZE 5120M,
'/home/oracle/data/tbs/mydata5.dbf' SIZE 5120M
AUTOEXTEND ON NEXT 100M MAXSIZE 10240M;
--创建用户
drop user qdcrms cascade;
create user qdcrms identified by qdcrms default tablespace CPTBS;
--授权
grant all privileges to qdcrms;
grant connect to qdcrms;
grant dba to qdcrms;
revoke unlimited tablespace from qdcrms;
--表空间配额
alter user qdcrms quota 0 on users;
alter user qdcrms quota unlimited on CPTBS;
-- Grant/Revoke object privileges
grant execute, read on directory ORACLE_HOME to qdcrms with grant option;
grant connect to qdcrms with admin option;
grant dba to qdcrms with admin option;
grant exp_full_database to qdcrms with admin option;
grant imp_full_database to qdcrms with admin option;
grant gather_system_statistics to qdcrms with admin option;
create directory dpdata as '/home/oracle/dpdata';
grant read,write on directory dpdata to flsdata;
--执行导入
—-注意:参数remap_shchema,remap_tablespace要写正确,否则会出ORA-31655错误
impdp \"sys/amarsoft as sysdba\" directory=DB_DUMP_FILE dumpfile=AIMSDATA20150526.DMP remap_schema=aimsdata:aims remap_tablespace=aims:mytbs
impdp \"sys/oracle as sysdba\" directory=DATA_PUMP_DIR dumpfile=irms.dmp
--查看表空间
select username,default_tablespace,temporary_tablespace from user_users;
create tablespace mytbs logging datafile '/u02/oradata/tbsdata/mytbs\mytbs01.dbf' size 400m AUTOEXTEND ON NEXT 10M MAXSIZE 20480m;
alter tablespace mytbs add datafile '/u02/oradata/tbsdata/mytbs\mytbs02.dbf' size 400m AUTOEXTEND ON NEXT 10M MAXSIZE 20480m;
alter tablespace mytbs add datafile '/u02/oradata/tbsdata/mytbs\mytbs03.dbf' size 400m AUTOEXTEND ON NEXT 10M MAXSIZE 20480m;
alter tablespace mytbs add datafile '/u02/oradata/tbsdata/mytbs\mytbs04.dbf' size 400m AUTOEXTEND ON NEXT 10M MAXSIZE 20480m;
alter tablespace mytbs add datafile '/u02/oradata/tbsdata/mytbs\mytbs05.dbf' size 400m AUTOEXTEND ON NEXT 10M MAXSIZE 20480m;
create tablespace mytbs logging
datafile '/home/oracle/data/tbs/mydata1.dbf' SIZE 5120M,
'/home/oracle/data/tbs/mydata2.dbf' SIZE 5120M,
'/home/oracle/data/tbs/mydata3.dbf' SIZE 5120M,
'/home/oracle/data/tbs/mydata4.dbf' SIZE 5120M,
'/home/oracle/data/tbs/mydata5.dbf' SIZE 5120M
AUTOEXTEND ON NEXT 100M MAXSIZE 10240M;
--创建用户
drop user qdcrms cascade;
create user qdcrms identified by qdcrms default tablespace CPTBS;
--授权
grant all privileges to qdcrms;
grant connect to qdcrms;
grant dba to qdcrms;
revoke unlimited tablespace from qdcrms;
--表空间配额
alter user qdcrms quota 0 on users;
alter user qdcrms quota unlimited on CPTBS;
-- Grant/Revoke object privileges
grant execute, read on directory ORACLE_HOME to qdcrms with grant option;
grant connect to qdcrms with admin option;
grant dba to qdcrms with admin option;
grant exp_full_database to qdcrms with admin option;
grant imp_full_database to qdcrms with admin option;
grant gather_system_statistics to qdcrms with admin option;
create directory dpdata as '/home/oracle/dpdata';
grant read,write on directory dpdata to flsdata;
--执行导入
—-注意:参数remap_shchema,remap_tablespace要写正确,否则会出ORA-31655错误
impdp \"sys/amarsoft as sysdba\" directory=DB_DUMP_FILE dumpfile=AIMSDATA20150526.DMP remap_schema=aimsdata:aims remap_tablespace=aims:mytbs
impdp \"sys/oracle as sysdba\" directory=DATA_PUMP_DIR dumpfile=irms.dmp
--查看表空间
select username,default_tablespace,temporary_tablespace from user_users;