Get column comments:
select * from user_col_comments
среда, 28 октября 2015 г.
пятница, 31 июля 2015 г.
How to disable and enable depended constraints
select --'truncate table '|| TABLE_NAME || ';' 'alter table ' || TABLE_NAME || ' enable constraint ' || CONSTRAINT_NAME || ';' from ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'R' and STATUS <> 'ENABLED' --and R_CONSTRAINT_NAME = 'C_MEDHISTCONS_PK' order by TABLE_NAME select 'alter table ' || TABLE_NAME || ' disable constraint ' || CONSTRAINT_NAME || ';' from ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'R' and R_CONSTRAINT_NAME = 'C_DCSPECS_PK'
вторник, 28 июля 2015 г.
How to create indexes by foreign key
select 'create index I_' || substr(C.CONSTRAINT_NAME, 3) || ' on ' || C.TABLE_NAME || '(' ||
(select rtrim(xmlagg(xmlelement(e, CC.COLUMN_NAME,',').extract('//text()') order by CC.COLUMN_NAME).GetClobVal(),',')
from ALL_CONS_COLUMNS CC
where CC.OWNER = C.OWNER
and CC.TABLE_NAME = C.TABLE_NAME
and CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME) || ');'
from (
select distinct C.OWNER, C.TABLE_NAME, C.CONSTRAINT_NAME
from
ALL_CONSTRAINTS C,
ALL_CONS_COLUMNS CC
where C.CONSTRAINT_TYPE = 'R'
and C.OWNER = 'PARUS'
and CC.OWNER = C.OWNER
and CC.TABLE_NAME = C.TABLE_NAME
and CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
and not CC.COLUMN_NAME in ('COMPANY', 'VERSION')
and CC.CONSTRAINT_NAME like 'C\_%' escape '\'
and not exists (
select *
from ALL_IND_COLUMNS IC
where IC.TABLE_OWNER = C.OWNER
and IC.TABLE_NAME = CC.TABLE_NAME
and IC.COLUMN_NAME = CC.COLUMN_NAME)) C
пятница, 10 июля 2015 г.
How to reverse string
select listagg(C) within group(order by RN desc) rvs from ( select substr(T.COL, rownum, 1) C, rownum RN from (select 'Привет' COL from DUAL) T connect by level <= length(T.COL))
среда, 3 июня 2015 г.
How to profile PL/SQL procedure
BEGIN
sys.DBMS_PROFILER.start_profiler('MAZDENYSH');
PR_FORM_0503169_2015_BASE(343162, int2date(1, 4, 2015), null, 0, null, 1, 1, null, null);
sys.DBMS_PROFILER.stop_profiler;
commit;
END;
/
select RUNID, RUN_DATE, RUN_COMMENT from PLSQL_PROFILER_RUNS;
RUNID RUN_DATE RUN_COMMENT
---------- -------- ----------------------
25 01.06.15 MAZDENYSH
26 01.06.15 MAZDENYSH
27 02.06.15 MAZDENYSH
select * from plsql_profiler_units where runid = 27;
RUNID UNIT_NUMBER UNIT_TYPE UNIT_OWNER UNIT_NAME UNIT_TIMESTAMP TOTAL_TIME SPARE1 SPARE2
---------- ----------- -------------------------------- -------------------------------- -------------------------------- -------------- ---------- ---------- ----------
27 1 ANONYMOUS BLOCK 29.11.01 0
27 2 FUNCTION PARUS INT2DATE 19.02.13 0
27 3 FUNCTION PARUS S2D 19.02.13 0
27 4 PACKAGE BODY PARUS PKG_STD 19.02.13 0
27 5 PROCEDURE PARUS PR_FORM_0503169_2015_BASE 02.06.15 0
27 6 PACKAGE BODY PARUS PKG_OPTIONS 24.04.15 0
27 7 PACKAGE BODY PARUS PKG_ALERT 24.04.15 0
27 8 FUNCTION PARUS UTILIZER 20.03.14 0
27 9 FUNCTION PARUS GET_OPTIONS_NUM 19.02.13 0
27 10 PACKAGE BODY PARUS PKG_SESSION 24.04.15 0
27 11 PROCEDURE PARUS FIND_VERSION_BY_COMPANY 24.04.15 0
27 12 PROCEDURE PARUS FIND_VERSION_BY_COMPANY_EX 24.04.15 0
27 13 PROCEDURE PARUS FIND_JURPERSONS_MAIN 19.02.13 0
27 14 FUNCTION PARUS CR 19.02.13 0
27 15 PACKAGE BODY PARUS PKG_CONDITIONS 24.04.15 0
27 16 FUNCTION PARUS STRTOK 19.02.13 0
27 17 FUNCTION PARUS F_QUOTED_STR 19.02.13 0
27 18 FUNCTION PARUS STRCNT 19.02.13 0
27 19 FUNCTION PARUS F_ESCAPE_STR 19.02.13 0
27 20 FUNCTION PARUS D2S 19.02.13 0
27 21 FUNCTION PARUS GEN_IDENT 19.02.13 0
21 rows selected
select pu.unit_name, pd.line#, pd.total_occur passes,
round(pd.total_time / 1000000000,5) total_time, us.text text, max(round(pd.total_time / 1000000000,5)) over() mxx
from plsql_profiler_data pd, plsql_profiler_units pu, user_source us
where pd.runid = &rpt_runid
and pd.unit_number = &rpt_unitid
and pd.runid = pu.runid
and pd.unit_number = pu.unit_number
and us.name = pu.unit_name
and us.line = pd.line#
and us.type in ('PACKAGE BODY','PROCEDURE','FUNCTION');
rpt_runid = 27
rpt_unitid = 5
вторник, 26 мая 2015 г.
How to find out corrupt index
ORA-01578: ORACLE data block corrupted (file # 8, block # 22921) ORA-01110: data file 8: '/data/app/oracle/oradata/MYSRVR/datafile/o1_mf_mysrvr_88m82mdj.dbf' select segment_name,segment_type,tablespace_name, owner from sys.dba_extents where file_id=8 and 22921 between block_id and block_id + blocks -1;
четверг, 12 марта 2015 г.
How to find out list of session which are blocking a temporary table
select S.username, S.osuser, S.machine, L.*, O.object_name from SYS.V_$LOCK L, SYS.V_$SESSION S, SYS.ALL_OBJECTS O where L.TYPE = 'TO' and L.SID = S.SID and L.ID1 = O.object_id and O.object_name = 'MICACTIVISBF'
вторник, 17 февраля 2015 г.
Parse string to a query
with test as (select 'ABC,DEF,GHI,JKL,MNO' str from dual) select regexp_substr (str, '[^,]+', 1, rownum) split from test connect by level <= length (regexp_replace (str, '[^,]+')) + 1
понедельник, 26 января 2015 г.
Thereapy for Zelenograd
-- Clearing UPDATELIST table
--1 930 637
begin
for rec in (
select T.ROWID TR, row_number() over (order by T.RN) RRN
from UPDATELIST T
where to_number(to_char(T.MODIFDATE, 'yyyy')) < 2015
order by RN)
loop
delete UPDATELIST where ROWID = rec.TR;
if mod(rec.RRN, 100) = 0 then
commit;
end if;
end loop;
commit;
end;
/
create tablespace PARUS_IND2 datafile
'D:\BASES\PZAO1\PARUS_IND2.DAT'
size 10M autoextend on next 1M
maxsize unlimited logging online;
select 'alter index ' || SEGMENT_NAME || ' rebuild tablespace PARUS_IND2 nologging;'
from DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'PARUS_IND'
and SEGMENT_TYPE = 'INDEX'
and OWNER = 'PARUS'
drop tablespace PARUS_IND including contents and datafiles;
пятница, 23 января 2015 г.
How to calculate size of tablespaces
select FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB, BLOCKS from ( select FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS from DBA_DATA_FILES union all select FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS from DBA_TEMP_FILES order by TABLESPACE_NAME, FILE_NAME );
Recreation indexes' tablespace
create tablespace PARUS_IND2 datafile 'D:\BASES\PZAO1\PARUS_IND2.DAT' size 10M autoextend on next 1M maxsize unlimited logging online; select 'alter index ' || SEGMENT_NAME || ' rebuild tablespace PARUS_IND2 nologging;' from DBA_SEGMENTS WHERE TABLESPACE_NAME = 'PARUS_IND' and SEGMENT_TYPE = 'INDEX' and OWNER = 'PARUS' drop tablespace PARUS_IND including contents and datafiles;
Подписаться на:
Сообщения (Atom)