WikiSysop: /* Character Set */

=ACL=
==creazione acl==

==interrogazioni acl==
col host for a40
col acl for a45
col acl_owner for a10
set pagesize 200 linesize 200
SELECT * FROM DBA_NETWORK_ACLS order by host,lower_port;

col principal for a25
col privilege for a20
col start_date for a4
col end_date for a4
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES order by principal,privilege;

select a.host,a.lower_port,p.acl,p.principal,p.privilege,p.is_grant
from DBA_NETWORK_ACLS a inner join DBA_NETWORK_ACL_PRIVILEGES p on (a.aclid=p.aclid)
order by p.acl,p.principal,a.host,a.lower_port;

SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,
DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)
UNION ALL
SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,
DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);

=Job=
==Job Immediato==

col job_name for a50
col owner for a20
select job_name, owner, enabled from dba_scheduler_jobs;

<pre>
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;
/
</pre>
Per analizzare uno schema:
<pre>
begin
dbms_scheduler.create_job(
job_name => 'analyze_schema',
job_type => 'PLSQL_BLOCK',
job_action => 'begin dbms_stats.gather_schema_stats(ownname=>''SCHEMA'',cascade=>true); end;',
enabled => TRUE,
auto_drop => TRUE,
comments => 'analyze'
);
end;
/
</pre>

=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;

<pre>
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,
substr(i.uniqueness,1,1) U, 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,bytes/(1024*1024) mb from dba_segments where segment_name=upper('&&1');

</pre>

==Indici con proprietario diverso==
select owner,index_name,table_owner,table_name from dba_indexes where table_owner!=owner;

=Processi e sessioni=
==verifica valori limite==
col resource_name for a40
select resource_name,current_utilization,max_utilization,limit_value from v$resource_limit;

==Elenco processi di background==

select s.sid, s.process, s.program, p.name from v$session s join v$bgprocess p using (paddr) ;

==sessioni==

===Sessioni Attive===

set linesize 220
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,(sysdate-s.sql_exec_start)*86400 sec_exec,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;

====versione RAC====
set linesize 220
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#||'@'||s.inst_id as sid_ser,p.spid,
s.username,s.machine,s.sql_id,s.prev_sql_id,
s.logon_time,(sysdate-s.sql_exec_start)*86400 sec_exec,substr(s.program,1,20) as program,s.seconds_in_wait as siw
from gv$session s
left outer join gv$process p on (p.addr=s.paddr and s.inst_id=p.inst_id)
where s.type='USER' and s.status='ACTIVE'
order by s.logon_time;

====con eventi====

set linesize 230
set pagesize 300
set autotrace off
col machine for a21
col username for a20
col program for a20
col sid_ser for a15
col spid for a10
col sql_id for a14
col prev_sql_id for a14
col siw for 9999999
col status for a15
col event for a32
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,s.status,
s.username,substr(s.machine,1,20) as machine,s.sql_id,s.prev_sql_id,
s.logon_time,(sysdate-s.sql_exec_start)*86400 sec_exec,substr(s.program,1,20) as program,s.seconds_in_wait as siw,s.event
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.status desc,s.logon_time;

===Sessioni ordinate per tipo===

set linesize 200
set pagesize 200
set autotrace off
col machine for a21
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.status,
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.username not in ('SYS','DBSNMP')
order by s.status,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==

