1.查询某个用户下某个对象的DDL语句

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 0
set long 50000

SELECT
        ''||DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER) AS DDL
FROM
        DBA_OBJECTS
WHERE
upper(OWNER)=upper('&v_owner')
and upper(OBJECT_NAME)=upper('&v_objname');

2.查询某个表的DDL语句

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000

sp_tabledef(upper('&tab_owner'), upper('&tab_name'));

3.查询某个用户下某个对象的DDL详细信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000

select owner||'.'||object_name   object_name,
       subobject_name            subobj_name,
       object_type  "Type",
       status,
       to_char(last_ddl_time,'yyyy/mm/dd hh24:mi') last_ddl
from dba_objects
where upper(object_name) like upper('&v_objname')
;

4.查询某用户下某个视图的DDL详细信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off

select owner,
       VIEW_TYPE  Type,
       view_name "Name"
from dba_views
where upper(view_name) like upper('&v_viewname')
order by 1,2;

5.查询对象的最新更新时间

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000

select owner,
       object_type,
       object_name,
       subobject_name,
       to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') last_ddl
from dba_objects
where upper(object_name)=upper('&v_obj_name')
and  upper(owner)=upper(nvl('&v_owner',owner))
order by 5,1,2,3;

6.查询某个用户下某个表的约束信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000

select
        co.owner,
        co.table_name,
        co.constraint_name,
        co.constraint_type,
        cc.column_name          cons_column_name,
        cc.position
from
        dba_constraints co,
        dba_cons_columns cc
where
        co.owner                = cc.owner
and     co.table_name           = cc.table_name
and     co.constraint_name      = cc.constraint_name
and     lower(co.owner)      like lower('&v_owner')
and     lower(co.table_name) like lower('&v_name')
order by
        owner,
        table_name,
        constraint_type,
        column_name,
        constraint_name,
        position;

7.查询某个表和索引的并发数信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 1000

prompt
prompt Parallel Tables
prompt =============================
select a.owner||'.'||a.table_name                       table_name
      ,substr(a.degree,1,12)                            degree
      ,a.partitioned
      ,a.num_rows
      ,to_char(b.created,'yyyy/mm/dd hh24:mi:ss')       created
      ,to_char(b.last_ddl_time,'yyyy/mm/dd hh24:mi:ss') last_ddl
from dba_tables a,dba_objects b
where a.owner=b.owner
and   a.table_name=b.object_name
and   b.object_type='TABLE'
and   a.degree<>'         1';

prompt
prompt Parallel Indexes
prompt =============================
select a.owner||'.'||a.index_name                       index_name
      ,substr(a.degree,1,12)                            degree
      ,a.partitioned
      ,a.num_rows
      ,to_char(b.created,'yyyy/mm/dd hh24:mi:ss')       created
      ,to_char(b.last_ddl_time,'yyyy/mm/dd hh24:mi:ss') last_ddl
from dba_indexes a,dba_objects b
where a.owner=b.owner
and   a.index_name=b.object_name
and   b.object_type='INDEX'
and   a.degree not in ('1','0');

8.查询表的详细信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set LONG 50000

--定义查询用户和对象
DEFINE owner=&owner
DEFINE object_name=&object_name

--对象简单信息
desc "&owner"."&object_name"

--获取对象的ddl信息
select DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, OWNER) AS DDL from DBA_OBJECTS
WHERE
     upper(owner)=upper('&owner') and    upper(OBJECT_NAME)=upper('&object_name');

--查询索引信息
select b.table_owner||'.'||b.table_name table_name,
       b.index_name,
       b.column_position,
       b.descend,
       b.column_name
from dba_ind_columns b
where upper(b.table_owner) = upper(nvl('&owner',b.table_owner))
and upper(b.table_name) like upper('&object_name')
order by b.table_owner||'.'||b.table_name,b.index_name, b.column_position, b.column_name
;

--查表询外键信息
select
        st.name,
        sc.name,
        c.TYPE$,
        c.CHECKINFO,
        c.VALID
from
        SYSCONS c    ,
        dba_indexes i,
        sysobjects si,
        sysobjects st,
        sysobjects sc
where
        c.FINDEXID   =si.id
    and c.id = sc.id
    and i.index_name =si.name
    and c.tableid = st.id
    and upper(i.TABLE_owner)=upper('&owner')
    AND upper(i.TABLE_NAME)   =upper('&object_name');

--查询统计信息
select
        st.COLID,
        ST.T_FLAG,
        ST.T_total,
        N_SMAPLE,
        n_distinct,
        N_NULL,
        V_MIN,
        V_MAX,
        COL_AVG_LEN,
        LAST_GATHERED
from
        sysstats st ,
        sysobjects t,
        sysobjects tow
where
        st.id   =t.id
    and t.schid =tow.id
    and upper(tow.name)=upper('&owner')
    and upper(t.name)  =upper('&object_name')  ;

--查询分区表信息
SELECT
        TABLE_OWNER
        ||'.'
        ||TABLE_NAME TNAME  ,
        PARTITION_NAME PNAME,
        HIGH_VALUE          ,
        PARTITION_POSITION PNUM
FROM
        DBA_TAB_PARTITIONS
where
        upper(table_owner)=upper('&owner')
    AND upper(TABLE_NAME) =upper('&object_name');

--查询分区表的统计信息
select
         t.name,
        st.COLID,
        ST.T_FLAG,
        ST.T_total,
        N_SMAPLE,
        n_distinct,
        N_NULL,
        V_MIN,
        V_MAX,
        COL_AVG_LEN,
        LAST_GATHERED
from
        sysstats st ,
        sysobjects t,
        sysobjects tow
where
        st.id   =t.id
and t.schid =tow.id
and (tow.name,t.name) in
      (SELECT TABLE_OWNER,TABLE_NAME||'_'||PARTITION_NAME
        FROM DBA_TAB_PARTITIONS 
        where table_owner=upper('&owner') 
        AND TABLE_NAME=upper('&object_name')
    ) order by name,colid;

--查询某个表的使用大小
SELECT
        O.OWNER
        ||'.'
        ||O.OBJECT_NAME AS TABLE_NAME,
        T.OWNER         AS USER_NAME ,
        T.TABLESPACE_NAME            ,
        T.STATUS                     ,
        TABLE_USED_PAGES(O.OWNER, O.OBJECT_NAME)*SF_GET_PAGE_SIZE()/1024/1024 AS TAB_USERD_M
FROM
        DBA_OBJECTS O,
        DBA_TABLES T
WHERE
        O.OBJECT_NAME=T.TABLE_NAME
AND     O.OBJECT_TYPE='TABLE'
AND     upper(O.OWNER)      =upper('&owner')
AND     upper(O.OBJECT_NAME)=upper('&object_name');