Oracle Database Scripts

Tuesday, February 27, 2024

optimizer parameters in oracle 19c db

begin

for x in (select name from v$parameter where name like 'optimizer%')

loop

dbms_output.put_line(x.name);

end loop;

end;

-------------------------------------------------------
optimizer_features_enable
optimizer_mode
optimizer_index_cost_adj
optimizer_index_caching
optimizer_dynamic_sampling
optimizer_ignore_hints
optimizer_secure_view_merging
optimizer_use_pending_statistics
optimizer_capture_sql_plan_baselines
optimizer_use_sql_plan_baselines
optimizer_use_invisible_indexes
optimizer_adaptive_reporting_only
optimizer_adaptive_plans
optimizer_inmemory_aware
optimizer_adaptive_statistics
optimizer_ignore_parallel_hints

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

-- Enable timing of SQL statements

SET TIMING ON;

-- Enable autotrace for detailed execution plans

SET AUTOTRACE ON;

-- Set the threshold for identifying slow queries (adjust as needed)

ALTER SESSION SET STATISTICS_LEVEL = ALL;

ALTER SESSION SET TIMED_STATISTICS = TRUE;

ALTER SESSION SET SQL_TRACE = TRUE;

-- Your SQL query goes here

SELECT *

FROM your_table

WHERE your_condition;

-- Reset session settings

ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

ALTER SESSION SET TIMED_STATISTICS = FALSE;

ALTER SESSION SET SQL_TRACE = FALSE;

-- Disable timing of SQL statements

SET TIMING OFF;

-- Display execution plan

-- Uncomment the line below if you want to see the execution plan

-- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Sunday, February 25, 2024

> /dev/null 2>&1 to prevent errors

> /dev/null : Redirects standard output to /dev/null, discarding it.

2>&1 : Redirects standard error to the same location as standard output. This ensures that both

standard output and standard error are discarded.

script.sh > script.log 2>&1

to prevent errors :-

command or script > /dev/null 2>&1

echo -e "something" > filename enable backlash escape

echo -e "india\nmaharashtra" > india.txt

cat india.txt

india

maharashtra

#!/usr/bin/expect to automate Linux commands ?

#!/usr/bin/expect

spawn some_command # Replace some_command with the command you want to automate

expect "ExpectedPrompt" # Replace ExpectedPrompt with the prompt you expect

send "your_input\r" # Replace your_input with the input you want to provide

interact # Allows you to interact with the spawned process manually if needed

chmod 777 example.exp

./example.exp

example.:

#!/usr/bin/expect

spawn su - goldengate

expect "Password: \r"

send "your_password\r"

expect eof # wait for spawn processes to finish

\r :- carriage return simulate "Enter"

\e or \x1B :- for escape key

Ctrl-a : - control plus a

[root@localhost ~]# cat send.sh

#!/bin/bash

expect<<!!

spawn vim ./file02.txt

expect "insert mode:\r"

send "i\r"

expect "type some content:\r"

send "I am living in Pune.\r"

expect "save exit\r"

send "\x1B"

send ":wq\r"

expect eof

!!

Saturday, February 17, 2024

grep command

grep -v "^#|^$" filename

^# :- line start with comment #

^$ :- empty lines

[a-zA-Z0-9_] :- alpha numeric with _

grep -o "^.\{1,10\}" filename :- find first 10 character word at the beginning of line.

grep -o ".\{1,10\}$" filename :- find last 10 character word at the end of line.

grep -o "^." filename :- find one character for beginning of each line.

grep -o ".$" filename :- find one character for end of the each line.

grep -e "^$" filename :- find the empty lines in the file.

grep -e "$" filename :- end of the line.

grep -e "^[a-zA-Z\]\{0,255\}\+\@\+[a-zA-Z\]\{0,255\}\+\:\+\~\+\\$" fun.txt : kayyum@AbdulKayyum:~$

grep -v "^[a-zA-Z\]\{0,255\}\+\@\+[a-zA-Z\]\{0,255\}\+\:\+\~\+\\$" fun.txt : kayyum@AbdulKayyum:~$

