1.查看表空间使用率。

select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total where free.tablespace_name = total.tablespace_name order by used_pct desc;

2、查询单个表空间使用率。

select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space where tablespace_name='TBL_SPACE' group by tablespace_name) free,(select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files where tablespace_name='TBL_SPACE' group by tablespace_name) totalwhere free.tablespace_name = total.tablespace_name order by used_pct desc;

3.查看ASM磁盘空间。

select name,state,type,free_mb,total_mb,usable_file_mb from v$asm_diskgroup;

4、查询oracle的连接数

select count(*) from v$session;

5、查询oracle的process

select count(*) from v$process;

6、查询用户下所有创建表的语句。

select'select dbms_metadata.get_ddl('||''''||'TABLE'||''''||','||''''||table_name||''''||') from dual;'||chr(10)||'select '||''''||'/'||''''|| ' from dual;' from user_tables;

7、查询当时创建用户的语句

select dbms_metadata.get_ddl('USER','USERNAME') from dual;

8、修改数据文件大小

alter database datafile '&path_name' resize 10G;alter database datafile &{file_id} resize 10G;

9、添加数据文件

alter tablespace &tablespace_name ADD datafile '&datafile_name' SIZE xxx;

10、查询告警日志文件位置

show parameter dumpselect * from v$diag_info;