rm -rf !(folder) in linux

shopt -s extglob  # Enable extended pattern matching

rm -rf !(folder)  # Delete everything except the 'folder' directory

shopt -u extglob  # Disable extended pattern matching (optional)

savepoint, rollback, commit in oracle db

 commit: make changes done in transaction permanent.

rollback : rollback the state of database to the last commit point.

savepoint : use to specify a point in transaction to which later you can rollback.

create table student 

(

id number primary key,

name varchar2(50),

college varchar2(50)

);

insert all

into student values (1,'pravin','ness')

into student values (2,'prakash','ness')

into student values (3,'pratap','ness')

into student values (4,'pranav','ness')

into student values (5,'prayank','ness')

select * from dual;

--create savepoint student_original --

savepoint student_original;

-- delete some records --

delete student where id=5;

-- rollback to student_original savepoint --

rollback to student_original;

-- most important fact --

you cannot rollback to any previous savepoint once you've triggered commit; 

Linux SSH REMOTE EXECUTION OF COMMANDS

 ssh -t -q oracle@pm "(df -h /u02; hostname -f; date)" 

read -p "enter server name: " server ; ssh -t -q $server "(sudo -iu oracle)"

or looping multiple servers :-

for servers in server1 server2 server3 server4 ;

do 

ssh -t -q $servers "(df -h /u02; hostname -f ; date)" ;

done;

EXPLANATION:- 

• ssh: This is the SSH command used for secure remote shell access.

• -t: This option is used to allocate a pseudo-terminal. It's often necessary when you want to run interactive commands remotely because it simulates an interactive terminal session.

• -q: This option stands for "quiet" and is used to suppress warning and diagnostic messages. It makes the SSH command less verbose.

• oracle@pm: This part specifies the username (oracle) and the hostname or IP address (pm) of the remote server you want to connect to. The @ symbol separates the username from the hostname.

• "(...)": The parentheses enclose a series of commands that you want to execute on the remote server. In this case, three commands are enclosed: df -h /u02, hostname -f, and date.

• -t option is essential when running multiple commands, as it ensures that the commands are executed in an environment that behaves like an interactive shell session.

File System Alerts Short Cuts Commands specially "find command"

 