<pre>
SET LINESize 200
set pagesize 200
set autotrace off
COL sid FORMAT 9999
COL serial# FORMAT 9999999
col sid_ser for a12
COL username FORMAT A30
COL machine FORMAT A50
COL prog% FORMAT 999.00
COL elapsed FORMAT A10
COL remaining FORMAT A10
col message for a80
spool longops2.log
SELECT s.sid || ',' || s.serial# sid_ser,
s.username,
-- s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) "prog%",
s.sql_id,
replace(sl.message,'Blocks done','') as message
FROM v$session s
inner join v$session_longops sl on ( s.sid = sl.sid AND s.serial# = sl.serial# AND sl.time_remaining > 0 )
order by s.sql_id;
spool off

</pre>



==Rollback==
select state,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL, UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100 from gv$fast_start_transactions;

=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=
Richiede AWR.

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 > (500*1024*1024)
group by sql_id order by 2;

Versione per statspack:

=Segment Advisor=

--
set pagesize 200
set linesize 200
set autotrace off
-- select tablespace_name, segment_name, segment_type, partition_name,
-- recommendations, c1 from
-- table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
spool segadv.txt
select '/*' || recommendations || ' */ ' || c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')) where c1 is not null order by segment_type,reclaimable_space;
spool off

=Logging=

-- tabelle
set pagesize 0 linesize 200 trim on trims on feedback off echo off timing off
select 'alter table ' || owner || '.' || table_name || ' logging;' from dba_tables where owner!='SYS' and logging='NO';
select 'alter index ' || owner || '.' || index_name || ' logging;' from dba_indexes where owner!='SYS' and logging='NO';
select 'alter table ' || owner || '.' || table_name || ' modify lob (' || column_name || ') (cache logging);' from dba_lobs where owner!='SYS' and logging='NO';

-- tabelle partizionate
select 'alter table ' || owner || '.' || table_name || ' modify default attributes logging; ' from dba_part_tables where owner!='SYS' and def_logging='NO';
select 'alter index ' || owner || '.' || index_name || ' modify default attributes logging; ' from dba_part_indexes where owner!='SYS' and def_logging='NO';
select 'alter table ' || table_owner || '.' || table_name || ' modify default attributes lob (' || column_name || ') (cache logging);' from dba_part_lobs where table_owner!='SYS' and def_logging='NO';

-- partizioni
select 'alter table ' || table_owner || '.' || table_name || ' modify partition ' || partition_name || ' logging;' from dba_tab_partitions where table_owner!='SYS' and logging='NO';
select 'alter index ' || index_owner || '.' || index_name || ' modify partition ' || partition_name || ' logging;' from dba_ind_partitions where index_owner!='SYS' and logging='NO' and status='USABLE';
select 'alter table ' || table_owner || '.' || table_name || ' modify partition ' || partition_name || ' lob (' || column_name || ') (cache logging);' from dba_lob_partitions where table_owner!='SYS' and logging='NO';

=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.

<pre>
--

set pagesize 200
set linesize 200

with
segs as (select tablespace_name,sum(bytes)/(1024*1024) mbs, count(*) nsegs from dba_segments group by tablespace_name),
files as (select tablespace_name,count(file_name) nfiles,sum(case when maxbytes=0 then bytes else maxbytes end)/(1024*1024) max,sum(bytes)/(1024*1024) mba
from dba_data_files group by tablespace_name)
select t.tablespace_name,
t.block_size,
f.nfiles,
s.nsegs,
round(f.max) max,
round(s.mbs) mbs,
round(f.max-s.mbs) sdiff,
round(((f.max-s.mbs)/f.max)*100) pcts,
round(f.mba) mba,
round(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 10;

with
tf as (select tablespace_name,count(*) nf,round(sum(bytes)/(1024*1024)) mba,round(sum(case when maxbytes=0 then bytes else maxbytes end)/(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;

</pre>



==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==
<pre>
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
/

</pre>

==Schedula pulizia recycle bin==
Cancella dal recyclebin tutti gli oggetti cancellati da almeno 3 giorni
<pre>
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;
/

</pre>

=Ricerca files orfani ASM=

http://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/

<pre>
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%'
)
);

</pre>

=constraints=

Per vedere le colonne. Va raffinata:

select c.constraint_name || ' ' || c.table_name || '(' || u.column_name || ')'
from dba_constraints c
inner join dba_cons_columns u on (u.owner=c.owner and u.constraint_name=c.constraint_name)
where c.constraint_type='R';

Per vedere le referenze tra tabelle:

select c.constraint_name || ' ' || c.table_name || ' -> ' || u.table_name
from dba_constraints c
inner join dba_constraints u on (u.owner=c.r_owner and u.constraint_name=c.r_constraint_name)
where c.constraint_type='R'
and (c.table_name = '&&1' or u.table_name='&&1');

select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS,VALIDATED from dba_constraints where table_name='&&1';

=Get DDL=
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);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PARTITIONING',true);
end;
/

==Tabelle==
select dbms_metadata.get_ddl(object_type=>'TABLE',schema=>'SCHEMA_NAME',name=>'TABLE_NAME') from dual;

==Reverse Grants==

Elenco delle grants concesse all'utente. Utile quando l'utente va ricreato, ad esempio in caso di import.

Vecchio metodo (si può migliorare gestendo la grant option):
<pre>
--
set pagesize 0 linesize 200 trim on trims on tab off feedback off
spool privilegi_esportati.sql
select 'grant ' || privilege || ' to ' || grantee || ';' from dba_sys_privs where grantee in ('USER1');
select 'grant ' || GRANTED_ROLE || ' to ' || grantee || ';' from dba_role_privs where grantee in ('USER1');
select 'grant ' || privilege || ' on ' || owner || '."' || table_name || '" to ' || grantee || ';' from dba_tab_privs where grantee in ('USER1');
spool off
set pagesize 200 feedback on
</pre>

Metodo più efficace:
<pre>

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;

</pre>

<pre>
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
</pre>

==Reverse Grants 2==

Elenco delle grants concesse all'utente. Utile quando l'utente va ricreato, ad esempio in caso di import.
<pre>

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;

</pre>

<pre>
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
</pre>

=Locked Objects=

<pre>
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)
;
</pre>

==lock==

<pre>
--

set linesize 190
set pagesize 200
col blocker for a30
col sid_serial for a15
col blockee for a30

col object_name for a30
col machine for a45
col osuser for a15

select c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c,
v$lock l
where b.sid = a.session_id
and a.object_id = c.object_id
and l.sid=b.sid
and (l.block=1 or l.request > 0 )
;

select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

</pre>

<pre>
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) '
AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
set heading off
select 'SQL STATEMENT BLOCCANTI:' as SQL_STATEMENT_BLOCCANTI from dual;
set heading on
select a.username,a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address and a.sid in ( select
s1.sid
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2) ;

</pre>

==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');

=Character Set=

col PROPERTY_NAME for a30
col PROPERTY_VALUE for a40
col DESCRIPTION for a60
select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

==Gestione stringhe con caratteri unicode==

<pre>
SQL> select asciistr('Caffé a €1,50') from dual;

ASCIISTR('Caffé a €1,50')
---------------------------------------------------------------
Caff\00E9 a \20AC1,50

SQL> select UNISTR('Caff\00E9 a \20AC1,50') from dual;

UNISTR('CAFF\00E9A\20AC1,50')
---------------------------------------
Caffé a €1,50

SQL>

</pre>

=Redefine Online=
<pre>
--
set timing on
drop table USER.EESKFILECOMUNICAZIONE_CP;
purge dba_recyclebin;

-- verifica se è utilizzabile
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('USER','EESKFILECOMUNICAZIONE',DBMS_REDEFINITION.CONS_USE_PK);
END;
/

-- organization index tablespace ODSSOLTBS overflow tablespace ODSSOLTBS
create table USER.EESKFILECOMUNICAZIONE_CP (IDSKFILE NUMBER(18),FBODY BLOB)
lob(FBODY) store as securefile (enable storage in row nocache nologging deduplicate compress high tablespace ODSLOBTBS)
partition by range (IDSKFILE) interval (5000000) ( partition values less than (5000000) )
compress for oltp tablespace ODSLOBTBS nologging;

-- insert /*+ append */ into USER.EESKFILECOMUNICAZIONE_CP
-- select * from USER.EESKFILECOMUNICAZIONE;

alter table USER.EESKFILECOMUNICAZIONE_CP enable row movement;
alter session force parallel dml parallel 8;
alter session force parallel query parallel 8;

EXEC DBMS_REDEFINITION.start_redef_table(uname=>'USER', orig_table=>'EESKFILECOMUNICAZIONE', int_table=>'EESKFILECOMUNICAZIONE_CP');

EXEC DBMS_REDEFINITION.sync_interim_table(uname=>'USER', orig_table=>'EESKFILECOMUNICAZIONE', int_table=>'EESKFILECOMUNICAZIONE_CP');

-- Copy dependents.
SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'USER',
orig_table => 'EESKFILECOMUNICAZIONE',
int_table => 'EESKFILECOMUNICAZIONE_CP',
copy_indexes => 1, -- Default
copy_triggers => TRUE, -- Default
copy_constraints => TRUE, -- Default
copy_privileges => TRUE, -- Default
ignore_errors => FALSE, -- Default
num_errors => l_num_errors,
copy_statistics => FALSE, -- Default
copy_mvlog => FALSE); -- Default

DBMS_OUTPUT.put_line('num_errors=' || l_num_errors);
END;
/

EXEC DBMS_REDEFINITION.finish_redef_table(uname=>'USER', orig_table=>'EESKFILECOMUNICAZIONE', int_table=>'EESKFILECOMUNICAZIONE_CP');

</pre>

[[Categoria:Database]]
[[Categoria:Oracle]]

Query Varie

2026-05-14T08:14:55Z

WikiSysop: /* Controllo Allocazione Spazio */

