Jayesh Pande

--To Check for SQL_ID Execution Plan in the DB-- set lines 201 trimout on trimspool on pages 0 col PLAN_TABLE_OUTPUT format A200 set serveroutput off alter session set statistics_lev el = all; select plan_table_output from table(dbms_xplan.display_cursor('&sql_id',null,'advanced')) ; --To Check for Existing SQL Profiles in the DB-- select name,category,status from dba_sql_profiles where name like '%&SQL_ID%' ; -- Tuning task created for specific a statement from the cursor cache-- DECLARE   l_sql_tune_task_id  VARCHAR2(100); BEGIN   l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (                           sql_id      => '&sql_id',                           scope  ...

Unusable indexes Oracle indexes can go into a UNUSABLE state after maintenance operation on the table or if the index is marked as 'unusable' with an ALTER INDEX command. A direct path load against a table or partition will also leave its indexes unusable. Queries and other operations against a table with unusable indexes will generate errors: ORA-01502: index ‘string.string’ or partition of such index is in unusable state Detecting The following SQL commands can be used to detect unusable indexes: Indexes: SELECT owner, index_name, tablespace_name FROM dba_indexes WHERE status = 'UNUSABLE'; Index partitions: SELECT index_owner, index_name, partition_name, tablespace_name FROM dba_ind_PARTITIONS WHERE status = 'UNUSABLE'; Index subpartitions: SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name FROM dba_ind_SUBPARTITIONS WHERE status = 'UNUSABLE'; Fixing The follow...

Create 12c database manually using database creation script. Note:- database is a simple database not container database --->OS version: [JAY@O12c pfile]# cat /etc/redhat-release Red Hat Enterprise Linux Server release 5.4 (Tikanga) [JAY@O12c pfile]# --->Database version: [oracle@O12c ~]$ sqlplus SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 18 07:35:10 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter user-name: /as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options --->Create following directory on server and give permission to oracle user and oinstall group. [JAY@O12c cdb1]#mkdir -p /u01/app/oracle/admin/orcl/dpdump [JAY@O12c cdb1]#mkdir -p /u01/app/oracle/admin/orcl/adump [JAY@O12c cdb1]#mkdir -p /u01/app/oracle/admin/orcl/pfile [JAY@O12c cdb1]#mkdir -p /u01/app/oracle/oradata/orcl/controlfile [JAY@O12c cdb1]#mkdir -p/u01...

With default profile in Oracle 11g, accounts are automatically locked 1 day ( PASSWORD_LOCK_TIME ) after 10 failed login attempt ( FAILED_LOGIN_ATTEMPTS ): SQL > SET lines 200 SQL > SET pages 200 SQL > SELECT * FROM dba_profiles WHERE PROFILE = 'DEFAULT' ORDER BY resource_name; PROFILE                         RESOURCE_NAME                      RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- DEFAULT                         COMPOSITE_LIMIT                 ...