DBA常用sql

转载http://space.itpub.net/?uid-26870952-action-viewspace-itemid-721816

查看并行进程
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
;

 

---     客户端机器名, 数据库用户名, 进程状态, 进程类型, 会话ID, 序列号, 系统进程ID
select s.terminal,s.username,s.status,s.action,s.sid,s.serial#,p.spid
from v$session s,v$process p
where s.paddr=p.addr
order by s.terminal,s.sid

select s.terminal,s.client_info,s.logon_time,s.program,s.username,s.schemaname,s.status,s.action,s.sid,s.serial#,p.spid
from v$session s,v$process p
where s.paddr=p.addr
 and s.client_info like '%192.168.3.1%' and s.schemaname='SCOTT'
order by s.terminal,s.sid

;

 

-- 删除进程
--------------------

alter system kill session 'sid,serial#'
alter system kill session '9,203'

看进程运行多久
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'
 ;