Monday, 19 June 2017

Script to Drop a user's objects.

This script will produce the alter/drop commands that we can use to drop all the user objects from a database.


REM Script to drop all user objects

SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF VERIFY OFF
BREAK ON username
TTITLE left _date center 'SQL To Drop A Users Objects' skip 2

PROMPT When prompted, enter the name of the user who''s objects you wish to drop
PROMPT Then cut and paste the commands presented to drop those objects.

SELECT 'alter table '
|| owner
|| '.'
|| table_name
|| ' drop constraint '
|| constraint_name
|| ' cascade;' code
FROM dba_constraints
WHERE (constraint_type = 'R') AND (owner = UPPER ('&&enter_user_name'))
UNION
SELECT DISTINCT 'DROP '
|| object_type
|| ' '
|| owner
|| '.'
|| object_name
|| ';' code
FROM dba_objects
WHERE (object_type NOT IN ('INDEX'))
AND (owner = UPPER ('&&enter_user_name'))
ORDER BY code DESC;


Sample Output

SQL> When prompted, enter the name of the user who’s objects you wish to drop
SQL> Then cut and paste the commands presented to drop those objects.

18-JUL-08 SQL To Drop A Users Objects

CODE
--------------------------------------------------------------------------------------
DROP TABLE SCOTT.UMCATEGORYCONVFACTOR;
DROP TABLE SCOTT.UOMCATEGORYCONVFACTOR;
DROP TABLE SCOTT.UDT_SOURCING;
DROP TABLE SCOTT.WEBSPRELOPTS;
DROP TABLE SCOTT.WEBSKUOPTS;
DROP TABLE SCOTT.WDDDATA;





I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!



Tuesday, 16 May 2017

Creating sql baseline to fix query with better execution plan

            
Hello everyone, hope you all doing good. It’s been too long I haven’t updated new post on my blog due to my busy schedule.
So here I will be sharing very interesting post of creating sql baseline and force query to use better execution plan (plan hash value).
Most of us have come across this scenario where query which was running fine till yesterday now suddenly running long.
So there could be many reasons why query performance has been changed suddenly, so one of the reason is change in query plan.
In such situation we need to find out best execution plan (Plan_hash_value) and force query to use that plan.
Below are the steps to create and fix bad query by creating sql baseline.

=====Step 1: Generate all previous history run details of sql_id from AWR ==

