表空占用情况

SELECT a.tablespace_name, round(a.bytes / 1024 / 1024) "Sum MB",
       round((a.bytes - b.bytes) / 1024 / 1024) "used MB",
       round(b.bytes / 1024 / 1024) "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
  FROM (SELECT tablespace_name, SUM(bytes) bytes
           FROM dba_data_files
          GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest
           FROM dba_free_space
          GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+)
 ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;



select t.owner, t.segment_name, sum(t.BYTES) / 1024 / 1024 as size_M
  from dba_segments t
 where t.tablespace_name = ''
 group by t.owner, t.segment_name
 order by 3 desc;

内在地址占用

SELECT DECODE(T.GRANTYPE,
              1,
              'shared pool',
              2,
              'large pool',
              3,
              'java pool',
              4,
              'streams pool',
              5,
              'free',
              6,
              'DEFAULT buffer cache',
              7,
              'KEEP buffer cache',
              8,
              'RECYCLE buffer cache',
              9,
              'DEFAULT 2K buffer cache',
              1,
              'DEFAULT 4K buffer cache',
              1,
              'DEFAULT 8K buffer cache',
              T.GRANTYPE),
       T.granstate,
       t.baseaddr addr,
       t.gransize
  FROM X$KSMGE T
  order by 3;
------------------------------------------------------------
SQL> set pages 1000
SQL> /

