Oracle Database Scripts

Sunday, May 7, 2023

File System Free Space Automatically Shell Script Linux

touch freethespace.sh

chmod 777 freethespace.sh

vim freethespace.sh

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

#!/bin/bash

echo "Enter year:"

read year

echo "Enter month:"

read month

echo "Enter day:"

read day

rm -rf ${year}_${month}_${day}_*

#rm -rf $(date --date='1 days ago' '+%Y_%m_%d')*

================================================

To check file exists in current directory :

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

#!/bin/bash

echo "Enter your file to be search:"

read file

if test -f $file

then

echo "file does exists !"

else

echo "no such file found !"

===============================================

ls -lrt | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Oct 18" | awk '{for(j=4;j<=NF;j++) printf("%s ",$j); printf("\n");}'

redo_551326936_2_657440.arc

redo_551326936_2_657441.arc

redo_551326936_1_758270.arc

redo_551326936_1_758271.arc

redo_551326936_1_758272.arc

redo_551326936_2_657442.arc

redo_551326936_1_758273.arc

redo_551326936_1_758274.arc

redo_551326936_1_758275.arc

redo_551326936_2_657443.arc

redo_551326936_1_758276.arc

redo_551326936_1_758277.arc

redo_551326936_2_657444.arc

redo_551326936_1_758278.arc

redo_551326936_1_758279.arc

ls -lrt | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Dec 22" | awk '{for(j=4;j<=NF;j++) printf("%s ",$j); printf("\n");}' | while read i;

do rm -rf $i ; done

ls -lrt *.arc | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Dec 23" | awk '{print $4}' | while read i; do rm -rf $i; done

ads00

ls -lrt | head -n 500 | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | awk '{print $4}' | while read n; do rm -rf $n; done

=========================================================================

for host and others to free up *.arc files

freeup.sh

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

#!/bin/bash

target_dir=/pac/host/bkup/orabkup01/host/archivelog/

