对表名、总行数、字段数、非空字段进行统计统计
这些sql不是原创的,但是我懒得去找地址了,就这样吧。
非原创
------------------------------------------------
----- 一、对所有的表名和总记录数进行(这段是从别的博客获取的)
----------------------------------------------
-- 1.先创建一个方法count_rows
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;
-- 2.查询出oracle中所有的表名以及对应的记录数
SELECT
A .TABLE_NAME,
A .num_rows,
count_rows (A .table_name),
b.COMMENTS
FROM
user_tables A,
user_tab_comments b
WHERE
A .TABLE_NAME = b.TABLE_NAME
ORDER BY
TABLE_NAME;
------------------------------------------------
----- 二、统计每张表的字段数,一张一张表统计(这个也是)
----------------------------------------------
-- 3.查询单张表所有的字段数
select count(*) from user_tab_columns where table_name=upper('表名')
------------------------------------------------
----- 三、统计每张表的非空字段(这个是自己参考别人写的)
----------------------------------------------
-- 4.查询单张表内所有的非空字段(具体的名称)
SELECT table_name,Column_name FROM user_tab_columns where table_name=upper('sys_dictionary') and Nullable = 'N'
oracle的安装
创建一个文件夹PLSQL,将plsql安装在这个目录下,然后,下载一个instantclient_11_2,在以下目录加入
E:\PLSQL\instantclient_11_2\NETWORK\ADMIN
放入tnsnames.ora文件
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
大功告成
还需要配置
工具------>首选项------>oracle主目录名,oci库
E:\PLSQL\instantclient_11_2
E:\PLSQL\instantclient_11_2\oci.dll