并行使用DML
ALTER SESSION ENABLE PARALLEL DML;
UPDATE/*+ parallel(t 10)*/ SAD_BOQ_LINES_T t SET t.CFG_MODEL_ID='';
UPDATE/*+ append parallel 10*/ SAD_BOQ_LINES_T t SET t.CFG_MODEL_ID='';
INSERT /*+append nologging*/ INTO sad.temp_sparts_th
SELECT /*+ parallel(t 20)*/ t.spart_line_id,t.version,t.distributed_version,t.org_spart_line_id FROM sad.sad_sparts_th t;
COMMIT;
查看回滚段
SELECT t.TABLESPACE_NAME,t.STATUS,ROUND(SUM(t.BLOCKS)*8/1024,2) FROM Dba_Undo_Extents t
GROUP BY t.TABLESPACE_NAME,t.STATUS
select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))
"Estimated time to complete"
from v$fast_start_transactions;
查看用户锁表
select s.inst_id
,s.sid
,s.serial#
,locked_mode
,object_name
,l.process
,s.username
,s.osuser
,s.status
,s.logon_time
from gv$locked_object l,dba_objects o,gv$session s
where l.object_id=o.object_id
and l.session_id=s.sid
and l.inst_id=s.inst_id ;
select /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/
行迁移:一行记录初始插入时可以存储在一个块中,但因后来更新导致行长增加,而块的自由空间已经完全满了,此时产生行迁移,将整行的数据迁移到一个新的数据块上,而将该行原先的空间只放一个指针,指向该行的新的位置。
解决办法:将块的pctfree的值调高
行链接:当第一次插入数据时一个块就不能存下一行记录的情况下,系统将使用链接在多个块中存储该记录。
解决办法:将块的大小调大。
查看行迁移的sql
select chain_cnt from dba_tables where table_name='你要查的表';
你用select s.sid,s.blocking_session,s.event from gv$session s where s.event like '%cache%';
dengfang WX175933(dwx175933) 2013-11-11 15:29:15
如果blocking_session 这列有值,就用select sid,serial# from gv$session where sid=blocking_session 将会话kill掉就好啦
a. 获取单个的建表和建索引的语法
set heading off;
set echo off;
Set pages 999;
set long 90000;
spool DEPT.sql
select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
spool off;
b.获取一个SCHEMA下的所有建表和建索引的语法,以scott为例:
set pagesize 0
set long 90000
set feedback off
set echo off
spool scott_schema.sql
connect scott/tiger;
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;
spool off;
c. 获取某个SCHEMA的建全部存储过程的语法
connect brucelau /brucelau;
spool procedures.sql
select
DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name)
from
user_objects u
where
object_type = 'PROCEDURE';
spool off;
另:
dbms_metadata.get_ddl('TABLE','TAB1','USER1')
三个参数中,第一个指定导出DDL定义的对象类型(此例中为表类型),第二个是对象名(此例中即表名),第三个是对象所在的用户名。.
要自己写程序了,执行一段SQL,让SQL查询结果就是我们想要的SQL脚本:
如下:
select
'INSERT INTO B_STATTEMPLATE ( N_ID,C_NAME, C_KBH, N_PRINT, N_TYPE, N_APP, N_VALID ) '
|| 'Values (' || To_Char(N_ID) ||',''' || C_NAME || ''', ''' || C_KBH || ''', '
|| To_Char( N_PRINT ) || ', ' || To_Char( N_TYPE ) || ', ' || '0, 0 );'
from b_stattemplate
BEGIN
DBMS_JOB.remove(jobid);
COMMIT;
END;
SELECT regexp_substr('sv01,sv02,sv03', '[^,]+', 1, rownum)
FROM dual
CONNECT BY rownum <= length('sv01,sv02,sv03') -
length(REPLACE('sv01,sv02,sv03', ',', '')) + 1