ITPub博客

首页 > 数据库 > Oracle > oracle-通过expdp&impdp 移动用户的表空间和修改用户owner

oracle-通过expdp&impdp 移动用户的表空间和修改用户owner

原创 Oracle 作者:zuoqi_71 时间:2014-03-24 15:21:54 0 删除 编辑


---1、现状:scott 使用了system tablespace,导致system tablespace空间使用异常
---2、解决:使用move或expdb 将scott数据从 system tbs--> users tbs


---3、select now
set lines 200
set pagesize 200
set long 8000


SELECT DBMS_METADATA.GET_DDL('TABLE','T1','SCOTT')  FROM DUAL;

  CREATE TABLE "SCOTT"."T1"
   (    "NO" VARCHAR2(2000),
        "CDATE" DATE,
        "MYBLOB" BLOB,
        "MYCLOB" CLOB
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAU
LT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"
 LOB ("MYBLOB") STORE AS BASICFILE (
  TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
 LOB ("MYCLOB") STORE AS BASICFILE (
  TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT))


col OWNER format a10
col TABLESPACE_NAME format a20
col TABLE_OWNER_NAME format a10
col TABLE_NAME format a10


SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME
 FROM DBA_INDEXES
 WHERE TABLE_NAME IN ('T1');


OWNER      INDEX_NAME                     INDEX_TYPE  TABLE_OWNER TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ----------  ----------- ---------- ---------------
SCOTT      SYS_IL0000066734C00003$$       LOB         SCOTT       T1         SYSTEM
SCOTT      SYS_IL0000066734C00004$$       LOB         SCOTT       T1         SYSTEM
SCOTT      T1_IDX                         NORMAL      SCOTT       T1         SYSTEM


select owner,tablespace_name,sum(bytes)/1024/1024/1024 sizeM
 from dba_segments
 where owner ='SCOTT'
 group by owner,tablespace_name;


OWNER      TABLESPACE_NAME           SIZEM
---------- -------------------- ----------
SCOTT      SYSTEM               .021728516


---4、move tablespace from system to users

status  type       extent space  allocation tablespace_name      tablespace_size(M) used_size(M) free_size(M) used_percentage(%)
------- ---------- ------ ------ ---------- -------------------- ------------------ ------------ ------------ ------------------
ONLINE  PERMANENT  LOCAL  AUTO   SYSTEM     SYSAUX                              530          497           33              93.77
ONLINE  PERMANENT  LOCAL  MANUAL SYSTEM     SYSTEM                             1024          594          430              58.01
ONLINE  PERMANENT  LOCAL  AUTO   SYSTEM     USERS                                48            2           46               4.17
ONLINE  PERMANENT  LOCAL  AUTO   SYSTEM     USER2                                50            1           49                  2
ONLINE  PERMANENT  LOCAL  AUTO   SYSTEM     USER_DATA01                         100            1           99                  1
ONLINE  TEMPORARY  LOCAL  MANUAL UNIFORM    TEMP                                 68           67            1              98.53
ONLINE  TEMPORARY  LOCAL  MANUAL UNIFORM    USER_TEMP01                         100            0          100                  0
ONLINE  UNDO       LOCAL  MANUAL SYSTEM     UNDOTBS1                            335           13          322               3.88


alter table scott.t1 move tablespace USERS;
alter table scott.t1 move lob(MYBLOB,MYCLOB) store as (tablespace USERS) ;
alter index scott.T1_IDX rebuild  tablespace USERS online;


status  type       extent space  allocation tablespace_name      tablespace_size(M) used_size(M) free_size(M) used_percentage(%)
------- ---------- ------ ------ ---------- -------------------- ------------------ ------------ ------------ ------------------
ONLINE  PERMANENT  LOCAL  AUTO   SYSTEM     SYSAUX                              530          497           33              93.77
ONLINE  PERMANENT  LOCAL  MANUAL SYSTEM     SYSTEM                             1024          572          452              55.86
ONLINE  PERMANENT  LOCAL  AUTO   SYSTEM     USERS                                48           24           24                 50
ONLINE  PERMANENT  LOCAL  AUTO   SYSTEM     USER2                                50            1           49                  2
ONLINE  PERMANENT  LOCAL  AUTO   SYSTEM     USER_DATA01                         100            1           99                  1
ONLINE  TEMPORARY  LOCAL  MANUAL UNIFORM    TEMP                                 68           67            1              98.53
ONLINE  TEMPORARY  LOCAL  MANUAL UNIFORM    USER_TEMP01                         100            0          100                  0
ONLINE  UNDO       LOCAL  MANUAL SYSTEM     UNDOTBS1                            335           13          322               3.88


col OWNER format a10
col TABLESPACE_NAME format a20
col TABLE_OWNER_NAME format a10
col TABLE_NAME format a10

SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME
 FROM DBA_INDEXES
 WHERE TABLE_NAME IN ('T1');


OWNER      INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ --------------------------- ------------------------------ ---------- --------------------
SCOTT      T1_IDX                         NORMAL                      SCOTT                          T1         USERS
SCOTT      SYS_IL0000066734C00003$$       LOB                         SCOTT                          T1         USERS
SCOTT      SYS_IL0000066734C00004$$       LOB                         SCOTT                          T1         USERS


select owner,tablespace_name,sum(bytes)/1024/1024/1024 sizeM
 from dba_segments
 where owner ='SCOTT'
 group by owner,tablespace_name;

OWNER      TABLESPACE_NAME           SIZEM
---------- -------------------- ----------
SCOTT      USERS                .021728516

---5、expdp & impdp move tablespace from system to users

$mkdir -p /oracle/app/oracle/oradata/exp_dir
create or replace directory wapdata_expdb as '/oracle/app/oracle/oradata/exp_dir';

grant read,write on directory wapdata_expdb to scott;

 


 

col DIRECTORY_PATH format a80
select * from dba_directories;

OWNER      DIRECTORY_NAME                 DIRECTORY_PATH
---------- ------------------------------ --------------------------------------------------------------------------------
SYS        ORACLE_OCM_CONFIG_DIR          /oracle/app/oracle/product/11.2.0/ccr/state
SYS        DATA_PUMP_DIR                  /oracle/app/oracle/admin/ko16/dpdump/
SYS        XMLDIR                         /oracle/app/oracle/product/11.2.0/rdbms/xml
SYS        WAPDATA_EXPDB                  /oracle/app/oracle/oradata/exp_dir

 

expdp scott/tiger directory=WAPDATA_EXPDB full=y logfile=ko16_full2.log dumpfile=ko16_full2.dmp ;
 
impdp scott/tiger directory=WAPDATA_EXPDB dumpfile=ko16_full2.dmp SCHEMAS=scott TABLE_EXISTS_ACTION=replace remap_tablespace=system:users

Import: Release 11.2.0.1.0 - Production on Mon Mar 24 14:20:41 2014                                                                                                             
                                                                                                                                                                                 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.                                                                                                   
                                                                                                                                                                                
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production                                                                                      
With the Partitioning, OLAP, Data Mining and Real Application Testing options                                                                                                   
Master table "SCOTT"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded                                                                                                        
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01":  scott/******** directory=WAPDATA_EXPDB dumpfile=ko16_full2.dmp SCHEMAS=scott TABLE_EXISTS_ACTION=replace remap_tablespace=system:users
Processing object type DATABASE_EXPORT/SCHEMA/USER                                                                                                                              
ORA-31684: Object type USER:"SCOTT" already exists                                                                                                                              
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT                                                                                                                
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT                                                                                                                        
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE                                                                                                                      
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA                                                                                                                    
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE                                                                                                                       
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA                                                                                                                  
. . imported "SCOTT"."T1"                                8.720 MB  262852 rows                                                                                                  
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX                                                                                                                 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS                                                                                           
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE                                                                                                               
ORA-31684: Object type PROCEDURE:"SCOTT"."AAA" already exists                                                                                                                   
ORA-31684: Object type PROCEDURE:"SCOTT"."ABCD" already exists                                                                                                                  
ORA-31684: Object type PROCEDURE:"SCOTT"."TEST" already exists                                                                                                                  
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE                                                                                                         
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."TEST" created with compilation warnings                                                                                         
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."ABCD" created with compilation warnings                                                                                         
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT"."AAA" created with compilation warnings                                                                                          
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS                                                                                                 
Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 7 error(s) at 14:20:49                                                                                                        

 
col OWNER format a10
col TABLESPACE_NAME format a20
col TABLE_OWNER_NAME format a10
col TABLE_NAME format a10

SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME
 FROM DBA_INDEXES
 WHERE TABLE_NAME IN ('T1');

OWNER      INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ --------------------------- ------------------------------ ---------- --------------------
SCOTT      SYS_IL0000068108C00003$$       LOB                         SCOTT                          T1         USERS
SCOTT      SYS_IL0000068108C00004$$       LOB                         SCOTT                          T1         USERS
SCOTT      T1_IDX                         NORMAL                      SCOTT                          T1         USERS


--6 expdp & impdp  resotre table to other user

 

col OWNER format a10
col TABLESPACE_NAME format a20
col TABLE_OWNER_NAME format a10
col TABLE_NAME format a10

SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLESPACE_NAME
 FROM DBA_INDEXES
 WHERE TABLE_NAME IN ('T1');

OWNER      INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ --------------------------- ------------------------------ ---------- --------------------
SCOTT      SYS_IL0000068108C00003$$       LOB                         SCOTT                          T1         USERS
SCOTT      SYS_IL0000068108C00004$$       LOB                         SCOTT                          T1         USERS
SCOTT      T1_IDX                         NORMAL                      SCOTT                          T1         USERS


expdp scott/tiger directory=WAPDATA_EXPDB full=y logfile=ko16_full3.log dumpfile=ko16_full3.dmp ; 

impdp scott/tiger DIRECTORY=WAPDATA_EXPDB DUMPFILE=ko16_full3.dmp TABLES=scott.t1 REMAP_SCHEMA=scott:system;

Import: Release 11.2.0.1.0 - Production on Mon Mar 24 14:36:39 2014                                                                              
                                                                                                                                                 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.                                                                    
                                                                                                                                                 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production                                                       
With the Partitioning, OLAP, Data Mining and Real Application Testing options                                                                    
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded                                                                          
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** DIRECTORY=WAPDATA_EXPDB DUMPFILE=ko16_full3.dmp TABLES=scott.t1 REMAP_SCHEMA=scott:system
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE                                                                                        
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA                                                                                   
. . imported "SYSTEM"."T1"                               8.720 MB  262852 rows                                                                   
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX                                                                                  
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS                                                            
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS                                                                  
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 14:36:50                                                                             

 

 

OWNER      INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ --------------------------- ------------------------------ ---------- --------------------
SCOTT      SYS_IL0000068108C00003$$       LOB                         SCOTT                          T1         USERS
SCOTT      SYS_IL0000068108C00004$$       LOB                         SCOTT                          T1         USERS
SCOTT      T1_IDX                         NORMAL                      SCOTT                          T1         USERS
SYSTEM     SYS_IL0000068164C00003$$       LOB                         SYSTEM                         T1         USERS
SYSTEM     SYS_IL0000068164C00004$$       LOB                         SYSTEM                         T1         USERS
SYSTEM     T1_IDX                         NORMAL                      SYSTEM                         T1         USERS

6 rows selected.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22193071/viewspace-1128351/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle-hot backup
请登录后发表评论 登录
全部评论

注册时间:2014-02-26

  • 博文量
    48
  • 访问量
    143835