DBA常用SQL

檢視並行程式
select a.QCinst_id,a.QCSID,count(*)
from gv$px_session a
where a.QCSID <> a.sid
group by a.QCinst_id,a.QCSID
order by 2,1
;

 

select p.SPID
from gv$session s,gv$process p
where s.INST_ID = 2
and s.SID = 462
and s.PADDR = p.ADDR
;

 

看程式執行多久
SELECT SID,
       decode(totalwork, 0, 0, round(100 * sofar / totalwork, 2)) "Percent",
       message "Message",
       start_time,
       elapsed_seconds,
       time_remaining
  from v$Session_longops
 where (sid = 305 and serial# = 20264)
 ORDER BY SID
 ;
 
 看錶空間使用情況
 select t.tablespace_name,sum(t.bytes)/1024/1024 "size msg"
,sum(f.bytes)/1024/1024 "free msg"
,round((sum(t.bytes)/1024/1024-sum(f.bytes)/1024/1024)/(sum(t.bytes)/1024/1024) * 100,2) "usd pct"
from (select t.tablespace_name,sum(t.bytes) bytes
from dba_data_files t
group by t.tablespace_name) t,
(select f.tablespace_name,sum(f.bytes) bytes
from dba_free_space f
group by f.tablespace_name) f
where t.tablespace_name = f.tablespace_name
group by t.tablespace_name
order by 4 desc
;

看正在執行的SQL
select t.SQL_TEXT,t.*
from v$sqltext t,v$session s
where t.ADDRESS=s.SQL_ADDRESS
  and t.HASH_VALUE=s.SQL_HASH_VALUE
  and s.SID ='36'
  and s.SERIAL# = '6724'
order by t.PIECE
;

表空間維護
CREATE TABLESPACE tbs_1
    LOGGING
    DATAFILE
    '/dev/rlv_ora_dw_1034' SIZE 8191M REUSE,
    '/dev/rlv_ora_dw_2034' SIZE 8191M REUSE,
    '/dev/rlv_ora_dw_3034' SIZE 8191M REUSE,
    '/dev/rlv_ora_dw_4034' SIZE 8191M REUSE
    EXTENT MANAGEMENT LOCAL
    UNIFORM. SIZE 32m
    SEGMENT SPACE MANAGEMENT MANUAL
;

ALTER TABLESPACE tbs_1
    ADD
    DATAFILE
    '/dev/rlv_ora_dw_1257' SIZE 8191M REUSE,
    '/dev/rlv_ora_dw_2257' SIZE 8191M REUSE,
    '/dev/rlv_ora_dw_3257' SIZE 8191M REUSE,
    '/dev/rlv_ora_dw_4257' SIZE 8191M REUSE
;

修改系統引數
ALTER SYSTEM SET parallel_max_servers=112 SCOPE=SPFILE;

ALTER SYSTEM SET pga_aggregate_target='128M' SCOPE=SPFILE;

ALTER SYSTEM SET db_cache_size='128M' SCOPE=SPFILE;

ALTER SYSTEM SET large_pool_size='1M' SCOPE=SPFILE;

ALTER SYSTEM SET sga_max_size='256M' SCOPE=SPFILE;

資源計劃管理
begin
dbms_resource_manager.create_simple_plan(simple_plan =>'test1_plan',
consumer_group1 =>'test_group1' ,group1_cpu =>80 ,
consumer_group2 =>'test_group2' ,group2_cpu =>20);
end;
/

BEGIN
 dbms_resource_manager_privs.grant_switch_consumer_group(
  grantee_name => 'scott',
  consumer_group => 'TEST_GROUP1',
  grant_option => FALSE
 );
END;
/
BEGIN
 dbms_resource_manager.set_initial_consumer_group(
  user => 'scott',
  consumer_group => 'SYS_GROUP'
 );
END;
/

begin
dbms_resource_manager.switch_consumer_group_for_sess(session_id => 316,session_serial => 54798,consumer_group => 'low_group');
end;
/

 

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = SYSTEM_PLAN;

檢視錶定義
execute dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'STORAGE',false)

select dbms_metadata.get_ddl('TABLE','TB_DW_SE_NETINTER_SUM')
from dual;

imp/exp
imp scott/tiger ignore=yes buffer=327680 fromuser=hr touser=scott tables=tb_dw_su_gsm_voice_0802:gsm_voice_20080215 file=hr_tb_dw_su_gsm_voice_0802:gsm_voice_20080215_data001.dmp,hr_tb_dw_su_gsm_voice_0802:gsm_voice_20080215_data002.dmp

從tbs_1 move table 和 partition到tbs_2
select  'alter table '||t.owner||'.'||t.segment_name||' move tablespace tbs_2 parallel (degree 10) nologging;'
from dba_segments t
where t.tablespace_name = 'TBS_1'
 and t.segment_type = 'TABLE'
union all
select 'alter table '||t.owner||'.'||t.segment_name||' move partition '||t.partition_name|| ' tablespace tbs_2 parallel (degree 10) nologging;'
from dba_segments t
where t.tablespace_name = 'TBS_1'
 and t.segment_type = 'TABLE PARTITION'
 ;