=ACL=
==creazione acl==

==interrogazioni acl==
col host for a40
col acl for a45
col acl_owner for a10
set pagesize 200 linesize 200
SELECT * FROM DBA_NETWORK_ACLS order by host,lower_port;

col principal for a25
col privilege for a20
col start_date for a4
col end_date for a4
SELECT * FROM DBA_NETWORK_ACL_PRIVILEGES order by principal,privilege;

select a.host,a.lower_port,p.acl,p.principal,p.privilege,p.is_grant
from DBA_NETWORK_ACLS a inner join DBA_NETWORK_ACL_PRIVILEGES p on (a.aclid=p.aclid)
order by p.acl,p.principal,a.host,a.lower_port;

SELECT PRINCIPAL, HOST, lower_port, upper_port, acl, 'connect' AS PRIVILEGE,
DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'connect'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID)
UNION ALL
SELECT PRINCIPAL, HOST, NULL lower_port, NULL upper_port, acl, 'resolve' AS PRIVILEGE,
DECODE(DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, PRINCIPAL, 'resolve'), 1,'GRANTED', 0,'DENIED', NULL) PRIVILEGE_STATUS
FROM DBA_NETWORK_ACLS
JOIN DBA_NETWORK_ACL_PRIVILEGES USING (ACL, ACLID);

=Job=
==Job Immediato==

col job_name for a50
col owner for a20
select job_name, owner, enabled from dba_scheduler_jobs;

<pre>
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;
/
</pre>
Per analizzare uno schema:
<pre>
begin
dbms_scheduler.create_job(
job_name => 'analyze_schema',
job_type => 'PLSQL_BLOCK',
job_action => 'begin dbms_stats.gather_schema_stats(ownname=>''SCHEMA'',cascade=>true); end;',
enabled => TRUE,
auto_drop => TRUE,
comments => 'analyze'
);
end;
/
</pre>

=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;

<pre>
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,
substr(i.uniqueness,1,1) U, 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,bytes/(1024*1024) mb from dba_segments where segment_name=upper('&&1');

</pre>

==Indici con proprietario diverso==
select owner,index_name,table_owner,table_name from dba_indexes where table_owner!=owner;

=Processi e sessioni=
==verifica valori limite==
col resource_name for a40
select resource_name,current_utilization,max_utilization,limit_value from v$resource_limit;

==Elenco processi di background==

select s.sid, s.process, s.program, p.name from v$session s join v$bgprocess p using (paddr) ;

==sessioni==

===Sessioni Attive===

set linesize 220
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,(sysdate-s.sql_exec_start)*86400 sec_exec,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;

====versione RAC====
set linesize 220
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#||'@'||s.inst_id as sid_ser,p.spid,
s.username,s.machine,s.sql_id,s.prev_sql_id,
s.logon_time,(sysdate-s.sql_exec_start)*86400 sec_exec,substr(s.program,1,20) as program,s.seconds_in_wait as siw
from gv$session s
left outer join gv$process p on (p.addr=s.paddr and s.inst_id=p.inst_id)
where s.type='USER' and s.status='ACTIVE'
order by s.logon_time;

====con eventi====

set linesize 230
set pagesize 300
set autotrace off
col machine for a21
col username for a20
col program for a20
col sid_ser for a15
col spid for a10
col sql_id for a14
col prev_sql_id for a14
col siw for 9999999
col status for a15
col event for a32
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,s.status,
s.username,substr(s.machine,1,20) as machine,s.sql_id,s.prev_sql_id,
s.logon_time,(sysdate-s.sql_exec_start)*86400 sec_exec,substr(s.program,1,20) as program,s.seconds_in_wait as siw,s.event
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.status desc,s.logon_time;

===Sessioni ordinate per tipo===

set linesize 200
set pagesize 200
set autotrace off
col machine for a21
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.status,
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.username not in ('SYS','DBSNMP')
order by s.status,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==