target_dir_no_of_files=$(ls -lrt /pac/host/bkup/orabkup01/host/archivelog/*.arc | wc -l)

if [ "${target_dir_no_of_files}" -gt "3000" ]

then

echo "you can remove files"

ls -lrt /pac/host/bkup/orabkup01/host/archivelog/*.arc | head -n 2000 | awk '{print $9}' | while read n; do rm -rf $n; done

else

echo "files less than 3000"

=========================================================================

host:/pac/host/arch/host/archivelog/ $ echo `date --date='1 days ago' +'%Y_%m_%d'`

2022_12_08

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

ls -lrt 2022_12_10/*.arc | head -n 10000 | awk '{print $9}' | while read n; do rm -rf $n; done

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

62G 2022_12_10

463G 2022_12_11

host:/pac/host/arch/host/archivelog/ $ ls -lrth `date --date='1 days ago' +'%Y_%m_%d'`/*.arc | wc -l

1453

host:/pac/host/arch/host/archivelog/ $ ls -lrt 2022_12_10/*.arc | wc -l

1453

host:/pac/host/arch/host/archivelog/ $ ls -lrth `date --date='0 days ago' +'%Y_%m_%d'`/*.arc | wc -l

10695

host:/pac/host/arch/host/archivelog/ $ echo `date --date='0 days ago' +'%Y_%m_%d'`

2022_12_11

pwd

ls -lrth /pac/host/arch/host/archivelog/`date --date='0 days ago' +'%Y_%m_%d'`/*.arc 2>/dev/null | wc -l

ls -lrth /pac/host/arch/host/archivelog/`date --date='1 days ago' +'%Y_%m_%d'`/*.arc 2>/dev/null | wc -l

/pac/host/arch/host/archivelog/`date --date='0 days ago' +'%Y_%m_%d'`/*.arc

=========================================================================

#!/bin/bash

target_dir=/pac/host/bkup/orabkup01/host/archivelog/

target_dir_no_of_files=$(ls -lrt /pac/host/bkup/orabkup01/host/archivelog/*.arc | wc -l)

if [ "$(ps -ef | grep ora_pmon_ | wc -l)" -gt "1" ]

then

if [ "${target_dir_no_of_files}" -gt "3000" ]

then

echo "you can remove files"

ls -lrt /pac/host/bkup/orabkup01/host/archivelog/*.arc | head -n 2000 | awk '{print $9}' | while read n; do rm -rf $n; done

df -h /pac | mail -s "Space has been free now on host " example@gmail.com

else

echo "files less than 3000"

=========================================================================

#!/bin/bash

target_dir_yesterday=/pac/host/arch/host/archivelog/$(date --date='1 days ago' +'%Y_%m_%d')

target_dir_today_no_of_files=$(ls -lrt /pac/host/arch/host/archivelog/$(date --date='0 days ago' +'%Y_%m_%d')/*.arc | wc -l)

#echo $target_dir_yesterday

#echo $target_dir_today_no_of_files

#echo $(ps -ef | grep ora_pmon_ | wc -l)

if [ "$(ps -ef | grep ora_pmon_ | wc -l)" -gt "1" ]

then

if [ "${target_dir_today_no_of_files}" -gt "10000" ]

then

echo "you can remove files"

#to delete the archivelogs

ls -lrt $target_dir_yesterday/*.arc 2>/dev/null | awk '{print $9}' | while read m; do rm -rf $m; done

ls -lrt /pac/host/arch/host/archivelog/$(date --date='0 days ago' +'%Y_%m_%d')/*.arc | head -n 8000 | awk '{print $9}' | while read n;do rm -rf $n; done

# mail to respect groups

df -h /pac | mail -s "Space has been free now on host " example@gmail.com

else

echo "files less than 3000"

=========================================================================

host:/pac/host/arch/host/archivelog/ $ df -h | awk '{print $5,$6}'

Use% Mounted

0% /dev

0% /dev/shm

11% /run

0% /sys/fs/cgroup

24% /

26% /boot

77% /usr/local/opt/oracle

1% /opt/app/ggs

72% /pac

13% /var

1% /tmp

78% /pac/prod_backup2

48% /pac/prod_backup

0% /run/user/0

0% /run/user/4264524

0% /run/user/3095009

0% /run/user/1563708

65% /nas/osd

host:/pac/host/arch/host/archivelog/ $ df -h | awk '{print $5,$6}' | awk '{print $2}' | grep /usr/local/opt/oracle

/usr/local/opt/oracle

host:/pac/host/arch/host/archivelog/ $ df -h | awk '{print $5,$6}' | awk '{print $2}' | grep /usr/local/opt/oracle | read a

host:/pac/host/arch/host/archivelog/ $ echo $a

/usr/local/opt/oracle

=========================================================================

df -h /pac | awk '{print $5,$6}' | grep "60" | awk '{print $1}'

60%

host:/pac/host/arch/host/archivelog/ $ cat ch.sh

#!/bin/bash

chk=$(df -h /pac | awk '{print $5,$6}' | grep "44" | awk '{print $1}' | grep -o "\w[[:digit:]]" )

if [[ "${chk}" -eq "44" ]]

then

echo "/pac is 44% full"

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

for i in 24 25; do ls -lrth *.arc | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Dec $i" | awk '{print $4}' | while read n; do echo $n; done; done

=========================================================================

#!/bin/sh

check_trails_count=`ls -lrt /opt/app/ggs/trails/lt* | wc -l`

check_space=`df -h /opt/app/ggs | awk '{print $5}' | grep "[[:digit:]]" | cut -b1-2`

check_gg=`ps -ef | grep -i PARAM | grep -i -e "\.prm" | wc -l`

if [[ "${check_trails_count}" -gt "100" ]] && [[ "${check_space}" -gt "90" ]] && [[ "${check_gg}" -gt "0" ]]

then

echo "yes"

ls -lrt /opt/app/ggs/trails/lt* | head -n 50 | awk '{print $9}' | while read n; do rm -rf $n; done

df -h /opt/app/ggs | mail -s "Free the Space on /opt/app/ggs on host" example@gmail.com

location: /opt/app/ggs/trails/trails_free_up.sh

=========================================================================

#!/bin/sh

count_arch=`ls -lrt /pac/lci8t1/arch/oraarch01/*.arc | wc -l`

check_oracle=`ps -ef | grep -i ora_pmon_${ORACLE_SID} | grep -v grep | grep -i pmon | wc -l` # best command to find oracle is running or not #

if [[ "${count_arch}" -gt "500" ]] && [[ "${check_oracle}" -gt "0" ]]

then

echo "${count_arch}"

ls -lrt /pac/lci8t1/arch/oraarch01/*.arc | head -n 200 | awk '{print $9}' | while read n; do rm -rf $n; done;

df -h /pac | mail -s "Space has been free ${hostname} " example@gmail.com

=========================================================================

Shell Script For Auto Backup old trail files and free the space in the mount point then sent mail

$ cat freeuptrails.sh

#!/bin/sh

PATH=/usr/bin:/sbin: ; export PATH # exporting path for using TAR in this shell script otherwise tar won't work

count_files=$(ls -lrth /opt/app/ggs/trails/hostname/lt* | wc -l)

nfiles=`echo "scale=1; 2/5" | bc`

files_count_delete=`echo "scale=0; $nfiles*$count_files" | bc`

result=`printf "%.0f\n" $files_count_delete`

echo $result

echo $count_files

files=$(ls -lrth /opt/app/ggs/trails/hostname/lt* | head -n $result )

disk_usage=$(df -h /opt/app/ggs/trails/hostname | awk '{print $5}' | grep -e "[[:digit:]]" | awk -F% '{print $1,$2}')

if [[ "${disk_usage}" -gt "90" ]]

then

if test ${result} -lt ${count_files}

then

# check if old backup.tar.gz exists if yes then delete it

bkp_file=/opt/app/ggs/trails/hostname/backup.tar.gz

if test -f ${bkp_file}

then

rm -f /opt/app/ggs/trails/hostname/backup.tar.gz

# make backup directory for storing old trails files

mkdir /opt/app/ggs/trails/hostname/backup

# for loop to move old trails files to backup folder

for i in ${files}

mv $i /opt/app/ggs/trails/hostname/backup

done

# tar the backup folder

tar -czf /opt/app/ggs/trails/hostname/backup.tar.gz -C /opt/app/ggs/trails/hostname backup --remove-files

wait

echo "work is done"

send=$(df -h /opt/app/ggs)

sendone=$(echo "current trail files count:"; ls -lrth /opt/app/ggs/trails/hostname/lt* | wc -l)

sendtwo=$(ls -lrth /opt/app/ggs/trails/hostname/backup.tar.gz)

echo -e "$send\n$sendone\n$sendtwo" | mailx -s "Space free now on hostname /opt/app/ggs" exampel@gmail.com

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

createuser.sh

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

#!/bin/bash

echo "enter username:"

read user;

echo "enter default tablespace:"

read default_tablespace;

echo "password:"

read password;

sqlplus / as sysdba << EOF > log.txt

alter session set "_oracle_script"=true ;

create user ${user} identified by ${password} default tablespace ${default_tablespace} temporary tablespace temp quota unlimited on users;

grant dba to ${user};

exit;

EOF

sqlplus ${user}/${password} << EOF > ${user}.txt

show user;

exit;

EOF

################################################################################################################################################

userdrop.sh

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

#!/bin/bash

echo "Enter username:"

read user

echo "password:"

read password

sqlplus / as sysdba << EOF > ${user}drop.txt

alter session set "_oracle_script"=true;

drop user ${user} cascade;

exit;

EOF

#########################################################################################################

createtablespace.sh

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

#!/bin/bash

echo "Enter tablespace_name:"

read tablespace_name

echo "path:"

read path

echo "Size:"

read size

echo "autoextend on/off"

read autoextend_on_off

echo "tablespace_file_no:"

read tablespace_file_no

sqlplus / as sysdba << EOF > ${tablespace_name}created.txt

create tablespace ${tablespace_name}

datafile '${path}${tablespace_name}_${tablespace_file_no}.dbf'

size ${size}

autoextend ${autoextend_on_off};

exit

EOF

###########################################################################################################

droptablespace.sh

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

#!/bin/bash

echo "Enter tablespace_name:"

read tablespace_name

sqlplus / as sysdba << EOF > ${tablespace_name}drop.txt

drop tablespace ${tablespace_name} including contents and datafiles;

exit;

EOF

########################################################################################################

mkscript.sh

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

#!/bin/bash

echo "Enter script name:"

read script_name

echo "Enter permission"

read permission

touch ${script_name}

chmod ${permission} ${script_name}

##########################################################################################################

createcontrolfile.sql

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

create controlfile <-------------------------

set database db_name |

logfile group 1 ('/u02/app/oracle/db_name/redo01_01.log', | #only change: /u02/app/oracle/db_name remaining all remains as it is

'/u02/app/oracle/db_name/redo01_02.log'), | # alter database backup controlfile to trace;

group 2 ('/u02/app/oracle/db_name/redo02_01.log', | # adrci > show home then $ cd /rdbms/trace/ $ ls -lrt alert_${db_name}.trc - copy the content to c.sql

'/u02/app/oracle/db_name/redo02_02.log'), | |

group 3 ('/u02/app/oracle/db_name/redo03_01.log', | |

'/u02/app/oracle/db_name/redo03_02.log') |-------------------------------------------------------------------------------------------------

resetlogs |

datafile '/u02/app/oracle/db_name/system01.dbf' size 30M, |

'/u02/app/oracle/db_name/sysaux01.dbf' size 50M, |

'/u02/app/oracle/db_name/users01.dbf' size 50M, |

'/u02/app/oracle/db_name/temp01.dbf' size 50M |

maxlogfiles 50 |

maxlogmembers 3 |

maxloghistory 400 |

maxdatafiles 200 |

maxinstances 6 |

archivelog; <-------------------------------

############################################################################################################

freespace.sh

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

echo "path"

read path

echo "year:"

read year

echo "month:"

read month

echo "day:"

read day

echo "find type option f or d"

read option

echo "days_of_files_to_kept"

read days_of_files_to_kept

rm -rf ${year}_${month}_${day}_*

# or can be used as below

find ${path} -type ${option} -mtime +${days_of_files_to_kept} -exec rm {} +

####################################################################################################################

extract.sh --> scripts to get query results without going inside database

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

#!/bin/bash

echo "Enter username:"

read user

echo "Enter password:"

read password

echo "Enter statement or query:"

read query

sqlplus ${user}/${password} << EOF > logon.txt

$query

exit;

EOF

#############################################################################################################################

switch.sh ---> scripts for listing of all directories and files

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

#!/bin/bash

echo "Enter path:"

read path

for i in $path

echo "list of files or folders"

ls -lrth ${i}

done

##############################################################################################################################

search.sh ----> scripts for

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

#!/bin/bash

count=$(wc -m file | tr -cd "[:digit:]")

i=1

while [[ $i -le $count ]]

cut -b $i file

(( i++ ))

done

#############################################################################################################################

bkup script is running or not :

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

#!/bin/bash

bkp_running=$(ps -ef | grep rman)

if [[ "$bkp_running" = "TRUE" ]]

then

echo "bkp is running"

else

echo "bkp is not running"

###############################################

Linux Mail send GUIDE

$ mail -s "$(echo -e "$Subject Tablespace Usage\nContent-Type: text/html;\n\n")" example@gmail.com < index.html

echo index.html | mail -s "$(echo -e "Tablespace Usage \nContent-Type: text/html")" example@gmail.com

$(echo -e "$Subject\nContent-Type: text/html;\n\n)"

Below is for SQLPLUS :-

$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" @/usr/local/opt/oracle/oraadmin/ipag/monitor/long_run_session.sql > ${MAILMSGFILE}
pr -e -t ${MAILMSGFILE} | mailx -s "IDIS Long Running Queries on $HOST: $ORACLE_SID" ${MAILLIST}

$ mail -s "$(echo -e "$Subject Tablespace Usage \n Content-Type: text/html \n charset=utf-8")" example@gmail.com

$ echo "attachment" | mailx -s "Tablespace usage" -a index.html example@gmail.com

oracle@hostame : path/ $ cat try.sh #!/bin/sh command=`df -h `

echo "${command}" > index.html

oracle@hostname: path/$ sh try.sh 
oracle@hostname: path/ $ cat testmail.sh
#!/bin/sh
echo "email_to:"
read email_to
echo "email_from:"
read email_from
echo "Subject:"
read subject
echo "file_path"
read filepath
(
 echo "To: ${email_to}"
 echo "From: ${email_from}"
 echo "Subject: ${subject}"
 echo "Mime-Version: 1.0"
 echo "Content-Type: text/html; charset='utf-8'"
 echo
 cat ${filepath}
) | sendmail -t
oracle@hostname: path/ $ sh testmail.sh
email_to:
user@sample.com
email_from:
oracle@hostname
Subject:
Space free
file_path
index.htm
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sample Shell Script To send HTML report From SQL Query 
host:log/ $ cat test.sh
#!/bin/sh


echo "Enter SQL query:"
read query

sqlplus / as sysdba << EOF
set markup HTML on;
spool index.html;
${query};
spool off;
set markup HTML off;
exit;
EOF

echo "email_to:"
read email_to
echo "email_from:"
read email_from
echo "Subject:"
read subject
echo "file_path"
read filepath
(
 echo "To: ${email_to}"
 echo "From: ${email_from}"
 echo "Subject: ${subject}"
 echo "Mime-Version: 1.0"
 echo "Content-Type: text/html; charset='utf-8'"
 echo
 echo "<body><style> th{background-color:gold;} table{border:2px solid blue;}</style></body>"
 echo
 cat ${filepath}
) | sendmail -t

ACTIVE SESSION & Inactive session AND BLOCKING SESSION IN ORACLE DB

Active :- waiters

Inactive:- holders

dba_blockers:- non-waiting sessions

dba_ddl_locks:- ddl locks

dba_dml_locks:- dml locks

dba_lock_interval:- 1 row for every lock username

dba_locks:- show all locks/latches

dba_waiters:- waiting session

dba_objects, all_objects, user_objects, dba_segments, gv$loced_objects, gv$session_longops

Real-time monitoring of sql query:- gv$sql_plan_monitor

col inst_id for a13
col sid for a12
col serial# for a12
col username for a14
col status for a12
col server for a14
col schemaname for a12
col osuser for a14
col machine for a13
col program for a13
select inst_id,sid,serial#,username,status,server,schemaname,osuser,machine,program,sql_id from gv$session
where status = 'ACTIVE';
#################### Working status and seconds v$session ###############################
col "Description" format a50
select sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from gv$session
where username = 'schema';
###################################################################################
select SID, osuser, machine, terminal, service_name,
logon_time, last_call_et
from gv$session
where username = 'schema';
###################################################################################
Session wait for each machine
-------------------------------------------
col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
decode(state, 'WAITING', 'Waiting',
'Working') state,
last_call_et, seconds_in_wait, event
from gv$session
where machine = 'host_name';

########################################################################

Blocking sessions occur when a session issues an "insert, update or delete" command that changes a row.

When the change occurs,"the row is locked until the session either commits the change, rolls the change

back or the user logs off the system."

select

blocking_session,

sid,

serial#,

wait_class,

seconds_in_wait

from

v$session

where

blocking_session is not NULL

order by

blocking_session;

=========================================================================

How to check Locking session/Blocking session

=========================================================================

col username for a15

col machine for a20

col event for a20

col program for a20

col osuser for a20

set line 3000

set pagesize 2000

SELECT

Inst_id,

s.username,

s.blocking_session blk_sess,

s.blocking_instance blk_inst,

s.sid,

s.serial#,

s.sql_id,

s.status,

s.seconds_in_wait waittime,

s.event,

s.machine

FROM

gv$session s

WHERE

blocking_session IS NOT NULL order by SECONDS_IN_WAIT;

=========================================================================

################################### How to search users on database##################

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

s.sid,

s.serial#,

--s.sql_id,

p.spid,

s.username,

s.program

FROM gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND';

INST_ID SID SERIAL# SPID USERNAME PROGRAM

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

1 30 15 3859 TEST sqlplus@oel5-11gr2.localdomain (TNS V1-V3)

1 23 287 3834 SYS sqlplus@oel5-11gr2.localdomain (TNS V1-V3)

1 40 387 4663 oracle@oel5-11gr2.localdomain (J000)

1 38 125 4665 oracle@oel5-11gr2.localdomain (J001)

############################ To get spid and then kill them#########################

$ ps -ef | grep ora

$ kill -9 spid

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#'; -------------------------> single instance db

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id'; ------------------> RAC instance db

###############################################################

USER MANAGEMENT IN ORACLE DATABASE

Check Current User Permission
------------------------------------------------------
select * from USER_ROLE_PRIVS where USERNAME=USER;
select * from USER_TAB_PRIVS where Grantee = USER;
select * from USER_SYS_PRIVS where USERNAME = USER;
----------------------------------------------------------------
To Check the roles granted to a user:
--------------------------------------
select * from dba_role_privs where grantee = 'username';
select * from dba_tab_privs where grantee = 'username';
select * from dba_sys_privs where grantee = 'username';
------------------------------------------------------------------

To check status of user on database:--

select

(select host_name from v$instance) "Host_Name",

(select to_char(sysdate,'DD-MM-YY HH:MM:SS') from dual) "Date",

username,

account_status

from dba_users

where

username='&username';

output:

Host_Name Date username account_status

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

example 08-May-23 scott open

To Check Profile for a user :-

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

select username,profile from dba_users where username='&username';

select profile,resource_name,limit from dba_profiles where profile='&profile';

To Change User/schema password :-

alter user username account unlocked;

alter user username identified by "password";

To Change Profile Resource limit:-

ALTER PROFILE sample_profile LIMIT <resource_name> <resource_limit>;

To Create Profile for User:-

This one is ideal but not preferable to use in production environment for security reason just we can use this for testing purpose on lab environment.

SQL>

create profile <profile_name> limit

composite_limit unlimited

sessions_per_user unlimited

cpu_per_session unlimited

cpu_per_call unlimited

logical_reads_per_session unlimited

logical_reads_per_call unlimited

idle_time unlimited

connect_time unlimited

private_sga unlimited

failed_login_attempts unlimited

password_life_time unlimited

password_reuse_time unlimited

password_reuse_max unlimited

password_verify_function null

password_lock_time unlimited

password_grace_time unlimited;

If We Want To Change Profile For User:-

alter user <username> profile <profile_name>;

GET DIRECTORY LOCATION ON ORACLE DATABASE

echo "
col name_col_plus_show_param for a20;
col value_col_plus_show_param for a80;
show parameter db_recovery_file_dest;
" | sqlplus \/ as sysdba | grep -o "\w*/[[:graph:]]*" > bkploc.txt

--------------------- To find the RMAN backup locations --------------------------------------------

echo "

echo "select fname from v\$backup_files where file_type='PIECE' order by completion_time desc fetch first 1 rows only;" | sqlplus \/ as sysdba | dirname $(grep -e "^/[[:graph:]]*")

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

TABLESPACE ORACLE DATABASE GUIDE

SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "Size (GB)",
SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,]
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
order by 5 desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
for getting usage for only given tablespace
---------------------------------------------------------------
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "Size (GB)",
SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
and fs.tablespace_name='&tablespace_name'
GROUP BY df.tablespace_name,df.bytes
order by 5 desc;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

