Steps to enable flashback mode

Steps to enable flashback mode:

Flashback mode in Oracle Database is a powerful feature that allows you to view and recover data from a previous point in time. It provides a way to rewind the database to a specific moment in the past, enabling you to analyze historical data, undo transactions, or recover from logical errors.

With flashback mode enabled, you can query data as it existed at a specific timestamp or system change number (SCN), providing a consistent view of the database as it appeared in the past. This is particularly useful when you need to diagnose issues, track changes, or retrieve lost data.

Oracle Database offers several flashback features, including:

Flashback Query: Allows you to perform queries on historical data by specifying a timestamp or SCN. This enables you to retrieve previous versions of rows and track changes over time.

Flashback Table: Provides the ability to revert a table to a previous state, effectively undoing changes made since a specified timestamp or SCN. This can be useful for correcting mistakes or recovering from data corruption.

Flashback Transaction: Enables you to undo a specific transaction or a set of transactions, restoring the affected data to its previous state. This is particularly useful when you need to reverse unintended changes or recover from logical errors.

Flashback Database: Allows you to perform a point-in-time recovery of the entire database, rolling it back to a specified SCN or timestamp. This feature is helpful for recovering from major incidents, such as data corruption or human errors affecting the entire database.

These flashback features in Oracle Database 19c provide administrators and developers with powerful tools for data recovery, analysis, and correction. They offer increased flexibility and efficiency in managing and restoring data, ultimately contributing to better data integrity and system reliability.

select instance_name from v$instance;

select log_mode,flashback_on from v$database;

SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON

------------------

NO

show parameter DB_FLASHBACK_RETENTION_TARGET;

select * from v$flash_recovery_area_usage order by 2;

ALTER DATABASE FLASHBACK ON;

SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON

------------------

YES To perform a flashback query in Oracle Database 12c and Oracle 19c: Ensure that the UNDO_RETENTION parameter is appropriately  set in the database initialization parameters.  This parameter determines the duration for which undo data is retained,  enabling flashback queries. Use the AS OF clause in your SQL query to specify the desired timestamp or  SCN (system change number For example: SELECT * FROM table_name AS OF TIMESTAMP TO_TIMESTAMP ('2022-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS'); This query retrieves the data from the table_name as it existed at the  specified timestamp. To implement Flashback Table in Oracle Database 12c and 19c: you can use the FLASHBACK TABLE statement.  This feature allows you to quickly restore a table to a previous state  without the need for performing a full database restore or recovery.  Here's how you can use Flashback Table in both versions: Ensure that the UNDO_RETENTION parameter is appropriately set in the  database initialization parameters. Execute the FLASHBACK TABLE statement to restore the table to a  specific point in time.  For example: FLASHBACK TABLE table_name TO TIMESTAMP TO_TIMESTAMP('2022-01-01 12:00:00',  'YYYY-MM-DD HH24:MI:SS'); This statement restores the table_name to its state as of the specified timestamp Note that to use Flashback Table, you need to have the necessary privileges  (such as the FLASHBACK ANY TABLE system privilege or be the owner of the table).  Also, ensure that you have sufficient undo retention to support the  flashback operation. To implement Flashback Transaction in Oracle Database 12c and 19c:  you can use the FLASHBACK TRANSACTION statement.  This feature allows you to undo one or more transactions, rolling back the  changes made by those transactions.  Here's how you can use Flashback Transaction in both versions: Oracle Database 12c and Oracle Database 19c: To perform a Flashback Transaction operation in Oracle Database 12c: Ensure that the UNDO_RETENTION parameter is appropriately set in the  database initialization parameters. Execute the FLASHBACK TRANSACTION statement to undo specific transactions. For example: SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('000200030000002D'); SELECT xid, logon_user FROM flashback_transaction_query WHERE xid IN (   SELECT versions_xid FROM employees VERSIONS BETWEEN TIMESTAMP   TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND   TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS') ); FLASHBACK TRANSACTION transaction_id; Replace transaction_id with the identifier of the transaction you want to undo.  You can obtain the transaction ID from the Oracle database transaction logs. To implement Flashback Database in Oracle Database 12c and 19c: you can use the FLASHBACK DATABASE statement.  This feature allows you to perform a point-in-time recovery of the  entire database, rolling it back to a specific SCN or timestamp.  Here's how you can use Flashback Database in both versions: Ensure that the database is running in ARCHIVELOG mode. Set the DB_FLASHBACK_RETENTION_TARGET parameter to specify the  desired duration for flashback data retention. Execute the FLASHBACK DATABASE statement to roll back the entire  database to a specific SCN or timestamp.  For example: FLASHBACK DATABASE TO SCN scn_number; Replace scn_number with the system change number (SCN) corresponding  to the desired point in time  to which you want to flash back the database. For more information check in the below link https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/flashback.html

Enjoy the work and do like me if you like this article!!!

Inserting data into a table

Inserting  a data through Dblinks:
 
Insert into  ADMIN.T71 (C1,
C2,
C3,
C4,
C5,
C6,
C7,
C8)
 select C1,
C2,
C3,
C4,
C5,
C6,
C7,
C8
from ARADMIN.T71@xyz.WORLD 


SELECT * FROM ARADMIN.T71  WHERE DATE_SUB(NOW(), INTERVAL 1 HOUR) < UPDATE_TIME 

INSERT INTO target_table (column1, column2, column3, ...)

SELECT column1, column2, column3, ...

FROM source_table;

INSERT INTO target_table (col1, col2, col3)

SELECT col1, col2, col3

FROM source_table;

Enjoy the work and do like me if you like this article!!!

Oracle Tablespace management

An Oracle tablespace is a logical storage container within the database. It organizes and manages data efficiently, storing tables, indexes, and other objects. It helps with performance optimization and simplifies backup and recovery. Tablespaces use data files to store data and metadata. They offer flexibility in storage characteristics and allocation, including different block sizes and storage quotas. Overall, tablespaces are crucial for data organization and management in Oracle databases.

Nice script for tablespace usage checking:

set lines 223 pages 999;

select tablespace_name,round((USED_SPACE*8)/1024)CURRENT_SIZE_MB,round((TABLESPACE_SIZE*8)/1024) MAX_SIZE_MB,

round(USED_PERCENT,2) USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS where USED_PERCENT>80

order by  USED_PERCENT; 

               or

select tablespace_name,round((USED_SPACE*8)/1024)CURRENT_SIZE_MB,round((TABLESPACE_SIZE*8)/1024) MAX_SIZE_MB,

round(USED_PERCENT,2) USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS where  tablespace_name like '%TEMP%' order by USED_PERCENT;

Create Tablespace script:

SELECT 'CREATE TABLESPACE ' || TABLESPACE_NAME || ' DATAFILE SIZE 500M;' FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT IN ('SYSTEM','UNDO1','SYSAUX','TEMP1','TOOLS'); 

SELECT 'DROP TABLESPACE ' || TABLESPACE_NAME || ';' FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT IN ('SYSTEM','UNDO1','SYSAUX','TEMP1','TOOLS' ); 

                                         

or

CREATE TABLESPACE "MARS"  DATAFILE '+DATA' size 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Query to tablespace autoextend:

set lines 200 pages 600

col file_name for a70

select file_name,autoextensible,ceil(maxbytes/(1024*1024)) maxsize_mb, ceil(bytes/(1024*1024)) cursize_mb from dba_data_files where tablespace_name='MARS';

To check if tablespace is having bigfile:

SELECT tablespace_name, bigfile FROM dba_tablespaces where tablespace_name='MARS';

Adding datafile:

alter tablespace PUB add datafile '/u01/app/oracle/oradata/mkeustg1/pub02.dbf' size 1G autoextend on maxsize unlimited;

              or

ALTER TABLESPACE MARS   ADD DATAFILE '+DATA1' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;

To check users default tablespace:

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where USERNAME='SCOTT';

Temp Tablespace:

select file_name,autoextensible,ceil(maxbytes/(1024*1024)) maxsize_mb, ceil(bytes/(1024*1024)) cursize_mb from dba_temp_files where tablespace_name='TEMP';

Resize Temp tablespace:

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON ;

To find the temp tablespace size:

SELECT A.tablespace_name tablespace, D.gb_total,

    SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024 gb_used,

    D.gb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024  gb_free

   FROM v$sort_segment A,

    (

   SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 /1024 gb_total

    FROM v$tablespace B, v$tempfile C

     WHERE B.ts#= C.ts#

      GROUP BY B.name, C.block_size) D

    WHERE A.tablespace_name = D.name

    GROUP by A.tablespace_name, D.gb_total;

To find what is occupied in the temp:

select * from v$temp_extent_pool;

Temp Tablespace usage by session:

SELECT b.tablespace,

ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,

a.inst_id as Instance,

a.sid||','||a.serial# AS sid_serial,

NVL(a.username, '(oracle)') AS username,

a.program, a.status, a.sql_id

FROM gv$session a, gv$sort_usage b, gv$parameter p

WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr

AND a.inst_id=b.inst_id AND a.inst_id=p.inst_id

ORDER BY temp_size desc;

To Chk is sorting is going on 

Select tablespace_name,USED_BLOCKS,FREE_BLOCKS,MAX_SORT_BLOCKS from   v$sort_segment;

Select   EXTENT_SIZE,CURRENT_USERS,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS  from v$sort_segment;

Shrink TEMP Tablespace to the smallest possible size:

ALTER TABLESPACE temp SHRINK SPACE;

ALTER TABLESPACE temp01 SHRINK SPACE KEEP 50M;

To check the space available in all tablespace:

col "Tablespace" for a22

col "Used MB" for 99,999,999

col "Free MB" for 99,999,999

col "Total MB" for 99,999,999

select df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB",

round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))

