잡다쿼리

KO16KSC5601, WE8DEC, US7ASCII

select 'drop table '||tname||' cascade constraint;' from tab

select 'drop sequence '||sequence_name||';' from user_sequences

select 'create sequence '||sequence_name||' increment by 1 start with '||(last_number + 1) ||' maxvalue 9999999999 nocycle;'
from user_sequences@sims

select 'create table '||tname||' as select * from '||tname||'@sims;' from tab@sims


create sequence SEQ_AEMT_FCLT increment by 1 start with 1 maxvalue 9999999999 nocycle;


select substr(table_name,4,20) from user_tables
minus
select substr(sequence_name,5,20) from user_sequences


select 'create sequence SEQ_'||substr(tname,4,20)||' increment by 1 start with 5000 maxvalue 9999999999 nocycle;' from tab
where substr(tname,1,2) = 'SA'

select 'create sequence SEQ_'||substr(tname,4,20)||' increment by 1 start with 1 maxvalue 9999999999 nocycle;' from tab
minus
select 'create sequence SEQ_'||substr(sequence_name,5,20)||' increment by 1 start with 1 maxvalue 9999999999 nocycle;' from user_sequences

create sequence SQ_ACTN_WRT increment by 1 start with 1 maxvalue 9999999999 nocycle;

select distinct 'update sa_dong set amd_cd = '||b.adm_cd||','||'lawblk_cd='||B.lawblk_cd||' where dong_id ='||
a.dong_id||';' , a.ku_id, a.dong_name, b.adm_cd, a.actv, B.lawblk_cd
from sa_dong a, KU_DONG b, sa_ku c
where b.stt <> '인천시'
and a.ku_id=c.ku_id
and b.stt||b.dtt||b.blk = Decode(c.ku_gubun,'0','서울시','1','경기도')||c.ku_name||a.dong_name
--order by a.dong_id


select 'update sa_dong set lawblk_cd='||''''||aa.adm_blk_cd||''''||' where dong_id = '||bb.dong_id||';'
from (select stt, dtt, blk, adm_blk_cd from dong where blk_gu ='1' and blk is not null ) AA,

(select a.dong_id, a.ku_id, b.ku_name, a.dong_name ,a.adm_cd, a.lawblk_cd
from sa_dong A, sa_ku b where a.ku_id = b.ku_id) BB

where aa.dtt||aa.blk = bb.ku_name||bb.dong_name


alter ROLLBACK SEGment sysrol online


create public ROLLBACK SEGment rb2 tablespace rbs
storage (initial 100k next 100K minextents 6 maxextents 121 optimal 600k)

create public ROLLBACK SEGment rb3 tablespace rbs
storage (initial 500k next 500K minextents 6 maxextents 121 optimal 3M)




Lock 체크 쿼리
select A.sid, B.session_id, A.schemaname, A.osuser, A.program, B.object_id, C.object_name
from v$session A, V$LOCKED_OBJECT B, dba_objects C
where A.sid = B.session_id
And C.OBJECT_ID= B.OBJECT_ID


테이블 싸이즈 수정
alter table TALBLE_NAME STORAGE( NEXT 500K MAXEXTENTS 121 PCTINCREASE 0 )


Export
svrmgr> !exp userid=sims/sims@sims file= ~ .dmp full=y
svrmgr> !imp userid=sims/sims@sims file= ~ .dmp owner=sims tables=대문자Table명 ignore=y



alter table SA_PRTN_CORPL3DG4437000 rename to SA_PRTN_CORP


alter index SYS_C004719 STORAGE( NEXT 1M MAXEXTENTS 121 PCTINCREASE 20 );
alter index SYS_C004877 STORAGE( NEXT 1M MAXEXTENTS 121 PCTINCREASE 20 );

--datafile삭제
startup mount
alter database datafile '*.dbf' offline drop;
alter database open;


--유져완전 죽이기(테이블 없앤 다음 하기)
select 'Drop '||object_type||' '||
Decode(object_type,'TABLE',object_name||' cascade constraint;',object_name||';') from user_objects
where object_type <> 'INDEX';


--Rollback Segment Size 변경
create public ROLLBACK SEGment rb2 tablespace rbs
storage (initial 100k next 100K minextents 6 maxextents 121 optimal 600k);