Oracle配置查询SQL汇总

计算DB的总容量

select round(sum(space)) all_space_M                        
  from (select sum(bytes) / 1024 / 1024 space               
          from dba_data_files                               
        union all                                           
        select nvl(sum(bytes) / 1024 / 1024, 0) space       
          from dba_temp_files                               
        union all                                           
        select sum(bytes) / 1024 / 1024 space from v$log);  

获取终端的字符集设置

SQL> select userenv('language') from dual;

查看控制文件

SQL> select name from v$controlfile;

查看联机日志文件

SQL> select group#,member from v$logfile;

查看数据文件

SQL> col tablespace_name for a20
SQL> col file_name for a45
SQL> select tablespace_name,file_name from dba_data_files;

查看临时文件

SQL> select tablespace_name,file_name from dba_temp_files;

查看当前session的process id和trace文件

SQL> select spid,tracefile from v$process where addr in 
(select paddr from v$session where sid = 
(select distinct sid from v$mystat));

查看数据库正在做什么？

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,   
       MACHINE, 
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, 
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT 
  FROM V$SESSION SES,   
       V$SQLtext_with_newlines SQL 
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS 
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE 
   and Ses.AUDSID <> userenv('SESSIONID') 
 order by runt desc, 1,sql.piece;

查询正在执行的SCHEDULER_JOB

select owner,job_name,
sid,b.SERIAL#,b.username,spid 
from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process
where session_id=sid and 
paddr=addr

查询正在执行的dbms_job

select job,b.sid,b.SERIAL#,b.username,spid 
from DBA_JOBS_RUNNING a ,v$session b,v$process
 where a.sid=b.sid and paddr=addr

确定系统默认临时表空间

SQL> select property_name,property_value 
from database_properties 
where property_name='DEFAULT_TEMP_TABLESPACE';

强制系统更新检查点

SQL> alter system checkpoint;

EM改为emctl unsecure dbconsole后，打开IE通过http的方式，可以正常登录了。

$ emctl unsecure dbconsole

添加redo log

alter database add logfile group 5 ‘/u01/app/oracle/oradata/orcl/redo5’ size 52M;
alter database add logfile member ‘/u01/app/oracle/oradata/orcl/redo4_3.log’ to group 4;
alter database drop logfile
alter database drop logfile member

检查归档切换的统计信息

set lines 200 pages 999
col date for a10
col 00 for 999
col 01 for 999
col 02 for 999
col 03 for 999
col 04 for 999
col 05 for 999
col 06 for 999
col 07 for 999
col 08 for 999
col 09 for 999
col 10 for 999
col 11 for 999
col 12 for 999
col 13 for 999
col 14 for 999
col 15 for 999
col 16 for 999
col 17 for 999
col 18 for 999
col 19 for 999
col 20 for 999
col 21 for 999
col 22 for 999
col 23 for 999
    
    
select to_char(first_time,'yyyy-mm-dd') "date",
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "00",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "01",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "02",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "03",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "04",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "05",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "06",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "07",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "08",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "09",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "12",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "13",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "14",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "15",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "16",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "17",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "18",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "19",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "20",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "21",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "22",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "23"
from v$log_history
group by to_char(first_time,'yyyy-mm-dd');

列出用户，状态，表空间和角色

select username,
        ACCOUNT_STATUS,
default_tablespace,
temporary_tablespace,
granted_role
from dba_users u,dba_role_privs r
where u.username = r.grantee
order by username

列出非sys,system的DBA用户

 select * from dba_role_privs 
 where grantee in
    ( select username from dba_users 
		where account_status='OPEN' and 
				username not in ('SYS','SYSTEM'))  and 
	granted_role='DBA' 
 order by grantee;

查看会话正在执行的SQL和上一次执行的SQL，(v$session,v$sql,v$process)

select s.username,s.sid,s.serial#,spid,sql_text
from v$session s,v$sql q,v$process p
where s.PADDR = p.ADDR
and (s.SQL_ID=q.sql_id or s.PREV_SQL_ID=q.sql_id)
and sid=72;

