创建表空间、用户、授权

--创建表空间
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;
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u013914393/article/details/51544000
个人分类: Oracle
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