How to find the high water mark of the table in oracle database

 --

 -- Show the High Water Mark for a given table, or all tables if ALL is specified for Table_Name.
--

SET LINESIZE 300
SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&Table_Name'),'ALL',a.table_name,Upper('&&Table_Name'))
AND a.owner = Upper('&&Table_Owner')
AND a.partitioned='NO'
AND a.logging='YES'
order by table_name;

op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN

Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;

END;

How to find who locked the database account

 -- Return code 1017 ( INVALID LOGIN ATTEMPT)

 -- Return code 28000 ( ACCOUNT LOCKED)

set pagesize 1299
set lines 299
col username for a15
col userhost for a13
col timestamp for a39
col terminal for a23
SELECT username,userhost,terminal,timestamp,returncode
FROM dba_audit_session
WHERE username='&USER_NAME' and returncode in (1017,28000);

How to convert the non-cdb database into CDB

Steps to Migrate Non-Container Database to Container Pluggable Database

Migrating to cdb database needs downtime, in order to reduce downtime we can create cdb database beforehand.

Details:

cdb database: TCDB011

non-cdb database: VSTORE

version: 19c

Step 1: Shutdown the VSTORE(non-cdb) database 

Step 2: Start the database in read only mode.

SQL > Startup open read only

Step3 - Generate  XML file for VSTORE database.

SQL> exec DBMS_PDB.DESCRIBE(pdb_descr_file => '/tmp/vstore_manifest.xml');

Step4 - Shut down the VSTORE database

SQL> Shutdown immediate

Note - From here, all steps will be performed on the Container database TCDB011

Step 5 - Start TESTDB database (container database) if not running

Step 6 - Check vstore (non-container database) compatibility with TCDB011 (container database)

SET SERVEROUTPUT ON

DECLARE

compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/tmp/vstore_manifest.xml')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

/

Step7 - Check for any errors resulting from the above step

col name for a10

col cause for a10

col type for a20

col message for a80

set lines 230

SELECT name, cause, type, message, status FROM PDB_PLUG_IN_VIOLATIONS WHERE name='VSTORE';

Note: If there are any errors, fix them before proceeding. 

common errors like parameter mismatch, component mismatch between non-cdb and cdb, patch level difference between non-cdb and cbd.

Step 8 - Plug the vstore database into container TCDB011

create pluggable database TCSTAGE using '/tmp/vstore_manifest.xml' NOCOPY tempfile reuse;

COPY - With this option, VSTORE will remain intact. All data files will remain untouched. For PDB files will be copied to the new location, provided with the parameter FILE_NAME_CONVERT.

NOCOPY - Existing files will be used and after completion of the operation, VSTORE will not remain usable. As new PDB is using the same data files.

MOVE - using parameter FILE_NAME_CONVERT, existing datafiles will be moved to the new location, hence after the operation completion, VSTORE will not be usable.

Step 9 - Run the PDB conversion script provided by Oracle

sqlplus / as sysdba

alter session set container=VSTORE;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Step 10 - Start PDB and verify the state

ask application team to verify the database by starting application.

Make necessary changes into configuration management, OEM. Initiate the fresh backup for the vstore database.

How to find the multiple plan hash value for particular sql_id

 select 

  SQL_ID 

, PLAN_HASH_VALUE 

, sum(EXECUTIONS_DELTA) EXECUTIONS

, sum(ROWS_PROCESSED_DELTA) CROWS

, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS

, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS

from DBA_HIST_SQLSTAT 

where SQL_ID in (

'&sqlid') 

group by SQL_ID , PLAN_HASH_VALUE

order by SQL_ID, CPU_MINS;

How to find the minimum and maximum execution time for a sql_id based on plan hash value

select sql_plan_hash_value, COUNT(*), MIN(sql_exec_start), MAX(sql_exec_start)

from   dba_hist_active_sess_history

where  sql_id = <sql_id>

How to find the Full Table Scans in the Database

 SELECT sp.sql_id ,sp.object_owner ,sp.object_name ,sa.sql_text as sqltext

,sa.sql_fulltext AS sql_fulltext ,sa.executions as no_of_full_scans ,tbl.row_num

,tbl.blocks ,tbl.buff_pool FROM v$sql_plan sp -- LEFT JOIN v$sqlarea sa ON

sa.address = sp.address AND sa.hash_value = sp.hash_value -- JOIN (SELECT

table_name ,owner ,num_rows AS row_num ,blocks ,buffer_pool AS buff_pool FROM

dba_tables WHERE 1 = 1) tbl ON tbl.table_name = sp.object_name AND tbl.owner =

sp.object_owner -- WHERE 1=1 AND operation = 'TABLE ACCESS' AND options = 'FULL'

AND object_owner = 'ORACLE_WORLD' -- for a particular user (or comment out to

get all) AND tbl.row_num &gt;= 100000 -- limit the table size to filter out small

tables -- ORDER BY no_of_full_scans DESC