<pre>
SET LINESize 200
set pagesize 200
set autotrace off
COL sid FORMAT 9999
COL serial# FORMAT 9999999
col sid_ser for a12
COL username FORMAT A30
COL machine FORMAT A50
COL prog% FORMAT 999.00
COL elapsed FORMAT A10
COL remaining FORMAT A10
col message for a80
spool longops2.log
SELECT s.sid || ',' || s.serial# sid_ser,
s.username,
-- s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) "prog%",
s.sql_id,
replace(sl.message,'Blocks done','') as message
FROM v$session s
inner join v$session_longops sl on ( s.sid = sl.sid AND s.serial# = sl.serial# AND sl.time_remaining > 0 )
order by s.sql_id;
spool off

</pre>



==Rollback==
select state,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL, UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100 from gv$fast_start_transactions;

=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=
Richiede AWR.

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 > (500*1024*1024)
group by sql_id order by 2;

Versione per statspack:

=Segment Advisor=

--
set pagesize 200
set linesize 200
set autotrace off
-- select tablespace_name, segment_name, segment_type, partition_name,
-- recommendations, c1 from
-- table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
spool segadv.txt
select '/*' || recommendations || ' */ ' || c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')) where c1 is not null order by segment_type,reclaimable_space;
spool off

=Logging=

-- tabelle
set pagesize 0 linesize 200 trim on trims on feedback off echo off timing off
select 'alter table ' || owner || '.' || table_name || ' logging;' from dba_tables where owner!='SYS' and logging='NO';
select 'alter index ' || owner || '.' || index_name || ' logging;' from dba_indexes where owner!='SYS' and logging='NO';
select 'alter table ' || owner || '.' || table_name || ' modify lob (' || column_name || ') (cache logging);' from dba_lobs where owner!='SYS' and logging='NO';

-- tabelle partizionate
select 'alter table ' || owner || '.' || table_name || ' modify default attributes logging; ' from dba_part_tables where owner!='SYS' and def_logging='NO';
select 'alter index ' || owner || '.' || index_name || ' modify default attributes logging; ' from dba_part_indexes where owner!='SYS' and def_logging='NO';
select 'alter table ' || table_owner || '.' || table_name || ' modify default attributes lob (' || column_name || ') (cache logging);' from dba_part_lobs where table_owner!='SYS' and def_logging='NO';

-- partizioni
select 'alter table ' || table_owner || '.' || table_name || ' modify partition ' || partition_name || ' logging;' from dba_tab_partitions where table_owner!='SYS' and logging='NO';
select 'alter index ' || index_owner || '.' || index_name || ' modify partition ' || partition_name || ' logging;' from dba_ind_partitions where index_owner!='SYS' and logging='NO' and status='USABLE';
select 'alter table ' || table_owner || '.' || table_name || ' modify partition ' || partition_name || ' lob (' || column_name || ') (cache logging);' from dba_lob_partitions where table_owner!='SYS' and logging='NO';

=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.

<pre>
--

set pagesize 200
set linesize 200

with
segs as (select tablespace_name,sum(bytes)/(1024*1024) mbs, count(*) nsegs from dba_segments group by tablespace_name),
files as (select tablespace_name,count(file_name) nfiles,sum(case when maxbytes=0 then bytes else maxbytes end)/(1024*1024) max,sum(bytes)/(1024*1024) mba
from dba_data_files group by tablespace_name)
select t.tablespace_name,
t.block_size,
f.nfiles,
s.nsegs,
round(f.max) max,
round(s.mbs) mbs,
round(f.max-s.mbs) sdiff,
round(((f.max-s.mbs)/f.max)*100) pcts,
round(f.mba) mba,
round(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 10;

with
tf as (select tablespace_name,count(*) nf,round(sum(bytes)/(1024*1024)) mba,round(sum(case when maxbytes=0 then bytes else maxbytes end)/(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;

</pre>



==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==
<pre>
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
/

</pre>

==Schedula pulizia recycle bin==
Cancella dal recyclebin tutti gli oggetti cancellati da almeno 3 giorni
<pre>
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;
/

</pre>

=Ricerca files orfani ASM=

http://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/

<pre>
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%'
)
);

</pre>

=constraints=

Per vedere le colonne. Va raffinata:

select c.constraint_name || ' ' || c.table_name || '(' || u.column_name || ')'
from dba_constraints c
inner join dba_cons_columns u on (u.owner=c.owner and u.constraint_name=c.constraint_name)
where c.constraint_type='R';

Per vedere le referenze tra tabelle:

select c.constraint_name || ' ' || c.table_name || ' -> ' || u.table_name
from dba_constraints c
inner join dba_constraints u on (u.owner=c.r_owner and u.constraint_name=c.r_constraint_name)
where c.constraint_type='R'
and (c.table_name = '&&1' or u.table_name='&&1');

select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS,VALIDATED from dba_constraints where table_name='&&1';

=Get DDL=
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);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PARTITIONING',true);
end;
/

==Tabelle==
select dbms_metadata.get_ddl(object_type=>'TABLE',schema=>'SCHEMA_NAME',name=>'TABLE_NAME') from dual;

==Reverse Grants==

Elenco delle grants concesse all'utente. Utile quando l'utente va ricreato, ad esempio in caso di import.

Vecchio metodo (si può migliorare gestendo la grant option):
<pre>
--
set pagesize 0 linesize 200 trim on trims on tab off feedback off
spool privilegi_esportati.sql
select 'grant ' || privilege || ' to ' || grantee || ';' from dba_sys_privs where grantee in ('USER1');
select 'grant ' || GRANTED_ROLE || ' to ' || grantee || ';' from dba_role_privs where grantee in ('USER1');
select 'grant ' || privilege || ' on ' || owner || '."' || table_name || '" to ' || grantee || ';' from dba_tab_privs where grantee in ('USER1');
spool off
set pagesize 200 feedback on
</pre>

Metodo più efficace:
<pre>

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;

</pre>

<pre>
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
</pre>

==Reverse Grants 2==

Elenco delle grants concesse all'utente. Utile quando l'utente va ricreato, ad esempio in caso di import.
<pre>

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;

</pre>

<pre>
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
</pre>

=Locked Objects=

<pre>
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)
;
</pre>

==lock==

<pre>
--

set linesize 190
set pagesize 200
col blocker for a30
col sid_serial for a15
col blockee for a30

col object_name for a30
col machine for a45
col osuser for a15

select c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c,
v$lock l
where b.sid = a.session_id
and a.object_id = c.object_id
and l.sid=b.sid
and (l.block=1 or l.request > 0 )
;

select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

</pre>

<pre>
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) '
AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
set heading off
select 'SQL STATEMENT BLOCCANTI:' as SQL_STATEMENT_BLOCCANTI from dual;
set heading on
select a.username,a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address and a.sid in ( select
s1.sid
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2) ;

