Oracle impdp只导入元数据占用大量空间以及如何删除空段

Oracle impdp只导入元数据占用大量空间以及如何删除空段

从某个库导出整个库的元数据,在另外一个新库导入元数据,发现导入时间久并且占用了大量空间。

有好几张的空表甚至能占用十几二十G大小的空间,看了一下都是按天分区的间隔分区表,每个分区会有8M的大小。

通过在源库使用dbms_metadata.get_ddl包查看某张表的ddl(不要用PLSQL Developer工具看)可以发现原因,

1、表的ddl中带了子句“SEGMENT CREATION IMMEDIATE”,即会立刻分配一个段给到表(或者分区)

2、表的ddl中分区表的每个分区的storage的初始化大小initial为8388608,即8k

导致在新库导入元数据的时候,也会分配空间,并且是每个分区都是8k。

那么现在又有另外的两个问题:

1、数据库参数deferred_segment_creation为默认值true,表示创建空表的时候不会分配空间,为什么ddl中会带“SEGMENT CREATION IMMEDIATE”

2、默认的ASSM管理表空间中,默认情况下,普通表的段里边的区大小是0~15号都是64k大小,而分区表是否每个分区则是8M。

先解答这两个疑问,

第一个问题很好解答,只要表里边曾经存在过数据,那么ddl的属性会自动变成“SEGMENT CREATION IMMEDIATE”,无论deferred_segment_creation是不是true

测试如下(需要使用非SYS用户):

16:27:59 SYS@xxxxdb(79)> show parameter deferred_segment_creation

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------
deferred_segment_creation            boolean                           TRUE
16:28:02 SYS@xxxxdb(79)> conn zkm/zkm
Connected.
16:28:50 ZKM@xxxxdb(79)> create table test(id int,name varchar2(20));

Table created.

Elapsed: 00:00:00.02


16:29:07 SYS@szceb2(81)> select dbms_metadata.get_ddl('TABLE','TEST','ZKM') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST','ZKM')
--------------------------------------------------------------------------------

  CREATE TABLE "ZKM"."TEST"
   (    "ID" NUMBER(*,0),
        "NAME" VARCHAR2(20)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"


16:29:46 ZKM@xxxxdb(79)> insert into test values(1,'zkm');

1 row created.

Elapsed: 00:00:00.01

16:29:08 SYS@szceb2(81)> select dbms_metadata.get_ddl('TABLE','TEST','ZKM') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST','ZKM')
--------------------------------------------------------------------------------

  CREATE TABLE "ZKM"."TEST"
   (    "ID" NUMBER(*,0),
        "NAME" VARCHAR2(20)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXT
ENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

第二个问题测试,

16:49:22 ZKM@xxxxdb(79)> CREATE TABLE test
16:49:35   2   (    "USERNAME" VARCHAR2(30) NOT NULL ENABLE,
16:49:35   3        "USER_ID" NUMBER NOT NULL ENABLE,
16:49:35   4        "ACCOUNT_STATUS" VARCHAR2(32) NOT NULL ENABLE,
16:49:35   5        "LOCK_DATE" DATE,
16:49:35   6        "EXPIRY_DATE" DATE,
16:49:35   7        "DEFAULT_TABLESPACE" VARCHAR2(30) NOT NULL ENABLE,
16:49:35   8        "TEMPORARY_TABLESPACE" VARCHAR2(30) NOT NULL ENABLE,
16:49:35   9        "CREATED" DATE NOT NULL ENABLE,
16:49:35  10        "INITIAL_RSRC_CONSUMER_GROUP" VARCHAR2(30),
16:49:35  11        "EXTERNAL_NAME" VARCHAR2(4000),
16:49:35  12        "CREATE_TIME" DATE
16:49:35  13   )
16:49:35  14  PARTITION BY RANGE ("CREATE_TIME") INTERVAL(NUMTODSINTERVAL(1,'DAY'))
16:49:36  15   (PARTITION "P_INIT"  VALUES LESS THAN (TO_DATE('2024-07-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS')));

Table created.

Elapsed: 00:00:00.04
16:49:37 ZKM@xxxxdb(79)> select extent_id,blocks,sys.format_bytes(bytes) sizes from user_extents where segment_name='TEST' order by extent_id;

no rows selected

Elapsed: 00:00:00.02
16:49:44 ZKM@xxxxdb(79)> INSERT INTO TEST SELECT T.*,SYSDATE FROM USER_USERS T WHERE ROWNUM=1;

1 row created.

Elapsed: 00:00:00.06
16:50:00 ZKM@xxxxdb(79)> select extent_id,blocks,sys.format_bytes(bytes) sizes from user_extents where segment_name='TEST' order by extent_id;

 EXTENT_ID     BLOCKS   SIZES
---------- ---------- ----------
         0       1024      8 MiB

Elapsed: 00:00:00.04

那么,impdp如何导入元数据的时候不要常见段呢?可以使用transform=segment_attributes:N,忽略“SEGMENT CREATION IMMEDIATE”,

eg:

impdp \' / as sysdba \' directory=dir20240704 dumpfile=metadata_DB.dmp logfile=imp_metadata_DB.log cluster=n schemas=user1,user2,user3 exclude=statistics,db_link table_exists_action=skip content=metadata_only transform=segment_attributes:N

对于已经分配了空间的空闲段,可以使用如下脚本来进行回收空间,

BEGIN
DBMS_SPACE_ADMIN.drop_empty_segments (
schema_name => 'schema',
table_name => 'table_name',
partition_name => 'partition_name');
END;
/

如果上边的参数为null,表示通配符,所有的空段都会被删除,比如直接执行下边的语句,会删除整个库里边的空段。

https://www.cnblogs.com/PiscesCanon/p/18290344

BEGIN
DBMS_SPACE_ADMIN.drop_empty_segments (
schema_name => '',
table_name => '',
partition_name => '');
END;
/

也可以只指定schema_name为某个用户,即删除某个用户下的所有空段。

最近更新

  1. Log4j2原理及应用详解(十三)

    2024-07-11 01:28:05       0 阅读
  2. web学习笔记(八十二)uniapp

    2024-07-11 01:28:05       0 阅读
  3. git clone/push报错:HTTP Basic: Access denied

    2024-07-11 01:28:05       0 阅读
  4. 高等数学用到的初等数学

    2024-07-11 01:28:05       0 阅读
  5. JVM 在什么情况下会触发垃圾回收?

    2024-07-11 01:28:05       0 阅读
  6. Dubbo 的本地伪装

    2024-07-11 01:28:05       0 阅读

热门阅读

  1. NLP - 基于bert预训练模型的文本多分类示例

    2024-07-11 01:28:05       6 阅读
  2. 二刷算法训练营Day57 | 动态规划(17/17)

    2024-07-11 01:28:05       6 阅读
  3. 自定义业务非受检异常

    2024-07-11 01:28:05       7 阅读
  4. GPT-5 一年半后发布?对此你有何期待?

    2024-07-11 01:28:05       6 阅读
  5. DSC主备归档报错

    2024-07-11 01:28:05       8 阅读
  6. DangerWind-RPC-framework---三、服务端下机

    2024-07-11 01:28:05       4 阅读
  7. spring管理bean源码解析

    2024-07-11 01:28:05       6 阅读
  8. a+=1和a=a+1的区别

    2024-07-11 01:28:05       5 阅读
  9. threadLocal

    2024-07-11 01:28:05       6 阅读
  10. GESP C++ 三级真题(2023年9月)T2 进制判断

    2024-07-11 01:28:05       5 阅读