DBA常用sql(二)

 session状态：
STATUS VARCHAR2(8) Status of the session:
ACTIVE - Session currently executing SQL
INACTIVE - sql及其session没有释放或正常退出......
KILLED - Session marked to be killed
CACHED - Session temporarily cached for use by Oracle*XA
SNIPED - Session inactive, waiting on the client

查询数据库中持锁和等待锁的用户信息
select  distinct o.object_name, sh.sid "SID", sh.SERIAL# "SERIAL", sh.username||'('||sh.sid||','||sh.SERIAL#||')' "Holder",
        sw.username||'('||sw.sid||','||sw.SERIAL#||')' "Waiter",
        decode(lh.lmode, 1, 'null', 2,
              'row share', 3, 'row exclusive', 4,  'share',
              5, 'share row exclusive' , 6, 'exclusive')  "Lock Type"
  from v$session sw, v$lock lw,all_objects o,  v$session sh, v$lock lh
where lh.id1  = o.object_id
  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = 'TM'
  and  lw.type = 'TM';

select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Wait' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where a.username is not null
and a.lockwait = b.kaddr
and c.hash_value =a.sql_hash_value
union
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Lock' "Status", a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.username is not null
and a.sid = b.sid
and b.request=0
and c.hash_value =a.sql_hash_value;

怎么在oracle中，把一个表空间的所有索引换到另一个表空间呢？

 。建立一个select_index.sql，里面写入以下内容。
SELECT 'ALTER index ' ||index_NAME|| ' REBUILD TABLESPACE INDEX_ISTQZDEV;' 
FROM USER_INDEXES; 
2。执行sqlplus username/password @select_index.sql > index.log
3。gawk '/ALTER index/ {print $0}' > alter_index.sql
4。在执行一下 sqlplus username/password @alter_index.sql .
5。表空间的所有索引都到另一个专门储存index的表空间里面去了。

 监控数据库性能的语句

 
监控数据库性能的SQL语句
1. 监控事例的等待
  select   event,
    sum(decode(wait_Time,0,0,1)) "Prev", 
    sum(decode(wait_Time,0,1,0)) "Curr",
    count(*) "Tot" 
       from  v$session_Wait 
       group by event 
       order by 4;
  
2. 回滚段的争用情况
      select   name, waits, gets, waits/gets "Ratio" 
       from  v$rollstat a, v$rollname b 
       where  a.usn = b.usn; 
  
3. 监控表空间的 I/O 比例
      select   df.tablespace_name name,df.file_name "file",f.phyrds pyr,
        f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
       from  v$filestat f, dba_data_files df
       where  f.file# = df.file_id
       order by df.tablespace_name;
  