"Pct. Free"

from

(select tablespace_name,

round(sum(bytes) / 1048576) TotalSpace

from dba_data_files

group by tablespace_name) df,

(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name

from dba_segments

group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name ;

Output:

Tablespace                 Used MB     Free MB    Total MB  Pct. Free

---------------------- ----------- ----------- ----------- ----------

SYSAUX                       2,786         814       3,600         23

To check the space available in that particular tablespace

select c.tablespace_name,

round(decode(d.autoext,NULL,decode(e.free,NULL,0,e.free)/(1024*1024),(f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz)-f.totsz+decode(e.free,NULL,0,e.free))/ (1024*1024))) free_mb,

round(f.totsz/(1024*1024)) Total_mb,

round((f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz))/(1024*1024)) maxsize_mb,

decode(d.autoext, NULL, 'NO','YES') ae,

round(decode(d.autoext,NULL,(f.totsz-decode(e.free,NULL,0,e.free))/f.totsz*100,(f.totsz-decode(e.free,NULL,0,e.free))/(f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz))*100)) as Used_pct

from dba_tablespaces c,

(select a.tablespace_name, count(b.autoextensible) autoext

from dba_tablespaces a, dba_data_files b

where a.tablespace_name = b.tablespace_name(+) and b.autoextensible = 'YES'

group by a.TABLESPACE_NAME

having count(b.autoextensible) > 0) d,

(select tablespace_name, sum(bytes) free

from dba_free_space

group by tablespace_name) e,

(select df1.tablespace_name, sum(df1.bytes) totsz, sum(df1.maxbytes) maxsz

from dba_data_files df1, dba_tablespaces ts1

where ts1.tablespace_name = df1.tablespace_name

group by df1.tablespace_name) f,