kayyum@AbdulKayyum:~$ cat new.txt | grep -e "grep\|#" | grep -o "grep.*\|#.*"

# grep :- remove empty lines

grep -v "^$" sample.txt

# grep :- remove commented lines.

grep -v "^#" sample.txt

# grep :- to find oracle datafiles

grep -o "[a-zA-Z0-9_\]\{0,255\}\.dbf" sample.txt

# grep :- find first 10 characters each line

grep -o "^.\{1,10\}" sample.txt

# grep :- find last 10 characters each line

grep -o ".\{1,10\}$" sample.txt

# grep :- remove commented and empty lines

grep -v "^#\|^$" sample.txt

# grep :- to find ' " ' double quotes in the file

grep -e "\"" file.txt

# grep :- to find '{ ' and '}' curly braces

grep -e "\{" file.txt

grep -e "\}" file.txt

# combine

grep -e "\{\|\}" file.txt

grep -e "\"\.\"" file.txt

grep -e "[a-zA-Z\]\{0,255\}" file.txt

SED COMMAND ON LINUX

to view file content with line number:-

cat -n file.txt

-- to delete last line in the file :-

sed -i '$d' filename

-- to delete first line in the file :-

sed -i '1d' filename

-- to append something

[root@localhost ~]# sed -i '$a\

> export ORACLE_HOME=/u02/ogg19c\

> export PATH=$ORACLE_HOME/bin:$PATH\

> export LD_LIBRARY_PATH=$ORACLE_HOME/lib

> ' bash_profile

[root@localhost ~]# cat bash_profile

india

is my country

export ORACLE_HOME=/u02/ogg19c

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

in above :- $ taken for last line of the file so append in the last.

-- to delete lines from file using sed command

[root@localhost ~]# cat -n bash_profile

1 india

2 is my country

3 export ORACLE_HOME=/u02/ogg19c

4 export PATH=$ORACLE_HOME/bin:$PATH

5 export LD_LIBRARY_PATH=$ORACLE_HOME/lib

[root@localhost ~]# # delete line number ---> 3 and 5 using below

[root@localhost ~]# sed -i '3,5d' bash_profile

[root@localhost ~]#

[root@localhost ~]# cat -n bash_profile

1 india

2 is my country

Wednesday, February 14, 2024

SCHEMAS procedure and functions

set lines 400;
col host for a20;
col owner for a20;
col instance for a20;
col grantee for a20;
col grantor for a20;
col table_name for a40;
select (select host_name from v$instance) "host", (select instance_name from v$instance) "instance", owner, object_type, count(*), to_char(sysdate,'DD/MM/YYYY HH:MI:SS AM') "sysdate" from dba_objects where owner=upper('&owner') group by object_type,owner;

select (select host_name from v$instance) "host", (select instance_name from v$instance) "instance", owner, count(*) schema_obj_count from dba_objects where owner=upper('&owner') group by owner'

select * from dba_tab_privs where grantee=upper('grantee') and table_name in ('DBMS_LOCK','DBMS_JOB');

-- List all tables in all schemas
SELECT OWNER
	,table_name
FROM dba_tables;

-- List all views in all schemas
SELECT OWNER
	,view_name
FROM dba_views;

-- List all indexes in all schemas
SELECT OWNER
	,index_name
	,table_name
FROM dba_indexes;

-- List all columns of a specific table in all schemas
SELECT OWNER
	,table_name
	,column_name
	,data_type
FROM dba_tab_columns
WHERE table_name = 'your_table_name';

-- List all procedures in all schemas
SELECT OWNER
	,object_name
FROM dba_objects
WHERE object_type = 'PROCEDURE';

-- List all triggers in all schemas
SELECT OWNER
	,trigger_name
	,table_name
FROM dba_triggers;

-- List all sequences in all schemas
SELECT sequence_owner
	,sequence_name
FROM dba_sequences;

-- List all synonyms in all schemas
SELECT OWNER
	,synonym_name
	,table_name
FROM dba_synonyms;

-- List all constraints in all schemas
SELECT OWNER
	,constraint_name
	,constraint_type
	,table_name
