Oracle中,为了获取object的定义语句,我们可以通过以下几种方式来实现:
1、视图定义可以通过user_views的text来查询得到;
2、存储过程、函数、包、类型等的定义可以通过dba_source视图来查询得到;
3、通过dbms_metadata.get_ddl可以得到所有object的ddl语句。
get_ddl的使用很简单,参数如下:
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL'
从以上参数定义中可以知道,我们基本上只要使用前两个参数即可,即对象类型和对象名称。如:
lyon@ORCL> select dbms_metadata.get_ddl('TABLE', 'TT') from dual;
DBMS_METADATA.GET_DDL('TABLE','TT')
--------------------------------------------------------------------------------
CREATE TABLE "LYON"."TT"
( "X" VARCHAR2(10),
"Y" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "LYONTBS"
已用时间: 00: 00: 00.45
即可得到表TT的定义语句。但是可以发现结果中含有storage子句和tablespace子句,有时候我们并不需要这些子句,那该怎么办呢?
这时就可以通过:set_transform_param过程来实现输出子句的控制。
如:
lyon@ORCL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.01
lyon@ORCL> select dbms_metadata.get_ddl('TABLE', 'TT') from dual;
DBMS_METADATA.GET_DDL('TABLE','TT')
--------------------------------------------------------------------------------
CREATE TABLE "LYON"."TT"
( "X" VARCHAR2(10),
"Y" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
TABLESPACE "LYONTBS"
已用时间: 00: 00: 00.34
可以发现,storage子句已经没有了。同样这个也适合于表空间子句:
lyon@ORCL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', false);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.01
lyon@ORCL> select dbms_metadata.get_ddl('TABLE', 'TT') from dual;
DBMS_METADATA.GET_DDL('TABLE','TT')
--------------------------------------------------------------------------------
CREATE TABLE "LYON"."TT"
( "X" VARCHAR2(10),
"Y" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
已用时间: 00: 00: 00.39
可以进一步连pctfree这些存储参数也忽略掉:
lyon@ORCL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.01
lyon@ORCL> select dbms_metadata.get_ddl('TABLE', 'TT') from dual;
DBMS_METADATA.GET_DDL('TABLE','TT')
--------------------------------------------------------------------------------
CREATE TABLE "LYON"."TT"
( "X" VARCHAR2(10),
"Y" NUMBER(*,0)
)
已用时间: 00: 00: 00.39
这样就只剩下了表的直接定义语句了。如果还想在这个定义语句的末尾加个截止符(;),那可以如下:
lyon@ORCL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.00
lyon@ORCL> select dbms_metadata.get_ddl('TABLE', 'TT') from dual;
DBMS_METADATA.GET_DDL('TABLE','TT')
--------------------------------------------------------------------------------
CREATE TABLE "LYON"."TT"
( "X" VARCHAR2(10),
"Y" NUMBER(*,0)
) ;
已用时间: 00: 00: 00.39
可能会有人说,加个终止标识符还不简单,直接在语句最后拼接一下不就行了。
不过如果要在多个对象多行记录输出的事情判断就不是那么简单了。而且这个参数的不仅会在表的结尾增加一个“;”那么简单,他还会在package、procedure、type的结尾增加一个“/”符号。如:
lyon@ORCL> select dbms_metadata.get_ddl('PROCEDURE', 'SHOW_SPACE') from dual;
DBMS_METADATA.GET_DDL('PROCEDURE','SHOW_SPACE')
--------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE "LYON"."SHOW_SPACE"
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE' )
as
l_free_blks number;
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.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
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( 'Free Blocks', l_free_blks );
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;
/
已用时间: 00: 00: 00.35
因此这个参数还是比较实用的。
当然,Oracle还为我们考虑了很多其他的情况,以上我只是罗列了一些最常用的参数,全部的使用可以参考下表: