How to get actual explain plan of query in oracle

dbms_xplan- The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display    the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and SQL_PLAN_STATISTICS_ALL fixed views.

DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.

    how to use

    -- first execute the query
select e.ename , e.sal, d.dname from emp e ,dept d where e.deptno = d.deptno;
    -- then search sql id for the query that is executed
select sql_id,sql_text from v$sqlarea where sql_text like '%select e.ename , e.sal, d.dname from emp e ,dept d where e.deptno = d.deptno;%'; 

SQL_ID        SQL_TEXT
------------- ---------------------------------------------------
1rk5afg9tcn4f select sql_id,sql_text from v$sqlarea where sql_tex
dmrp9jvfxd89f select e.ename , e.sal, d.dname from emp e ,dept d
4fdqm0tbnycyb select sql_id,sql_text from v$sqlarea where sql_tex

    -- chose the correct sql_id wrt to your query, then excute the below query to get actual plan
select * from table(dbms_xplan.display_cursor('dmrp9jvfxd89f'));

  

For any modification , updatation in post please comment

Difference between explain plan and autotrace

The main difference between explain plan and autotrace is only that expalin plan produces execution plan without execute the statement where as autrace gave execution plan after execution of the statement.

EXPLAIN PLAN

SQL> select count(*) from emp;
  COUNT(*)
----------
        14

SQL> explain plan for delete from emp where ename = 'SMITH';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 161811703
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |    19 |     3   (0)| 00:00:01 |
|   1 |  DELETE            | EMP  |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    19 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select count(*) from emp;
  COUNT(*)
----------
        14 

(OUTPUT SPACING HAS BEEN MODIFIED , dont try to match it)

AUTOTRACE

SQL> select count(*) from emp;
  COUNT(*)
----------
        14
SQL> set autotrace on
SQL> delete from emp where ename = 'SMITH';
1 row deleted.

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Ti
-------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |    19 |     3   (0)| 00
|   1 |  DELETE            | EMP  |       |       |            |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    19 |     3   (0)| 00
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          7  db block gets
          7  consistent gets
          2  physical reads
       1152  redo size
        673  bytes sent via SQL*Net to client
        610  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off

SQL> select count(*) from emp;
  COUNT(*)
----------
        13

Now You can see the difference that in case of explain plan the row count of table remain same ,
but in case of autotrace one row has been deleted and then it produce the execution plan.

Execution plan of query in Oracle

Today i study about the area of execution plans and this is the post , that what i collect after reading several
websites and blog and youtube and presentation from scribd. My main motive to get actual execution plan and below is the notes.

We can get the query execution plan by below ways:-
1. explain plan
2. autotrace
3. tkprof
4. dbms_xplan.display_cursor

First three ways are used for estimated execution plan of query in oracle and fourth give us acual execution plan.
1. explain plan
->explain plan statement that produces execution plan without execute the statement
    - where to use
        - in long running queries.
        - if we need a fair idea of flow of execution plans.

    how to use:
    -
    explain plan for select * from emp;
    -
    select * from table(dbms_xplan.display);

2. autotrace  
->autotrace on same as explain plan (generation of plan is similler as "explain plan")
    - autotrace statement produces execution plan after execution of the statement.
    - need to wait till query is not executed
  how to use:


 
sql> set autotrace on               [block 1,2,3 will display]
sql> set autotrace trace           [block 2,3 will display]
sql> set autotrace on explain   [block 1,2 will display]
sql> set autotrace on statistics [block 1,3 will display]

--enable autotrace from sql*plus window
see here
Difference between explain plan and autotrace

3. tkprof  
-> tkprof
    - get reports after only when we enable trace
    - have execution plan , more detail info all disk inputs and outputs
    - get info from trace file

    Detail usability

4.dbms_xplan.display_cursor
->dbms_xplan.display_cursor show actual execution plan from dynamic performance views

    More details See here :
    How to get actual execution plan in oracle

 Similler series of posts
1. Difference between explain plan and autotrace
2. Usability of tkprof
3. How to get actual explain plan of query in oracle

For any modification , updatation in post please comment

why query is get faster after create a index

Index is method, applied on table to access the data faster

If we do not create index on table, and we execute the query and then oracle engine do the full table scan to return the required data.

figure explanation

Branch Blocks and Leaf Blocks