(select df2.tablespace_name, sum(df2.bytes) nonauto_sz

from dba_data_files df2, dba_tablespaces ts2

where ts2.tablespace_name = df2.tablespace_name and df2.autoextensible = 'NO'

group by df2.tablespace_name) g

where c.tablespace_name = d.tablespace_name(+) and

c.tablespace_name = e.tablespace_name(+) and

c.tablespace_name = f.tablespace_name and

c.tablespace_name = g.tablespace_name(+) and c.tablespace_name like '%UNDO%' order by c.tablespace_name;

Calculate how much undo tablespace we need exactly

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *

       g.undo_block_per_sec) / (1024*1024)

      "NEEDED UNDO SIZE [MByte]"

  FROM (

       SELECT SUM(a.bytes) undo_size

         FROM v$datafile a, v$tablespace b, dba_tablespaces c

        WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'

          AND b.name = c.tablespace_name AND a.ts# = b.ts#

       ) d,

      v$parameter e, v$parameter f,

       (

       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

         undo_block_per_sec

         FROM v$undostat

       ) g

 WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';

Solution:

Increasing UNDO tablespace size is not the solution for this.

Run Undo advisory

Increase UNDO Retension Period

Enable Undo Retention Guarantee

Run this Select Query during non-business hours where heavy transactions are not happening.

Tune the Query

Query to find tablespace growth in last week:

select b.tsname tablespace_name , MAX(b.used_size_Gb) cur_used_size_Gb , round(AVG(inc_used_size_Gb),2)avg_increas_Gb  

from ( SELECT a.days,a.tsname , used_size_Gb , used_size_Gb - LAG (used_size_Gb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_Gb

from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024*1024),2)) used_size_Gb

from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# 

AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7

GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname 

ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;

Resize.sh (Script)

#!/bin/csh

$ORACLE_HOME/bin/sqlplus -S aradmin/Gu1nn3ss@ITSMLDP4 << EOF

Spool Resize.lst

@Resize.sql

spool off

exit

EOF

Hostname: $ nohup  Resize.sh & (Press enter)

Enjoy the work and do like me if you like this article!!!

Database Monitoring scripts

Cronjob:

0 8 * * * /path/to/your/Monitoring.sh

Save the below file with name Monitoring.sh

chmod +x script_name.sh

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Path to the alert log file

ALERT_LOG_FILE="/path/to/alert.log"

# Function to send an email

