impdp不包含空闲block

最新推荐文章于 2024-11-27 16:44:45 发布

mercenarylin

最新推荐文章于 2024-11-27 16:44:45 发布

阅读量677

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle

本文链接：https://blog.youkuaiyun.com/mercenarylin/article/details/8767205

oracle 专栏收录该内容

48 篇文章

订阅专栏

本文介绍了在Oracle数据库中如何创建表、删除数据、查看表空间占用情况，并通过导出和导入数据来调整表空间大小的过程。文章还展示了如何利用Oracle的统计信息功能优化表空间使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

scott@ORCL> create table t as select * from dba_objects;

表已创建。

scott@ORCL> select extents,blocks from dba_segments where segment_name ='T' and owner='SCOTT';

   EXTENTS     BLOCKS
---------- ----------
        25       1280
        
scott@ORCL> delete from t where object_id not in ('1','2');

已删除81723行。

scott@ORCL> commit;

提交完成。

scott@ORCL> select extents,blocks from dba_segments where segment_name ='T' and owner='SCOTT';

   EXTENTS     BLOCKS
---------- ----------
        25       1280
        
scott@ORCL> col owner for a15;
scott@ORCL> col object_name for a25;
--可以看出null不包含在not in 中
scott@ORCL> select owner,object_name,object_id  from t;  

OWNER           OBJECT_NAME                OBJECT_ID
--------------- ------------------------- ----------
SYS             C_OBJ#                             2
FJNX            NCDW.11.80.47
SCOTT           DBLINK1.11.80.47
SCOTT           DBLINK2.11.80.47

scott@ORCL> select segment_name,bytes from dba_segments where  segment_name ='T' and owner='SCOTT';

SEGMENT_NAME                           BYTES
------------------------------------- ----------
T                                      10485760
--收集统计信息之后，bytes不会变少
scott@ORCL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL 过程已成功完成。

scott@ORCL> select segment_name,bytes from dba_segments where  segment_name ='T' and owner='SCOTT';

SEGMENT_NAME     BYTES
---------------- ----------
T                10485760

C:\Users\Administrator>expdp scott/tiger tables=T directory=dump1 dumpfile=t.dmp;

Export: Release 11.2.0.1.0 - Production on 星期日 4月 7 13:18:37 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=T directory=dump1 dumpfile=t.dmp;
正在使用 BLOCKS 方法进行估计...                   ------按照blocks计算，所以是否有统计信息没关系
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 10 MB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . 导出了 "SCOTT"."T"                                 10.97 KB       4 行
已成功加载/卸载了主表 "SCOTT"."SYS_EXPORT_TABLE_01"
******************************************************************************
SCOTT.SYS_EXPORT_TABLE_01 的转储文件集为:
  D:\DUMP1\T.DMP;
作业 "SCOTT"."SYS_EXPORT_TABLE_01" 已于 13:19:11 成功完成
*/

C:\Users\Administrator>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on 星期日 4月 7 13:20:55 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

scott@ORCL> drop table t purge;

表已删除。

scott@ORCL> exit;

C:\Users\Administrator>impdp scott/tiger directory=dump1 dumpfile=t.dmp;

Import: Release 11.2.0.1.0 - Production on 星期日 4月 7 13:21:39 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "SCOTT"."SYS_IMPORT_FULL_01"
启动 "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=dump1 dumpfile=t.dmp;
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "SCOTT"."T"                                 10.97 KB       4 行
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
作业 "SCOTT"."SYS_IMPORT_FULL_01" 已于 13:21:47 成功完成
*/
C:\Users\Administrator>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on 星期日 4月 7 13:21:57 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

---重新导入之后，只导入数据，而不导入空闲的block
scott@ORCL> select segment_name,bytes,blocks from dba_segments where  segment_name ='T' and owner='SCOTT';

SEGMENT_NAME              BYTES     BLOCKS
-------------------- ---------- ----------
T                         65536          8
---之所以四行记录分配了8个block，是因为oracle最小空间分配单位：extent
scott@ORCL> select segment_name,bytes,blocks from dba_extents where segment_name='T' and owner='SCOTT';

SEGMENT_NAME              BYTES     BLOCKS
-------------------- ---------- ----------
T                         65536          8