select s.username,s.sid,s.serial#,spid,
DECODE(q.sql_id,s.SQL_ID, 'Curruct sql is :'|| q.sql_text,s.PREV_SQL_ID,'Last sql is :'|| q.sql_text) "SQL Text"
from v$session s,v$sql q,v$process p
where s.PADDR = p.ADDR
and (s.SQL_ID=q.sql_id or s.PREV_SQL_ID=q.sql_id)
and sid=72;

查看索引被索引的字段

select * from user_ind_columns where index_name=upper('SYS_C0011435')

检查某个表的区ID，块ID和块个数

SQL> select extent_id,block_id,blocks 
from dba_extents 
where segment_name='EMP' order by extent_id;

查看当前的SCN

SQL> select timestamp_to_scn(sysdate) from dual;
SQL> select CURRENT_SCN from v$database;

手工删除数据库

SQL> startup mount  
SQL> alter system enable restricted session;
SQL> drop database;

删除表空间的同时删除数据文件

SQL> drop tablespace including contents and datafiles tbs_4;

mount状态下删除一个丢失数据文件的表空间

connect / as sysdba;
startup mount;
alter database datafile '丢失的DBF数据文件' offline drop;
alter database open;
drop tablespace...

sqlplus强制登陆数据库DB

sqlplus -prelim / as sysdba
sqlplus -prelim '/ as sysdba'

查看系统中硬解析，软解析的统计数据

 select s.name, m.value
    from   v$statname s, v$mystat m
    where  s.statistic# = m.statistic#
    and    s.name like 'parse%(%';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                      12
parse count (hard)                                                        5
parse count (failures)                                                    0
parse count (describe)                                                    0

授予普通用户访问sys schema 中的数据字典视图

 grant select any dictionary to testdic;

查询非默认参数

SELECT INST_ID,
  NAME,
  VALUE,
  DESCRIPTION,
  ISDEPRECATED
FROM gv$system_parameter
WHERE ISDEFAULT = 'FALSE'
ORDER BY NAME,
  INST_ID;

确定正在运行和将来运行的调度作业

SELECT owner,
  job_name,
  enabled,
  state,
  TO_CHAR(NEXT_RUN_DATE,'YYYY-MON-DD HH24:MI:SS') next_run
FROM dba_scheduler_jobs
WHERE STATE != 'DISABLED'
ORDER BY 1,2  

列出所有分区表

 select OWNER, 
                 TABLE_NAME, 
                 TABLESPACE_NAME, 
                 logging, 
                 partitioned,
                 owner       sdev_link_owner,
                 table_name  sdev_link_name,
                 'TABLE'     sdev_link_type
            from sys.dba_tables
           where table_name not like 'BIN$%'
             and partitioned = 'YES'
             and owner not in ('SYS','SYSTEM')
           order by 1, 2

oracle显示长事务，长事务会在v$session_longops表中可以查到！

 set linesize 200  
    set pagesize 5000  
    col transaction_duration format a45  
      
    with transaction_details as  
    ( select inst_id  
      , ses_addr  
      , sysdate - start_date as diff  
      from gv$transaction  
    )  
    select s.username  
    , to_char(trunc(t.diff))  
                 || ' days, '  
                 || to_char(trunc(mod(t.diff * 24,24)))  
                 || ' hours, '  
                 || to_char(trunc(mod(t.diff * 24 * 60,24)))  
                 || ' minutes, '  
                 || to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))  
                 || ' seconds' as transaction_duration  
    , s.program  
    , s.terminal  
    , s.status  
    , s.sid  
    , s.serial#  
    from gv$session s  
    , transaction_details t  
    where s.inst_id = t.inst_id  
    and s.saddr = t.ses_addr  
    order by t.diff desc  
    /  

重建EM
emca -config dbcontrol db -repos recreate