Anuroop Challa

Creating SQL Tuning Task with sql_id ============================= First get sql_id of sql those who try to create task Hope as dba you all know how get sql_id. and here i take below sql-id to create task sql_id= 2w3y1nfj1uanf Note: remember in below red color sql_id change as per your id. Step1: - Create task:  SET serveroutput ON DECLARE   v_tune_taskid  VARCHAR2(100); BEGIN   v_tune_taskid := dbms_sqltune.create_tuning_task (                           sql_id      => ' 2w3y1nfj1uanf ',                           scope       => dbms_sqltune.scope_comprehensive,                           time_limit  => 30,                           task_name...

SQL Tuning Task Creation ORA-13780: SQL statement does not exist. ================================================= example:- declare  l_sql_tune_task_id  varchar2(100); begin  l_sql_tune_task_id := dbms_sqltune.create_tuning_task (         sql_id      => 'your sql_id',         scope       => dbms_sqltune.scope_comprehensive,         time_limit  => 10800,         task_name   => 'tuning task name',         description => 'tuning task description');  dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); end; / declare * ERROR at line 1: ORA-13780: SQL statement does not exist. ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 125 ORA-06512: at "SYS.DBMS_SQLTUNE", line 655 solution :----  SELECT SNAP_ID FROM DBA_HIST_...

ADD/Remove UNDO tablespace in oracle database:- =====================================  every database having default undo tablespace, if you want to change it do follow below procedure. 1. first create new tablespace with new(as you want to create) Example:- CREATE UNDO TABLESPACE <NEW_NAME> DATAFILE '<datafile_location/name.dbf>' SIZE 1000M AUTOEXTEND OFF ONLINE RETENTION NOGUARANTEE BLOCKSIZE 8K FLASHBACK ON; ALTER SYSTEM SET UNDO_TABLESPACE = <NEW_NAME>; ALTER SYSTEM SET UNDO_RETENTION = <SET as per NEED>; Once new one created, if you want drop old one follow steps below. check any session already using old one  with below sql SQL> select tablespace_name, owner, segment_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1' and status='ONLINE'; you will get SID&Serial# if any in use, kill them if have or else you can proceed to drop old one. To kill SQL> alter system kill session 'SID,...

ADD/Remove TEMP tablespace in oracle database:- =====================================   To add new Temp tablespace and change it as default do follow below procedure. 1. First create New temptablespace and change it to default temp. CREATE TEMPORARY TABLESPACE <NEW_TEMP_NAME> TEMPFILE '<teampfile location.dbf' SIZE 1000M AUTOEXTEND OFF TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <NEW_TEMP_NAME>; Remove the old temptable space , before remove you must check any session already in use or not. to check use below query to get live session(SID&serial#)  those who use old temp. SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#, a.username,a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr; THEN kill session if any session in use. alter system kill session 'SID_NUMBER, SERIAL#'; we can drop old temporary tablespace. ...

Query Performance Tuning ====================  In this post i will give few tips while writing sql query, how we can write better query with less cost  If you follow below steps keep in mind while writing query you can tune up your sql while writning  time. Below are some tips which can be used as guideline.   Make sure am not expert, But what i learn have been post here to help some one.    If you know more points or you want to add on more please post in comment session it help to me and also others to learn.    Even though i can miss some silly points or known point as well.    so please help to remind them !!! DISTINCT:-  Do not use, If the objective can be achieved otherwise. DISTINCT incurs an extra sort operation and therefore slows your queries down.    Indexed columns:- Do not modify indexed columns with functions like RTRIM, TO_CHAR, UPPER, TRUNC, As this will prevent the optimizer from ...

B-tree vs bitmap index =================   In this post i would like talk about index that to mainly b-tree vs bit man index.   Both are used to play key role in performance of query. lets some comparison of both:- B-tree :- it is one Default index, Like create index on one or columns using tree like structure. Cardinality is High, means less duplicate don`t confuse some people call it function based index as in. syntax example:- CREATE INDEX <index_name> ON <table_name>( function (column_name)); Bitmap :- Carnality  is Low, means more duplicate. bitmap index create name with bimap key word, Bitmap only can use in oracle Enterprise edition, standard edition we can not use this feature. syntax example:- CREATE BITMAP INDEX  <index_name> ON <table_name>  ( column_name ); still we can add on more points, but i want to hear from your side also. so please post your opinion about index types in comment ...

IMPDP 10g,11g and 12c parameters  :- =============================== IMPDP 10g $ impdp help=y Import: Release 10.2.0.5.0 - 64bit Production on Monday, 12 February, 2018 12:00:08 Copyright (c) 2003, 2007, Oracle.  All rights reserved. The Data Pump Import utility provides a mechanism for transferring data objects between Oracle databases. The utility is invoked with the following command:      Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp You can control how Import runs by entering the 'impdp' command followed by various parameters. To specify parameters, you use keywords:      Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)      Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp USERID must be the first parameter on the command line. Keyword               Description (Default) --------------------------------------...

EXPDP 10g,11g and 12c parameters:- EXPDP 10g:-  expdp help=y Export: Release 10.2.0.5.0 - 64bit Production on Monday, 12 February, 2018 11:55:11 Copyright (c) 2003, 2007, Oracle.  All rights reserved. The Data Pump export utility provides a mechanism for transferring data objects between Oracle databases. The utility is invoked with the following command:    Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp You can control how Export runs by entering the 'expdp' command followed by various parameters. To specify parameters, you use keywords:    Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)    Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott                or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. Keyword             ...

 All locks and their definition in database ======================================== by using below ssql you get same out put. ------------------------------------------------------------------------- SELECT TYPE, name, description FROM v$lock_type ORDER BY TYPE; ------------------------------------------------------- output:- TYPE   NAME DESCRIPTION AB Auto BMR A general class of locks used by auto BMR for various purposes AC Application Continuity Synchronizes new service activation AD ASM Disk AU Lock Synchronizes accesses to a specific ASM disk AU AE Edition Lock Prevent Dropping an edition in use AF Advisor Framework This enqueue is used to serialize access to an advisor task AG Analytic Workspace Generation Synchronizes generation use of a particular workspace AH ASM Relocation Lock High Protects locked extent pointers during ASM file relo...