查看表定义与统计所有表中数量

一、查看表定义

CALL SP_TABLEDEF('whapts', '表名');
或者
 SELECT TABLEDEF('模式名','表名');
或者
select dbms_metadata.get_ddl('TABLE','表名','模式名') from dual;
或者
自定义查询表定义函数
CREATE OR REPLACE FUNCTION SF_SHOW_TABLE_DEFINE
(
SCH_NAME VARCHAR2(500),
TAB_NAME VARCHAR2(1000) 
)
return varchar(8000)
as
V_INX_ID VARCHAR2(8000);
V_SQL VARCHAR2(8000);
V_SQL_ALL VARCHAR2(8000);
v_sql1 varchar2(8000);
V_CUR
CURSOR;
BEGIN
v_sql1 =TABLEDEF(SCH_NAME,TAB_NAME);

OPEN V_CUR FOR 
SELECT T2.OBJECT_ID FROM ALL_INDEXES T1, ALL_OBJECTS T2
 WHERE T1.OWNER =T2.OWNER 
 AND T1.INDEX_NAME=T2.OBJECT_NAME 
 AND T2.GENERATED ='N' 
 AND T1.OWNER =SCH_NAME 
 AND T1.TABLE_NAME=TAB_NAME;
LOOP
FETCH V_CUR INTO V_INX_ID;
EXIT
WHEN V_CUR%NOTFOUND;

SELECT INDEXDEF(V_INX_ID,1) INTO V_SQL FROM DUAL;
 
 V_SQL_ALL=V_SQL_ALL||''||V_SQL;
END LOOP;
V_SQL_ALL=v_sql1||''||V_SQL_ALL;
RETURN V_SQL_ALL;
END;

用法：SELECT SF_SHOW_TABLE_DEFINE('模式名','表名')

二、统计模式下所有的表名以及表的数据量

--这种方式是比较简单的
--创建表存储信息
create table test(table_name varchar,cnt bigint);

--将以下SYSDBA替换成需要统计的模式名
declare
sql varchar ;
cnt bigint ;
begin
for rec in (select table_name from dba_tables where owner = 'SYSDBA') loop
 sql = 'select count(*) from SYSDBA."'||rec.table_name||'"';
 execute immediate sql into cnt;
 insert into test values (rec.table_name,cnt);
 commit;
end loop;
select *from  test;
--最后记得删除这个表
end;

使用系统表ALL_TABLES
SELECT T.TABLE_NAME AS 表名,TABLE_ROWCOUNT(T.OWNER,T.TABLE_NAME) AS 数据量,U.COMMENTS AS 注释
FROM ALL_TABLES T,ALL_TAB_COMMENTS U where T.TABLE_NAME =U.TABLE_NAME AND T.OWNER='WHAPTS'
或者
SELECT OWNER,TABLE_NAME,NUM_ROWS,* FROM SYS.ALL_TABLES 
WHERE OWNER IN (
SELECT NAME FROM SYS.SYSOBJECTS 
  WHERE TYPE$='SCH'
 AND PID IN(SELECT ID FROM SYS.SYSOBJECTS WHERE TYPE$='UR' AND SUBTYPE$='USER' AND NAME  IN('WHAPTS'))
) order by owner,table_name

三、查看某个表被哪些视图引用了

select * from DBA_dependencies T WHERE  t.REFERENCED_NAME = 'TD_NEWS'