</pre>

==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');

=Character Set=

col PROPERTY_NAME for a30
col PROPERTY_VALUE for a40
col DESCRIPTION for a60
select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

=Redefine Online=
<pre>
--
set timing on
drop table USER.EESKFILECOMUNICAZIONE_CP;
purge dba_recyclebin;

-- verifica se è utilizzabile
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('USER','EESKFILECOMUNICAZIONE',DBMS_REDEFINITION.CONS_USE_PK);
END;
/

-- organization index tablespace ODSSOLTBS overflow tablespace ODSSOLTBS
create table USER.EESKFILECOMUNICAZIONE_CP (IDSKFILE NUMBER(18),FBODY BLOB)
lob(FBODY) store as securefile (enable storage in row nocache nologging deduplicate compress high tablespace ODSLOBTBS)
partition by range (IDSKFILE) interval (5000000) ( partition values less than (5000000) )
compress for oltp tablespace ODSLOBTBS nologging;

-- insert /*+ append */ into USER.EESKFILECOMUNICAZIONE_CP
-- select * from USER.EESKFILECOMUNICAZIONE;

alter table USER.EESKFILECOMUNICAZIONE_CP enable row movement;
alter session force parallel dml parallel 8;
alter session force parallel query parallel 8;

EXEC DBMS_REDEFINITION.start_redef_table(uname=>'USER', orig_table=>'EESKFILECOMUNICAZIONE', int_table=>'EESKFILECOMUNICAZIONE_CP');

EXEC DBMS_REDEFINITION.sync_interim_table(uname=>'USER', orig_table=>'EESKFILECOMUNICAZIONE', int_table=>'EESKFILECOMUNICAZIONE_CP');

-- Copy dependents.
SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'USER',
orig_table => 'EESKFILECOMUNICAZIONE',
int_table => 'EESKFILECOMUNICAZIONE_CP',
copy_indexes => 1, -- Default
copy_triggers => TRUE, -- Default
copy_constraints => TRUE, -- Default
copy_privileges => TRUE, -- Default
ignore_errors => FALSE, -- Default
num_errors => l_num_errors,
copy_statistics => FALSE, -- Default
copy_mvlog => FALSE); -- Default

DBMS_OUTPUT.put_line('num_errors=' || l_num_errors);
END;
/

EXEC DBMS_REDEFINITION.finish_redef_table(uname=>'USER', orig_table=>'EESKFILECOMUNICAZIONE', int_table=>'EESKFILECOMUNICAZIONE_CP');

</pre>

[[Categoria:Database]]
[[Categoria:Oracle]]