#TEMP TABLESPACE ######################
select a.tablespace_name tablespace,
d.TEMP_TOTAL_GB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_USED_GB,
d.TEMP_TOTAL_GB - sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_FREE_GB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 / 1024 TEMP_TOTAL_GB
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.TEMP_TOTAL_GB;
=========================================================================
select file_name,autoextensible,sum(bytes/1024/1024/1024) from dba_data_files where tablespace_name='&tablespace_name' group by file_name,autoextensible order by file_name desc;
=========================================================================
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)"
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
=========================================================================
select tablespace_name, bytes / (1024*1024*1024) "Size (GB) from dba_data_files;
for resize datafile :
alter database datafile 'path' resize 4G ;
for adding datafile :
alter tablespace tablespace_name add datafile 'path' size 3G autoextend off ;

################################ FOR RAC DB ################
Add or Resize Datafile in RAC database

alter tablespace tablespace_name add datafile '+ASM_NAME' size nG autoextend on ;
alter tablespace tablespace_name add datafile '+ASM_NAME' size nG autoextend off;

alter database datafile '+/path/to/datafile' resize nG;

FRA STATUS ON ORACLE DATABASE

set linesize 500
col NAME for a20
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(SPACE_USED/1024/1024/1024,2)/ROUND(SPACE_LIMIT/1024/1024/1024,2))*100) from v$recovery_file_dest) "Percentage Used %",
(select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2)
from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
from V$RECOVERY_FILE_DEST;

