Contenuto cancellato Contenuto aggiunto

In linea

Versione attuale delle 13:37, 18 set 2026

Tracciare operazioni nell'alert log

EXEC SYS.DBMS_SYSTEM.kfnwrt(1, 'NOTA OPERATORE: Inizio manutenzione di Mario Rossi');

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 in esecuzione

SELECT job_name, state, running_instance, session_id 
FROM dba_scheduler_running_jobs 
WHERE owner = '&1';

Elenco Job

col job_name for a40
col owner for a15
col last_start_date for a30
col next_run_date for a30
col start_date for a30
alter session set nls_timestamp_tz_format='DD/MM/YYYY HH24:MI:SS.FF TZH';
select job_name, owner, enabled, state, failure_count, LAST_START_DATE, NEXT_RUN_DATE, START_DATE from dba_scheduler_jobs order by last_start_date;

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

abilitare e disabilitare job

BEGIN
  DBMS_SCHEDULER.ENABLE(name => 'nome_schema.nome_job' );
END;
/

Job Immediato

Per creare un indice:

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

Per analizzare uno schema:

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

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

Per eseguire lo shrink delle tabelle:

--
set verify off echo off
alter table &1 enable row movement;
-- non mette lock esclusivo ma non libera lo spazio
alter table &1 shrink space compact;
-- libera lo spazio ma mette lock esclusivo per liberare spazio alla fine
alter table &1 shrink space;         
alter table &1 disable row movement;

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

Sessioni

Sessioni e processi

Gestione Spazio Disco

Gestione Spazio

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

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):

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

Metodo più efficace:


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

Reverse Grants 2