ORACLE常用SQL语句

一、查询锁及原因并解锁

--查找锁：
select o.object_name as 对象名称,
       s.username,
       s.sid,s.serial#,
       p.spid as 系统进程号,
       s.logon_time
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id 
  and l.session_id=s.sid 
  and s.paddr=p.addr;
/
--解锁
alter system kill session '488,659'--sid,serial#
/
--查找锁所操作的语句
select sql_text
  from v$session s,
       v$sqltext_with_newlines swn
 where decode(s.sql_hash_value,0,prev_hash_value,sql_hash_value) = swn.hash_value
   and s.sid = :sid
order by piece

 

二、查询最耗CPU的SQL语句

--对用top查询到的System_Process_ID查询出SID与SERIAL#
select s.sid,
       s.serial#
  from v$session s,
       v$process p
 where s.PADDR = p.ADDR
   and p.SPID = :System_Process_ID     ---此ID在Linux中用top指令执行后可以查看到，看CPU消耗最高那个
/

查询出具体的SQL语句
select sql_text
  from v$session s,
       v$sqltext_with_newlines swn
 where decode(s.sql_hash_value,0,prev_hash_value,sql_hash_value) = swn.hash_value
   and s.sid = :sid
order by piece
/
alter system kill session '1,1' 
/
--或者在Linux杀掉进程
kill -9 System_Process_ID

 

三、长时间等待的SQL语句

---查询引起等待的SQL
select SID,
       SERIAL#,
       USERNAME,
       BLOCKING_SESSION,
       BLOCKING_SESSION_STATUS,
       BLOCKING_INSTANCE,
       event,
       wait_time
  from v$session s
-- where BLOCKING_SESSION_STATUS = 'VALID'

/

查询出具体的SQL语句
select sql_text
  from v$session s,
       v$sqltext_with_newlines swn
 where decode(s.sql_hash_value,0,prev_hash_value,sql_hash_value) = swn.hash_value
   and s.sid = :sid
order by piece

/

alter system kill session '1,1' 

 

四、解决数据文件达到最大值

--查询数据文件是否达到最大值
SELECT file_name,
       bytes/1024/1024 "File_Size(MB)",
       maxbytes/1024/1024 "MaxFileSize(MB)",
       ddf.AUTOEXTENSIBLE
  from dba_data_files ddf
/
---当数据文件达到最大值会在警告日志文件中提示alert_SID.log
---解决方法一、
alter tablespace tablespace_name
add datafile '/oracle/oradata/orcl/tablesapce_name02.dbf'
size 10M autoextend on maxsize unlimited
---解决方法二、
alter database
datafile '/oracle/oradata/orcl/tablesapce_name01.dbf'
autoextend on maxsize unlimited

 

五、临时表空间过大而磁盘空间不足

---查看数据库当前所有属性
select * 
  from database_properties
-- where property_name = 'DEFAULT_TEMP_TABLESPACE'
/
---查看当然临时表空间
select file_name,
       tablespace_name,
       bytes/1024/1024 "MB",
       autoextensible
  from dba_temp_files
/
--创建新的临时表空间
create temporary tablespace temp_new
tempfile '/oracle/oradata/orcl/temp_new01.dbf'
size 10M autoextend on maxsize 20G
/
--修改默认的临时表空间
alter database default temporary tablespace temp_new
/
--查询出有多进程在旧表空间运行
select se.username,
       se.sid,
       se.serial#,
       su.extents,
       su.blocks * to_number(rtrim(p.value)) as space
  from v$sort_usage su,
       v$parameter p,
       v$session se,
       v$sql s
 where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash
   and s.address = su.sqladdr
order by se.username,se.sid
/
--杀掉上面查询出来的所以语句
alter system kill session 'sid,serial#'
/
--再次确认没有任何进程在旧表空间运行后删除旧表空间
drop tablespace temp including contents and datafiles

 

六、回滚表空间过大

--查询所有表空间使用情况
SELECT   a.tablespace_name, ROUND ((a.maxbytes / 1024 / 1024), 2) "sum MB",
         ROUND ((a.BYTES / 1024 / 1024), 2) "datafile MB",
         ROUND (((a.BYTES - b.BYTES) / 1024 / 1024), 2) "used MB",
         ROUND (((a.maxbytes - a.BYTES + b.BYTES) / 1024 / 1024),2) "free MB"
--         ROUND (((a.BYTES - b.BYTES) / a.maxbytes) * 100, 2) "percent_used"
    FROM (SELECT   tablespace_name, SUM (BYTES) BYTES, SUM (maxbytes) maxbytes
              FROM dba_data_files
--             WHERE maxbytes != 0
          GROUP BY tablespace_name) a,
         (SELECT   tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) largest
              FROM dba_free_space
          GROUP BY tablespace_name) b
   WHERE a.tablespace_name = b.tablespace_name
