Oracle‎ > ‎

SQL

SQL code snippets to make life a little easier.

Granting Read-Only Access

Thanks to Gwen Shapira for the original code. This script generates the GRANT sql statements to grant read-only (SELECT) access on all relevant objects in one user's schema to another user.


set lines 256 head off feed off pages 0 trimspool on
spool grant.sql

select 'set echo on' from dual;
select 'spool grant.log' from dual;

select 'GRANT SELECT ON '||owner||'.'||object_name||' TO new_user;'
from all_objects
where owner = old_user
and object_type in ('SEQUENCE','TABLE','VIEW','MATERIALIZED VIEW')
order by owner, object_name;

select 'spool off' from dual;
select 'exit' from dual;

spool off
exit


Finding Distinct File Locations

Handy when building db_file_name_convert strings

select distinct substr(file_name, 1, instr(file_name, '/', -1)-1) path
from dba_data_files;

To take it the next logical step:

  1  select distinct ''''||
  2     substr(file_name, 1, instr(file_name, '/', -1)-1)||
  3     ''',''/mnt/zfssa/data/FOO/datafile/''' conv_strings
  4* from dba_data_files
SQL> /

CONV_STRINGS
--------------------------------------------------------------------------------
'+DATA1/foo/datafile','/mnt/zfssa/data/FOO/datafile'
'+DATA2/foo/datafile','/mnt/zfssa/data/FOO/datafile'

Historical Temp Usage
select * from dba_hist_active_sess_history
where sample_time between TO_TIMESTAMP('20170427 19:45:00','YYYYMMDD HH24:MI:SS') AND TO_TIMESTAMP('20170427 19:50:00','YYYYMMDD HH24:MI:SS')
order by TEMP_SPACE_ALLOCATED desc NULLS LAST;

Subpages (3): Explain Plan Random Data SQL*Plus
Comments