Oracle Patching Guide

col action_time for a28
col action for a8
col version for a8
col comments for a30
col status for a10
set line 999 pages 999
select patch_id, source_version,patch_type,target_version, status,Action_time from dba_registry_sqlpatch order by action_time ;
=========================================================================
col action_time for a28;
col description for a80;
set line 999 pages 999;
select patch_id, action, description, action_time from dba_registry_sqlpatch order by action_time;
Output:
----------------
PATCH_ID ACTION DESCRIPTION ACTION_TIME
---------- -------- ------------------------------------------------------------ ----------------------------
30869156 APPLY Database Release Update : 19.7.0.0.200414 (30869156) 09-JUL-20 10.25.59.281935 AM
30805684 APPLY OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684) 09-JUL-20 10.25.59.286150 AM
31281355 APPLY Database Release Update : 19.8.0.0.200714 (31281355) 22-JUL-20 01.30.47.736379 PM
30805684 ROLLBACK OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684) 22-JUL-20 01.36.22.283160 PM
31219897 APPLY OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897) 22-JUL-20 01.36.22.287535 PM
***************************************************************************************************************
To check GRID PATCH VERSION:
$ps -ef | grep -i d.bin
$ cd bin
/bin:$
./crsctl query crs releasepatch
./crsctl query crs activeversion
./crsctl query crs releaseversion
./crsctl query crs softwarepatch [node_name]
eg. ./crsctl query crs softwarepatch flpd670
./crsctl query crs softwareversion [node_name]
eg. ./crsctl query crs softwareversion flpd670
or login as oragrid ---> sqlplus / as sysadmin ---> run SQL Query