--ORDER BY ((a.BYTES - b.BYTES) / a.maxbytes) DESC
/
--查询所有回滚表空间
select file_name,
       bytes/1024/1024 "Used MB",
       tablespace_name,
       autoextensible
  from dba_data_files
 where tablespace_name like '%UNDO%'
/
--查询还有多少历史数据在此回滚段
select *
  from dba_rollback_segs
 WHERE status = 'ONLINE'
   and tablespace = 'UNDOTBS_OLD'
/
create undo tablespace undotbs_new
datafile '/oracle/oradata/orcl/undotbs_new.dbf'
size 10M autoextend on maxsize 20G
/
alter system set undo_tablespace = undotbs_new scope = both
/
select * from v$parameter  where name like '%undo%'
/
--还有多少数据在回滚段，等status都为OFFLINE后即可删除Undotbs_old
select *
  from dba_rollback_segs
 WHERE status = 'ONLINE'
   and tablespace = 'UNDOTBS_OLD'
/
drop tablespace undotbs_old including contents and datafiles

 

七、归档日志导致空间不足

--进入归档模式

rman target/
/

显示所有归档数据
list archivelog all
/

删除所有归档数据
delete archivelog all
/

查看归档参数
select *
  from v$parameter
 where name like '%recovery%'
/

修改归档参数
alter system set db_recovery_file_dest_size=20G scope =both

 

八、关于监听文件Listener.ora与HostName 路径

Listener.ora  

Linux:  $ORACLE_HOME/Network/admin/listener.ora

Windows:  C:\orant\NET80\ADMIN

 

Hosts

Linux:   /etc/sysconfig/network

        不相同： /etc/hosts    域名与IP地址解析，加快速度

Windows:  C:\Windows\System32\drivers\etc

 

十、还原数据库

1.使用dbca创建一个与备份的数据库同名的数据库

然后

create pfile='/oracle/orcl/pfile' from spfile

shutdown immediate

startup pfile='/oracle/orcl/pfile'   ---只能启动到mount状态

alter database backup controlfile to trace as '/trace/backup_file_directory'

shutdown immediate

startup nomount pfile='/oracle/orcl/pfile'

2.执行如下sql语句
create controlfile reuse database "orcl" noresetlogs NoArchivelog
    MaxLogfiles 16
    MaxLogmembers 3
    MaxDatafiles 100
    MaxInstances 8
    MaxLoghistory 292
logfile
    group 1 '/oracle/11.2.0/oradata/orcl/redo1.log' size 50M,
    group 2 '/oracle/11.2.0/oradata/orcl/redo2.log' size 50M,
    group 3 '/oracle/11.2.0/oradata/orcl/redo3.log' size 50M
datafile
    '/oracle/11.2.0/oradata/orcl/system01.dbf',
    '/oracle/11.2.0/oradata/orcl/undotbs01.dbf',
    '/oracle/11.2.0/oradata/orcl/sysaux01.dbf',
    '/oracle/11.2.0/oradata/orcl/users01.dbf',
character set ZHS16GBK;
recover database
alter database open;

 

十一、9i连接查询

SELECT FIRST_VALUE (emp_id) OVER (PARTITION BY part ORDER BY lev) emp_id,
       ROW_NUMBER () OVER (PARTITION BY part ORDER BY lev DESC) rn, part,
       manager_id AS manager_id
FROM (SELECT emp_id, manager_id, LEVEL lev, (ROWNUM - LEVEL) part
        FROM emp
      CONNECT BY emp_id = PRIOR manager_id);

十二、检测表空间的使用率

select a.tablespace_name,

          round((a.maxbytes/1024/1024),2) "sum MB",

          round((a.bytes/1024/1024),2) "datafile MB",

          round(((a.bytes-b.bytes)/1024/1024),2) "used MB",

          round(((a.maxbytes-a.bytes+b.bytes)/1024/1024),2) "free MB",

          round(((a.bytes-b.bytes)/a.maxbytes)*100,2) "percent_used"

  from 

           (select tablespace_name,

                       sum(bytes) bytes,

                       sum(maxbytes) maxbytes

              from dba_data_files

            where maxbytes != 0

        group by tablespace_name) a,

           (select tablespace_name,

                       sum(bytes) bytes,

                       max(bytes) largest

               from dba_free_space

         group by tablespace_name) b

where a.tablespace_name = b.tablespace_name

order by ((a.bytes - b.bytes)/a.maxbytes) desc