_____________________________________________________________________________________

set pages 0
set lines 2000
_____________________________________________________________________________________

CREATE A COPY OF A TABLE WITHOUT CONTENT
========================================
create table xyz_new as select * from xyz where 1=0;
_____________________________________________________________________________________

EXTRACT DDL USING DBMS_METADATA
===============================
set pages 1000
set lines 200
set long 90000
set longchunksize 90000

begin
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS', true);
end;
/

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

-- The 'OWNER' argument is sometimes optional if you are connected as the same user who owns the object you are trying to retreive the DDL for.
-- Pasted below are the statements to generate the DDL for most of the object types.

select dbms_metadata.get_ddl('CLUSTER','C_MLOG#','SYS') from dual;
select dbms_metadata.get_ddl('CONTEXT','LT_CTX') from dual;
select dbms_metadata.get_ddl('DB_LINK','PROD.WORLD','ADAM') from dual;
select dbms_metadata.get_ddl('DB_LINK','TEST.WORLD','PUBLIC') from dual;
select dbms_metadata.get_ddl('FUNCTION','TO_DATE_FUNC','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','REPCAT$_REPCOLUMN_PK','SYSTEM') from dual;
select dbms_metadata.get_ddl('JAVA_SOURCE','java_util','ADAM') from dual;
select dbms_metadata.get_ddl('JAVA_SOURCE','/6c363944_Dumper','SYS') from dual;
select dbms_metadata.get_ddl('LIBRARY','UTL_SMT_LIB','SYS') from dual;
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','BS_TAB_MV','SYS') from dual;
select dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG','MLOG$_BS_TABLE','SYS') from dual;
select dbms_metadata.get_ddl('OPERATOR','OLAP_EXPRESSION') from dual;
select dbms_metadata.get_ddl('OPERATOR','OLAP_EXPRESSION','SYS') from dual;
select dbms_metadata.get_ddl('PACKAGE','XMLPARSER') from dual;
select dbms_metadata.get_ddl('PACKAGE','XMLPARSER','SYS') from dual;
select dbms_metadata.get_ddl('PACKAGE_BODY','STATSPACK','PERFSTAT') from dual;
select dbms_metadata.get_ddl('PROCEDURE','ORA$_SYS_REP_AUTH','SYSTEM') from dual;
select dbms_metadata.get_ddl('PROFILE','PRFADMIN') from dual;
select dbms_metadata.get_ddl('SEQUENCE','STATS$SNAPSHOT_ID','PERFSTAT') from dual;
select dbms_metadata.get_ddl('SYNONYM','/2fddc42_paintARGB_PRE_ONTO_S5','PUBLIC') from dual;
select dbms_metadata.get_ddl('TABLE','SQLEXPERT_PLAN1','SYSTEM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
select dbms_metadata.get_ddl('TRIGGER','DEF$_PROPAGATOR_TRIG','SYSTEM') from dual;
select dbms_metadata.get_ddl('TYPE','XMLSEQ_IMP_T','SYS') from dual;
select dbms_metadata.get_ddl('TYPE_BODY','ORACLE_LOADER','SYS') from dual;
select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION','SYS') from dual;
select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION') from dual;
select dbms_metadata.get_ddl('PACKAGE','DBMS_METADATA','SYS') from dual;

JOB=PROCOBJ!:
select dbms_metadata.get_ddl('PROCOBJ','SCOTTS_JOB','SCOTT') from dual;


select distinct 'select dbms_metadata.get_ddl(''PROFILE'',''' || profile || ''') from dual;' from dba_profiles order by 1;
select distinct 'select dbms_metadata.get_ddl(''ROLE'',''' || role || ''') from dual;' from dba_roles order by 1;


_____________________________________________________________________________________

set pages 0
set lines 2000
set heading off
set feedback off
_____________________________________________________________________________________

CREATE REDO LOGS
================
select 'alter database add logfile (''' || f.member || ''') size ' || l.bytes/1024/1024 || 'M;' "ADD_REDOLOGFILES" from v$log l, v$logfile f where l.group# = f.group# order by 1;


RENAME DATAFILES OR REDO LOGS (STARTUP MOUNT!)
==============================================
- generate alter/move commands
- shutdown
- move files
- startup mount
- alter database rename file...
- alter database open

spool move.tmp
select 'mv ' || name, name "MOVE_DATAFILES" from v$datafile;
select 'mv ' || member, member "MOVE_LOGFILES" from v$logfile;
spool off

spool rename.tmp
select 'alter database rename file ''' || name || ''' to ''' || name || ''';' "RENAME_DATAFILES" from v$datafile;
select 'alter database rename file ''' || member || ''' to ''' || member || ''';' "RENAME_LOGFILES" from v$logfile;
spool off

grep -e'^mv' move.tmp | awk '{print $1, $2, $3}' | sort > move.ksh; cat move.ksh
grep -e'^alter' rename.tmp | awk '{print $1, $2, $3, $4, $5, $6, $7}' | sort > rename.sql; cat rename.sql



KILL SESSION
============
select 'alter system kill session ''' || sid || ',' || serial# || ''';' "KILL_SESSION" from v$session;



DATAFILES WHICH NEED RECOVERY
=============================
select 'ALTER DATABASE RECOVER DATAFILE ''' || name || ''';' from v$datafile_header where recover = 'YES';



RESIZE DATAFILE
===============
select 'ALTER DATABASE DATAFILE ''' || name || ''' RESIZE 2G;' "STMT" from v$datafile order by 1;



RECOMPILE INVALID OBJECTS
=========================
describe ALL_OBJECTS

select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from ALL_OBJECTS
where STATUS = 'INVALID'
  and OBJECT_TYPE <> 'PACKAGE BODY'
  and OWNER like '&OWNER' || '%'
union
select 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;'
from ALL_OBJECTS
where STATUS='INVALID'
  and OBJECT_TYPE='PACKAGE BODY'
  and OWNER like '&OWNER'||'%';



CREATE ALTER USER STATEMENT (E.G. TO CHANGE TEMPORARILY USER'S PASSWORD AFTERWARDS)
===================================================================================
describe DBA_USERS
describe SYS.USER$

--- versions 7-10g
select 'ALTER USER ' || USERNAME || ' IDENTIFIED BY VALUES ''' || PASSWORD || ''';' from DBA_USERS where USERNAME like 'SCO%' order by 1;

--- version 11g+
select 'ALTER USER ' || NAME || ' IDENTIFIED BY VALUES ''' || SPARE4 || ''';' from SYS.USER$ where NAME like 'SCO%' order by 1;



RESTORE POINT
=============
select 'drop restore point '   || name || ';' "DROP_RESTORE_POINT" from v$restore_point;
select 'create restore point ' || name || ' guarantee flashback database;' "CREATE_RESTORE_POINT" from v$restore_point;



GATHER PRIVILEGES
=================
describe DBA_SYS_PRIVS
select 'grant ' || PRIVILEGE || ' to ' || GRANTEE  || ';' "GRANTS" from DBA_SYS_PRIVS where GRANTEE like 'DBSNMP%' order by 1;

describe DBA_USERS
select 'create user '||username||' identified by values '''||password||''''||chr(10)||
  ' default tablespace '||DEFAULT_TABLESPACE||
  ' temporary tablespace '||TEMPORARY_TABLESPACE||
  ' PROFILE DEFAULT;'
  from dba_users
--where username like 'SCO%'
  order by username;
  
select 'CREATE ROLE ' || role || ' NOT IDENTIFIED;'
from dba_roles;

select 'ALTER USER ' || username || ' QUOTA ' || decode(max_bytes,-1,'UNLIMITED',max_bytes) || ' ON ' || tablespace_name || ';'
from dba_ts_quotas
order by username;

select  'GRANT ' || privilege || ' TO ' || grantee || ';'
from dba_sys_privs
order by grantee;

select  'GRANT ' || granted_role || ' TO ' || grantee || ';'
from dba_role_privs
order by grantee;

select  'GRANT ' || granted_role || ' TO ' || role || ';'
from dba_role_privs, dba_roles
where grantee = role;

select 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name ||
' TO '|| grantee || decode (grantable,'NO','',' WITH GRANT OPTION')||';'
from sys.dba_tab_privs;

select 'GRANT ' || privilege || ' ON ' || owner || '."' || table_name || '" TO ' || grantee || ';' from dba_tab_privs
where grantee='PUBLIC'
  and owner in 
  (select username from dba_users );


  
PUBLIC SYNONYMS
===============
describe DBA_SYNONYMS

select 'CREATE OR REPLACE PUBLIC SYNONYM "' || synonym_name || '" FOR ' || table_owner || '."' || table_name || '";' "STMT"
from dba_synonyms
where db_link is null
  and owner='PUBLIC'
order by 1;

select 'CREATE OR REPLACE PUBLIC SYNONYM "' || synonym_name || '" FOR ' || table_owner || '."' || table_name || '"@' || db_link || ';' "STMT"
from dba_synonyms
where DB_LINK is not null
  and owner='PUBLIC'
order by 1;



DATABASE LINKS
==============
set lines 500
select 'create database link ' || db_link || ' connect to ' || username || ' identified by ' || username || '-password using ''' || host || ''';' "STMT"
  from dba_db_links
 where db_link like 'AQ%'
 order by 1;

 
 
DIRECTORIES
===========
select 'drop directory ' || directory_name || ';' "STMT" from dba_directories order by 1;
select 'create or replace directory ' || directory_name || ' as ''' || directory_path || ''';' "STMT" from dba_directories order by 1;



NLS PARAMETERS
==============
select 'alter system set ' || name || ' = ''' || value || ''' scope = spfile;' from v$parameter where name like 'nls%'; 



JOBS
====
select 'exec dbms_scheduler.enable(''' || owner || '.' || job_name || ''');' FROM all_scheduler_jobs WHERE owner = '<OWNER>' ORDER BY 1;
select 'exec dbms_scheduler.disable(''' || owner || '.' || job_name || ''');' FROM all_scheduler_jobs WHERE owner = '<OWNER>' ORDER BY 1;

_____________________________________________________________________________________