这几天做性能优化,网上找了些资料。
有时候需要导出某用户下的所有table、view、sequence、trigger等信息,下面的SQL可以将这些信息select出来:
select
*
from
user_tables;
select * from user_views;
select * from user_sequences;
select * from user_triggers;
select * from user_views;
select * from user_sequences;
select * from user_triggers;
想查找表的数据条数
试试这个
select
t.table_name,t.num_rows
from
user_tables t
如果没有值,那就创建个函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
create
or
replace
function
count_rows(table_name
in
varchar2
,
owner in varchar2 default null )
return number
authid current_user
IS
num_rows number ;
stmt varchar2 ( 2000 );
begin
if owner is null then
stmt : = ' select count(*) from " ' || table_name || ' " ' ;
else
stmt : = ' select count(*) from " ' || owner || ' "." ' || table_name || ' " ' ;
end if ;
execute immediate stmt into num_rows;
return num_rows;
end
owner in varchar2 default null )
return number
authid current_user
IS
num_rows number ;
stmt varchar2 ( 2000 );
begin
if owner is null then
stmt : = ' select count(*) from " ' || table_name || ' " ' ;
else
stmt : = ' select count(*) from " ' || owner || ' "." ' || table_name || ' " ' ;
end if ;
execute immediate stmt into num_rows;
return num_rows;
end
再执行查询
select
table_name, count_rows(table_name) nrows
from
user_tables