Oracle 常用的经典SQL查询

/*1、查看表空间的名称及大小*/


select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;

/*2、查看表空间物理文件的名称及大小*/

select tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_spacefrom dba_data_filesorder by tablespace_name;

3,查看回滚段名称及大小

select segment_name,tablespace_name,r.status,(initial_extent / 1024) InitialExtent,(next_extent / 1024) NextExtent,max_extents,v.curext CurExtentFrom dba_rollback_segs r, v$rollstat vWhere r.segment_id = v.usn(+)order by segment_name;

4、查看控制文件

select name from v$controlfile;

5、查看日志文件

select member from v$logfile;

6.查看表空间的使用情况

select sum(bytes) / (1024 * 1024) as free_space, tablespace_namefrom dba_free_spacegroup by tablespace_name;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,C.BYTES FREE,(B.BYTES * 100) / A.BYTES "% USED",(C.BYTES * 100) / A.BYTES "% FREE"FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE CWHERE A.TABLESPACE_NAME = B.TABLESPACE_NAMEAND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

7.查看数据库对象

select owner, object_type, status, count(*) count#from all_objectsgroup by owner, object_type, status;

8. 查看数据库的版本

select versionfrom Product_Component_Versionwhere substr(product, 1, 6) = 'Oracle';

9. 查看数据库的创建日期和归档方式

select Created, Log_Mode From V$DATABASE;

10.捕捉运行很久的SQL

column username format a12
column opname format a16
column progress format a8select username,sid,opname,round(sofar * 100 / totalwork, 0) || '%' as progress,time_remaining,sql_textfrom v$session_longops, v$sqlwhere time_remaining <> 0and sql_address = addressand sql_hash_value = hash_value

11. 查看数据表的参数信息

SELECT partition_name,high_value,high_value_length,tablespace_name,pct_free,pct_used,ini_trans,max_trans,initial_extent,next_extent,min_extent,max_extent,pct_increase,FREELISTS,freelist_groups,LOGGING,BUFFER_POOL,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,sample_size,last_analyzedFROM dba_tab_partitions
--WHERE table_name = :tname AND table_owner = :townerORDER BY partition_position

12.查看还没提交的事务

select * from v$locked_object;

13.查看object为哪些进程所用。

select p.spid,s.sid,s.serial# serial_num,s.username user_name,a.type object_type,s.osuser os_user_name,a.owner,a.object object_name,decode(sign(48 - command),1,to_char(command),'Action Code #' || to_char(command)) action,p.program oracle_process,s.terminal terminal,s.program program,s.status session_statusfrom v$session s, v$access a, v$process pwhere s.paddr = p.addrand s.type = 'USER'and a.sid = s.sidand a.object = 'SUBSCRIBER_ATTR'order by s.username, s.osuser

14.回滚段查看

select rownum,sys.dba_rollback_segs.segment_name Name,v$rollstat.extents                 Extents,v$rollstat.rssize                  Size_in_Bytes,v$rollstat.xacts                   XActs,v$rollstat.gets                    Gets,v$rollstat.waits                   Waits,v$rollstat.writes                  Writes,sys.dba_rollback_segs.status       statusfrom v$rollstat, sys.dba_rollback_segs, v$rollnamewhere v$rollname.name(+) = sys.dba_rollback_segs.segment_nameand v$rollstat.usn(+) = v$rollname.usnorder by rownum

15.耗资源的进程（top session）

select s.schemaname schema_name,decode(sign(48 - command),1,to_char(command),'Action Code #' || to_char(command)) action,status session_status,s.osuser os_user_name,s.sid,p.spid,s.serial# serial_num,nvl(s.username, '[Oracle process]') user_name,s.terminal terminal,s.program program,st.value criteria_valuefrom v$sesstat st, v$session s, v$process pwhere st.sid = s.sidand st.statistic# = to_number('38')and ('ALL' = 'ALL' or s.status = 'ALL')and p.addr = s.paddrorder by st.value desc, p.spid asc, s.username asc, s.osuser asc

16.查看锁（lock）情况

