我常用的oracle语句

查看oracle数据库的某个表上已经建立了那些索引:select INDEX_NAME from dba_indexes where table_name='[table_name]'; table_name要大写

 

 启动oracle：
    $ sudo su - oracle
     $ sqlplus /nolog
     SQL > connect / as sysdba
     SQL > shutdown (immediate | normal | ...)
     SQL > exit c

启动listern:
    $ sudo su - oracle
    $ lsnrctl start
查看正在运行的sql：
    select a.program,b.spid,c.sql_text from v$session a,v$process b,v$sqlarea c where a.paddr=b.addr and a.sql_hash_value=c.hash_value and a.username is not null;
启动OEM(Oracle Enterprise Management)服务
    $ sudo su - oracle
    $ emctl start dbconsole
    页面察看http://10.60.1.133:1158/em ，用户名/密码：sys / Bhs^s6Kx
   
查看某个字段在那个存储过程中出现：
      select distinct name from user_source where lower(text) like '%dim_sepid%';

查看每个表占用空间的大小：
    Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name order by Sum(bytes)/1024/1024;

查看表空间占用空间的大小：
    Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name order by Sum(bytes)/1024/1024;


alter tablespace UNDOTBS2 add datafile '/data1/datafiles/undo03.dbf' size 10G autoextend off;
alter tablespace QIHOODW1 add datafile '/data2/datafiles/dw16.dbf' size 20000m autoextend off;
alter tablespace QIHOODW1 add datafile '/data2/datafiles/dw17.dbf' size 20000m autoextend off;

   
查看各个数据文件的使用情况：
set pagesize 1000   
set linesize 132
col TS_NAME form a24
col PIECES form 9999
col PCT_FREE form 999.9
col PCT_USED form 999.9
select *
  from (select Q2.OTHER_TNAME TS_NAME,
               PIECES,
               TS_SIZE TS_SIZE,
               NVL(LARGEST_CHUNK, 0) LARGESTPC,
               NVL(TOTAL_FREE, 0) TOTALFREE,
               NVL(ROUND((TOTAL_FREE / TS_SIZE) * 100, 2), 0) PCT_FREE,
               TS_SIZE - TOTAL_FREE WHATSUSED,
               NVL(100 - ROUND((TOTAL_FREE / TS_SIZE) * 100, 2), 100) PCT_USED,
               decode(nvl(100-round((total_free/ts_size)*100,0),100),
                 85,'+',86,'+',87,'+',88,'+',89,'++',90,'++',91,'++',
                 92,'++',93,'++',94,'+++',95,'+++',96,'+++',97,'++++',
                 98,'+++++',99,'+++++',100,'+++++','') PROBLEM
          from (select DFS.TABLESPACE_NAME,
                       count(*) PIECES,
                       ROUND(max(DFS.BYTES) / 1024 / 1024, 2) LARGEST_CHUNK,
                       ROUND(sum(DFS.BYTES) / 1024 / 1024, 2) TOTAL_FREE
                  from DBA_FREE_SPACE DFS
                 group by TABLESPACE_NAME) Q1,
               (select TABLESPACE_NAME OTHER_TNAME,
                       ROUND(sum(DDF2.BYTES) / 1024 / 1024, 2) TS_SIZE
                  from DBA_DATA_FILES DDF2
                 group by TABLESPACE_NAME) Q2
         where Q2.OTHER_TNAME = Q1.TABLESPACE_NAME(+) /*and Q2.OTHER_TNAME='USERS'*/
         order by NVL(100 - ROUND((TOTAL_FREE / TS_SIZE) * 100, 0), 100) desc) T
 where ROWNUM < 20;
 
 察看各个数据文件的使用情况：
select    b.file_id　　文件ID,
        b.tablespace_name　　表空间,
        b.file_name　　　　　物理文件名,
        b.bytes　　　　　　　总字节数,
        (b.bytes-sum(nvl(a.bytes,0)))　　　已使用,
        sum(nvl(a.bytes,0))　　　　　　　　剩余,
        sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比
from    dba_free_space a,dba_data_files b
where    a.file_id=b.file_id
group by
        b.tablespace_name,
        b.file_name,
        b.file_id,
        b.bytes
order by b.tablespace_name;
查询哪些对象被锁：
    select object_name,machine,s.sid,s.serial#
    from v$locked_object l,dba_objects o ,v$session s
    where l.object_id=o.object_id and l.session_id=s.sid;
杀死一个进程：
    alter system kill session '101 ,63926'; (其中24,111分别是上面查询出的sid,serial#)
如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：
    select spid, osuser, s.program
    from v$session s,v$process p
    where s.paddr=p.addr and s.sid=24 （24是上面的sid）
在OS上杀死这个进程（线程）：
    1)在unix上，用root身份执行命令:
        #kill -9 12345（即第3步查询出的spid）
    2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
        orakill sid thread
    其中：
        sid：表示要杀死的进程属于的实例名
        thread：是要杀掉的线程号，即第3步查询出的spid。
        例：c:>orakill orcl 12345
       
关闭GATHER_STATS_JOB的自动统计功能：
    从oracle 10g开始,Oracle在建库后就默认地创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO(RBO,基于规则的优化器; CBO,基于开销的优化器;
        CBO依靠准确的统计信息来产生优化的执行路径，所以DBA需要确保定期收集统计信息)的统计信息
    这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启。调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集
        统计信息。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。
    可以通过以下查询这个job的运行情况：
        select    *
        from    dba_scheduler_jobs
        where    job_name='GATHER_STATS_JOB';
    其实同在10点运行的job还有一个AUTO_SPACE_ADVISOR_JOB:
        select    job_name,to_char(last_start_date,'yyyymmdd hh24:mi:ss') as last_start_date
        from    dba_scheduler_jobs;
    然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。
    而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库hang或者crash。
    所以建议最好关闭这个自动统计信息收集功能：
        首先以sysdba的身份登录：
        sqlplus sys/Bhs^s6Kx as sysdba
        exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
    手工运行gather_stats_job:
        exec dbms_stats.gather_database_stats_job_proc
        运行这个后，就会改变sql的执行计划。
    删掉表的统计信息：
        analyze table table_name delete statistics;