FROM dba_constraints;

-- List all sequences in all schemas
SELECT sequence_owner
	,sequence_name
FROM dba_sequences;

-- List all materialized views in all schemas
SELECT OWNER
	,mview_name
FROM dba_mviews;

-- List all materialized view logs in all schemas
SELECT OWNER
	,log_table
	,master
FROM dba_mview_logs;

-- List all user-defined types (UDTs) in all schemas
SELECT OWNER
	,type_name
FROM dba_types;

-- List all packages in all schemas
SELECT OWNER
	,object_name
FROM dba_objects
WHERE object_type = 'PACKAGE';

-- List all package procedures and functions in all schemas
SELECT OWNER
	,object_name
	,PROCEDURE_NAME
	,function_name
FROM dba_procedures
WHERE object_type = 'PACKAGE';

-- List all indexes on a specific table in all schemas
SELECT table_owner
	,index_name
	,column_name
FROM dba_ind_columns
WHERE table_name = 'your_table_name';

-- List all database links in all schemas
SELECT OWNER
	,db_link
FROM dba_db_links;

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

Creating simple procedure:-

-- Create a simple procedure that prints a message
CREATE OR REPLACE PROCEDURE print_message
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, this is a simple procedure!');
END;
/

output:-

-- Execute the procedure

BEGIN

print_message;

END;

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

Creating simple functions:-

-- Create a simple function that adds two numbers

CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER)

RETURN NUMBER

result NUMBER;

BEGIN

result := a + b;

RETURN result;

END;

output:-

-- Call the function

DECLARE

sum_result NUMBER;

BEGIN

sum_result := add_numbers(5, 7);

DBMS_OUTPUT.PUT_LINE('Sum: ' || sum_result);

END;

Tuesday, February 13, 2024

for loop in shell script ?

#!/bin/bash

start=3

end=8

for i in $(seq $start $end); do

if test ${i} -lt ${end}

then

echo "${i} less than ${n} "

if test ${i} -eq ${end}

then

echo "${i} equal to ${n} "

done

Monday, February 12, 2024

EXPDP & IMPDP Automation Shell Script ?

kayyum@AbdulKayyum:~$ cat *.sh

#!/bin/bash

select com in expdp impdp

case ${com} in

"expdp")

command="expdp "

select opt in userpassword directory dumpfile schemas tables logfile exclude include show_command Quite

case ${opt} in

"userpassword")

read -p "Enter user/password: " userpassword

command+="${userpassword} "

;;

"directory")

read -p "Enter directory: " directory

command+="directory=${directory} "

;;

"dumpfile")

read -p "Enter dumpfile: " dumpfile

command+="dumpfile=${dumpfile} "

;;

"schemas")

read -p "number of schemas: " n

schem=""

for i in $(seq 1 $n)

read -p "Enter schemaname: " schm

if test ${i} -lt ${n}

then

#read -p "Enter schemaname: " schm

schem+="${schm}, "

if test ${i} -eq ${n}

then

schem+="${schm}"

done

command+="schemas=${schem} "

;;

"tables")

read -p "number of tables: " n

tabl=""

for i in $(seq 1 $n)

read -p "Enter tablename: " tbl

if test ${i} -lt ${n}

then

# read -p "Enter tablename: " tbl

tabl+="${tbl},"

if test ${i} -eq ${n}

then

tabl+="${tbl}"

done

command+="tables=${tabl} "

;;

"exclude")

command+="exclude=STATISTICS "

echo "exclude=STATISTICS added !"

;;

"include")

read -p "Enter number of tables for include: " n

schemtbl=""

for i in $(seq 1 $n)

read -p "Enter schema.table: " schematable

if test ${i} -lt ${n}

then

#read -p "Enter schema.table: " schematable

schemtbl+="'${schematable}',"

if test ${i} -eq ${n}

then

schemtbl+="'${schematable}'"

done

command+='include=table:"in('${schemtbl}')"'

;;

"logfile")

read -p "Enter logfile: " logfile

command+="logfile=${logfile} "

;;

"show_command")