send_email() {

    SUBJECT="Database Alert Log Error"

    MESSAGE="An error was found in the database alert log. Please check the log file for details."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check the alert log for errors

check_alert_log() {

    ERROR_COUNT=$(grep -i "ORA-" "${ALERT_LOG_FILE}" | wc -l)

    if [ $ERROR_COUNT -gt 0 ]; then

        send_email

    fi

}

# Main script execution

check_alert_log

****Try this**************************************************

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Path to the alert log file

ALERT_LOG_FILE="/path/to/alert.log"

# Function to send an email

send_email() {

    SUBJECT="Database Alert Log Error"

    MESSAGE="An error was found in the database alert log. Please check the log file for details."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check the alert log for errors

check_alert_log() {

    ERROR_COUNT=$(awk '/ORA-/ {count++} END {print count}' "${ALERT_LOG_FILE}")

    if [ $ERROR_COUNT -gt 0 ]; then

        send_email

    fi

}

# Main script execution

check_alert_log

***********************************************************

Here's a script that checks if the database and listener are down and sends an email notification if either of them is not running:

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Listener configuration

LISTENER_NAME="your_listener_name"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Function to send an email

send_email() {

    SUBJECT="Database or Listener Down"

    MESSAGE="The database or listener is not running. Please check the status."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check if the database is running

check_database() {

    DB_STATUS=$(echo "SELECT 'Database Open' FROM dual;" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Database Open")

    if [ $DB_STATUS -eq 0 ]; then

        send_email

    fi

}

# Check if the listener is running

check_listener() {

    LISTENER_STATUS=$(lsnrctl status ${LISTENER_NAME} | grep -c "The listener supports no services")

    if [ $LISTENER_STATUS -eq 1 ]; then

        send_email

    fi

}

# Main script execution

check_database

check_listener

***********************************************************

Here's a script that checks if there are any RMAN backups, export backups, or statistics jobs running and sends an email notification if any of them are in progress:

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Function to send an email

send_email() {

    SUBJECT="Database Backup or Job Running"

    MESSAGE="There is a database backup or job running. Please check the status."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check if there are any RMAN backups running

check_rman_backup() {

    RMAN_STATUS=$(echo "SELECT 'RMAN Backup Running' FROM v\$rman_status WHERE status = 'RUNNING';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "RMAN Backup Running")

    if [ $RMAN_STATUS -gt 0 ]; then

        send_email

    fi

}

# Check if there are any export backups running

check_export_backup() {

    EXPORT_STATUS=$(echo "SELECT 'Export Backup Running' FROM v\$session WHERE program LIKE 'expdp%';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Export Backup Running")

    if [ $EXPORT_STATUS -gt 0 ]; then

        send_email

    fi

}

# Check if there are any statistics jobs running

check_stats_job() {

    STATS_STATUS=$(echo "SELECT 'Statistics Job Running' FROM dba_scheduler_running_jobs WHERE job_name LIKE '%GATHER_STATS_JOB%';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Statistics Job Running")

    if [ $STATS_STATUS -gt 0 ]; then

        send_email

    fi

}

# Main script execution

check_rman_backup

check_export_backup

check_stats_job

***********************************************************

Here's a script that performs a basic health check on the database and sends an email notification if the database performance is below a certain threshold

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Threshold values (you can adjust these values as per your requirements)

CPU_THRESHOLD=80

MEMORY_THRESHOLD=80

# Function to send an email

send_email() {

    SUBJECT="Database Health Check Alert"

    MESSAGE="The database performance is below the acceptable threshold. Please investigate the issue."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check CPU usage

check_cpu_usage() {

    CPU_USAGE=$(top -b -n 1 | grep "%Cpu(s)" | awk '{print $2}' | cut -d. -f1)

    if [ $CPU_USAGE -ge $CPU_THRESHOLD ]; then

        send_email

    fi

}

# Check memory usage

check_memory_usage() {

    MEMORY_USAGE=$(free | awk '/Mem/ { printf("%.2f"), $3/$2 * 100 }')

    if (( $(echo "$MEMORY_USAGE >= $MEMORY_THRESHOLD" | bc -l) )); then

        send_email

    fi

}

# Main script execution

check_cpu_usage

check_memory_usage

***********************************************************

Here's a script that checks if a tablespace has less space available than a specified threshold and sends an email notification immediately

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Tablespace and threshold configuration

TABLESPACE_NAME="your_tablespace_name"

THRESHOLD_PERCENTAGE=80

# Function to send an email

send_email() {

    SUBJECT="Tablespace Space Alert"

    MESSAGE="The tablespace '${TABLESPACE_NAME}' has less space available. Please check and take appropriate actions."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check tablespace space usage

check_tablespace_space() {

    SPACE_USAGE=$(echo "SELECT ROUND((1 - (df.totalspace - fs.freespace) / df.totalspace) * 100, 2) AS used_percentage FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS totalspace FROM dba_data_files WHERE tablespace_name = '${TABLESPACE_NAME}' GROUP BY tablespace_name) df, (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS freespace FROM dba_free_space WHERE tablespace_name = '${TABLESPACE_NAME}' GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name;" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | awk '{print $1}')

    if (( $(echo "$SPACE_USAGE >= $THRESHOLD_PERCENTAGE" | bc -l) )); then

        send_email

    fi

}

# Main script execution

check_tablespace_space

*******************************************************

Here's a script that checks if there are any failed RMAN backups, export backups, or statistics jobs, and sends an email notification immediately

#!/bin/bash

# Database credentials

DB_USERNAME="your_db_username"

DB_PASSWORD="your_db_password"

DB_SID="your_db_sid"

# Email configuration

MAIL_SERVER="your_mail_server"

MAIL_PORT="your_mail_port"

MAIL_USERNAME="your_mail_username"

MAIL_PASSWORD="your_mail_password"

MAIL_FROM="your_email_address"

MAIL_TO="recipient_email_address"

# Function to send an email

send_email() {

    SUBJECT="Backup or Job Failure Alert"

    MESSAGE="There is a failure in one or more backups or jobs. Please check the status."

    echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \

        openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \

        -CApath /etc/ssl/certs/ -ign_eof \

        -auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \

        -to "${MAIL_TO}" -from "${MAIL_FROM}"

}

# Check for failed RMAN backups

check_failed_rman_backups() {

    FAILED_BACKUPS=$(echo "SELECT 'RMAN Backup Failed' FROM v\$rman_backup_job_details WHERE status = 'FAILED';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "RMAN Backup Failed")

    if [ $FAILED_BACKUPS -gt 0 ]; then

        send_email

    fi

}

# Check for failed export backups

check_failed_export_backups() {

    FAILED_EXPORTS=$(echo "SELECT 'Export Backup Failed' FROM dba_datapump_jobs WHERE state = 'NOT RUNNING' AND operation = 'EXPORT' AND status = 'EXCEPTION';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Export Backup Failed")

    if [ $FAILED_EXPORTS -gt 0 ]; then

        send_email

    fi

}

# Check for failed statistics jobs

check_failed_stats_jobs() {

    FAILED_JOBS=$(echo "SELECT 'Statistics Job Failed' FROM dba_scheduler_job_run_details WHERE status = 'FAILED' AND job_name LIKE '%GATHER_STATS_JOB%';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Statistics Job Failed")

    if [ $FAILED_JOBS -gt 0 ]; then

        send_email

    fi

}

# Main script execution

check_failed_rman_backups

check_failed_export_backups

check_failed_stats_jobs

Enjoy the work and do like me if you like this article!!!

Schema refresh steps from Test to Pre-production environment

Scenario 1:
To copy the MNPHIST schema (only the structure not the data) from SIT1  to Pre-production environment:


Source: HOST = Test1
Target: HOST = preTest 

SQL> select name,instance_name,open_mode,to_char(STARTUP_TIME,'dd/mm/yy hh24:mi:ss'),STATUS ,HOST_NAME from v$database,v$instance;
NAME      INSTANCE_NAME    OPEN_MODE            TO_CHAR(STARTUP_T STATUS    HOST_NAME
--------- ---------------- -------------------- ----------------- --------------------
MVMNPTS1  mvmnpts1_2       READ WRITE           30/06/12 00:09:01 OPEN    prser032

SQL>  SELECT OBJECT_TYPE,COUNT(*) FROM dba_objects WHERE OWNER='MNPHIST' GROUP BY OBJECT_TYPE;

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                   42
INDEX                      124
TABLE                       62


srvctl status database -d MVMNPT -v -f

SELECT Sum(bytes)/1024 AS total_size_GB FROM dba_segments WHERE owner ='GPCOMP_MT02';

SELECT Sum(bytes)/1024/1024/1024 AS total_size_GB FROM dba_segments WHERE owner like 'MNPHIST';

SELECT OBJECT_TYPE,COUNT(*) FROM dba_objects WHERE OWNER='MNPHIST' GROUP BY OBJECT_TYPE;
NAME      INSTANCE_NAME    OPEN_MODE            TO_CHAR(STARTUP_T STATUS    HOST_NAME
--------- ---------------- -------------------- ----------------- ------------
MNPT      MNPT_2           READ WRITE           18/07/12 14:58:39 OPEN    prser035


SQL> SELECT * FROM dba_directories;
OWNER                          DIRECTORY_NAME    DIRECTORY_PATH
------------------------------ ------------------------------
SYS                            MNP_EXP_DUMP_PRE    /export_MNP/preprod === =here dump file is there
SYS                            MNP_EXP_DUMP_PROD    /export_MNP/prod
SYS                            XMLDIR    /u01/app/oracle/product/11.2.0/MNPT/rdbms/xml
SYS                            EXP_BKP_MNPT2    /dump/exp_bkp_MNPT2
SYS                            DATA_PUMP_DIR    /u01/app/oracle/product/11.2.0/MNPT/rdbms/log/
SYS                            ORACLE_OCM_CONFIG_DIR    /u01/app/oracle/product/11.2.0/MNPT/ccr/state

expdp \'/ as sysdba \' directory=DATA_PUMP_DIR dumpfile=MNPHIST_expdp_source.dmp logfile=MNPHIST_expdp_source.log schemas=MNPHIST CONSISTENT=Y CONTENT=metadata_only job_name=MNPHISTexpdp 

Ftp the dump file from source to the target

impdp \'/ as sysdba \' directory=DATA_PUMP_DIR dumpfile=MNPHIST_expdp_source.dmp logfile=MNPHIST_expdp_source.log schemas=MNPHIST CONSISTENT=Y CONTENT=metadata_only job_name=MNPHISTexpdp

Sytax to run Export and Import in background  if its too big:
nohup expdp \'/ as sysdba \' directory=MNP_EXP_DUMP_PRE dumpfile=MNPHIST_expdp_target.dmp logfile=MNPHIST_expdp_target.log schemas=MNPHIST CONSISTENT=Y CONTENT=metadata_only job_name=MNPHISTexpdp &

nohup impdp \'/ as sysdba \' directory=MNP_EXP_DUMP_PRE dumpfile=MNPHIST_expdp_source.dmp logfile=MNPHIST_impdp_source.log schemas=MNPHIST job_name=MNPHISTimpdp &

@?/rdbms/admin/utlrp.sql: 
ORACLE_HOME/rdbms/admin/utlrp.sql to compile the invalid objects
Count the objects:
select object_type,status,count(*) from dba_objects where owner='MNPHIST' group by object_type,status;

Enjoy the work and do like me if you like this article!!!

Database growth scripts

Query to find out the Oracle Database size:
SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM    (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;

Example output :
Create Ti Database Name Database Size Used Space  Used in %   Free Space   Free in %   Growth DAY  Growth DAY in %  Growth WEEK  Growth WEEK in %
--------- ------------- ------------- ----------- ----------- ------------ ----------- ----------- ---------------- ------------ ----------------
06/DEC/10 xxxxxxxx       11645818.5 MB 2996866.5 MB 25.73% MB   8648951.56 MB 74.27% MB   8075.16 MB  .069% MB         56526.13 MB  .485% MB
 

To check database growth in months:

SELECT TO_CHAR(creation_time, 'YYYY-MM') AS month,

       SUM(bytes) / 1024 / 1024 / 1024 AS growth_in_gb

FROM dba_segments

WHERE creation_time >= TRUNC(SYSDATE, 'MM')

GROUP BY TO_CHAR(creation_time, 'YYYY-MM')

ORDER BY month;

SET FEEDBACK ON

select to_char(creation_time, 'RRRR Month') "Month",

    sum(bytes)/1024/1024/1024 "Growth in GBs"

    from sys.v_$datafile

    where creation_time > SYSDATE-365

    group by to_char(creation_time, 'RRRR Month');

Query to find tablespace growth in last week:

select b.tsname tablespace_name , MAX(b.used_size_Gb) cur_used_size_Gb , round(AVG(inc_used_size_Gb),2)avg_increas_Gb  

from ( SELECT a.days,a.tsname , used_size_Gb , used_size_Gb - LAG (used_size_Gb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_Gb

from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024*1024),2)) used_size_Gb

from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# 

AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7

GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname 

ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;

Enjoy the work and do like me if you like this article!!!

User management

Create user script

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where USERNAME='MARS';

create user MARS identified by Z96bix#123;

CREATE USER MARS IDENTIFIED BY XXXXXXX DEFAULT TABLESPACE USERS PROFILE CUSTOMER;

Change User Default Tablespace

alter user usr1 default tablespace example;

CREATE USER SCOTT

  IDENTIFIED BY  xyz123##

  DEFAULT TABLESPACE ARSYSTEM

  TEMPORARY TABLESPACE ARTMPSPC

  PROFILE SCOTT_USER

  ACCOUNT UNLOCK;

  -- 4 Roles for SCOTT 

  GRANT ARADMIN_READ_ONLY TO SCOTT;

  GRANT AROLE TO SCOTT;

  GRANT DATA_MANAGER TO SCOTT WITH ADMIN OPTION;

  GRANT SELECT_CATALOG_ROLE TO SCOTT;

  ALTER USER SCOTT DEFAULT ROLE ALL;

 
ALTER USER SCOTT IDENTIFIED BY "Sc123!"  ACCOUNT LOCK PASSWORD EXPIRE;
 (This will ensure scott the Scott user password will changed and on successfull login it will prompt to change the password and the account will be lock

To make the password as expire:

alter user DATASAFE password expire;

To set the same password

alter user MARS identified by values 'S:6854B22E744A65D6C10456319075DC87CA65A3BAF93C6126F539818AC2E3;T:196';

To unlock the account:

alter user dev_class account lock;

alter user dev_class account unlock;

To check when the password of a user has changed:

select name, to_char(ptime,'DD/MM/YY HH24:MIS') as pw_last_changed from sys.user$

Query to find the user password change and account lock history:

SELECT NAME, CTIME, PTIME, LTIME, LCOUNT FROM SYS.USER$ WHERE NAME='CT2467408';

  -- 7 System Privileges for SCOTT 

  GRANT CREATE ANY SNAPSHOT TO SCOTT;

  GRANT CREATE ANY TABLE TO SCOTT;

  GRANT CREATE DATABASE LINK TO SCOTT;

  GRANT CREATE ROLE TO SCOTT;

  GRANT CREATE TABLE TO SCOTT;

  GRANT ON COMMIT REFRESH TO SCOTT;

  GRANT UNLIMITED TABLESPACE TO SCOTT;

GRANT CONNECT TO MARS;

GRANT RESOURCE TO MARS;

GRANT CREATE SESSION TO MARS;

GRANT SELECT ANY DICTIONARY TO MARS;

CREATE PROFILE SERVICE_ACCOUNT

LIMIT

COMPOSITE_LIMIT DEFAULT

SESSIONS_PER_USER DEFAULT

CPU_PER_SESSION DEFAULT

CPU_PER_CALL DEFAULT

LOGICAL_READS_PER_SESSION DEFAULT

LOGICAL_READS_PER_CALL DEFAULT

IDLE_TIME DEFAULT

CONNECT_TIME DEFAULT

PRIVATE_SGA DEFAULT

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LIFE_TIME UNLIMITED

PASSWORD_REUSE_TIME 31536000/86400

PASSWORD_REUSE_MAX 10

PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION

PASSWORD_LOCK_TIME 432000/86400

PASSWORD_GRACE_TIME UNLIMITED

INACTIVE_ACCOUNT_TIME DEFAULT

/

alter user MARS profile new_profile;

 alter profile PROFILE_DBA_USER limit PASSWORD_VERIFY_FUNCTION "PWD_VERIFY_FUNCTION_12C";

Tablespace Quotas for REPORTER:

ALTER USER REPORTER QUOTA UNLIMITED ON REPASSM_TEMP;

ALTER USER REPORTER QUOTA 50M ON REPHOME;

To get metadata of a user:

set heading off;

set echo off;

SET TRIMSPOOL ON

SET FEEDBACK OFF

set lines 1000 pages 4000; 

set long 1000000 

select dbms_metadata.get_ddl('USER','T466464') from dual;

SELECT DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','T466464') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','VAIBHAVE') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SUB') from dual;

SELECT dbms_metadata.get_ddl('ROLE','SUB') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','RESOURCE') from dual;

Enjoy the work and do like me if you like this article!!!

Daily Usefull Oracle Database scripts

************Db  status script *****************************

SQL>select 'Hostname      : ' || host_name

      ,'Instance Name : ' || instance_name

      ,'Started At    : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime

      ,'Uptime        : ' || floor(sysdate - startup_time) || ' days(s) ' ||

       trunc( 24*((sysdate-startup_time) -

       trunc(sysdate-startup_time))) || ' hour(s) ' ||

       mod(trunc(1440*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||

       mod(trunc(86400*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' seconds' uptime

from sys.v_$instance;

********For RAC server use the below query************************

select 'Hostname      : ' || host_name

      ,'Instance Name : ' || instance_name

      ,'Started At    : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime

      ,'Uptime        : ' || floor(sysdate - startup_time) || ' days(s) ' ||

       trunc( 24*((sysdate-startup_time) -

       trunc(sysdate-startup_time))) || ' hour(s) ' ||

       mod(trunc(1440*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||

       mod(trunc(86400*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' seconds' uptime

from sys.gv_$instance;

                                      (or)

SQL> select to_char(start_time, ‘dd-mon-yy hh24:mi:ss’) start_time,

 to_char(end_time,‘dd-mon-yy hh24:mi:ss’) end_time

 from stats$dates;

************Which schemas  taking up all of the free space************

set pages 999

col "size MB" format 999,999,999

col "Objects" format 999,999,999

select      obj.owner "Owner"

,              obj_cnt "Objects"

,              decode(seg_size, NULL, 0, seg_size) "size MB"

from        (select owner, count(*) obj_cnt from dba_objects group by owner) obj

,              (select owner, ceil(sum(bytes)/1024/1024) seg_size

               from dba_segments group by owner) seg

where      obj.owner  = seg.owner(+)

order       by 3 desc ,2 desc, 1;

To check which tables are accessing frequently on the database********

 set lines 80;

set pages 999;

column avg_touches           format 999

column myname heading 'Name' format a30

column mytype heading 'Type' format a10

column buffers               format 999,999

SELECT  object_type mytype,object_name myname,blocks,COUNT(1) buffers,AVG(tch) avg_touches

FROM sys.x$bh a,dba_objects b,dba_segments s WHERE a.obj = b.data_object_id

and b.object_name = s.segment_name and b.owner not in ('SYS','SYSTEM')

GROUP BY   object_name,object_type,blocks,obj HAVING AVG(tch) > 5 AND  COUNT(1) > 20;

*****Rman backup job status************************************

col STATUS format a9

col hrs format 999.99

select

SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,

elapsed_seconds/3600                   hrs

from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,   

       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"  

FROM V$SESSION_LONGOPS   

WHERE OPNAME LIKE 'RMAN%'  

  AND OPNAME NOT LIKE '%aggregate%'  

  AND TOTALWORK != 0   

  AND SOFAR  != TOTALWORK;  

**To find out the table size****************************************

SQL> select segment_type, sum(bytes/1024/1024) size_mb from dba_segments  where OWNER ='V500' and SEGMENT_NAME='CR_REPORT_STYLE_PROFILE' group by segment_type;

*****Checked and found no segment could not extend****************

 SELECT s.owner, s.segment_type, s.segment_name,s.tablespace_name ,s.next_extent

       FROM dba_segments s

      WHERE s.next_extent * ( 1 + s.pct_increase / 100 ) >

      (SELECT MAX( f.bytes ) FROM dba_free_space f

         WHERE f.tablespace_name = s.tablespace_name );

no rows selected

***Displaying Segment Information********************************

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'INDEX'     AND OWNER='ARADMIN' AND SEGMENT_NAME='T171_PERF_2';

***Query to check the tablespace usage metrics*******

SQL> select * from dba_tablespace_usage_metrics order by used_percent desc;

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT

------------------------------ ---------- --------------- ------------

D_R_MEDIUM                         251784          286336   87.9330577

D_R_SMALL                          375408          438016   85.7064582

L_R_SMALL                          788560          933760   84.4499657

*****Here is a simple way to calculate the average number of transactions per second since database startup

select round(sum(s.value / (86400 * (SYSDATE - startup_time))),3) "TPS" from v$sysstat s ,v$instance i where s.NAME in ('user commits','transaction rollbacks');

*****script gives me the database growth as follows**************

select to_char(creation_time, 'RRRR Month') "MONTH",

sum(bytes)/1024/1024 "GROWTH IN MB"

from sys.v_$datafile

where creation_time >= SYSDATE-365

group by to_char(creation_time, 'RRRR Month');

MONTH GROWTH IN MB;

SQL>select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB

 from   v$datafile group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM') order by   1, 2;

how many  datafiles were added in month********

select 

  to_char(CREATION_TIME,'RRRR') year,

  to_char(CREATION_TIME,'MM') month,

  sum(bytes/1024/1024)  size_mb

 from    v$datafile

 group by

 to_char(CREATION_TIME,'RRRR'),

 to_char(CREATION_TIME,'MM')

 order by 

   1, 2;

This script will show "spaced used total" for a specific Oracle table, essentiall computing the Oracle table size over time************************

 col c1 format a15 heading 'snapshot|date' 

col c2 format a25 heading 'table|name' 

col c3 format 999,999,999 heading 'space|used|total'

select 

   to_char(begin_interval_time,'yy/mm/dd hh24:mm')     c1, 

   object_name c2,space_used_total c3 from 

   dba_hist_seg_stat       s, 

   dba_hist_seg_stat_obj   o, 

   dba_hist_snapshot       sn 

where    o.owner = 'V500' and    s.obj# = o.obj# and    sn.snap_id = s.snap_id and    object_name='CR_REPORT_STYLE_PROFILE' 

order by begin_interval_time;

****User quotas on tablespaces*********************************

select username

,      tablespace_name

,      decode(max_bytes, -1, 'unlimited'

       , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"

from   dba_ts_quotas

where  tablespace_name not in ('TEMP');

************ Checking Active and Inactive session script **************

SQL>select sid,serial#,LOCKWAIT,STATUS,username from v$session;

SQL>select USERNAME,OSUSER,PROGRAM,LOGON_TIME from  v$session where status in ('ACTIVE');

SQL>select SID,SERIAL#,USERNAME,LOCKWAIT,STATUS,PROGRAM,LOGON_TIME from  v$session where USERNAME='XXXX' and STATUS='ACTIVE';

SQL>select USERNAME,OSUSER,PROGRAM,LOGON_TIME from  v$session where status in ('ACTIVE');

1

************To Chk is sorting is going on **************************

SQL>Select tablespace_name,USED_BLOCKS,FREE_BLOCKS,MAX_SORT_BLOCKS from   v$sort_segment;

SQL> Select   EXTENT_SIZE,CURRENT_USERS,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS  from v$sort_segment;

SQL> SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM   V$temp_space_header    GROUP  BY tablespace_name;

SQL>select FILE_NAME,BYTES/1024/1024 from dba_temp_files where 

TABLESPACE_NAME='TEMP';

SQL> ALTER TABLESPACE <tablespace_name>   ADD  TEMPFILE < tempfile name > SIZE <Est_mb>m                              

                                                      OR 

SQL>ALTER DATABAS TEMPFILE <tempfile name>    RESIZE  < Current_size + Est_mb>m;

**** dead locks script***************

SQL>select 'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking

from v$lock l1, v$lock l2

where l1.block =1 and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2;

************* List locks script************************************

column lock_type format a12

column mode_held format a10

column mode_requested format a10

column blocking_others format a20

column username format a10

SQL>SELECT session_id

, lock_type

, mode_held

, mode_requested

, blocking_others

, lock_id1

FROM dba_lock l

WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread');

************ Show locked objects script***************************

set lines 100 pages 999

col username format a20

col sess_id format a10

col object format a25

col mode_held format a10

SQL>select oracle_username || ' (' || s.osuser || ')' username

, s.sid || ',' || s.serial# sess_id

, owner || '.' || object_name object

, object_type

, decode( l.block

, 0, 'Not Blocking'

, 1, 'Blocking'

, 2, 'Global') status

, decode(v.locked_mode

, 0, 'None'

, 1, 'Null'

, 2, 'Row-S (SS)'

, 3, 'Row-X (SX)'

, 4, 'Share'

, 5, 'S/Row-X (SSX)'

, 6, 'Exclusive', TO_CHAR(lmode)) mode_held

from v$locked_object v

, dba_objects d

, v$lock l

,v$session s

where v.object_id = d.object_id

and v.object_id = l.id1

and v.session_id = s.sid

order by oracle_username

, session_id;

************Show which row is locked script***********************

SQL>select do.object_name

, row_wait_obj#

, row_wait_file#

, row_wait_block#

, row_wait_row#

, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, 

ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)

from v$session s

, dba_objects do

where sid=&sid

and s.ROW_WAIT_OBJ# = do.OBJECT_ID;

*********Check Running jobs status script***************************

SQL>SELECT job,schema_user, to_char(last_date, 'mm/dd/yyyy hh:mi pm') lastdate, to_char(next_date, 'mm/dd/yyyy hh:mi pm') nextdate, failures, broken, what  FROM dba_jobs  ORDER BY next_date;

                                Or

SELECT 

 'Job:'|| job,

 WHAT,

 'Next:'|| to_char(NEXT_DATE,'dd-Mon-yyyy HH24:MI'),

 ' Last:'|| to_char(LAST_DATE,'dd-Mon-yyyy HH24:MI'),

 ' Broken:'|| BROKEN

 FROM dba_jobs;

                               Or

SQL>select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,BROKEN,FAILURES,TOTAL_TIME from user_jobs;

Create a DBMS_STATS.gather_schema_stats job that runs daily at 11pm (23:00) ******************

 Set SERVEROUT ON

 Set pagesize 200

 

 DECLARE

 jobno NUMBER;

 BEGIN

 DBMS_JOB.submit

 (job => jobno,

 what => 'DBMS_STATS.gather_schema_stats(ownname => ''scott'', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);',

 next_date => trunc(sysdate)+23/24,

 interval => 'SYSDATE + 1',

 no_parse => TRUE );

 

 DBMS_OUTPUT.put_line ('Created Job - the job number is:' || TO_CHAR (jobno));

 COMMIT;

 END;

 /

******To detect Oracle long running queries script*******************

SET PAGESIZE 80
SET LINESIZE 100
COL SQL_TEXT WORD_WRAP
SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS,
TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME,
TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME,
TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME
FROM V$SESSION_LONGOPS
WHERE TIME_REMAINING != 0;

***To detect long operations**************************************

select sid,username,opname,sql_address,

        to_char(start_time ,'mm/dd/yyyy hh24:mi:ss') as sttime,

        to_char(last_update_time,'mm/dd/yyyy hh24:mi:ss') as luptime,

        to_char(time_remaining,'9999999999') as timeremain,

        to_char(elapsed_seconds,'999999999') as elapstime

from v$session_longops a

join v$sqltext_with_newlines b on b.address=a.sql_address

where  (last_update_time - start_time) >= (1/24/4)

order by sid,opname,a.sql_address,last_update_time desc;

**************To Show all running SQLs in the Db**************

SQL>select distinct spid, s.sid, s.serial#,to_char(sysdate -

last_call_et/(24*3600),'mm/dd/yyhh24:mi:ss') "LAST_ACTIVITY",

       logon_time,

       osuser,

       s.program,

       schemaname,

       sql_text

  from v$session s,

       v$process p,

       v$sql t

  where s.paddr=p.addr

    and t.hash_value(+)=s.sql_hash_value

    and s.type !='BACKGROUND';

Query to check which user process has occupied high memory usage*****

SQL> SELECT spid, program,

            pga_max_mem      max,

            pga_alloc_mem    alloc,

            pga_used_mem     used,

            pga_freeable_mem free

       FROM V$PROCESS

      WHERE spid = 471102;

***what the session is doing and how much CPU they have consumed******

SQL>select ss.sid,se.command,ss.value CPU ,se.username,se.program 

from v$sesstat ss, v$session se

where ss.statistic# in 

(select statistic# 

from v$statname 

where name = 'CPU used by this session')

and se.sid=ss.sid 

and ss.sid>6

order by ss.sid;

*****Users and Sessions CPU consumption can be obtained by below query*****

Set lines 1000

select ss.username, se.SID,VALUE/100 cpu_usage_seconds

from v$session ss, v$sesstat se, v$statname sn

where se.STATISTIC# = sn.STATISTIC#

and NAME like '%CPU used by this session%'

and se.SID = ss.SID and ss.status='ACTIVE'

and ss.username is not null

order by VALUE desc;

 shows Day wise,User wise,Process id of server wise- CPU and I/O consumption****

set linesize 140

col spid for a6

col program for a35 trunc

select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,

ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,

round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day

from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg

where s.paddr=p.addr and ss.sid=s.sid

and ss.statistic#=12 and si.sid=s.sid

and bg.paddr(+)=p.addr

and round((ss.value/100),0) > 10

order by 8;

*****To find out what sql  session(s) are executing******

SQL>select s.sid, event, wait_time, w.seq#, q.sql_text

from v$session_wait w, v$session s, v$process p, v$sqlarea q

where s.paddr=p.addr and

s.sid=&p and s.sql_address=q.address;

                       or

SELECT SQL_TEXT

FROM V$SQLAREA

WHERE (ADDRESS, HASH_VALUE) IN

      (SELECT SQL_ADDRESS, SQL_HASH_VALUE

       FROM V$SESSION

       WHERE SID = &sid_number);

               or

SQL>SELECT sql_id, child_number, sql_text FROM v$sql WHERE sql_text LIKE '%online discount%' AND sql_text NOT LIKE '%v$sql%';

****To check username,osuser,terminal,sid,serial# connected to the Database*****

SELECT USERNAME, OSUSER, TERMINAL, SID, SERIAL#,

       PROCESS, PROGRAM FROM V$SESSION ORDER BY OSUSER;

               

*****Show all connected users*********************

set lines 100 pages 999

col ID format a15

select username

,      sid || ',' || serial# "ID"

,      status

,      last_call_et "Last Activity"

from   v$session

where  username is not null

order by status desc

,        last_call_et desc;

*****Time since last user activity*********************

set lines 100 pages 999

select username

,      floor(last_call_et / 60) "Minutes"

,      status

from   v$session

where  username is not null

order by last_call_et;

******Show tables that have analyze dates older than today**********

select     count(last_analyzed) left_to_do

from      dba_tables

where    owner = '&schema'

and       trunc(last_analyzed) < trunc(sysdate)

order     by 1;

                                   OR

SELECT OWNER,TABLE_NAME, NUM_ROWS,LAST_ANALYZED,DURATION FROM DBA_TABLES WHERE OWNER='ARADMIN' AND LAST_ANALYZED < (SYSDATE -7) AND NUM_ROWS >100

                                  OR

select OWNER,TABLE_NAME,LAST_ANALYZED,NUM_ROWS,STATUS from dba_tables where OWNER=’ARADMIN’ order by LAST_ANALYZED;

Enjoy the work and do like me if you like this article!!!