[oracle@pm u02]$ find ./* -type d -name "archivelog" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

612M    ./app/oracle/fast_recovery_area/CHENNAI/archivelog
660M    ./app/oracle/fast_recovery_area/SAKRI/archivelog

[oracle@pm u02]$ find ./* -type d -name "trace" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

0       ./app/oracle/product/21c/dbhome_1/network/trace

17M     ./app/oracle/diag/rdbms/chennai/chennai/trace

2.9M    ./app/oracle/diag/rdbms/sakri/chennai/trace

29M     ./app/oracle/diag/rdbms/sakri/sakri/trace

40K     ./app/oracle/diag/rdbms/unknown/chennai/trace

4.0K    ./app/oracle/diag/clients/user_oracle/host_3163451292_110/trace

1.3M    ./app/oracle/diag/tnslsnr/pm/listener/trace

0       ./app/oracle/homes/OraDB21Home1/network/trace

[oracle@pm u02]$ find ./* -type d -name "alert" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

8.9M    ./app/oracle/diag/rdbms/chennai/chennai/alert

168K    ./app/oracle/diag/rdbms/sakri/chennai/alert

11M     ./app/oracle/diag/rdbms/sakri/sakri/alert

16K     ./app/oracle/diag/rdbms/unknown/chennai/alert

20K     ./app/oracle/diag/clients/user_oracle/host_3163451292_110/alert

2.2M    ./app/oracle/diag/tnslsnr/pm/listener/alert

[oracle@pm u02]$ find ./* -type d -name "*backup*" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

4.6M    ./app/oracle/product/21c/dbhome_1/inventory/backup

164M    ./app/oracle/fast_recovery_area/CHENNAI/autobackup

19M     ./app/oracle/fast_recovery_area/SAKRI/autobackup

12K     ./app/oraInventory/backup

[oracle@pm u02]$ find ./* -type d -name "*dump*" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

158M    ./app/oracle/product/21c/dbhome_1/rdbms/utl/upgrade_dump_seed

0       ./app/oracle/diag/rdbms/chennai/chennai/cdump

0       ./app/oracle/diag/rdbms/sakri/chennai/cdump

0       ./app/oracle/diag/rdbms/sakri/sakri/cdump

0       ./app/oracle/diag/rdbms/unknown/chennai/cdump

0       ./app/oracle/diag/clients/user_oracle/host_3163451292_110/cdump

0       ./app/oracle/diag/tnslsnr/pm/listener/cdump

18M     ./app/oracle/admin/chennai/adump

4.0K    ./app/oracle/admin/chennai/dpdump

13M     ./app/oracle/admin/sakri/adump

[oracle@pm u02]$ find ./* -type d -name "*crs*" -exec du -sh {} +  2>&1 | grep -v "Permission denied"

208M    ./app/oracle/product/21c/dbhome_1/crs

24K     ./app/oracle/product/21c/dbhome_1/inventory/Components21/oracle.rdbms.crs

32K     ./app/oracle/product/21c/dbhome_1/inventory/Templates/crs

0       ./app/oracle/diag/crs

others RAC GRID:-

find /opt -type d -name "*crsd*" 2>&1 | grep -v "Permission denied"

find ./* -type d -name "*crsd*"  2>&1 | grep -v "Permission denied"

find ./* -type d -name "*evm*"  2>&1 | grep -v "Permission denied"

find ./* -type d -name "*orarootagent*"   2>&1 | grep -v "Permission denied"

find ./* -type d -name "*oraagent*"   2>&1 | grep -v "Permission denied"

find ./* -type d -name "*ohas*"    2>&1 | grep -v "Permission denied"

+++++++++++ To find files older than some hours +++++++++++++++++++++++++++++++

find . -daystart -type f -newermt "$(date -d 'today 10 hours ago' +'%Y-%m-%d %H:%M:%S')"

find . -type f -writable

find . -type f -executable

find . -type f -readable 

+++++++++++++files older between 5 hours to 10 hours from the daystart++++++++++++++

find .  -daystart -type f -newermt "$(date -d 'today 10 hours ago' +'%Y-%m-%d %H:%M:%S')" ! -newermt "$(date -d 'today 5 hours ago' +'%Y-%m-%d %H:%M:%S')"

find . -type f -newermt '5 hours ago' 

find . -type f -newermt '5 minutes ago' 

find . -type f -newermt '5 days ago' 

find . -type f -newermt '5 months ago' 

find . -type f -newermt '5 years ago' 

find . -type f -newermt 'yesterday' 

we can use 

"-type d" flag for directories 

"-type b" flag for block devices

"-type f" flag for files 

NLS (NATIONAL LANGUAGE SUPPORT) in SQL

 NLS 

(national language support)

nls_sort and nls_comp are parameters used to 
control the sorting and comparison behaviour of
characters in SQL queries. 

1. NLS_SORT:-
binary-binary sorting (ascii)
french-french linguistic sorting.
german-german linguistic sorting.
spanish-spanish linguistic sorting
binary_ci-binary sorting with case-insensitivity.
french_ci-french linguistic sorting with case-insensitivity.
example:-
alter session set nls_sort='FRENCH';

2. NLS_COMP:-
binary-binary comparison (case-sensitive).
linguistic- linguistic comparison(case-insensitive).
example:-
alter session set nls_comp='LINGUISTIC';

overall best for case-insensitive search in SQL oracle:-

alter session set nls_sort='binary_ci';

alter session set nls_comp='linguistic';

select * from nls_database_parameters;

select * from nls_instance_parameters;

select * from nls_session_parameters;

PL/SQL TRICKS SINGLE AND DOUBLE AMPERSAND AND BIND VARIABLE

 How to use below:-

&value, &column_name 👉for temporary submission of variable every time you execute a query it'll ask you for entering the value.

&&variable or &&column_name:-

iit's use to set permanent submission in current session;

BIND VARIABLES (:VARIABLE):-

GROUP BY EXPRESSION:-

select column1,column2,column3,...,columnN from table_name

group by column1,column2,column3,...,columnN;

we've to mention all columns in group by expression as below highlighted text...

SQLPLUS COMMAND LINE some set commands

 set sqlprompt "_user '@' _connect_identifier > "

[oracle@pm admin]$ cat $ORACLE_HOME/sqlplus/admin/glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
set lines 400;
set timing on;
set time on;
-- added by kayyum
set sqlprompt "_user '@' _connect_identifier > "
[oracle@pm admin]$
[oracle@pm admin]$
[oracle@pm admin]$ sq
SQL*Plus: Release 21.0.0.0.0 - Production on Wed Sep 6 18:41:04 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
18:41:04 SYS @ sakri >

SET AUTOTRACE ON;

SELECT * FROM employees;

SET SERVEROUTPUT ON;

BEGIN

  DBMS_OUTPUT.PUT_LINE('Hello, World!');

END;

/

SET LINESIZE 100;

SELECT employee_id, first_name, last_name FROM employees;

SET PAGESIZE 25;

SELECT * FROM products;

SET FEEDBACK OFF;

SELECT * FROM customers;

SET VERIFY OFF;

SELECT '&variable_name' FROM dual;

SET HEADING OFF;

SELECT department_name FROM departments;

SET NULL "N/A";

SELECT phone_number FROM employees WHERE employee_id = 9999;

SET SQLFORMAT CSV;

SELECT * FROM products;

SET TIMING ON;

SELECT * FROM orders;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Your SQL statements here

COMMIT;

SET ECHO ON;

SELECT * FROM employees;

SET TERMOUT OFF;

SELECT * FROM customers;

SQL> SET TIMING ON;

elapsed time 00:01:00 

to see time also 

SQL> set time on;

11:00:00 SQL> --- this way we can see---

to set col size :-

col column_name for a<size>;

example: 

SQL> col student_id for a20;

to clear col size:-

clear column;

SQL> clear column;

+++++++++++++++++++++ Auto format SQLPLUS CONSOLE COLUMNS +++++++++

-- Generate COLUMN commands for all columns in a table

SELECT 'COLUMN ' || column_name || ' FORMAT ' ||

  CASE

    WHEN data_type = 'NUMBER' THEN '999999'

    WHEN data_type = 'DATE' THEN 'DATE ''YYYY-MM-DD'''

    ELSE 'A' || data_length

  END || ';'

FROM user_tab_columns

WHERE table_name = upper('&YOUR_TABLE_NAME');

sqltune use with example

 The DBMS_SQLTUNE package in Oracle is used for SQL tuning tasks, including recommendations for optimizing SQL statements. You can use it to analyze and tune individual SQL queries or entire SQL workloads. Here's a basic overview of how to use the DBMS_SQLTUNE package to tune a specific SQL query:

Enable the SQL Tuning Advisor:

Before you can use the SQL Tuning Advisor, you need to ensure that it's enabled. You can enable it using the following command as a user with DBA privileges:

sql

Copy code

exec dbms_sqltune.set_tuning_task_parameter('enabled','true');

Create a SQL Tuning Task:

To tune a specific SQL query, you can create a tuning task. You'll need to provide the SQL statement you want to tune as input. You can do this using the DBMS_SQLTUNE.CREATE_TUNING_TASK procedure:

sql

Copy code

DECLARE
    task_name VARCHAR2(30);
BEGIN
    task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_text => 'SELECT * FROM your_table WHERE your_condition',
        scope    => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
        time_limit => 60, -- Optional: Set a time limit for tuning (in seconds).
        task_type => DBMS_SQLTUNE.TYPE_TEXT
    );
    dbms_output.put_line('Task Name: ' || task_name);
END;
/

Replace 'SELECT * FROM your_table WHERE your_condition' with your actual SQL statement.

You can adjust the time_limit parameter to specify how long the tuning task should run.

Execute the Tuning Task:

After creating the tuning task, you can execute it using the DBMS_SQLTUNE.EXECUTE_TUNING_TASK procedure:

sql

Copy code

BEGIN

    dbms_sqltune.execute_tuning_task(task_name=>'your_task_name');
END;
/

Replace 'your_task_name' with the task name returned when you created the tuning task.

Retrieve Tuning Recommendations:

Once the tuning task has completed, you can retrieve tuning recommendations using the DBMS_SQLTUNE.REPORT_TUNING_TASK procedure:

sql

Copy code

DECLARE
    report CLOB;
BEGIN

    report := dbms_sqltune.report_tuning_task('your_task_name');
    dbms_output.put_line(report);
END;
/

This will generate a report with recommendations for improving the SQL query's performance. You can review this report to see suggested actions.

Implement Tuning Recommendations:

Based on the recommendations provided in the report, you can choose to implement changes to your SQL statement, such as creating indexes, rewriting queries, or changing optimizer settings.

Drop the Tuning Task (Optional):

If you no longer need the tuning task, you can drop it using the DBMS_SQLTUNE.DROP_TUNING_TASK procedure:

sql

Copy code

BEGIN

    dbms_sqltune.drop_tuning_task('your_task_name');
END;

Be sure to clean up tasks when you're done to avoid cluttering the system.

Remember that SQL tuning is an iterative process, and you may need to make multiple adjustments and re-run tuning tasks to achieve the desired performance improvements.

create directory and files in oracle database

 CREATE OR REPLACE DIRECTORY my_dir AS '/path/to/your/directory';

grant read,write on directory my_dir to sys;

CREATE TABLE file_table (

    file_id NUMBER PRIMARY KEY,

    file_name VARCHAR2(255),

    file_data BFILE

);

INSERT INTO file_table (file_id, file_name, file_data)

VALUES (1, 'example.txt', BFILENAME('MY_DIR', 'example.txt'));

DECLARE

    file_content BLOB;

BEGIN

    SELECT file_data INTO file_content FROM file_table WHERE file_id = 1;

    -- Process the file_content (BLOB) as needed.

END;

CREATE OR REPLACE PROCEDURE write_to_file(p_file_name VARCHAR2, p_file_content CLOB) AS

    file_handle UTL_FILE.FILE_TYPE;

BEGIN

    file_handle := UTL_FILE.FOPEN('MY_DIR', p_file_name, 'W');

    UTL_FILE.PUT_LINE(file_handle, p_file_content);

    UTL_FILE.FCLOSE(file_handle);

END;

BEGIN

    write_to_file('new_file.txt', 'This is the content of the new file.');

END;

drop  directory my_dir;

--------------------------------# How To Access External Tables #-------------------------------------

create or replace directory my_dir as '/path/to/dir';

grant read,write on directory my_dir to sys;

CREATE TABLE external_data (

    column1 NUMBER,

    column2 VARCHAR2(255),

    column3 DATE

)

ORGANIZATION EXTERNAL (

    TYPE ORACLE_LOADER

    DEFAULT DIRECTORY my_dir  -- Specify the directory object created earlier.

    ACCESS PARAMETERS (

        RECORDS DELIMITED BY NEWLINE

        FIELDS TERMINATED BY ','  -- Set the appropriate delimiter for your file.

        MISSING FIELD VALUES ARE NULL

    )

    LOCATION ('your_file.csv')  -- Specify the name of your external file.

)

REJECT LIMIT UNLIMITED;  -- Allows unlimited rejected records.

SELECT * FROM external_data;

drop table external_data;

ssh-keygen commands in linux and windows

 locations:-

windows :-

C:\Users\<username>\.ssh

C:\Users\<username>\.ssh\authorized_keys

C:\Users\<username>\.ssh\id_rsa                  👈private key 

C:\Users\<username>\.ssh\id_rsa.pub            👈public key

Linux :-

/home/<username>/.ssh 

/home/<username>/.ssh/authorized_keys        👈authorized key  

/home/<username>/.ssh/id_rsa                        👈private key 

/home/<username>/.ssh/id_rsa.pub                 👈public key 

permissions:-

chmod 700 ~/.ssh  or   chmod 777  /home/<username>/.ssh 

chmod 600 ~/.ssh/authorized_keys  or   chmod  600  /home/<username>/.ssh/authorized_keys

chmod 600 ~/.ssh/id_rsa            or chmod 600   /home/<username>/.ssh/id_rsa

chmod 644 ~/.ssh/id_rsa.pub    or chmdo 644    /home/<username>/.ssh/id_rsa.pub

To generate ssh key on windows and linux 

ssh-keygen -t rsa -b 4096 -C  "user@gmail.com"  

or

ssh-keygen -t rsa 

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

share your id_rsa.pub key to target server ~/.ssh directory and activate id_rsa private key on source server(your own server/windows)

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

Maths on Linux Terminal

 for i in {1..10}; do echo | awk -v var=$i '{print var}' ; done

[oracle@stby ~]$  for i in {1..10}; do echo | awk -v var=$i '{print var}' ; done;
1
2
3
4
5
6
7
8
9
10

----------------------------------------------------------------------
$ a=55

$ b=87

$ echo $((a+b))

[oracle@stby ~]$ a=55

[oracle@stby ~]$ b=45

[oracle@stby ~]$

[oracle@stby ~]$ echo | awk -v var0=$a -v var1=$b '{print "division is: ", var1/var0}'

division is:  0.818182

to have division :-

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

[oracle@stby ~]$ printf "%.01f\n" $(echo "scale=1; 4/9" | bc)

0.4

[oracle@stby ~]$ printf "%.02f\n" $(echo "scale=1; 4/9" | bc)

0.40

[oracle@stby ~]$ printf "%.03f\n" $(echo "scale=1; 4/9" | bc)

0.400

[oracle@stby ~]$ printf "%.03f\n" $(echo "scale=3; 4/9" | bc)

0.444

[oracle@stby ~]$ printf "%.06f\n" $(echo "scale=6; 4/9" | bc)

0.444444

[oracle@stby ~]$ # %.0nf 👉"n"--- number of digits after decimal

[oracle@stby ~]$ # scale=6 👉 numbers of decimals digits can be calculated

1. unziping 

unzip -o /path/to/source/*.zip -d /path/to/destination/

-o :- source

-d :- destination

2. cat file_name

3. strings binary_file

4. tar -cvzf backup.tar.gz --remove-files dir/

-cvzf :- create zip file

-xvzf :- extract zip file

--remove-files :- once the file archived it will then get deleted.

5. export day=Monday

unset day

6. date -d 'tomorrow'

date -d 'next week'

date -d 'next sun'

date -d 'next year'

date -d 'next month'

date -d 'next day'

date -d +'%r' for 12 hour timing

date -d +'%R' for 24 hour timing

date + %b   ---> Sep

date + %B   ---> September

date -d '+- days/months/years' +'%Y_%m_%d %H_%M_%s'   ---> like 2023_09_04 09_05_45

7. to see shell 

echo $SHELL

8. to do maths

echo $(( 5+5 ))

printf "%.0f\n" $(echo "scale=1; 2/5" | bc)   ---> 

%.0f ---> 1

%.01f --> 1.0

%.02f --> 1.00

%.03f --> 1.000

i.e %.0nf ---> n means number of digits after decimal.