echo ${command}

;;

"Quite")

break

;;

esac

done;;

"impdp")

command="impdp "

select opt in userpassword directory dumpfile schemas tables logfile exclude include show_command remap_objects Quite

case ${opt} in

"userpassword")

read -p "Enter user/password: " userpassword

command+="${userpassword} "

;;

"directory")

read -p "Enter directory: " directory

command+="directory=${directory} "

;;

"dumpfile")

read -p "Enter dumpfile: " dumpfile

command+="dumpfile=${dumpfile} "

;;

"schemas")

read -p "number of schemas: " n

schem=""

for i in $(seq 1 $n)

read -p "Enter schemaname: " schm

if test ${i} -lt ${n}

then

# read -p "Enter schemaname: " schm

schem+="${schm},"

if test ${i} -eq ${n}

then

schem+="${schm}"

done

command+="schemas=${schem} "

;;

"tables")

read -p "number of tables: " n

tabl=""

for i in $(seq 1 $n)

read -p "Enter tablename: " tbl

if test ${i} -lt ${n}

then

# read -p "Enter tablename: " tbl

tabl+="${tbl},"

if test ${i} -eq ${n}

then

tabl+="${tbl}"

done

command+="tables=${tabl} "

;;

"logfile")

read -p "Enter logfile: " logfile

command+="logfile=${logfile} "

;;

"exclude")

command+="exclude=STATISTICS "

echo "exclude=STATISTICS added !"

;;

"include")

read -p "Enter number of tables for include: " n

schemtbl=""

for i in $(seq 1 $n)

read -p "Enter schema.table: " schematable

if test ${i} -lt ${n}

then

#read -p "Enter schema.table: " schematable

schemtbl+="'${schematable}',"

if test ${i} -eq ${n}

then

schemtbl+="'${schematable}'"

done

command+='include=table:"in('${schemtbl}')"'

;;

"remap_objects")

select option in remap_schema remap_table remap_tablespace remap_datafile remap_directory remap_partition main_menu

case ${option} in

"remap_schema")

content=" remap_schema="

read -p "Enter source_schema: " source_schema

read -p "Enter target_schema: " target_schema

content+="${source_schema}:${target_schema} "

echo "${content} "

command+="${content} "

;;

"remap_table")

content="remap_table="

read -p "Enter source_schema.source_table: " source1

read -p "Enter target_schema.target_table: " target

content+="${source1}:${target} "

echo "${content} "

command+="${content} "

;;

"remap_tablespace")

content="remap_tablespace="

read -p "Enter source_tablespace: " source_tablespace

read -p "Enter target_tablespace: " target_tablespace

content+="${source_tablespace}:${target_tablespace} "

echo "${content} "

command+="${content} "

;;

"remap_datafile")

content="remap_datafile="

read -p "Enter source_datafile: " source_datafile

read -p "Enter target_datafile: " target_datafile

content+="${source_datafile}:${target_datafile} "

echo "${content} "

command+="${content} "

;;

"remap_directory")

content="remap_directory="

read -p "Enter source_directory: " source_directory

read -p "Enter target_directory: " target_directory

content+="${source_directory}:${target_directory} "

echo "${content} "

command+="${content} "

;;

"remap_partition")

content="remap_partition="

read -p "Enter source_schema.source_table.source_partition: " source1

read -p "Enter target_schema.target_table.target_partition: " target

content+="${source1}:${target} "

echo "${content} "

command+="${content} "

;;

"main_menu")

break

;;

esac

done

;;

"show_command")

echo ${command}

;;

"Quite")

break

;;

esac

done;;

esac

done

Wednesday, February 7, 2024

tomcat config script

#!/bin/bash

# Variables

TOMCAT_VERSION="9.0.59" # Adjust to the desired Tomcat version

TOMCAT_INSTALL_DIR="/opt/tomcat"

TOMCAT_USER="tomcat"

TOMCAT_PASSWORD="your_password" # Change this to a secure password

TOMCAT_MANAGER_USER="admin"

TOMCAT_MANAGER_PASSWORD="your_manager_password" # Change this to a secure password

