如何查找某个对象的定义（V$_X$_DBA）[8]

sql> alter tablespace temp add tempfile 'd:\oracle92\oradata\test1\temp01.dbf' size 10m;

表空间已更改。

sql> @c:\temp\get_obj_sql.sql
sql> set heading off echo off pages 10000 long 90000
输入 object_type 的值:  table
输入 object_name 的值:  emp
输入 object_owner 的值:  lunar
原值    1: select dbms_metadata.get_ddl(upper('&object_type'),upper('&object_name'),upper('&object_o
wner')) from dual
新值    1: select dbms_metadata.get_ddl(upper('table'),upper('emp'),upper('lunar')) from dual

  create table "lunar"."emp"
   (    "empno" number(4,0) not null enable,
        "ename" varchar2(10),
        "job" varchar2(9),
        "mgr" number(4,0),
        "hiredate" date,
        "sal" number(7,2),
        "comm" number(7,2),
        "deptno" number(2,0)
   ) 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 "system"

sql>
sql>

dbms_metadata.get_ddl也可以用来查询其他对象的创建语句，使用方法如下；
 select dbms_metadata.get_ddl('对象类型','对象名','用户名') from dual;

例如：
oracle@cs_db02:/arch1/lunar/tools > get_obj_sql.sh procedure aa misc
connected.

  create or replace procedure "misc"."aa" is
begin
delete from error_tip;
end aa;