select /*+ RULE */ls.osuser os_user_name,ls.username user_name,decode(ls.type,'RW','Row wait enqueue lock','TM','DML enqueue lock','TX','Transaction enqueue lock','UL','User supplied lock') lock_type,o.object_name object,decode(ls.lmode,1,null,2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive',null) lock_mode,o.owner,ls.sid,ls.serial# serial_num,ls.id1,ls.id2from sys.dba_objects o,(select s.osuser,s.username,l.type,l.lmode,s.sid,s.serial#,l.id1,l.id2from v$session s, v$lock lwhere s.sid = l.sid) lswhere o.object_id = ls.id1and o.owner <> 'SYS'order by o.owner, o.object_name

17.查看等待（wait）情况

SELECT v$waitstat.class,v$waitstat.count count,SUM(v$sysstat.value) sum_valueFROM v$waitstat, v$sysstatWHERE v$sysstat.name IN ('db block gets', 'consistent gets')group by v$waitstat.class, v$waitstat.count

18.查看sga情况

SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC

19.查看catached object

select owner,name,db_link,namespace,type,sharable_mem,loads,executions,locks,pins,keptfrom v$db_object_cache;

20.查看V$SQLAREA

SELECT SQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,DISK_READS,BUFFER_GETS,ROWS_PROCESSEDFROM V$SQLAREA

数据字典

############### 数据字典 ##########
set wrap off
select * from v$dba_users;
grant select on table_name to user/rule;
select * from user_tables;
select * from all_tables;
select * from dba_tables;
revoke dba from user_name;
shutdown immediate
startup nomount
select * from v$instance;
select * from v$sga;
select * from v$tablespace;
alter session set nls_language=american;
alter database mount;
select * from v$database;
alter database open;
desc dictionary
select * from dict;
desc v$fixed_table;
select * from v$fixed_table;
set oracle_sid=foxconn
select * from dba_objects;
set serveroutput on
execute dbms_output.put_line('sfasd');

############# 控制文件 ###########

select * from v$database;
select * from v$tablespace;
select * from v$logfile;
select * from v$log;
select * from v$backup;

备份用户表空间

alter tablespace users begin backupselect * from v$archived_log;alter system set control_file='$ORACLE_HOME/oradata/u01/ctrl01.ctl','$ORACLE_HOME/oradata/u01/ctrl02.ctl' scope=spfile;cp $ORACLE_HOME/oradata/u01/ctrl01.ctl $ORACLE_HOME/oradata/u01/ctrl02.ctlstartup pfile='../initSID.ora'select * from v$parameter where name like 'control%' ;show parameter control;select * from v$controlfile_record_section;select * from v$tempfile

备份控制文件

alter database backup controlfile to '../filepath/control.bak';

/*备份控制文件，并将二进制控制文件变为了 asc 的文本文件*/

 alter database backup controlfile to trace;

######redo log###########archive log list;
alter system archive log start; -- 启动自动存档
alter system switch logfile; -- 强行进行一次日志switch
alter system checkpointl -- 强行进行一次checkpoint
alter tablespace users begin backup;
alter tablespace offline;/*checkpoint同步频率参数FAST_START_MTTR_TARGET,同步频率越高，系统恢复所需时间越短*/show parameter fast;show parameter log_checkpoint;/*加入一个日志组*/
alter database add logfile group 3('/$ORACLE_HOME/oracle/ora_log_file6.rdo' size 10M);/*加入日志组的一个成员*/alter database add logfile member '/$ORACLE_HOME/oracle/ora_log_file6.rdo' to group 3;/*删除日志组中的某个成员，但每个组的最后一个成员不能被删除*/
alter database drop logfile member '/$ORACLE_HOME/oracle/ora_log_file6.rdo'/*清除在线日志*/
alter database clear logfile '/$ORACLE_HOME/oracle/ora_log_file6.rdo';alter database clear logfile group 3;/*清除非归档日志*/
alter database clear unarchived logfile group 3;/*重命名日志文件*/
alter database rename file '/$ORACLE_HOME/oracle/ora_log_file6.rdo' to '/$ORACLE_HOME/oracle/ora_log_file6a.rdo';show parameter db_create;alter system set db_create_online_log_dest_1='path_name';select * from v$log;
select * from v$logfile;/*数据库归档模式到非归档模式的互换，要启动到mount状态下才能改变，startup mount,然后再打开数据库*/
alter database noarchivelog/archivelog;
achive log start; -- 启动自动归档
alter system archive all; --手工归档所有日志文件
select * from v$archived_log;
show parameter log_archive;

###############分析日志文件logmnr#################

1） 在init.ora 中set utl_file_dir 参数
2） 重新启动oracle
3) create 目录文件
desc dbms_logmnr_d;
dbms_logmnr_d.build;
4)加入日志文件 add/remove log file
dbms_logmnr.add_logfile
dbms_logmnr.removefile
5)start logmnr
dbms_logmnr.start_logmnr
6)分析出来的内容查询 v$logmnr_content --sqlredo/sqlundo