# Update and install necessary packages

sudo yum update -y

sudo yum install -y java wget

# Download and extract Tomcat

cd /tmp

wget https://downloads.apache.org/tomcat/tomcat-9/v${TOMCAT_VERSION}/bin/apache-tomcat-${TOMCAT_VERSION}.tar.gz

tar -xf apache-tomcat-${TOMCAT_VERSION}.tar.gz

sudo mv apache-tomcat-${TOMCAT_VERSION} ${TOMCAT_INSTALL_DIR}

# Create Tomcat user and group

sudo groupadd ${TOMCAT_USER}

sudo useradd -g ${TOMCAT_USER} -d ${TOMCAT_INSTALL_DIR} -s /bin/nologin ${TOMCAT_USER}

# Set ownership and permissions

sudo chown -R ${TOMCAT_USER}:${TOMCAT_USER} ${TOMCAT_INSTALL_DIR}

sudo chmod +x ${TOMCAT_INSTALL_DIR}/bin/*.sh

# Configure Tomcat users for Manager and Admin roles

echo -e " <user username=\"${TOMCAT_MANAGER_USER}\" password=\"${TOMCAT_MANAGER_PASSWORD}\" roles=\"manager-gui,admin-gui\"/>\n" | sudo tee -a ${TOMCAT_INSTALL_DIR}/conf/tomcat-users.xml

# Set Tomcat environment variables (optional)

# You may customize this section based on your requirements

# Create systemd service for Tomcat

cat <<EOL | sudo tee /etc/systemd/system/tomcat.service

[Unit]

Description=Apache Tomcat Web Application Container

After=syslog.target network.target

[Service]

Type=forking

Environment=CATALINA_PID=${TOMCAT_INSTALL_DIR}/temp/tomcat.pid

Environment=CATALINA_HOME=${TOMCAT_INSTALL_DIR}

Environment=CATALINA_BASE=${TOMCAT_INSTALL_DIR}

ExecStart=${TOMCAT_INSTALL_DIR}/bin/startup.sh

ExecStop=${TOMCAT_INSTALL_DIR}/bin/shutdown.sh

User=${TOMCAT_USER}

Group=${TOMCAT_USER}

[Install]

WantedBy=multi-user.target

EOL

# Reload systemd and start Tomcat

sudo systemctl daemon-reload

sudo systemctl start tomcat

sudo systemctl enable tomcat

# Open firewall port 8080

sudo firewall-cmd --add-port=8080/tcp --permanent

sudo firewall-cmd --reload

# Cleanup

rm -rf /tmp/apache-tomcat-${TOMCAT_VERSION}.tar.gz

echo "Tomcat installation and configuration completed successfully."

echo "Tomcat Manager URL: http://localhost:8080/manager/html"

echo "Tomcat Admin URL: http://localhost:8080/host-manager/html"

Sunday, February 4, 2024

Explain plan concepts cardinality & selectivity

Cardinality is the estimated number of rows the step will return. cost is the estimated amount of work the plan will do. A higher cardinality => you're going to fetch more rows => you're going to do more work => the query will take longer. Thus the cost is (usually) higher.

Selectivity = No. of rows returning from the query / total no of rows
i.e. Selectivity = (select * from table where rownum<40) / select * from table
Cardinality = total no of rows * Selectivity

Oracle Database Scripts

Tuesday, February 27, 2024

optimizer parameters in oracle 19c db

Sunday, February 25, 2024

> /dev/null 2>&1 to prevent errors

echo -e "something" > filename enable backlash escape

#!/usr/bin/expect to automate Linux commands ?

Saturday, February 17, 2024

grep command

SED COMMAND ON LINUX

Wednesday, February 14, 2024

SCHEMAS procedure and functions

Tuesday, February 13, 2024

for loop in shell script ?

Monday, February 12, 2024

EXPDP & IMPDP Automation Shell Script ?

Wednesday, February 7, 2024

tomcat config script

Sunday, February 4, 2024

Explain plan concepts cardinality & selectivity

Enable OpenSSH on Windows 11

Translate