Find SID from Process ID (PID) in Oracle

 How to find SID from PID in Oracle Database?

The below query helps in finding the Session ID using Process ID within Oracle Database,

SELECT s.SID,
       s.serial#,
       s.machine,
       s.osuser,
       s.terminal,
       s.username
  FROM v$process P LEFT OUTER JOIN v$session s ON P.addr = s.paddr
 WHERE P.spid = 12345;

Oracle Procedure to Search for a Particular String/Character

Oracle Procedure to Search for a Particular String/Character - Full Schema Scan

The below mentioned Oracle procedure will search for any particular string/character in all the tables for a particular schema and will list the count of number of rows for every column where that string/character is found. The code can be altered as per the need.

DECLARE

  match_count INTEGER;

  v_owner VARCHAR2(255) :='ENTER_SCHEMA_HERE';

  v_data_type VARCHAR2(255) :='VARCHAR2';

-- Type the string you are looking at

  v_search_string VARCHAR2(4000) := '@';

BEGIN

  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP

    EXECUTE IMMEDIATE

    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' like ||':1'

    INTO match_count

    USING v_search_string;

    IF match_count > 0 THEN

      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );

    END IF;

  END LOOP;

END;

/

I hope this helps !!

Query to find the Tracefile using SID in Oracle Database

How to find the Tracefile using SID in Oracle?

Query to find locks information in details | Oracle Database

Query to find detailed information regarding Locks in Oracle Database

Query to Kill Multiple Sessions Together | Oracle Database

How to kill multiple sessions simultaneously in Oracle Database?

Query to generate SQL statements when inactive/active sessions are very high and we need to kill lot of them

Query to check Active Inactive Sessions Count | Oracle Database

How to find Active Inactive Sessions count in Oracle Database?

Query to find the SQL Statements with Multiple SQL IDs | Oracle Database

How to find the SQL Statements with Multiple SQL IDs in Oracle Database?

Query to find the SQL_ID from SQL_TEXT | Oracle Database

How to find the SQL_ID from SQL Text?

Script to see RMAN running jobs and to kill them - Oracle Database

 The below mentioned script helps you to find out already running RMAN operations (if any) in the database. You can modify the query as per the output you want to get but this particular one will show you the list of all the RMAN jobs which are currently in running state and are not yet completed. This query will also show you the jobs which might not be running because of manual cancellation but are still pending to be cleared up from the queue.

select sid, serial#, start_time, totalwork,  sofar, (sofar/totalwork) * 100 pct_done 
from v$session_longops
where totalwork > sofar
AND opname like 'RMAN%';

If you want to kill some particular RMAN Job which you do not want to exist in the database, use the below mentioned queries.

For Single Instance:

alter system kill session 'SID,SERIAL#' immediate;

In Oracle's multi-instance environment (Oracle RAC), it might happen that when you run the above mentioned query, it'll fail with the below mentioned error,

ERROR at line 1: 
ORA-00026: missing or invalid session ID

This is because in multi-instance environment, you need to specify the instance_id as well with the query. There are chances that the instance from where you are trying to kill the job is different from the instance from where the job has run.

For Oracle RAC (multi instance environments):

alter system kill session 'SID,SERIAL#,@instance_id' immediate;

RMAN Incremental Backup Script for Oracle Database

RMAN Incremental Backup Script for Oracle Database - Level 1 Backup

RMAN Script to take Incremental Oracle database Backup (L1) as well as delete the unwanted backups and archive logs

Please find below the script:


run {
    allocate channel ch1 type disk connect 'sys/********@db_name' ;
    allocate channel ch2 type disk connect 'sys/********@db_name' ;
    allocate channel ch3 type disk connect 'sys/********@db_name' ;
    allocate channel ch4 type disk connect 'sys/********@db_name' ;
    allocate channel ch5 type disk connect 'sys/********@db_name' ;
    allocate channel ch6 type disk connect 'sys/********@db_name' ;

RMAN Script to take full database Backup | Oracle Database

RMAN Script to take full database Backup L0- Oracle Database

RMAN Script to take full Oracle database Backup or also called as Level 0 Backup as well as delete the unwanted backups and archive logs

Please find below the script:


run {
    allocate channel ch1 type disk connect 'sys/********@db_name' ;
    allocate channel ch2 type disk connect 'sys/********@db_name' ;
    allocate channel ch3 type disk connect 'sys/********@db_name' ;
    allocate channel ch4 type disk connect 'sys/********@db_name' ;
    allocate channel ch5 type disk connect 'sys/********@db_name' ;
    allocate channel ch6 type disk connect 'sys/********@db_name' ;

SQL Query to Display information on all active database sessions - Oracle Database

Displays information on all active database sessions in Oracle Database

The below mentioned script displays information regarding all active database sessions inside your Oracle database:

SET LINESIZE 500

SET PAGESIZE 1000

COLUMN username FORMAT A15

COLUMN machine FORMAT A25

COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,

       s.osuser,

       s.sid,

       s.serial#,

       p.spid,

       s.lockwait,

       s.status,

       s.module,

       s.machine,

       s.program,

       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,

       s.last_call_et AS last_call_et_secs

FROM   v$session s,

       v$process p

WHERE  s.paddr  = p.addr

AND    s.status = 'ACTIVE'

ORDER BY s.username, s.osuser;

SQL Query to List all objects being accessed in the schema - Oracle Database

Oracle SQL Query to List all objects being accessed in the schema 

Below given is the script that will display you all the objects that are being accessed by a particular schema:

SET LINESIZE 255

SET VERIFY OFF

COLUMN object FORMAT A30

SELECT a.object,

       a.type,

       a.sid,

       b.serial#,

       b.username,

       b.osuser,

       b.program

FROM   v$access a,

       v$session b

WHERE  a.sid    = b.sid

AND    a.owner  = DECODE(UPPER('&1'), 'ALL', a.object, UPPER('&1'))

AND    a.object = DECODE(UPPER('&2'), 'ALL', a.object, UPPER('&2'))

ORDER BY a.object;