User DDL

set termout off
set linesize 19000
set pages 50000
set feedback off
set trim on
set echo off
set serveroutput on
set long 99999999
set longchunksize 20000 pagesize 0
column Extracted_DDL for a1000
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
select (case
when ((select count(*)
from dba_users
where username = '$dbuser' and profile <> 'DEFAULT') > 0)
then chr(10)||' -- Note: Profile'||(select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.username = '$dbuser')
else to_clob (chr(10)||' -- Note: Default profile, no need to create!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_users
where username = '$dbuser') > 0)
then ' -- Note: Create user statement'||dbms_metadata.get_ddl ('USER', '$dbuser')
else to_clob (chr(10)||' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = '$dbuser') > 0)
then ' -- Note: TBS quota'||dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '$dbuser')
else to_clob (chr(10)||' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '$dbuser') > 0)
then ' -- Note: Roles'||dbms_metadata.get_granted_ddl ('ROLE_GRANT', '$dbuser')
else to_clob (chr(10)||' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from V\$PWFILE_USERS
where username = '$dbuser' and SYSDBA='TRUE') > 0)
then ' -- Note: sysdba'||chr(10)||to_clob (' GRANT SYSDBA TO '||'"'||'$dbuser'||'"'||';')
else to_clob (chr(10)||' -- Note: No sysdba administrative Privilege found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = '$dbuser') > 0)
then ' -- Note: System Privileges'||dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '$dbuser')
else to_clob (chr(10)||' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = '$dbuser') > 0)
then ' -- Note: Object Privileges'||dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '$dbuser')
else to_clob (chr(10)||' -- Note: No Object Privileges found!')
end ) from dual;

Gather Stats Script

 -- -----------------------------------------------------------------------------------
-- File Name    : gather_stats_script.sql
-- Author       : Mohamed Fowjil
-- Description  : Gathers statistics for the database using DBMS_STATS procedures.
-- Last Modified: 2024-07-01
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
-- Define variables for timing
VARIABLE start_time VARCHAR2(100);
VARIABLE end_time VARCHAR2(100);
-- Get current timestamp as start time
BEGIN
   SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') INTO :start_time FROM DUAL;
   DBMS_OUTPUT.PUT_LINE('Start Time: ' || :start_time);
END;
/
-- Spool start time to separate log file
SPOOL gather_stats_log.txt
SELECT 'Start Time: ' || :start_time FROM DUAL;
SPOOL OFF
-- Set global preferences for DBMS_STATS
EXEC DBMS_STATS.SET_GLOBAL_PREFS('DEGREE', 8);
EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'AUTOMATIC');
-- Gather dictionary stats
BEGIN
   DBMS_STATS.GATHER_DICTIONARY_STATS();
   DBMS_OUTPUT.PUT_LINE('Gathered dictionary stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering dictionary stats: ' || SQLERRM);
END;
/
-- Gather fixed objects stats
BEGIN
   DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();
   DBMS_OUTPUT.PUT_LINE('Gathered fixed objects stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering fixed objects stats: ' || SQLERRM);
END;
/
-- Gather database stats
DECLARE
   l_options VARCHAR2(200);
BEGIN
   l_options := 'GATHER STALE';
   DBMS_STATS.GATHER_DATABASE_STATS(
      options => l_options,
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
      granularity => 'ALL',
      cascade => TRUE,
      degree => 8,
      no_invalidate => DBMS_STATS.AUTO_INVALIDATE
   );
   DBMS_OUTPUT.PUT_LINE('Gathered database stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering database stats: ' || SQLERRM);
END;
/
-- Get current timestamp as end time
BEGIN
   SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') INTO :end_time FROM DUAL;
   DBMS_OUTPUT.PUT_LINE('End Time: ' || :end_time);
END;
/

--SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON
SET TIMING ON
SET HEADING ON