1,查看数据库的基本属性:
select dbid 数据库编号,name 数据库名称,db_unique_name 全局名称,created 创建时间,log_mode 归档方式,open_mode 访问方式,platform_name 版本类型 from v$database;
2,查看所有数据库对象的类别和大小:
select count(name) num_instances,type,sum(source_size) source_size,sum(parsed_size) parsed_size,sum(code_size) code_size,
sum(error_size) error_size, sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2;
num_instances type source_size parsed_size code_size error_size size_required
------------- -------------------------- ----------- ----------- ---------- ---------- -------------
264 function 90226 163614 286766 0 540606
16417 java class 0 0 79741994 0 79741994
306 java data 0 0 9061460 0 9061460
770 java resource 0 0 10738795 0 10738795
843 package 5570226 5597328 2593462 0 13761016
787 package body 16979979 0 20748156 0 37728135
83 procedure 195788 80300 266716 0 542804
77 sequence 0 21269 0 0 21269
170 trigger 202051 0 176799 0 378850
1859 type 897097 1438090 587281 0 2922468
173 type body 326762 13013 922875 0 1262650
11 rows selected.
3,查看当前数据库的object数目:
select decode(o.type#,1,'index',2,'table',3,'cluster',4,'view',5,'synonym',6, 'sequence','other') 对象类型 , count(*) 对象数目
from sys.obj$ o where o.type# > 1 group by decode(o.type#,1,'index',2,'table',3,'cluster',4,'view',5,'synonym',6,'sequence','other')
union select 'column', count(*) from sys.col$
union select 'db link' , count(*) from all_objects;
对象类型 对象数目
---------------- ----------
cluster 10
column 55879
db link 49896
other 23667
sequence 141
synonym 20027
table 1634
view 3670
8 rows selected.
4,查看当前数据库的字符集:
select userenv('language') from dual;
或者：
select userenv('lang') from dual;
或者：
select * from sys.props$ where;
5,查看数据库文件的属性;
查看控制文件的命令如下
select name from v$controlfile;
查看数据文件的命令如下
select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
查看日志文件的命令如下
select * from v$logfile;
查看日志文件的组名、成员数量、状态和大小，命令如下
select group#,members,bytes,status from v$log;
查看临时文件的命令如下
select name,bytes from v$tempfile;