Archivelog Complete Guide

************Daily Archive Log Generation****************
SQL> select trunc(COMPLETION_TIME,'DD') Day, thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated from gv$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
DAY THREAD# GB ARCHIVES_GENERATED
-------------------- ---------- ---------- ------------------
10-OCT-2016 00:00:00 1 19 233
11-OCT-2016 00:00:00 1 34 417
12-OCT-2016 00:00:00 1 42 522
*************Hourly Archive Log Generation****************
set pages 1000
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select trunc(COMPLETION_TIME,'HH') Hour,thread# ,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives from gv$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;

HOUR THREAD# GB ARCHIVES
--------- ---------- ---------- ----------
18-MAR-23 1 0 1
18-MAR-23 1 0 1
18-MAR-23 1 2 5
18-MAR-23 1 0 2
*****************************************************

TABLE CONSTRAINTS STATUS

set lines 400
col constraint_name for a20;
col constraint_type for a20;
col search_condition for a20;
select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION,validated from USER_CONSTRAINTS where table_name in ('table_name');

select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION,validated from USER_CONSTRAINTS where owner='schema';

col owner for a20;
col constraint_name for a20;
col constraint_type for a5;
col table_name for a20;
col status for a20;
col validated for a5;
col invalid for a5;
col index_owner for a20;
col index_name for a20;
select owner,constraint_name,constraint_type,table_name,status,validated,invalid,index_owner,index_name from all_constraints where owner='&username' and table_name='&table';

