Query Varie
Jump to navigation
Jump to search
Job Immediato
begin
dbms_scheduler.create_job(
job_name => 'il_mio_job',
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ''create index owner.index_name on owner.table(col01) online nologging''; end;',
enabled => TRUE,
auto_drop => TRUE,
comments => 'crea indice'
);
end;
/
Indici
Elenco Indici
set pagesize 200
set linesize 140
col column_name for a30
col pos for 99
col table_name for a30
col index_name for a35
select table_owner || '.' || table_name table_name, index_owner || '.' || index_name index_name, column_name, column_position pos
from dba_ind_columns
where table_name=upper('&&1')
order by 1,2,4;
set pagesize 200
set linesize 140
set autotrace off
col column_name for a30
col pos for 99
col table_name for a38
col index_name for a43
col tbs for a20
select c.table_owner || '.' || c.table_name table_name, c.index_owner || '.' || c.index_name || ' ' || i.generated index_name,
c.column_name, c.column_position pos, i.tablespace_name tbs
from dba_ind_columns c
inner join dba_indexes i on
(c.table_owner=i.table_owner and c.table_name=i.table_name and
c.index_owner=i.owner and c.index_name=i.index_name)
where c.table_name=upper('&&1')
order by 1,2,4;
col owner for a40
col tablespace_name for a40
select owner,tablespace_name from dba_segments where segment_name=upper('&&1');
indici fuori posto
select owner,index_name,table_owner,table_name from dba_indexes where table_owner!=owner;
Processi e sessioni
Elenco processi di background
select s.sid, s.process, s.program, p.name from v$session s join v$bgprocess p using (paddr) ;
sessioni
set linesize 200 set pagesize 200 set autotrace off col machine for a35 col username for a20 col program for a20 col sid_ser for a15 col spid for a10 alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS'; select s.sid ||','||s.serial# as sid_ser,p.spid, s.username,s.machine,s.sql_id,s.prev_sql_id, s.logon_time,substr(s.program,1,20) as program,s.seconds_in_wait as siw from v$session s left outer join v$process p on (p.addr=s.paddr) where s.type='USER' and s.status='ACTIVE' order by s.logon_time;
--
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,
s.username,substr(s.machine,1,20) as machine,s.sql_id,s.prev_sql_id,
s.logon_time,substr(s.program,1,20) as program,s.seconds_in_wait as siw
from v$session s
left outer join v$process p on (p.addr=s.paddr)
where s.type='USER'
and s.status='ACTIVE'
and s.username not in ('SYS','DBSNMP')
order by s.logon_time;
long operations
set pagesize 200
set linesize 200
set autotrace off
col message for a90
col username for a30
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
spool longops.txt
select username,sql_id,start_time,message from
(
select
username,
sql_id,
opname,
start_time,
target,
sofar,
totalwork,
units,
elapsed_seconds,
message
from
v$session_longops
where time_remaining!=0
order by start_time desc
)
where rownum <=16;
spool off
Piano della query per sql_id
--
set autotrace off
set pagesize 200
set linesize 200
select DBMS_LOB.substr(sql_fulltext, 3000) from v$sql where sql_id = '&1';
-- select plan_table_output from table(dbms_xplan.display_cursor('&1',null,'basic'));
-- select plan_table_output from table(dbms_xplan.display_cursor('&1'));
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'&1',cursor_child_no=>&2));
Storico query che hanno usato più TEMP
select sql_id,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time > sysdate-2 and TEMP_SPACE_ALLOCATED > (50*1024*1024) group by sql_id order by sql_id;
Controllo Allocazione Spazio
Questa query controlla lo spazio allocato, e lo confronta con lo spazio di massima crescita dei datafiles. Utile per evidenziare casi in cui è necessario aggiungere altri datafiles.
--
set pagesize 200
set linesize 200
with
segs as (select tablespace_name,round(sum(bytes)/(1024*1024)) mbs from dba_segments group by tablespace_name),
files as (select tablespace_name,count(file_name) nfiles,round(sum(maxbytes)/(1024*1024)) max,round(sum(bytes)/(1024*1024)) mba
from dba_data_files group by tablespace_name)
select t.tablespace_name,
t.block_size,
f.nfiles,
f.max,
s.mbs,
f.max-s.mbs sdiff,
round(((f.max-s.mbs)/f.max)*100) pcts,
f.mba,
f.mba-s.mbs shrinkable,
round(((f.mba-s.mbs)/f.mba)*100) pctsrnk
from dba_tablespaces t
inner join files f on (t.tablespace_name=f.tablespace_name)
left outer join segs s on (t.tablespace_name=s.tablespace_name)
order by 7;
with
tf as (select tablespace_name,count(*) nf,round(sum(bytes)/(1024*1024)) mba,round(sum(maxbytes)/(1024*1024)) max from dba_temp_files group by tablespace_name)
select g.group_name,
t.tablespace_name,
f.nf nfiles,
f.mba,
f.max,
round(s.allocated_space/(1024*1024)) mballoc,
round(s.free_space/(1024*1024)) mbfree
from tf f
left outer join dba_tablespace_groups g on (g.tablespace_name=f.tablespace_name)
left outer join dba_temp_free_space s on (s.tablespace_name=f.tablespace_name)
right outer join dba_tablespaces t on (t.tablespace_name=f.tablespace_name)
where t.contents='TEMPORARY'
order by 1,2,3;
Versioni vecchie
-- set pagesize 200 set linesize 200 with segs as (select tablespace_name,round(sum(bytes)/(1024*1024)) mbs from dba_segments group by tablespace_name), files as (select tablespace_name,count(file_name) nfiles,round(sum(maxbytes)/(1024*1024)) max,round(sum(bytes)/(1024*1024)) mba from dba_data_files group by tablespace_name) select t.tablespace_name, t.block_size, f.nfiles, f.max, f.mba, s.mbs, f.mba-s.mbs diff, f.max-f.mba mdiff, round(((f.max-f.mba)/f.max)*100) pcta, round(((f.mba-s.mbs)/f.mba)*100) pcts from dba_tablespaces t inner join files f on (t.tablespace_name=f.tablespace_name) left outer join segs s on (t.tablespace_name=s.tablespace_name) order by 7; with tf as (select tablespace_name,round(sum(bytes)/(1024*1024)) mba,round(sum(maxbytes)/(1024*1024)) max from dba_temp_files group by tablespace_name) select g.group_name, t.tablespace_name, f.mba, f.max, round(s.allocated_space/(1024*1024)) mballoc, round(s.free_space/(1024*1024)) mbfree from tf f left outer join dba_tablespace_groups g on (g.tablespace_name=f.tablespace_name) left outer join dba_temp_free_space s on (s.tablespace_name=f.tablespace_name) right outer join dba_tablespaces t on (t.tablespace_name=f.tablespace_name) where t.contents='TEMPORARY' order by 1,2,3;
select TBS.MB_MAX,
TBS.MB_TBS,
SEG.MB_SEG,
TBS.MB_MAX-SEG.MB_SEG MB_FREE,
TBS.TABLESPACE_NAME
from (select round(sum(f.maxbytes)/(1024*1024)) MB_MAX,
round(sum(f.user_bytes)/(1024*1024)) MB_TBS,
f.tablespace_name TABLESPACE_NAME
from dba_data_files f
group by f.tablespace_name) TBS,
(select round(sum(s.bytes)/(1024*1024)) MB_SEG,
s.tablespace_name TABLESPACE_NAME
from dba_segments s
group by s.tablespace_name) SEG
where SEG.tablespace_name=TBS.tablespace_name;
select TBS.MB_MAX,
TBS.MB_TBS,
SEG.MB_SEG,
TBS.MB_MAX-SEG.MB_SEG MB_FREE,
100-(100*(TBS.MB_MAX-SEG.MB_SEG)/nullif(TBS.MB_MAX,0)) USED,
100*(TBS.MB_MAX-SEG.MB_SEG)/nullif(TBS.MB_MAX,0) FREE,
TBS.TABLESPACE_NAME
from (select round(sum(f.maxbytes)/(1024*1024)) MB_MAX,
round(sum(f.user_bytes)/(1024*1024)) MB_TBS,
f.tablespace_name TABLESPACE_NAME
from dba_data_files f
group by f.tablespace_name) TBS,
(select round(sum(s.bytes)/(1024*1024)) MB_SEG,
s.tablespace_name TABLESPACE_NAME
from dba_segments s
group by s.tablespace_name) SEG
where SEG.tablespace_name=TBS.tablespace_name
order by 5;
Autoextend datafiles
Autoextend datafiles:
begin for c1 in (select file_id from dba_data_files) loop execute immediate 'alter database datafile ' || c1.file_id || ' autoextend on next 128M maxsize unlimited'; end loop; end; /
Autoextend tempfiles:
begin for c1 in (select file_id from dba_temp_files) loop execute immediate 'alter database tempfile ' || c1.file_id || ' autoextend on next 128M maxsize unlimited'; end loop; end; /
Shrink datafiles
set linesize 1000 pagesize 0 feedback off trimspool on with hwm as ( -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents ) select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn ), hwmts as ( -- join ts# with tablespace_name select name tablespace_name,relative_fno,hwm_blocks from hwm join v$tablespace using(ts#) ), hwmdf as ( -- join with datafiles, put 5M minimum for datafiles with no extents select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes from hwmts right join dba_data_files using(tablespace_name,relative_fno) ) select case when autoextensible='YES' and maxbytes>=bytes then -- we generate resize statements only if autoextensible can grow back to current size '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ ' ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;' else -- generate only a comment when autoextensible is off '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999) ||'M after setting autoextensible maxsize higher than current size for file ' || file_name||' */' end SQL from hwmdf where bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed order by bytes-hwm_bytes desc /
Schedula pulizia recycle bin
Cancella dal recyclebin tutti gli oggetti cancellati da almeno 3 giorni
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'purge_recyclebin_3days',
job_type => 'PLSQL_BLOCK',
job_action => '
begin
for c1 in (select owner,type,OBJECT_NAME from dba_recyclebin where to_date(DROPTIME,''YYYY-MM-DD:HH24:MI:SS'') < sysdate-3 and CAN_PURGE=''YES'')
loop
begin
execute immediate ''purge '' || c1.type || '' '' || c1.owner || ''."'' || c1.object_name || ''"'';
exception
when others then
null;
end;
end loop;
end;
',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
enabled => TRUE);
END;
/
Ricerca files orfani ASM
http://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/
DEFINE ASMGROUP="DGDATA"
set linesize 200
set pagesize 50000
col reference_index noprint
col type format a15
col files format a80
WITH v_asmgroup AS (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'),
v_parentindex AS (SELECT parent_index
FROM v$asm_alias
WHERE group_number = (SELECT group_number FROM v_asmgroup)
AND alias_index=0),
v_asmfiles AS (SELECT file_number, type
FROM v$asm_file
WHERE group_number = (SELECT group_number FROM v_asmgroup)),
v_dbname AS (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database)
SELECT 'rm '|| files files FROM -- this line show the delete command
(
SELECT '+&ASMGROUP'||files files, type
FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type
FROM (SELECT file_number,alias_directory,name, reference_index, parent_index
FROM v$asm_alias) aa,
(SELECT parent_index FROM v_parentindex) a,
(SELECT file_number, type FROM v_asmfiles) b
WHERE aa.file_number=b.file_number(+)
AND aa.alias_directory='N'
-- missing PARAMETERFILE, DATAGUARDCONFIG
AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE')
START WITH aa.PARENT_INDEX=a.parent_index
CONNECT BY PRIOR aa.reference_index=aa.parent_index)
WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM v_dbname)
MINUS (
SELECT upper(name) files, 'DATAFILE' type FROM v$datafile
UNION ALL
SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile
UNION ALL
SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%'
UNION ALL
SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%'
)
);
Reverse Grants
Elenco delle grants concesse all'utente. Utile quando l'utente va ricreato, ad esempio in caso di import.
set long 2000000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
variable v_username VARCHAR2(30);
exec:v_username := upper('UTENTE01');
spool grants_UTENTE01.sql
SELECT dbms_metadata.get_ddl('USER','UTENTE01') FROM dual;
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from dba_role_privs rp
where rp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from dba_sys_privs sp
where sp.grantee = :v_username
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from dba_tab_privs tp
where tp.grantee = :v_username
and rownum = 1
/
spool off;
clear screen
accept uname prompt 'Enter User Name : '
accept outfile prompt ' Output filename : '
spool &&outfile..gen
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&uname') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&uname') from dual;
spool off
Locked Objects
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS'; col object_name for a30 col machine for a30 SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, s.inst_id, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE, S.PORT, S.LOGON_TIME, SQ.SQL_FULLTEXT FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S, GV$PROCESS P, GV$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND (L.SESSION_ID = S.SID and l.inst_id=s.inst_id) AND (S.PADDR = P.ADDR and s.inst_id=p.inst_id) AND (S.SQL_ADDRESS = SQ.ADDRESS and s.inst_id=sq.inst_id) ; SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE, S.PORT, S.LOGON_TIME, SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND (L.SESSION_ID = S.SID) AND (S.PADDR = P.ADDR) AND (S.SQL_ADDRESS = SQ.ADDRESS) ;
Tablespaces Read Only
select 'alter tablespace ' || tablespace_name || ' read only;' from dba_tablespaces where contents='PERMANENT'
and status='ONLINE' and tablespace_name not in ('SYSTEM','SYSAUX');
select 'alter tablespace ' || tablespace_name || ' read write;' from dba_tablespaces where contents='PERMANENT'
and status='READ ONLY' and tablespace_name not in ('SYSTEM','SYSAUX');