DECODE(T.GRANTYPE,1,'SHAREDPOOL',2,'LARG GRANSTATE        ADDR               GRANSIZE
---------------------------------------- ---------------- ---------------- ----------
0                                        INVALID          000007FF00000000   16777216
0                                        INVALID          000007FF01000000   16777216
0                                        INVALID          000007FF02000000   16777216
0                                        INVALID          000007FF03000000   16777216
0                                        INVALID          000007FF04000000   16777216
0                                        INVALID          000007FF05000000   16777216
0                                        INVALID          000007FF06000000   16777216
0                                        INVALID          000007FF07000000   16777216
0                                        INVALID          000007FF08000000   16777216
0                                        INVALID          000007FF09000000   16777216
0                                        INVALID          000007FF0A000000   16777216
0                                        INVALID          000007FF0B000000   16777216
0                                        INVALID          000007FF0C000000   16777216
0                                        INVALID          000007FF0D000000   16777216
0                                        INVALID          000007FF0E000000   16777216
0                                        INVALID          000007FF0F000000   16777216
0                                        INVALID          000007FF10000000   16777216
0                                        INVALID          000007FF11000000   16777216
0                                        INVALID          000007FF12000000   16777216
0                                        INVALID          000007FF13000000   16777216
0                                        INVALID          000007FF14000000   16777216
0                                        INVALID          000007FF15000000   16777216
0                                        INVALID          000007FF16000000   16777216
0                                        INVALID          000007FF17000000   16777216
0                                        INVALID          000007FF18000000   16777216
0                                        INVALID          000007FF19000000   16777216
0                                        INVALID          000007FF1A000000   16777216
0                                        INVALID          000007FF1B000000   16777216
0                                        INVALID          000007FF1C000000   16777216
0                                        INVALID          000007FF1D000000   16777216
0                                        INVALID          000007FF1E000000   16777216
0                                        INVALID          000007FF1F000000   16777216
0                                        INVALID          000007FF20000000   16777216
0                                        INVALID          000007FF21000000   16777216
0                                        INVALID          000007FF22000000   16777216
0                                        INVALID          000007FF23000000   16777216
KEEP buffer cache                        ALLOC            000007FF24000000   16777216
KEEP buffer cache                        ALLOC            000007FF25000000   16777216
KEEP buffer cache                        ALLOC            000007FF26000000   16777216
KEEP buffer cache                        ALLOC            000007FF27000000   16777216
KEEP buffer cache                        ALLOC            000007FF28000000   16777216
KEEP buffer cache                        ALLOC            000007FF29000000   16777216
KEEP buffer cache                        ALLOC            000007FF2A000000   16777216
KEEP buffer cache                        ALLOC            000007FF2B000000   16777216
KEEP buffer cache                        ALLOC            000007FF2C000000   16777216
KEEP buffer cache                        ALLOC            000007FF2D000000   16777216
KEEP buffer cache                        ALLOC            000007FF2E000000   16777216
KEEP buffer cache                        ALLOC            000007FF2F000000   16777216
KEEP buffer cache                        ALLOC            000007FF30000000   16777216
KEEP buffer cache                        ALLOC            000007FF31000000   16777216
KEEP buffer cache                        ALLOC            000007FF32000000   16777216
KEEP buffer cache                        ALLOC            000007FF33000000   16777216
KEEP buffer cache                        ALLOC            000007FF34000000   16777216
streams pool                             ALLOC            000007FF35000000   16777216
streams pool                             ALLOC            000007FF36000000   16777216
java pool                                ALLOC            000007FF37000000   16777216
large pool                               ALLOC            000007FF38000000   16777216
shared pool                              ALLOC            000007FF39000000   16777216
shared pool                              ALLOC            000007FF3A000000   16777216
shared pool                              ALLOC            000007FF3B000000   16777216
shared pool                              ALLOC            000007FF3C000000   16777216
shared pool                              ALLOC            000007FF3D000000   16777216
shared pool                              ALLOC            000007FF3E000000   16777216
shared pool                              ALLOC            000007FF3F000000   16777216
shared pool                              ALLOC            000007FF40000000   16777216
shared pool                              ALLOC            000007FF41000000   16777216
shared pool                              ALLOC            000007FF42000000   16777216
shared pool                              ALLOC            000007FF43000000   16777216
shared pool                              ALLOC            000007FF44000000   16777216
shared pool                              ALLOC            000007FF45000000   16777216
shared pool                              ALLOC            000007FF46000000   16777216
shared pool                              ALLOC            000007FF47000000   16777216
shared pool                              ALLOC            000007FF48000000   16777216
shared pool                              ALLOC            000007FF49000000   16777216
shared pool                              ALLOC            000007FF4A000000   16777216
shared pool                              ALLOC            000007FF4B000000   16777216
shared pool                              ALLOC            000007FF4C000000   16777216
shared pool                              ALLOC            000007FF4D000000   16777216
shared pool                              ALLOC            000007FF4E000000   16777216
shared pool                              ALLOC            000007FF4F000000   16777216
shared pool                              ALLOC            000007FF50000000   16777216
shared pool                              ALLOC            000007FF51000000   16777216
shared pool                              ALLOC            000007FF52000000   16777216
shared pool                              ALLOC            000007FF53000000   16777216
shared pool                              ALLOC            000007FF54000000   16777216
shared pool                              ALLOC            000007FF55000000   16777216
shared pool                              ALLOC            000007FF56000000   16777216

87 rows selected.

SQL>

Expdp的include导出procedure

SQL> SELECT * FROM dba_directories ;

OWN DIRECTORY_NAME DIRECTORY_PATH
------- ---------------------- ---------------
SYS DPUMPDIR /oracle/bakup

SQL>grant read ,write on directory DPUMPDIR to &user;

C:/Users/Liangwei>expdp help=y
...

INCLUDE
包括特定对象类型。
例如, INCLUDE=TABLE_DATA。

expdp user/password directory=DPUMPDIR dumpfile=prodedure.dmp include=PROCEDURE

当然PROCEDURE 也可以换成其它对象如：

INDEX，TABLE，FUNCTION，VIEW，DATABASE LINK，SYNONYM，PACKAGE BODY，SEQUENCE，LOB，PACKAGE

C:/Users/Liangwei>impdp help=y

...

SQLFILE
将所有的 SQL DDL 写入指定的文件。

impdp user/password directory=DPUMPDIR dumpfile=prodedure.dmp SQLFILE=procedure.sql

将prodedure.dmp 中的信息导出为SQL语句，prodedure.dmp 中的存储过程并不会导入到数据库中。