spool 1_truncateActiveUserTables_02262023.out

set echo on

show user

select * from global_name;

-- disable the constraints so the table can be truncated

ALTER TABLE SCHEMA.TABLE

DISABLE CONSTRAINT CONSTRAINT_NAME;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE_NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

-- enable the constraints

ALTER TABLE SCHEMA.TABLE

ENABLE CONSTRAINT CONSTRAINT_NAME;

-- disable the constraints

ALTER TABLE SCHEMA.TABLE

DISABLE CONSTRAINT CONSTRAINT_NAME;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE_NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

-- enable the constraints

ALTER TABLE SCHEMA.TABLE

ENABLE CONSTRAINT CONSTRAINT_NAME;

drop table SCHEMA.TABLE_OLD_BACKUP;

create table SCHEMA.TABLE_NEW_BACKUP as select * from SCHEMA.TABLE;

select count(*) from SCHEMA.TABLE_NEW_BACKUP;

select count(*) from SCHEMA.TABLE;

truncate table SCHEMA.TABLE;

-- confirm counts are all 0

select count(*) from SCHEMA.TABLE;

spool off

********************* To check status of constraints on tables **********************

set lines 400

col constraint_name for a20;

col constraint_type for a20;

col search_condition for a20;

select CONSTRAINT_NAME,CONSTRAINT_TYPE ,SEARCH_CONDITION,validated from USER_CONSTRAINTS where table_name in ('TABLE','TABLE');