A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. The upper-level branch blocks of a B-tree index contain index data that points to lower-level index blocks. In Figure , the root branch block has an entry 0-40, which points to the leftmost block in the next branch level. This branch block contains entries such as 0-10 and 11-19. Each of these entries points to a leaf block that contains key values that fall in the range.

Branch blocks store the minimum key prefix needed to make a branching decision between two keys. This technique enables the database to fit as much data as possible on each branch block. The branch blocks contain a pointer to the child block containing the key. The number of keys and pointers is limited by the block size.
The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. Each entry is sorted by (key, rowid). Within a leaf block, a key and rowid is linked to its left and right sibling entries. The leaf blocks themselves are also doubly linked. In Figure  the leftmost leaf block (0-10) is linked to the second leaf block (11-19).
 --------------

-> After create a index, Database retrieve the rows by traverse the index , instead of traverse the table. It help to to save the data input output reads and disk loads.

If sql statement only access indexed columns in select clause, then Database will load the index and start the index range scan.( it fetch the data after index scan).

example
select ename from emp where ename = 'smith';
It is assumed that the index is created on emp (ename) , and query contain only ename column in select clause of query, then database will do index scan and return desired result.

If sql statement have the others column instead of indexed columns in select clause of sql statement

then database retrive the other data with help of rowid (as u can see the figure , leaf block store rowid with each indexed item , which point the table rowid).
example
select ename,empno, mgr, sal, job from emp where ename = 'smith';
It is assumed that the index is created on emp (ename) , and query contain other columns in select clause of query, then database will do index scan and use rowid (which point to table data) and return the desired result.

Enable log in mysql

To enable query log in mysql execute the below steps:

First of all we need to locate my.ini file where we can set the variables for mysql server.

my.ini file will reside at path "C:\ProgramData\MySQL\MySQL Server 5.*\"

--permanent changes
Open file in editor (notepad, npp , editplus any of one) and look for below variables

Default setting of variables:-
# General and Slow logging.
log-output=NONE
general-log=0
general_log_file="machine_name.log"
slow-query-log=0
slow_query_log_file="machine_name-slow.log"
long_query_time=2

# General and Slow logging.
log-output=FILE
[enable all type of logging, Can also set to TABLE]
general-log=0
[Set it to 1 , to enable it]
general_log_file="machine_name.log"
[any filename, default path "C:\ProgramData\MySQL\MySQL Server 5.*\data"]
slow-query-log=1
[Set it to 1 , to enable it]
slow_query_log_file="machine_name-slow.log"
[any filename, default path "C:\ProgramData\MySQL\MySQL Server 5.*\data"]
long_query_time=2
[slow query time, query will logged into file if it take >2 sec]

--Temporary changes
show variables like '%log_file%';
    note down file name
set global log-output='FILE';
set global slow-query-log=1;
set global long_query_time=2;


Note:

same Queries can be used in linux and the path to log file is "var/lib/mysql"

Uninstall mysql sever from Linux machine

1. Get the mysql installed rpm and its dependency.

[root@localhost ~]# rpm -qa | grep -i mysql
MySQL-devel-* 
MySQL-test-*
MySQL-server-*
MySQL-client-* 
MySQL-shared-*
MySQL-shared-compat-*
MySQL-embedded-*

2. Above query return some result as shown and then remove all rpms i.e. return by query

[root@localhost ~]# rpm -e MySQL-* 

OR [remove them one by one]

[root@localhost ~]# rpm -e MySQL-devel-* (full rpm name)
[root@localhost ~]# rpm -e MySQL-test-*
[root@localhost ~]# rpm -e MySQL-server-*
[root@localhost ~]# rpm -e MySQL-client-*
[root@localhost ~]# rpm -e MySQL-shared-*
[root@localhost ~]# rpm -e MySQL-shared-compat-*
[root@localhost ~]# rpm -e MySQL-embedded-* 

3. After uninstall rpm check for the directory existence at path /var/lib/mysql if exists remove directory.

[root@localhost ~]# cd /var/lib 
[root@localhost lib]# rmdir -p mysql

Mysql command on linux terminal move to bash shell

[root@localhost ~]# mysql

bash3.2

The Problem might be that mysql server is not installed or it is not installed

properly on linux machine.

When i check the log files i got the error as 

mysql: relocation error: mysql: symbol strmov, version libmysqlclient_16 not defined in file 
libmysqlclient.so.16 with link time reference 

So i uninstall the mysql completely and reinstall it. 

Series of post :-

Install mysql on linux
Uninstall mysql from linux machine

