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

Datafile Resize for Specific Tablespace

 DECLARE
  v_tablespace_name VARCHAR2(30) := 'YOUR_TABLESPACE_NAME'; -- Change to your tablespace name
  v_new_size        VARCHAR2(20) := '1024M'; -- New size for each datafile
BEGIN
  FOR rec IN (SELECT file_name
              FROM dba_data_files
              WHERE tablespace_name = v_tablespace_name) 
  LOOP
    EXECUTE IMMEDIATE 'ALTER DATABASE DATAFILE ''' || rec.file_name || ''' RESIZE ' || v_new_size;
  END LOOP;
END;
/

ADRCI - Purge Database Logs/traces

 #!/bin/bash
# Script By Mohamed Fowjil
# Function to purge logs and traces older than 30 days using ADRCI for a specific ADR home
purge_adrci() {
    local home_path="$1"
    echo "Purging logs and traces older than 30 days for ADR home: $home_path"
    adrci exec="set homepath $home_path; purge -age 43200"  # 43200 minutes = 30 days
}
# Function to rotate alert logs
rotate_alert_log() {
    local alert_log="$1"
    if [ -f "$alert_log" ]; then
        echo "Rotating alert log: $alert_log"
        mv "$alert_log" "${alert_log}.$(date +%Y%m%d%H%M%S).bak"
        touch "$alert_log"
    else
        echo "Alert log not found: $alert_log"
    fi
}
# Function to rotate listener logs
rotate_listener_log() {
    local listener_log="$1"
    if [ -f "$listener_log" ]; then
        echo "Rotating listener log: $listener_log"
        mv "$listener_log" "${listener_log}.$(date +%Y%m%d%H%M%S).bak"
        touch "$listener_log"
    else
        echo "Listener log not found: $listener_log"
    fi
}
# Function to purge audit files older than 30 days
purge_audit_files() {
    local audit_directory="$1"
    if [ -d "$audit_directory" ]; then
        echo "Purging audit files older than 30 days in: $audit_directory"
        find "$audit_directory" -type f -name "*.aud" -mtime +30 -exec rm -f {} \;
    else
        echo "Audit directory not found: $audit_directory"
    fi
}
# Main script execution
echo "Starting Oracle Database log management..."
# Fetch the list of ADR homes
adr_homes=$(adrci exec="show homes" | grep -v "ADR Homes:" | grep -v "^$")
# Check if there are any ADR homes to process
if [ -z "$adr_homes" ]; then
    echo "No ADR homes found."
    exit 1
fi
# Purge logs for Oracle Database homes
for home in $adr_homes; do
    if [[ "$home" == *"rdbms"* ]]; then
        purge_adrci "$home"
    fi
done
# Rotate alert logs for database  instances
alert_logs=("/u01/app/oracle/diag/rdbms/*/*/trace/alert*.log")
for log in "${alert_logs[@]}"; do
    for log_file in $log; do
        rotate_alert_log "$log_file"
    done
done
# Rotate listener logs
##listener_logs=("/u01/app/oracle/diag/tnslsnr/*/listener*/alert/log.xml" )
for log in "${listener_logs[@]}"; do
    for log_file in $log; do
        rotate_listener_log "$log_file"
    done
done


# Purge audit files in common directories
audit_directories=("/u01/app/oracle/admin/*/adump")
for dir in "${audit_directories[@]}"; do
    for audit_dir in $dir; do
        purge_audit_files "$audit_dir"
    done
done
echo "Oracle Database log management completed."

ASM Usage

set lines 999;
col diskgroup for a15
col diskname for a15
col path for a35
select a.name DiskGroup,b.name DiskName, ROUND(b.total_mb /1024,2) as Total_GB, ROUND((b.total_mb-b.free_mb) /1024,2)As Used_GB, ROUND(b.free_mb / 1024,2) as Free_GB,b.path,b.header_status, ROUND((b.total_mb - b.free_mb) / b.total_mb * 100, 2) AS "USED%"
from v$asm_disk b, v$asm_diskgroup a
where a.group_number (+) =b.group_number
order by b.group_number,b.name;

FRA Usage

set linesize 500
col NAME for a50
select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)",
round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)",
round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" ,
(select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2)
from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
from V$RECOVERY_FILE_DEST;

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

Test Database Connection

#!/bin/bash

# Set Oracle environment variables

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/19c/db_1

export ORACLE_SID=DBSPRD

export PATH=$ORACLE_HOME/bin:$PATH

# Log file

LOG_FILE=connection_test.log

# Function to test connection

test_connection() {

  echo "Testing Oracle connection..."

  start_time=$(date +%s.%N)

  sqlplus -s / as sysdba <<EOF >> $LOG_FILE

WHENEVER SQLERROR EXIT SQL.SQLCODE

SELECT 'Connected to Oracle' FROM dual;

EXIT;

EOF

  end_time=$(date +%s.%N)

  duration=$(echo "$end_time - $start_time" | bc)

  echo "Connection time: ${duration} seconds" >> $LOG_FILE

}

# Run the test multiple times

for i in {1..10}; do

  test_connection

done

echo "Test completed. Check $LOG_FILE for details."

ORA-23515: materialized views and/or their indices exist in the tablespace

Tablespace drop

ORA-23515: materialized views and/or their indices exist in the tablespace

Solution :

Step 1: Find the materialized views and/or their indices

SQL> set head off
SQL> set newpage none
SQL> set pagesize 9999
SQL> spool drop_materialized_view.sql
SQL>select ‘drop materialized view ‘||owner||’.’||name||’ TABLE;’ from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name =’xxxxxx’);
SQL>spool off

Step 2:  Drop the materialized views and/or their indices

SQL>@drop_materialized_view.sql

Step 3: Drop the tablespace

SQL>drop tablespace xxxxxx including contents and datafiles;

Tablespace dropped.