DATA PUMP IMPDP EXPDP

set lines 400;
col owner_name for a10;
col job_name for a20;
col operation for a40;
col job_mode for a20;
col state for a20 ;
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs
where state='EXECUTING';
==================================================
select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;
======================================================
+++++++++++++++ Shows waiting status of data pump ++++++++++++++
set lines 400;
col module for a20;
col state for a10;
col event for a40;
col sql_text for a80;
select s.sid, s.module, s.state,
substr(s.event, 1, 21) as event,
s.seconds_in_wait as secs,
substr(sql.sql_text, 1, 30) as sql_text
from v$session s
join v$sql sql on sql.sql_id = s.sql_id
where s.module like 'Data Pump%'
order by s.module, s.sid;
****************** showing percentage completed ocptechnology *************************
set lines 400;
col opname for a20;
col username for a20;
SELECT OPNAME, SID, SERIAL#, username,CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS WHERE OPNAME in
( select d.job_name from v$session s, v$process p, dba_datapump_sessions d where p.addr=s.paddr and s.saddr=d.saddr )
AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

***************** DBAGENISIs****************************
SELECT SID, SERIAL#, USERNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;
==========================================================
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "Size (GB)",
SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
order by 5 desc;
---------------------------------------------------------------------------------------------
alter tablespace JBBOS_DAT add datafile '+DATA' size 30g;

--------------------------------------------------------------------------------------
select a.tablespace_name tablespace,
d.TEMP_TOTAL_GB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_USED_GB,
d.TEMP_TOTAL_GB - sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_FREE_GB
from gv$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 / 1024 TEMP_TOTAL_GB
from gv$tablespace b, gv$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.TEMP_TOTAL_GB;

--------------------------------------------------------------------------------
select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;
===================================================================

select tablespace_name,file_name,bytes/(1024*1024*1024) "size_GB" from dba_temp_files;
alter tablespace TEMP add tempfile '+DATA' size 30G;
=========================================================================

Notes: expdp and impdp
If the Target database’s release is higher than the source, the VERSION parameter typically does not have to be specified because all objects in the source database will be compatible with the higher target release.
The VERSION parameter should be used when the Source database’s release is higher than the Target database.

expdp can be done --> from lower version to higher version
expdp can be need (version=oracle version) ---> from higher version to lower version
=================================================================================================
[user@hostname]$ cat schema_export_prod_<date-time>.par
DIRECTORY=DATA_PUMP
SCHEMAS=<schema_name>
DUMPFILE=exp_prod_01132023_%U.dmp
LOGFILE=exp_prod_schema_01132023.log
PARALLEL=6
FILESIZE=10G
EXCLUDE=STATISTICS
COMPRESSION=ALL
CLUSTER=N
STATUS=30
JOB_NAME=exp_prod_schema_01132023