Install mysql in Linux

1. Download the Mysql package from :
mysql>downloads>MySQL Cluster Community Edition
Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive (292mb))

2. Extract the downloaded archive to some folder and remember the path

3.Open terminal and move to extracted path and execute below command

[root@localhost mysql]# rpm -ivh MySQL-*.rpm

Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [ 14%]
   2:MySQL-devel            ########################################### [ 29%]
   3:MySQL-embedded         ########################################### [ 43%]
   4:MySQL-shared-compat    ########################################### [ 57%]
   5:MySQL-shared           ########################################### [ 71%]
   6:MySQL-server           ########################################### [ 86%]
   7:MySQL-test             ########################################### [100%]

completed

4.Now start the mysql service

/etc/init.d/mysqld start    or
/etc/init.d/mysql start

5.(you might got this error)

[root@localhost ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@localhost ~]# /usr/bin/mysqladmin -u root -p password 'somepassword'
Enter password:
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

How to resolve above error
(Execute below commands one by one)

#/etc/init.d/mysql stop

Shutting down MySQL.. SUCCESS!
    #mysqld_safe --skip-grant-tables &;
    #mysql -u root
    mysql> use mysql;
    mysql> update user set password=PASSWORD("newPassword") where User='root';
    mysql> flush privileges;
    mysql> quit
    /etc/init.d/mysqld stop [or     /etc/init.d/mysql stop]
    /etc/init.d/mysqld start [or    /etc/init.d/mysql start]

6. you may also got this error

mysql> use test
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql>

[SOLUTION]

mysql> SET PASSWORD = PASSWORD('new_password');
Query OK, 0 rows affected (0.00 sec)

Postgres Create drop constraint script with the help of function

Create drop script for constraints in postgres sql :

CREATE or REPLACE FUNCTION fn_get_fk(tbl_name varchar, col_name varchar) RETURNS varchar
language PLPGSQL AS $$
DECLARE
   Vtbl varchar(150);
   Vqrys varchar(150);
   Vcol varchar(150);
   Vcon varchar(150);
   Vqryd varchar(300);
   Vqrya varchar(300);
   Vsch varchar(150);
   VqryAdd varchar(30000) = '';
   VqryDrp varchar(30000) = '';
   Vrtn varchar;

   Ctbl_list refcursor;
   Ctblcon information_schema.table_constraints %rowtype;
   Vtblkcu information_schema.key_column_usage;
BEGIN
-- OPEN CURSOR WITH REFERENCING TABLE FOR GIVEN PARENT TABLE
 open Ctbl_list for execute 'SELECT *
    FROM information_schema.table_constraints tc
    right JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_catalog = ccu.constraint_catalog
      AND tc.constraint_schema = ccu.constraint_schema
      AND tc.constraint_name = ccu.constraint_name
      and ccu.table_name in ('||quote_literal(tbl_name)||')
      and ccu.column_name in ('||quote_literal(col_name)||')
    WHERE lower(tc.constraint_type) in (''foreign key'');' ;
 loop fetch next from Ctbl_list into Ctblcon ;
 exit when not found;
    Vtbl = Ctblcon.table_name;
    Vcon = Ctblcon.constraint_name;
    Vsch = Ctblcon.constraint_schema;
--GENERATE DROP CONSTRAINT STATEMENT
    Vqryd = 'alter table '|| Vsch||'.'||Vtbl ||' drop constraint ' || Vcon;
    VqryDrp = VqryDrp ||';'||chr(10)||Vqryd;
    raise info 'Qry : %; ',Vqryd;
--GET REFRENCING COLUMN NAME
    Vqrys = 'select * from information_schema.key_column_usage where constraint_name='||quote_literal(Vcon) ;
  EXECUTE Vqrys into Vtblkcu;
-- GENERATE ADD CONSTRAINT STATEMENT
Vqrya = 'alter table '|| Vsch||'.'||Vtbl ||' add constraint ' || Vcon ||' foreign key('||Vtblkcu.column_name||')
references personal.m_pis_master(str_empno)';
raise notice 'add constraint : %; ',Vqrya;
VqryAdd = VqryAdd || chr(10) || Vqrya || ';';
end loop;
-- CONCATENATE BOTH ADD & DROP STATEMENT
Vrtn ='DROP CONSTRAINT : '|| VqryDrp ||chr(10)||'ADD CONSTRAINT : '||chr(10)||VqryAdd;
RETURN Vrtn;
END;
$$; 

