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

最新推荐文章于 2024-06-01 10:35:00 发布

cuicang5610

最新推荐文章于 2024-06-01 10:35:00 发布

阅读量620

点赞数

文章标签：数据库

---1、现状：scott 使用了system tablespace，导致system tablespace空间使用异常
---2、解决：使用move或expdb 将scott数据从 system tbs--&gt 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');

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');

--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');

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22193071/viewspace-1128351/

cuicang5610

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle-通过expdp&impdp 移动用户的表空间和修改用户owner

---1、现状：scott 使用了system tablespace，导致system tablespace空间使用异常---2、解决：使用move或expdb 将scott数据从 system tbs--&amp...
复制链接

扫一扫