[user@hostname]$ cat schema_prod_expdp_table_tablename.par
DIRECTORY=DATA_PUMP
SCHEMAS=<schema_name>
DUMPFILE=exp_prod_<schema_name>_<table_name>_%U.dmp
LOGFILE=exp_prod_<schema_name>_<table_name>.log
PARALLEL=12
FILESIZE=15G
COMPRESSION=ALL
CLUSTER=N
STATUS=30
#JOB_NAME=exp_prod_<schema_name>_<table_name>_01132023
INCLUDE=TABLE:"IN('<table_name>')"

[user@hostname]$ cat imp_<schema_name>.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp_prod_<schema_name>_01282023_%U.dmp
LOGFILE=IMP_<schema_name>_<table_name>_02022023.log
TABLE_EXISTS_ACTION=REPLACE
#o#JOB_NAME=<schema_name>_<table_name>
EXCLUDE=VIEW,PROCEDURE,PACKAGE,FUNCTION,TRIGGER
#schemas=<schema_name>
EXCLUDE=SCHEMA:"IN('<schema1_name>','<schema2_name>')"
PARALLEL=32
CLUSTER=N
FULL=Y

[user@hostname]$ cat <schema_name>_<host_name>.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=<schema_name>_<table_name>_%U.dmp
LOGFILE=IMP_<schema_name>_<table_name>_10022018.log
EXCLUDE=SCHEMA:"IN('<schema_name>','<schema_name>')"
SCHEMAS=<schema_name>
##SCHEMAS=<schema_name>
parallel=25
TABLE_EXISTS_ACTION=REPLACE
CLUSTER=N

[user@hostname]$ cat <schema_name>.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=<schema_name>_<table_name>_%U.dmp
LOGFILE=IMP_<schema_name>_<table_name>.log
EXCLUDE=SCHEMA:"IN('<schema_name>','<schema_name>')"
SCHEMAS=<schema_name>
##SCHEMAS=<schema_name>
parallel=25
TABLE_EXISTS_ACTION=REPLACE
CLUSTER=N

[user@hostname]$ cat <database>_FULL_EXP.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=Full_database_%U.dmp
LOGFILE=Full_<schema_name>_<table_name>.log
##JOB_NAME=03282016_IMP_EXPORT_ST1_NEW
FILESIZE=10G
CLUSTER=N
PARALLEL=4
full=y
EXCLUDE=SCHEMA:"IN('<schema_name>',...,'<schema_name>)"

[user@hostname]$ cat <database>_FULL_EXP.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=Full_DATABASE_exp_%U.dmp
LOGFILE=Full_database_exp.log
##JOB_NAME=<full_database_exp>
FILESIZE=10G
CLUSTER=N
PARALLEL=4
full=y
EXCLUDE=SCHEMA:"IN('<schema_name>',...,'<schema_name>')"

[user@hostname]$ cat DATABASE_TABLE_INDEX_CREATE_SCRIPT.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=NAME_%U.dmp
LOGFILE=database_INDEX.log
JOB_NAME=database_INDEX
CLUSTER=N
PARALLEL=24
INCLUDE=INDEX
sqlfile=DATABASE_INDEXES_CREATE.sql

[user@hostname]$ cat <schema_name>_export_prod.par
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=<schema_name>
DUMPFILE=exp_prod_<schema_name>_%U.dmp
LOGFILE=exp_prod_<schema_name>.log
PARALLEL=12
FILESIZE=15G
EXCLUDE=STATISTICS
COMPRESSION=ALL
CLUSTER=N
STATUS=30
EXCLUDE=TABLE:"IN ('<table_name>',...,'<table_name>')"
JOB_NAME=exp_prod_<schema_name>_<table_name>

########### Most Important Note ######################
Always take expdp backup for schemas before dropping its objects
------------>> Take expdp schema backup before dropping schema objects <<---------------
or take restore point or rman backup before doing same if asked.

Oracle Database Scripts

Sunday, May 7, 2023

File System Free Space Automatically Shell Script Linux

Linux Mail send GUIDE

ACTIVE SESSION & Inactive session AND BLOCKING SESSION IN ORACLE DB

###############################################################

USER MANAGEMENT IN ORACLE DATABASE

GET DIRECTORY LOCATION ON ORACLE DATABASE

TABLESPACE ORACLE DATABASE GUIDE

FRA STATUS ON ORACLE DATABASE

Oracle Patching Guide

Archivelog Complete Guide

TABLE CONSTRAINTS STATUS

DATA PUMP IMPDP EXPDP

Enable OpenSSH on Windows 11

Translate