Execute the above function by adding table and its column name on which we need to find the dependency

SELECT * FROM fn_get_fk('language','language_id')

This script will be helpful in finding parent child relationship of tables depending foreign keys or you can modify the function accordingly.......................

sys_connect_by_path in mysql

In this we are going to introduced with mysql hierarchical data. In this post i am going to concat the hierarchical data from a table or we can say sys_connect_by_path in mysql or wm_concat in mysql.


1. create table

CREATE TABLE t_hierarchy (
        id int(10) unsigned NOT NULL AUTO_INCREMENT,
        parent int(10) unsigned NOT NULL,
        PRIMARY KEY (id),
        KEY ix_hierarchy_parent (parent, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Fill Data in table

DELIMITER $$
CREATE PROCEDURE prc_fill_hierarchy (level INT, fill INT)
BEGIN
        DECLARE _level INT;
        DECLARE _fill INT;
        INSERT
        INTO    t_hierarchy (id, parent)
        VALUES  (1, 0);
        SET _fill = 0;
        WHILE _fill < fill DO
                INSERT
                INTO    t_hierarchy (parent)
                VALUES  (1);
                SET _fill = _fill + 1;
        END WHILE;
        SET _fill = 1;
        SET _level = 0;
        WHILE _level < level DO
                INSERT
                INTO    t_hierarchy (parent)
                SELECT  hn.id
                FROM    t_hierarchy ho, t_hierarchy hn
                WHERE   ho.parent = 1
                        AND hn.id > _fill;
                SET _level = _level + 1;
                SET _fill = _fill + POWER(fill, _level);
        END WHILE;
END
$$
DELIMITER ;

START TRANSACTION;
CALL prc_fill_hierarchy(6, 5);
COMMIT;

3. create function

delimiter $$
CREATE FUNCTION hierarchy_sys_connect_by_path(delimiter TEXT, node INT) RETURNS TEXT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
     DECLARE _path TEXT;
 DECLARE _cpath TEXT;
        DECLARE _id INT;
    DECLARE EXIT HANDLER FOR NOT FOUND RETURN _path;
    SET _id = COALESCE(node, @id);
      SET _path = _id;
    LOOP
                SELECT  parent
              INTO    _id
         FROM    t_hierarchy
         WHERE   id = _id
                    AND COALESCE(id <> @start_with, TRUE);
              SET _path = CONCAT(_id, delimiter, _path);
  END LOOP;
END
$$

delimiter ;

4. Hierarchical Query

SELECT  hi.id AS id,
        hierarchy_sys_connect_by_path('/', hi.id) AS tree_position,
        parent
FROM    (
        SELECT  id,
                CAST(@level AS SIGNED) AS level
        FROM    (
                SELECT  @start_with := 1,
                        @id := @start_with,
                        @level := 0
                ) vars, t_hierarchy
        WHERE   @id IS NOT NULL
        ) ho
JOIN    t_hierarchy hi
ON      hi.id = ho.id limit 10

The result will be below format :
43293    | 1/3/12/67/312/1957/9172/43293

moreover we can replace the input variable i.e. connector here we are using '/'.

Source : http://explainextended.com/2009/03/19/hierarchical-queries-in-mysql-adding-ancestry-chains/

Related posts

Procedure Hierarchical /recursive data (parent-child relationship)

For any queries please comment :-)

DML- DATATYPE

Today We are going to study about oracle datatypes and their type where to use , which to use.
I am going to post today difference between varchar2(1 char) and nvarchar2(1).

Series of post

Sort Characters (of datatype char, varchar) in order of month name

Today only one difference, but timely i will update this post, with more diff's of datatypes.
Check the below set of queries

create table t (a varchar2(1));
create table t1 (b varchar2(1 char));

SQL> insert into t values (unistr('\0161'));
insert into t values (unistr('\0161'))
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T"."A" (actual: 2, maximum: 1)
SQL> insert into t1 values (unistr('\0161'));
1 row created.
SQL> select * from t1;
B
-
Ü

If you look at above queries, here i create two tables having datatypes varchar2(1) and
varchar2(1 char). If i insert data in both tables (data is same and byte len is two), table with datatype varchar2(1) is fail to insert and table with datatype varchar2(1 char) has been not failed the reason being byte length of string.

 On other hand nvarchar2(1) and varchar2(1 char) will not fail in above case.
The NVARCHAR2 datatype was for that want to use Unicode values without changing the character set for database (which is used by VARCHAR2). The NVARCHAR2 is a Unicode-only datatype.
Both columns in your example (Unicode VARCHAR2(1 CHAR) and NVARCHAR2(1)) would be able to store the same data, however the byte storage will be different. Some strings may be stored more efficiently in one or the other.

Recover database if it is in between crashed

Create New database using old datafiles.

For this we will use oradim approach which is generally used while manual database creation.

prerequisite:
- New window.
- No previous instance.
- Cold backup or copied datafile ( while database running , it doesn't matter).
- Oracle should be installed with option ( software only ).
- Chose new instance name ( here we are using PROD).
- Directory structure should be same as in pfile.


Start 1,2,3 go :-)

1. Open cmd

set oracle_sid=prod
oradim -new -sid prod -intpwd prod -startmode m -pfile D:\app\pfile.ora

You might got this error
DIM-00014: Cannot open the Windows NT Service Control Manager.
solution open cmd with "run as administrator"

2. Below is the content of my pfile.ora file

db_name='PROD'
memory_target=1G
processes = 150
audit_file_dest='D:\app\username\admin\adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest=D:\app\username\admin\prod\flash_recovery_area
db_recovery_file_dest_size=2G
diagnostic_dest=D:\app\username\admin\prod\diagnostic_dest
dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ("D:\app\username\oradata\prod\CONTROL1.ctl")
compatible ='11.2.0'

3. At this stage we need to create control file after startup database at nomount state
    connect with sysdba user
   (before creation of control file and reuse of datafile , copy all the datafiles and redo log file)

C:\Windows\system32> sqlplus sys/prod as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 9 15:35:48 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile='D:\app\pfile.ora'
ORACLE instance started.

Total System Global Area  644468736 bytes
Fixed Size                  1376520 bytes
Variable Size             192941816 bytes
Database Buffers          444596224 bytes
Redo Buffers                5554176 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG
  2  MAXLOGFILES 16
  3  MAXLOGMEMBERS 3
  4  MAXDATAFILES 100
  5  MAXINSTANCES 8
  6  MAXLOGHISTORY 18688
  7  LOGFILE
  8  GROUP 1 'D:\app\username\oradata\prod\REDO01.LOG' SIZE 50M,
  9  GROUP 2 'D:\app\username\oradata\prod\REDO02.LOG' SIZE 50M,
 10  GROUP 3 'D:\app\username\oradata\prod\REDO03.LOG' SIZE 50M
 11  DATAFILE
 12  'D:\app\username\oradata\prod\SYSTEM01.DBF',
 13  'D:\app\username\oradata\prod\SYSAUX01.DBF',
 14  'D:\app\username\oradata\prod\UNDOTBS01.DBF',
 15  'D:\app\username\oradata\prod\USERS01.DBF',
 16  'D:\app\username\oradata\prod\TRY01.DBF',
 17  'D:\app\username\oradata\prod\LOB_INDEX.DBF'
 18  CHARACTER SET AL32UTF8
 19  ;

Control file created.

after creation of control file the system will automatically move to MOUNT State.

 (media recovery needed) 

SQL>  RECOVER DATABASE;
Media recovery complete.

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> alter database open;

Database altered.

SQL> conn scott/tiger
Connected.

Thats it.. 

Now we need to create spfile from pfile, so that database can startup without any problem after shutdown

 create spfile from pfile='D:\app\pfile.ora';

You may also like this post
Oracle de install / clear all previous directory

Oracle Deinstallation

 While DE-installation some times the folders at installation path not get removed, so we need to do some manual work to remove those directories. After follow below instruction you can able to remove all oracle installation folders and files.

• Uninstall all Oracle components using the Oracle Universal Installer (OUI).
• Delete the HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE key which contains registry entries for all Oracle products by using regedit.
• Delete Oracle services at registry location: HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/. (Oracle services are starts with “Ora”).
• Reboot the machine.
• Delete the ORACLE installation folder
• Empty the recycle bin.

Oracle 10g installtion in red hat linux (11g)

10g/ 11g



1.Installtion of Oracle10g :-login as root user and make these changes:-

   open the terminal by right click on desktop

   and open the sysctl.conf file and define all these parameter in this.

   # gedit /etc/sysctl.conf (press enter) and paste these parameter in

     this file and save it.

kernel.sem=250 32000 250 250
net.ipv4.ip_local_port_range=1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144

2. execute below command from root user

# useradd oracle
# passwd oracle
# groupadd oinstall
# groupadd dba
# usermod -g oinstall -G dba oracle
# usermod -g oinstall -G apache apache
# chown -R oracle:oinstall /u01
# chown -R oracle:oinstall /u02
# chmod -R 777 /u01
# chmod -R 777 /u02

 

3.Apply all rpm as listed at below link


    http://docs.oracle.com/cd/E11882_01/install.112/e24321/pre_install.htm#LADBI1112 (11g)

    http://docs.oracle.com/cd/B19306_01/install.102/b14203/prelinux.htm (chapter 2.9.1 ) 10g

   using command on terminal:-

# rmp -ivh (path of rpm)
   or

   (you can use yum repository to install them)

 

4. For 11g user, change the entry of redhat release from this file:-

   # gedit /etc/redhat-release (press enter)

     replace 5 with 4 and save it.



5.logout from root user and login as oracle users and create bash_profile:-(if not exists)


$ [oracle@localhost ~]$vi .bash_profile

 
 
  
$ gedit .bash_profile(and paste the parameter and save it)

export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_TERM=xterm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl
export editor=gedit
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/rdbms/jlib


6. open the terminal and make directory:-

$ mkdir -p /u01/app/oracle/product/10.2.0



7. copy the 10g dump on desktop before starting the setup

   logout from oracle user and login again to reflect the

    changes of bash_profile.



8. Open the terminal run the setup like this

$ cd /home/oracle/Desktop/10g-dump(press enter)
$ [oracle@localhost 10g-dump]$./runInstaller (press enter)



9. After completig the installtion open terminal and

    login as root($ su - root) user and run

    both script that showing in the end the name is:-

# /home/oracle/orainventory/orainstRoot.sh
# /u01/app/oracle/product/10.2.0/db_1/root.sh



10. Open the terminal and login in oracle

$ sqlplus



you may also like:---

ORA-01102 cannot mount database in EXCLUSIVE mode

Oracle startup error : ORA-01078: failure in processing system parameters LRM-00109

ORA-00845: MEMORY_TARGET not supported on this system

 

ORA-01102 cannot mount database in EXCLUSIVE mode

ORA-01102 cannot mount database in EXCLUSIVE mode

    Cause: Some other instance has the database mounted exclusive or shared.
    Action: Shut down the other instance or mount in a compatible mode.

shu immediate and try again to startup the database server

In my case there is problem in .bash_profile
where ORACLE_SID is not set.

"login with ORACLE user"
vim .bash_profile

"press i and add"
ORACLE_SID=orcl

restart/logout the machine to reflect the change to oracle user

 sqlplus sys/orcl as sysdba

SQL startup pfile='/u01/app/oracle/product/11.2.0/dbhome/dbs/*.ora'
ORACLE instance started.

Database mounted.
Database opened.

Oracle startup error : ORA-01078: failure in processing system parameters LRM-00109

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/opt/oracle/product/11.2.0/dbhome_1/dbs/inittest01.ora'

This error is because of unavailability of file or current user does not have access to spfile.ora at path $ORACLE_HOME/dbs/*.ora

[root@localhost ~]# cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[root@localhost dbs]# ls -l
-rwxrwxrwx. 1 oracle oinstall 2851 May 15  2009 init.ora
-rwxrwxrwx. 1 oracle oinstall   24 Oct 31 23:06 lkORCL
-rw-r-----. 1 oracle oinstall 1536 Oct 31 23:10 orapworcl
-rwxrwxrwx. 1 oracle oinstall  911 Nov  1 00:48 spfileorcl.ora
[root@localhost dbs]# 

make sure *.ora file must have permission to oracle user  

if file not have permissions , then use this commands to grant permissions 

[root@localhost dbs]# chown -R oracle:oinstall *
[root@localhost dbs]# chmod -R 777 *

Sometimes you got the error
ORA-01078: failure in processing system parameters
LRM-00123: invalid character found in the input file


When we try to startup the database using pfile , sometime above error can be shown, because of your pfile is containing invalid chars.

To overcome this error edit your ora file using "vim" or "gedit" editor and remove all invalid characters. Then then startup your database.

Error handling in PLSQL – SQLERRM and SQLCODE

Error Handling / Exception Handling in Plsql

While named or anonymous plsql block execution, will fail because of some error in between of execution because of dml error. We can capture the error and can also handle them by adding "EXCEPTION" block inside the plsql block, which leads to the successful execution of plsql block.

declare
    v_var varchar2(1000);
    TABLE_MISSING EXCEPTION;
    PRAGMA EXCEPTION_INIT(TABLE_MISSING,-942);
begin
    execute immediate ' select count(*) from v$datafile' into v_var;
    dbms_output.put_line(v_var);
    EXCEPTION
        WHEN TABLE_MISSING THEN
        NULL;
end;
/

We can also print the error or can store them into the variable, and can use them for further investigation. For print the error we need to use SQLCODE and SQLERRM funtion

declare
    v_var varchar2(1000);
    TABLE_MISSING EXCEPTION;
    PRAGMA EXCEPTION_INIT(TABLE_MISSING,-942);
begin
    execute immediate ' select count(NAME) from v$datafile' into v_var;
    dbms_output.put_line(v_var);
    EXCEPTION
        WHEN TABLE_MISSING THEN
        DBMS_OUTPUT.PUT_LINE('Exception: SQLCODE=' || SQLCODE || SQLERRM );
        raise;
end;
/

alter table move command

"alter table move" command


This command is generally used to move the segments of table from one tablespace to other tablespace, so
 how to use this command:

ALTER TABLE TABLE_NAME MOVE TABLESPCE tablespace_name;

after this command indexes are become invalid, in that way we need to rebuild the indexes and at same we can change the segment of indexes
how to:

(MOVE INDEX TO TABLESPACE (NOT DOMAIN INDEX / FULL TEXT INDEXES,.,,, IOT- TOP  AND LOB ARE THESE ARE THE CUISINES OF DOMAIN INDEX))

ALTER INDEX INDEX_NAME REBUID TABLESPACE TRY;

then need to MOVE LOB SEGMENT TO NEW TABLESPACE
the table_name , and column_name information can get from the user_lobs data dictionary table:

alter table table_name move lob(column_name) store as segment_name_unique ( tablespace try);

Domain index lob segment can moved by rebuild with replace parameters 

ALTER INDEX DOMAIN_INDEX_NAME REBUILD PARAMETERS('REPLACE LEXER HYPHEN_LEXER STORAGE MYSTORE');

you may also like 

for last command follow this post::

Full Text Index / Domain Index ( create datastore, assign tablespace for storage)

Space Tuning / Reclaim space from tablespace / Freeup unused space

First of all identified most wasted space tables after this we will move the table into another tablespace. For this create a new tablespace or we can move table in a preexisting tablespace with the help of  "alter table move" command.

alter table table_name move tablespace tablespace_name;

after executing this command now we need to rebuild index (with or without tablespace clause)

alter index index_name rebuild tablespace tablespace_name;

*note table should not contain Full text index or domain index
If it contains then the scenerio is :

1. Collect create index script only for domain indexes 
2. Save it at some safe place
3. Drop domain index
4. Move table with "alter table move" command as above.
5. Rebuild indexes as above
6. And Rebuild domain indexes

If you forgot to drop domain index , No problem u might got some error as below:
ORA-02327: cannot create index on expression with datatype LOB
ORA-30967: operation directly on the Path Table is disallowed
 

SQL> alter table DR$BLB_01$I move tablespace try;
alter table DR$BLB_01$I move tablespace try
            *
ERROR at line 1:
ORA-30967: operation directly on the Path Table is disallowed


SQL> alter table DR$BLB_01$K move tablespace try;

Table altered.

SQL> alter table DR$BLB_01$N move tablespace try;

Table altered.

SQL> alter table DR$BLB_01$R move tablespace try;
alter table DR$BLB_01$R move tablespace try
            *
ERROR at line 1:
ORA-30967: operation directly on the Path Table is disallowed


SQL> alter table EMP move tablespace try;

Table altered.

and indexes
SQL> alter index SYS_IL0000074265C00002$$ rebuild tablespace try;
alter index SYS_IL0000074265C00002$$ rebuild tablespace try
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

solution to this

alter table table_name move lob(column_name_having_blob_clob_datatype) store as a tablespace tablespace_name;


Read also::
short and brief description on "alter table move"

Finding USED UNUSED INDEX IN ORACLE

Finding USED UNUSED INDEX IN ORACLE

In Oracle by default index tracking / monitoring is off. In order to find unused used index we need to set "INDEX MONITORING" on.

command:
alter index index_name monitoring usage;

data will save in table "v$object_usage"

select * from v$object_usage;

This data dictionary table is independently store the schema data. To access this data dictionary table a schema/user does not need any extra grant or privileges.

Nanoseconds / Millisecond in Mysql

MySQL 5.6.4 milliseconds (fractional seconds) are supported by the newly introduced type TIMESTAMP(fsp), where fsp stands for fractional seconds precision. fsp ranges from 0 to 6 with 0 indicating there's no fractional part.

Mycurrent mysql version is 5.5.0024
after upgrade it to 5.6.0013, now i able save millisecond in database.

create table tymdate( column1 datetime(6));
insert into tymdate values ('2013-10-19 11:12:59.045673');

note*- Application users / Developers Please don't forget to update mysql j connector.

RowID Equivalent in SQLSERVER

Physical location of a row in SQL Server

Introduction

In Oracle, each row can be identified by the ROWID column. It is a pseudo column. This column contains the information about the address of row saved in datafile.

ROWNUM is quite different thing, if we compare it to ROWID, rownum to particular row will not remain fix, it gets change over any dml operation done on a table.

ROWID equivalent in sqlserver, we have  

SQLSERVER 2008 - %%physloc%%
SQLSERVER 2005 - %%lockres%%


QUERY USING PHYSLOC

select %%physloc%% from table_name;

To decode the value of above column, we can use below function
sys.fn_PhysLocFormatter



select %%physloc%% , sys.fn_physlocformatter(%%physloc%%) from table_name;

 

Result will be as below:

0xA901000001000000  (1:425:0)

How to read above format ?
row with ID = ???? is located in the file 1 on page 425 and in slot 0.

using this we can identify the actual data file of the row, using the view sys.database_files.

Some Queries

select * from table_name where %%physloc%% = 0xA901000001000000

this will return row

SQL Server Delete Cascade

src - http://randomconsultant.blogspot.in/2008/10/sql-server-cascade-delete.html

Delete cascade , recursive delete sqlserver 

CREATE Procedure spDeleteRows
/* 
Recursive row delete procedure. 

It deletes all rows in the table specified that conform to the criteria selected, 
while also deleting any child/grandchild records and so on.  This is designed to do the 
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys 
table to find any child tables, then deletes the soon-to-be orphan records from them using 
recursive calls to this procedure. Once all child records are gone, the rows are deleted 
from the selected table.   It is designed at this time to be run at the command line. It could 
also be used in code, but the printed output will not be available.
*/
 (
 @cTableName varchar(50), /* name of the table where rows are to be deleted */
 @cCriteria nvarchar(1000), /* criteria used to delete the rows required */
 @iRowsAffected int OUTPUT /* number of records affected by the delete */
 )
As
set nocount on
declare  @cTab varchar(255), /* name of the child table */
 @cCol varchar(255), /* name of the linking field on the child table */
 @cRefTab varchar(255), /* name of the parent table */
 @cRefCol varchar(255), /* name of the linking field in the parent table */
 @cFKName varchar(255), /* name of the foreign key */
 @cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
 @cChildCriteria nvarchar(1000), /* criteria to be used to delete 
                                           records from the child table */
 @iChildRows int /* number of rows deleted from the child table */

/* declare the cursor containing the foreign key constraint information */
DECLARE cFKey CURSOR LOCAL FOR 
SELECT SO1.name AS Tab, 
       SC1.name AS Col, 
       SO2.name AS RefTab, 
       SC2.name AS RefCol, 
       FO.name AS FKName
FROM dbo.sysforeignkeys FK  
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id 
                              AND FK.fkey = SC1.colid 
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id 
                              AND FK.rkey = SC2.colid 
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id 
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id 
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName

OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
     BEGIN
 /* build the criteria to delete rows from the child table. As it uses the 
           criteria passed to this procedure, it gets progressively larger with 
           recursive calls */
 SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' + 
                              @cRefTab +'] WHERE ' + @cCriteria + ')'
 print 'Deleting records from table ' + @cTab
 /* call this procedure to delete the child rows */
 EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
 FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
     END
Close cFKey
DeAllocate cFKey
/* finally delete the rows from this table and display the rows affected  */
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName

In the above scenario, we were trying to perform the following :

    DELETE FROM X WHERE field1 = '234'

Using this procedure, we would use the following command:

    exec spDeleteRows 'X', 'field1 = ''234''', 0