Oracle 查询函数、存储过程、触发器、表、视图等

--查询所有对象
select distinct type from user_source；

--查询当前用户的序列总数
select count(*) from dba_sequences where sequence_owner='XXXXXX';

--查看当前用户的所有序列
select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner='XXXXXX';

--视图
select count(*)  from user_views;
select *  from user_views;

--物理视图
select count(*) from user_mviews;
select * from user_mviews;

--表
select count(*) from user_tables;
select * from user_tables;

--字段数
select sum(column_count)
  from (SELECT TABLE_NAME, COUNT(*) AS column_count
          FROM USER_TAB_COLUMNS
         GROUP BY TABLE_NAME) b;

SELECT TABLE_NAME, COUNT(*) AS column_count
          FROM USER_TAB_COLUMNS
         GROUP BY TABLE_NAME；

--触发器
select count(*) from user_triggers;
select * from user_triggers;

--存储过程
select count(*) from user_procedures where object_type='PROCEDURE';
select * from user_procedures where object_type='PROCEDURE';

--索引
select count(*) from user_indexes;
select * from user_indexes;

--函数
select count(*) from user_procedures where object_type='FUNCTION';
select * from user_procedures where object_type='FUNCTION';

--包
select object_name,count(object_name) from user_procedures where object_type='PACKAGE'group by object_name;
select object_name from user_procedures where object_type='PACKAGE' group by object_name;

--包体
select name,count(name) from ALL_SOURCE t where t.TYPE = 'PACKAGE BODY' and owner = 'XXXXXX'group by name;
select name from ALL_SOURCE t where t.TYPE = 'PACKAGE BODY' and owner = 'XXXXXX'group by name;

--类型
select name,count(name) from ALL_SOURCE t where t.TYPE = 'TYPE' and owner = 'XXXXXX'group by name;
select name from ALL_SOURCE t where t.TYPE = 'TYPE' and owner = 'XXXXXX'group by name;
select name from ALL_SOURCE t where t.TYPE = 'TYPE' and owner = 'XXXXXX'group by name;

--类型体
select name,count(name) from ALL_SOURCE t where t.TYPE = 'TYPE' and owner = 'XXXXXX'group by name;
select name from ALL_SOURCE t where t.TYPE = 'TYPE' and owner = 'XXXXXX'group by name;
select name from ALL_SOURCE t where t.TYPE = 'TYPE' and owner = 'XXXXXX'group by name;

--java source
select name,count(name) from ALL_SOURCE t where t.TYPE = 'JAVA SOURCE' and owner = 'XXXXXX'group by name;
select name from ALL_SOURCE t where t.TYPE = 'JAVA SOURCE' and owner = 'XXXXXX'group by name;
select name from ALL_SOURCE t where t.TYPE = 'JAVA SOURCE' and owner = 'XXXXXX'group by name;

--查询dblink数
SELECT count(*) FROM all_db_links;

--查询jobs数
SELECT count(*) FROM dba_scheduler_jobs WHERE owner = 'XXXXXX';