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为某个用户，即删除某个用户下的所有空段。