ORACLE管理常用sql

查杀session：
set heading off;
set pagesize 0;
col sessions format a80
Select  '***--***>>No.'||rownum||': '||b.sid||','||b.serial#||' --['|| b.status||']--> '||a.sql_text sessions
From    V$sql a, V$session b
Where   b.sql_address=a.address
    and b.sql_hash_value=a.hash_value
    and b.username='ORCL';

alter system kill session 'sid,serial#' ;

查看表空间使用率
set pagesize 0;
set heading off;
select
    df.tablespace_name "表空间名",
    totalspace "总空间M",
    totalspace-freespace "使用空间M",
    round((1-freespace/totalspace)*100,2) "使用率%"
from
 (select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df,
 (select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name)  fs
where df.tablespace_name=fs.tablespace_name order by 1;

表空间所有数据文件
set heading off;
set long 90000;
col tablespace_name format a30
col file_name format a80
select tablespace_name, file_name from dba_data_files order by 1,2

表空间总数据文件大小
select round(sum(bytes)/1024/1024) totalspace from dba_data_files where tablespace_name = 'TS_200607_DATA';

改变数据文件的大小
select 'alter database datafile '''||file_name||''' resize 28M;'
from dba_data_files
where tablespace_name like upper('TS_200607_DATA') and bytes!=1024*1024*24

查看回滚段命中率情况
select
    rn.name,rs.gets 被访问次数,
    rs.waits 等待回退段块的次数,
    (rs.waits/rs.gets)*100 命中率
from v$rollstat rs, v$rollname rn;

查看表的分区
select distinct partition_name from user_tab_partitions order by 1;

查看表空间的所有表（但是表可能会跨表空间）
select distinct segment_name from user_segments where tablespace_name='TS_STAT_DATA';

col segment_name format a25
select table_name, index_name from user_indexes where table_name in (
select  segment_name
from    user_segments
where   tablespace_name='TS_ORDER_DATA_2006Q4'
    and segment_name like 'T_ORDER_USER_%'
group by segment_name)
order by 1

select dbms_metadata.get_ddl('INDEX','IDX_FLOW_ACCT_200701_FEETYPE','ORCL') from dual;

表空间所有表的所有索引
select table_name, index_name from user_indexes
where   table_name in (select distinct segment_name from user_segments where tablespace_name='TS_STAT_DATA')
    and index_name like 'IDX_%'
order by 1;

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool clear.sql
select 'drop index '||index_name||';' from user_indexes
where   table_name in (select distinct segment_name from user_segments where tablespace_name='TS_STAT_DATA')
    and index_name like 'IDX_%'
order by 1;
spool off;

select table_name, index_name from user_indexes
    where index_name in (
    select distinct segment_name from user_segments where tablespace_name='TS_TEST') order by 1,2;

select 'drop index '||index_name||';' from user_indexes
    where index_name in (
    select distinct segment_name from user_segments where tablespace_name='TS_TEST');

-- 查看建表语句
set heading off;
set echo off;
Set pages 999;
set long 90000;
spool get_single.sql
select dbms_metadata.get_ddl('TABLE','T_SYS_STATION','ORCL') from dual;
--select dbms_metadata.get_ddl('INDEX','INDXX_PQZJYW','SCOTT') from dual;
spool off;

ORACLE数据库的PL/SQL语句执行的优化器，有基于代价的优化器（CBO）和基于规则的优化器（RBO）。
    RBO的优化方式，依赖于一套严格的语法规则，只要按照规则写出的语句，不管数据表和索引的内容是否发生变化，不会影响PL/SQL语句的"执行计划"。
    CBO自ORACLE7版被引入，ORACLE自7版以来采用的许多新技术都是只基于CBO的，如星型连接排列查询，哈希连接查询，反向索引，索引表，分区表和并行查询等。CBO计算各种可能"执行计划"的"代价"，即cost，从中选用cost最低的方案，作为实际运行方案。各"执行计划"的cost的计算根据，依赖于数据表中数据的统计分布，ORACLE数据库本身对该统计分布是不清楚的，须要分析表和相关的索引，才能搜集到CBO所需的数据。
    CBO是ORACLE推荐使用的优化方式，要想使用好CBO，使SQL语句发挥最大效能，必须保证统计数据的及时性。
    统计信息的生成可以有完全计算法和抽样估算法。SQL例句如下：
    完全计算法： analyze table abc compute statistics;
    抽样估算法(抽样20%)： analyze table abc estimate statistics sample 20 percent;
    对表作完全计算所花的时间相当于做全表扫描，抽样估算法由于采用抽样，比完全计算法的生成统计速度要快，如果不是要求要有精确数据的话，尽量采用抽样分析法。建议对表分析采用抽样估算，对索引分析可以采用完全计算。
sqlplus scott/tiger << EOF
    set pagesize 5000
    set heading off
    SPOOL ANALYTAB.SQL
    SELECT "ANALYZE TABLE SCOTT."||TABLE_NAME||" ESTIMATE STATISTICS SAMPLE 20 PERCENT ;" FROM USER_TABLES；
    SPOOL OFF
    SPOOL ANALYIND.SQL
    SELECT "ANALYZE TABLE SCOTT."||TABLE_NAME||" ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR ALL INDEXES;" FROM USER_TABLES；
    SPOOL OFF
    SPOOL ANALYZE.LOG
    @ANALYTAB.SQL
    @ANALYIND.SQL
    SPOOL OFF
    EXIT

ALTER DATABASE ADD LOGFILE GROUP 4('/data1/oradata/ORCL/sys/redo04.log') SIZE 51200K reuse;
ALTER DATABASE ADD LOGFILE GROUP 5('/data2/oradata/ORCL/sys/redo05.log') SIZE 51200K reuse;
ALTER DATABASE ADD LOGFILE GROUP 6('/data3/oradata/ORCL/sys/redo06.log') SIZE 51200K reuse;

ALTER DATABASE ADD LOGFILE GROUP 1 (
'/data1/oradata/ORCL/sys/redo11.log',
'/data2/oradata/ORCL/sys/redo12.log',
'/data3/oradata/ORCL/sys/redo13.log',
'/data4/oradata/ORCL/sys/redo14.log') SIZE 1048576K reuse;

实现：
1、首先查看系统有哪些回滚段及其状态。

col owner format a20
col status format a10
col segment_name format a20
col tablespace_name format a20
col segment format a30
SELECT OWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM(BYTES)/1024/1024 M
 FROM DBA_SEGMENTS
 WHERE SEGMENT_TYPE='ROLLBACK'
 GROUP BY OWNER,SEGMENT_NAME,TABLESPACE_NAME
 /

DROP SEQUENCE "ORCL"."SEQ_SYS_ALARM_ID";
CREATE SEQUENCE "ORCL"."SEQ_SYS_ALARM_ID"
  start with 1
  maxvalue 99999999
  minvalue 1
cycle
order;
DROP SEQUENCE "SEQ_SYS_FAV_ID";
CREATE SEQUENCE "SEQ_SYS_FAV_ID"
increment by 1
start with 1
  maxvalue 999999999
  minvalue 1
cycle
order;

set pagesize 0;
set heading off;
col TABLESPACE_NAME format a16;
col file_name format a60;
select TABLESPACE_NAME, file_name from dba_data_files order by 1,2;

select 'drop TABLESPACE '||TABLESPACE_NAME||' including contents and datafiles;' from dba_tablespaces order by 1;

create undo tablespace TS_UNDO DATAFILE
'/data2/oradata/sys/ts_undo.dbf' size 6G reuse AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;

alter system set undo_tablespace=TS_UNDO;

create temporary tablespace TS_TEMP TEMPFILE
'/data2/oradata/sys/tmp_file.dbf' size 10G reuse AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL uniform size 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TSTEMP;

create bigfile tablespace TS_TEST DATAFILE
'/data2/oradata/sys/test.dbf' size 10G reuse AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL segment space management auto;

create bigfile tablespace TBS_SYS
datafile '/data1/oradata/ORCL/df_sys.dbf' size 2G reuse AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
 NOLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;