break off sdate
set lines 2000
set linesize 2000
col SDATE format a10
col STIME format a10
select to_char(begin_interval_time,'YYYY/MM/DD') SDATE,to_char(begin_interval_time,'HH24:MI')  STIME,s.snap_id,
        sql_id, plan_hash_value PLAN,
        ROUND(elapsed_time_delta/1000000,2) ET_SECS,
        nvl(executions_delta,0) execs,
        ROUND((elapsed_time_delta/decode(executions_delta,null,1,0,1,executions_delta))/1000000,2) ET_PER_EXEC,
        ROUND((buffer_gets_delta/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_lio,
        ROUND((CPU_TIME_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_cpu_ms,
        ROUND((IOWAIT_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000, 2) avg_iow_ms,
        ROUND((DISK_READS_DELTA/decode(executions_delta,null,1,0,1,executions_delta)), 2) avg_pio,
        ROWS_PROCESSED_DELTA num_rows
from DBA_HIST_SQLSTAT S,  DBA_HIST_SNAPSHOT SS
where s.sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
order by sdate,stime;

Enter value for sql_id: 7hgwdax4mn20v


SDATE      STIME         SNAP_ID SQL_ID              PLAN    ET_SECS        EXECS ET_PER_EXEC        AVG_LIO AVG_CPU_MS AVG_IOW_MS    AVG_PIO   NUM_ROWS
---------- ---------- ---------- ------------- ---------- ---------- ------------ ----------- -------------- ---------- ---------- ---------- ----------
2016/09/13 11:00           23468 7hgwdax4mn20v 2844841640    1421.36            1     1421.36        4,301.0  152720.78     312.25        269        336
2016/09/13 15:00           23472 7hgwdax4mn20v 2844841640    1070.12            1     1070.12        4,291.0  126987.69     348.34        270        336
2016/09/13 17:00           23474 7hgwdax4mn20v 2844841640    1211.98            1     1211.98        4,374.0  183347.13     157.32        270        336
2016/09/13 18:00           23475 7hgwdax4mn20v 2844841640     596.91            0      596.91          116.0   45123.14          0          0          0
2016/09/14 01:00           23482 7hgwdax4mn20v 2844841640    1038.41            1     1038.41        4,309.0  123455.23     413.94        272        336
2016/09/14 02:00           23483 7hgwdax4mn20v 1355798266     193.82            1      193.82        2,748.0   76799.32      28.95         16        168
2016/09/14 03:00           23484 7hgwdax4mn20v 2844841640        .04            0         .04             .0         38          0          0          0
2016/09/14 03:00           23484 7hgwdax4mn20v          0        .04            0         .04             .0      38.99          0          0          0
2016/09/14 10:00           23491 7hgwdax4mn20v 2844841640     626.06            0      626.06           64.0   54658.69          0          0          0

/*In this scenario sql_id=7hgwdax4mn20v and plan_hash_value for good plan that we want to force is 1355798266.*/
Follow below steps to create sql baseline for sql_id

 =====Step 2: Drop SQL Tuning Set (STS) if exists=========================

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'SQL_FOR_7hgwdax4mn20v');
END;

======Step 3: Create SQL Tuning Set ========================================

BEGIN
  DBMS_SQLTUNE.create_sqlset (
    sqlset_name  => 'SQL_FOR_7hgwdax4mn20v',
    description  => 'SQL tuning set for 7hgwdax4mn20v');
END;
/

/* Populate STS from AWR by specifying snapshot for desired plan which we found using above query.
In this scenario snap id's are 23483 and 23484 and change plan_hash_value accordingly.*/

DECLARE
  l_cursor  DBMS_SQLTUNE.sqlset_cursor;
BEGIN
  OPEN l_cursor FOR
    SELECT VALUE(p)
    FROM   TABLE (DBMS_SQLTUNE.select_workload_repository (
                    23483,  -- begin_snap
                    23484,  -- end_snap
                    q'<sql_id in ('7hgwdax4mn20v') and plan_hash_value in (1355798266)>',  -- basic_filter 
                    NULL, -- object_filter
                    NULL, -- ranking_measure1
                    NULL, -- ranking_measure2
                    NULL, -- ranking_measure3
                    NULL, -- result_percentage
                    100)   -- result_limit
                  ) p;

  DBMS_SQLTUNE.load_sqlset (
    sqlset_name     => 'SQL_FOR_7hgwdax4mn20v',
    populate_cursor => l_cursor);
END;
/

=========Step 4: Check Sql set details ================================

column text format a20
select sqlset_name, sqlset_owner, sqlset_id, sql_id,substr(sql_text,1,20) text,elapsed_time,buffer_gets,
parsing_schema_name, plan_hash_value, bind_data from dba_sqlset_statements where sqlset_name ='SQL_FOR_7hgwdax4mn20v';

=========Step 5: Load desired plan from STS as SQL Plan Baseline=======

DECLARE
  L_PLANS_LOADED  PLS_INTEGER;
BEGIN
  L_PLANS_LOADED := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    SQLSET_NAME => 'SQL_FOR_7hgwdax4mn20v');
END;

========Step 6: Check sql plan baseline details =======================

SELECT sql_handle, plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines
WHERE signature IN (SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')
order by accepted,enabled;

========Step 7: Enable fixed=YES=======================================

var pbsts varchar2(30);
exec :pbsts := dbms_spm.alter_sql_plan_baseline('SQL_64e76e773c55f1c4','SQL_PLAN_69tvffwy5bwf480a6275e','FIXED','YES');

=======tep 8: Purge old execution plan from shared pool ===============

Find below two parameter which are required to purge specific sql from shared pool.

select address||','||hash_value from gv$sqlarea where sql_id = '7hgwdax4mn20v';

ADDRESS||','||HASH_VALUE
----------------------------------------------------------------------------------------
000000058B4B7E40,1879818331

Now use below command to purge sql from shared pool.

exec sys.dbms_shared_pool.purge('000000058B4B7E40,1879818331','C',1);


Tuesday, 1 December 2015

Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c


Connecting to the root of a container database is the same as that of any previous database instance. On the database server you can use OS Authentication.
 
[oracle@vmdb12c ~]$ sqlplus / as sysdba


SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 30 11:38:44 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL> show con_name

CON_NAME
——————————
CDB$ROOT  
The V$SERVICES views can be used to display available services from the database.

SQL> select name,pdb from v$services order by 2;

NAME PDB
——————– ———-
zwcXDB CDB$ROOT
SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
ptest12c PTEST12C
ptest12c_1 PTEST12C_1
ptest12c_2 PTEST12C_2

6 rows selected.  

The lsnrctl utility allows you to display the available services from the command line.

[oracle@vmdb12c ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2015 12:17:38

Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER_TEST12C
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 16-NOV-2015 18:02:53
Uptime 14 days 18 hr. 14 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /data/amit/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /data/amit/app/oracle/diag/tnslsnr/bigdata2/listener_test12c/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost.localdomain)(PORT=5500))(Security=(my_wallet_directory=/data/amit/app/oracle/admin/test12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PTEST12C" has 2 instance(s).
Instance "PTEST12C", status UNKNOWN, has 1 handler(s) for this service...
Instance "test12c", status READY, has 1 handler(s) for this service...
Service "ptest12c_1" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Service "ptest12c_2" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Service "ptest12c_3" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Service "test12c" has 2 instance(s).
Instance "test12c", status UNKNOWN, has 1 handler(s) for this service...
Instance "test12c", status READY, has 1 handler(s) for this service...
Service "test12cXDB" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@vmdb12c ~]$ lsnrctl service 

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2015 12:19:05

Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "PTEST12C" has 2 instance(s).
Instance "PTEST12C", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:71 refused:0
LOCAL SERVER
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "ptest12c_1" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "ptest12c_2" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "ptest12c_3" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "test12c" has 2 instance(s).
Instance "test12c", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:10 refused:0
LOCAL SERVER
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "test12cXDB" has 1 instance(s).
Instance "test12c", status READY, has 1 handler(s) for this service...
Handler(s):
.......
The command completed successfully

Connections using services are unchanged from previous versions.
easy connect


[oracle@vmdb12c ~]$ sqlplus system/oracle123@202.137.235.171:1521/PTEST12C_2

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:24:39 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Dec 01 2015 13:00:19 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------

PTEST12C_2

[oracle@vmdb12c ~]$ sqlplus system/oracle123@202.137.235.171:1521/PTEST12C_3

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:24:39 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Dec 01 2015 13:00:19 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------

PTEST12C_3

tnsnames.ora


[oracle@vmdb12c ~]$sqlplus system/oracle@PTEST12C_3

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:47:25 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Dec 01 2015 15:24:39 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> show con_name


CON_NAME
------------------------------

PTEST12C_3

[oracle@vmdb12c ~]$sqlplus system/oracle@PTEST12C_1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:47:25 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue Dec 01 2015 15:24:39 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show con_name

CON_NAME
------------------------------
PTEST12C_1

tnsnames.ora


TEST12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test12c)
)
)
PTEST12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PTEST12C)
)
)
PTEST12C_1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PTEST12C_1)
)
)
PTEST12C_3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PTEST12C_3)
)
)
LISTENER_TEST12C =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

When logged in to the CDB as an appropriately privileged user, the ALTER SESSION command can be used to switch between containers within the container database.

[oracle@vmdb12c ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 1 15:53:10 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=PTEST12C_1;

Session altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PTEST12C_1 READ WRITE NO

SQL> alter session set container=PTEST12C_2;

Session altered.

SQL> show pdbs


CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PTEST12C_2 READ WRITE NO

SQL> alter session set container=cdb$root;

Session altered.

SQL> show pdbs


CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO 
4 PTEST12C_1 READ WRITE NO
5 PTEST12C_2 READ WRITE NO
6 PTEST12C_3 READ WRITE NO

SQL> select sys_context('USERENV','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
——————————————————————————–
CDB$ROOT


SQL> alter session set container=PTEST12C_1

Session altered.

SQL> select sys_context('USERENV','CON_NAME') from dual;


SYS_CONTEXT('USERENV','CON_NAME')
——————————————————————————– 

PTEST12C_1




I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!