4. 监控文件系统的 I/O 比例
      select   substr(a.file#,1,2) "#", substr(a.name,1,30) "Name", 
        a.status, a.bytes, b.phyrds, b.phywrts 
       from  v$datafile a, v$filestat b 
       where  a.file# = b.file#; 
  
5.在某个用户下找所有的索引
      select   user_indexes.table_name, 
        user_indexes.index_name,
        uniqueness, 
        column_name
       from  user_ind_columns, user_indexes
       where  user_ind_columns.index_name = user_indexes.index_name and 
        user_ind_columns.table_name = user_indexes.table_name 
       order by user_indexes.table_type, user_indexes.table_name,
        user_indexes.index_name, column_position;
 
10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%
      SELECT   name, gets, misses, immediate_gets, immediate_misses,
        Decode(gets,0,0,misses/gets*100) ratio1,
        Decode(immediate_gets+immediate_misses,0,0,
         immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
       FROM  v$latch 
       WHERE  name IN ('redo allocation', 'redo copy'); 

11.  监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size 
      SELECT   name, value 
       FROM  v$sysstat 
       WHERE  name IN ('sorts (memory)', 'sorts (disk)'); 
  

12. 监控当前数据库谁在运行什么SQL语句
      SELECT   osuser, username, sql_text 
       from  v$session a, v$sqltext b
       where  a.sql_address =b.address order by address, piece;

使用 ANALYZE 命令检测移植和链接
ANALYZE TABLE sales.order_hist COMPUTE STATISTICS;
SQL> SELECT num_rows, chain_cnt FROM dba_tables
2 WHERE table_name=‘ORDER_HIST’;
NUM_ROWS CHAIN_CNT
--------- ---------
168 102

SQL> ANALYZE TABLE sales.order_hist LIST CHAINED ROWS;
Table analyzed.
SQL> SELECT owner_name, table_name, head_rowid
2 FROM chained_rows
3 WHERE table_name = 'ORDER_HIST';
OWNER_NAME TABLE_NAME HEAD_ROWID
---------- ---------- ------------------
SALES ORDER_HIST AAAAluAAHAAAAA1AAA
SALES ORDER_HIST AAAAluAAHAAAAA1AAB
...

计算多个缓冲池的命中率
SQL> SELECT name,
1 - (physical_reads / (db_block_gets +
consistent_gets)) "HIT_RATIO"
2 FROM sys.v$buffer_pool_statistics
3 WHERE db_block_gets + consistent_gets > 0;
NAME HIT_RATIO
------------------ ----------
KEEP .983520845
RECYCLE .503866235
DEFAULT .790350047
建表语句
CREATE TABLE employee(
id NUMBER(7),
last_name VARCHAR2(25),
dept_id NUMBER(7))
PCTFREE 20 PCTUSED 50
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE data;

建立索引语句:
CREATE INDEX summit.employee_last_name_idx
ON summit.employee(last_name)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;

建立位图索引语句:
CREATE BITMAP INDEX orders_region_id_idx
ON summit.orders(region_id)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;

重建索引:
ALTER INDEX summit.orders_region_id_idx REBUILD
TABLESPACE indx02;

使用 APPEND 提示可调用直接装载插入命令如下所示
INSERT /*+APPEND */ INTO [ schema. table
[ [NO]LOGGING ]
sub-query;

清除联机日志文件
alter database clear logfile;

1:索引在线更新：
ALTER INDEX owner.tablename REBUILD ONLINE ；
不能用带表空间信息。如：This one works fine within PL/SQL:
ALTER INDEX LISADMIN.Isample REBUILD TABLESPACE INDX
ALTER INDEX LISADMIN.Isample REBUILD ONLINE TABLESPACE INDX

2:删除重复行，保留最大值的行信息：
delete from 'table'
where (a,b,c) not in (select a,b,max(c) from 'table' group by a,b);

3：分区参数信息的更新
CREATE TABLE WDZW1 (
WDBH NUMBER (10) NOT NULL,
WDZW CLOB)
TABLESPACE TESTSPACE PCTFREE 5 PCTUSED 90 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 512 NEXT 512 )
PARTITION BY RANGE (WDBH)
(
PARTITION WDZW11 VALUES LESS THAN (100000)
PCTFREE 5 PCTUSED 90 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 512k NEXT 512k PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 999),
PARTITION WDZW21 VALUES LESS THAN (200000)
PCTFREE 5 PCTUSED 90 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 512k NEXT 512k PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 999）
）；
CREATE UNIQUE INDEX PK_WDZW ON
WDZW1(WDBH)
TABLESPACE TESTSPACE PCTFREE 10 STORAGE(INITIAL 512 NEXT 512 PCTINCREASE 0 ) ；
查看分区扩展信息：

SQL> select INITIAL_EXTENT, NEXT_EXTENT ,MIN_EXTENTS ,MAX_EXTENTS from dba_segments
2 where segment_type = 'LOB PARTITION';

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
-------------- ----------- ----------- -----------
20480 20480 1 249
20480 20480 1 249

修改扩展分区：
alter table wdzw1 modify lob(wdzw) (storage (MAXEXTENTS unlimited))

修改后结果信息：

SQL> select INITIAL_EXTENT, NEXT_EXTENT ,MIN_EXTENTS ,MAX_EXTENTS from dba_segments
2 where segment_type = 'LOB PARTITION';

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
-------------- ----------- ----------- -----------
20480 20480 1 2147483645
20480 20480 1 2147483645

判断字段中是否包含中文的方法

SQL> create table test(a varchar2(10));

Table created.

SQL> insert into test values('鸟');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test values('深刻');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into test values('aaa');

1 row created.

SQL> insert into test values('bbb');

1 row created.

SQL> commit;

Commit complete.

SQL> select a from test;

A
----------
鸟
深刻
aaa
bbb

SQL> select a from test where asciistr(a) like '%/%';

A
----------
鸟
深刻

SQL> select a from test where asciistr(a) not like  '%/%';

A
----------
aaa
bbb