oracle檢視建立物件的DDL語句

C:\Documents and Settings\zero>sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 6月 3 15:18:28 2008

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


連線到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS@zero>set pages 0
SYS@zero>set long 1000000
SYS@zero>conn test/test
已連線。
TEST@zero>desc test;
 名稱                                      是否為空? 型別
 ----------------------------------------- -------- ----------------------------
 SUPERID                                            VARCHAR2(20)
 ID                                                 VARCHAR2(20)

TEST@zero>select dbms_metadata.get_ddl('TABLE','TEST','TEST') FROM DUAL;

  CREATE TABLE "TEST"."TEST"
   (    "SUPERID" VARCHAR2(20),
        "ID" VARCHAR2(20)
   ) 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)
  TABLESPACE "MOF"



已選擇 1 行。

TEST@zero>select dbms_metadata.get_ddl('INDEX','BM_INDEX','TEST') FROM DUAL;

  CREATE BITMAP INDEX "TEST"."BM_INDEX" ON "TEST"."BM_IDX" ("WAY")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "MOF"



已選擇 1 行。

TEST@zero>select dbms_metadata.get_ddl('PROCEDURE','SHOW_SPACE','TEST') FROM DUAL;

  CREATE OR REPLACE PROCEDURE "TEST"."SHOW_SPACE"

 ( p_segname in varchar2,

    p_owner   in varchar2 default user,

    p_type    in varchar2 default 'TABLE',

    p_partition in varchar2 default NULL )

  as

      l_total_blocks              number;

      l_total_bytes               number;

      l_unused_blocks             number;

      l_unused_bytes              number;

      l_LastUsedExtFileId         number;

      l_LastUsedExtBlockId        number;

      l_last_used_block           number;

      procedure p( p_label in varchar2, p_num in number )

      is

      begin

         dbms_output.put_line( rpad(p_label,40,'.') ||

                                p_num );

     end;

  begin



      dbms_space.unused_space

      ( segment_owner     => p_owner,

       segment_name      => p_segname,

        segment_type      => p_type,

        partition_name    => p_partition,

        total_blocks      => l_total_blocks,

       total_bytes       => l_total_bytes,

       unused_blocks     => l_unused_blocks,

       unused_bytes      => l_unused_bytes,

        last_used_extent_file_id => l_LastUsedExtFileId,

       last_used_extent_block_id => l_LastUsedExtBlockId,

       last_used_block => l_last_used_block );



      p( 'Total Blocks', l_total_blocks );

      p( 'Total Bytes', l_total_bytes );

     p( 'Unused Blocks', l_unused_blocks );

     p( 'Unused Bytes', l_unused_bytes );

     p( 'Last Used Ext FileId', l_LastUsedExtFileId );

      p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );

      p( 'Last Used Block', l_last_used_block );

  end;



已選擇 1 行。

TEST@zero>