Explain Plan in Oracle

Explain plan in Oracle shows execution plan in Oracle which is choose by oracle optimizer for select statements, insert, update and delete statements. Informations we can get from explain plan in Oracle are :

• Table used for sql statement we want to explain
• Access method in tables we access.
• Steps for executing the sql
• join method
  
• sorting and agregating

Steps using explain plan in Oracle :

• Create table plan by execute script utlxplan.sql locates in $ORACLE_HOME/rdbms/admin/utlxplan.sql
• Use explain plan by using this sql : EXPLAIN PLAN
  SET STATEMENT_ID = 'some identifier'
  INTO plan_table
  FOR sql_statement;
• Check explain plan result : Select * from PLAN_TABLE
  where statement_id = 'some identifier';
• If we use cost based optimizer we can use this sql to check explain plan result : SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' '
  ||object_name||' '||
  DECODE(id,0,'Cost = '|| position) AS "Query Plan"
  FROM plan_table
  START WITH id = 0
  AND statement_id='SQL Pak Hari'
  CONNECT BY PRIOR ID = PARENT_ID
  AND statement_id = 'SQL Pak Hari';
• Thats all
  

Apache on Solaris 10

Solaris 10 already have Apache in it, in order to use it you just need to start the service. The step to start the Apache server are :
1. Go to /usr/apache/bin : #cd /usr/apache/bin
2. To start the service : #./apachectl start
You will get error message if you don’t have httpd.conf located in /etc/apache/
To create htttpd.conf just copy httpd.conf-example located in /etc/apache/ :
#cp /etc/apache/httpd.conf-example /etc/apache/httpd.conf
After that try again run command #./apachectl start
If it success than in your solaris will show message ./apachectl start: httpd started
3.After that you can try your Apache server using your browser and type : http://yourIPserver/

That’s all

RMAN setup using catalog database quick guide

To setup RMAN using catalog database follow these steps :
1. Create user for catalog database repository in catalogue database
-Create tablespace for catalog database
sql>CREATE TABLESPACE RMAN_CAT
DATAFILE ‘RMAN_CAT’ SIZE 500 M
-Create user for catalog database
sql>create user rman_cat identified by rman_cat
temporary tablespace temp
default tablespace rman_cat
quota unlimited on rman_cat
-Grant appropriate privilege for user created
sql>grant recovery_catalog_owner,resource,create session to rman_cat
-Connect to recovery catalog using RMAN
$rman catalog rman_cat/rman_cat@rman_cat
RMAN>create catalog tablespace rman_cat
2. Connect to target database (Database to be backup) and catalog database using RMAN
-Connect using rman and register database
$rman target sys/pass@target catalog rman_cat/rman_cat@rman_cat
RMAN>register database;
-Check RMAN configuration
RMAN> Show all;
Command above will see RMAN configuration

Automate kill inactive user in oracle database

Step by step to automatically kill inactive user in oracle database
To see inactive user in oracle database you can use this query “select * from v$session where status=’INACTIVE’ or user=’sys’”
1. Create procedure for killing inactive oracle database user (oracle user have to be grant dbms_sql to execute this procedure)
CREATE OR REPLACE PROCEDURE kill_session_1 ( session_id in varchar2,serial_num in varchar2)
AS
cur INTEGER;
ret INTEGER;
string VARCHAR2(100);
BEGIN
–
– Comment out the following three lines to
– not use KILL
–
string :=’ALTER SYSTEM KILL SESSION’ || CHR(10) ||CHR(39)||session_id||’,'||serial_num||CHR(39);
–
– Uncomment the following 4 lines to use DISCONNECT
–
–string := ‘ALTER SYSTEM DISCONNECT SESSION’ || CHR(10) || CHR(39)||session_id||’,'||serial_num||CHR(39)||CHR(10)||’ POST_TRANSACTION’;
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur,string,dbms_sql.native);
ret := dbms_sql.execute(cur) ;
dbms_sql.close_cursor(cur);
END;
/
2. Create sql script ORA_KILL.sql for spooling command to execute procedure created (see no 1)
REM
REM ORA_KILL.SQL
REM FUNCTION: Kills nonessential Oracle sessions (those that aren’t
REM owned)
REM : by SYS or “NULL”
REM DEPENDENCIES: Depends on kill_session procedure
REM MRA 9/12/96
REM
SET HEADING OFF TERMOUT OFF VERIFY OFF ECHO OFF
SPOOL kill_all.sql
SELECT ‘EXECUTE kill_session_1(‘||chr(39)||sid||chr(39)||’,'||
chr(39)||serial#||chr(39)||’);’ FROM v$session
WHERE username = ‘ECARE2′ and status=’INACTIVE’
/
SPOOL OFF
START kill_all.sql

3. Create shell script to automate execute sql script using cronjob (for unix user)
sqlplus -s $schema/$pass@$dbLink as sysdba<set timing on
@ORA_KILL