Geeks - Tip of the Day

krishnaindia2007 · #**141** (**permalink**) 09-09-2008

Query to find locked objects

select oracle_username, os_user_name,locked_mode,object_name,object_type
from v$locked_object a, dba_objects b
where a.object_id=b.object_id

krishnaindia2007 · #**142** (**permalink**) 09-18-2008

How to enable archive log mode?

To enable archive log mode

1. Shut down the database

2. Take full offline backup

3.create pfile from spfile if you are spfile , Using the following command sql>create pfile='c:\pfile.ora' from spfile

4. Then edit pfile or init.ora file and specify the archive log destination
log_archive_start='true'
log_archive_dest='f:\archive'
log_archive_max_processes=4

To specify multiple destinations
log_archive_dest_1=’f:\archive’
log_archive_dest_2=’e:\archive’

5.creat spfile from pfile='c:\pfile.ora'

6.create archive destination folders

7.startup mount

8.alter database archivelog;

9.archive log list;

10.alter database open;

11.sho parameter log;

12. Alter database backup controlfile to trace; - this is for safety purpose

13. Sql>shutdown immediate;

14.sql>startup.

It is recommended that you take a full backup after you brought the database in archive log mode.

krishnaindia2007 · #**143** (**permalink**) 09-19-2008

To Disable Archive Log Mode

1. Shut Down the database

2. Comment Archive log destinations in parameter file
# LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
# LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc2”

3. Startup Mount

4. ALTER DATABASE NOARCHIVELOG;

5. Shutdown the database and take full offline backup.

krishnaindia2007 · #**144** (**permalink**) 09-21-2008

How to export data based on a query?

EXP file=f1.dmp log=f1.log tables=y query=\"WHERE DEPTNO=10\"

susarlasireesha · #**145** (**permalink**) 10-01-2008

Creation of linux user and Creation of Oracle directory

1. Connect as root user

For adding user to linux ,assigning dba group to username
2 useradd -d /home/username -m -g dba -s /bin/sh username

3 passwd username

enter password for username
reenter password for username

4 connect as username in linux

5 cd $HOME

For read,write privileges to username

6 chmod 777 username

7 Add env variable to .bash_profile

8 Run .bash_profile

9 check connection of sqlplus

For Creation of oracle directory

1. connect as username

2 mkdir LOGFILES

3 chmod 755 LOGFILES

4 chmod g+w LOGFILES

5 for creating directory

sqlplus > conn system/xxxxxxxx

create or replace directory LOGDIR as '/home/username/LOGFILES'
grant read,write on directory LOGDIR to scott

sqlplus > conn scott/tiger

test the simple script

DECLARE
output_file utl_file.file_type;
begin
output_file := utl_file.fopen ('LOGDIR','x.txt', 'w');
utl_file.put_line (output_file, 'XXXXXXXXXXXX');
utl_file.fclose(output_file);
Exception
when others then null;
end;

6 Test the x.txt file in LOGFILES.

krishnaindia2007 · #**146** (**permalink**) 10-02-2008

sequence related queries

last number selected from sequence

Code:

 sql> select sequence_name, last_number 
             2 from user_sequences 
             3 where sequence_name='smp_vdu_principals_sequence'; 
sequence_name                    last_number
 ------------------------------ ----------- 
smp_vdu_principals_sequence  21

next number from sequence

Code:

 
sql> select sequence_name, (last_number + increment_by) next_value 
    2 from user_sequences 
    3 where sequence_name ='smp_vdp_region_id_seq'; 

sequence_name           next_value 
------------------------------ ---------- 
smp_vdp_region_id_seq    22

krishnaindia2007 · #**147** (**permalink**) 10-03-2008

Database Shutdown -I

You must be connected as SYSDBA or SYSOPER to shutdown a database.

Shutdown Modes

I.Shutdown Normal
1.No New Connections are allowed
2.The server waits for all users to disconnect before completing the shutdown.
3.Database and redo buffers are written to disk.
4.The SGA memory allocation is released and background processes are terminated.
5.The database is closed and dismounted.

II.Shutdown Transactional
1.No new connections are allowed
2.No connected client can start a new transaction
3.Clients are disconnected as soon as the current transaction ends.
4.Showdown proceeds when all the transactions are finished.

krishnaindia2007 · #**148** (**permalink**) 10-03-2008

Database Shutdown -II

III.Shutdown Immediate
1.No New connections are allowed.
2.Connected clients are disconnected and Sql Statements in process are not completed.
3.Oracle rollback active transactions.
4.Oracle closes and dismounts the database

IV.Shutdown abort
1.Current Sql statements are immediately terminated.
2.Users are disconnected.
3.Database and redo buffers are not written to disk.
4.Uncommitted transactions are not rolled back.
5.The instance is terminated without closing files.
6.The database is not closed and dismounted.
7.Database recovery by SMON must occur on the next startup.

krishnaindia2007 · #**149** (**permalink**) 10-03-2008

Starting Up a Database

Database can be started up in various stages.

I. Nomount
This stage is used to
1.Create a database
2.To recreate a control file.

It includes the following tasks
1.Read spfile.ora or init.ora file.
2.Allocate SGA
3.Startup the background processes.
4.Open a log file named ALERT_SID.LOG.

II.Mount
This stage is used to
1.Rename database files
2.Enable / Disable redo log archiving options
3.To perform full database recovery.

It includes the following tasks
1.Database is mounted but not open.
2.It is assosiated with instance.
3.Locates and opens the control file specified in parameter file
4.Reads control file to obtain the names and status of datafiles and redo log files.
5.But it does not check to verify the existence of datafiles and redo log files.

III.Open
1.Any valid user can connect to database
2.It opens datafiles and redo log files.

krishnaindia2007 · #**150** (**permalink**) 10-03-2008

Startup Command options

I.STARTUP FORCE
1.It aborts the current instance
2.Starts a new normal instance

II.STARTUP RESTRICT
1.It restrict connection to users with the RESTRICTED SESSION privilege.
2.Used to perform database maintenance activities.

III.STARTUP RECOVER
It begins media recovery when starts

krishnaindia2007 · #**151** (**permalink**) 10-04-2008

Dynamic performanc views - I

Dynamic performance views are identified by the prefix v_$.
Public synonyms for these views have the prefix v$.
The v$ views provide a continually updated look at internal statistics

Instance related views
I.v$sga
It contains summary information on system global area.

Code:

 
sql> select * 
    2 from v$sga; 
Name                      Value 
-------------------- ---------- 
fixed size                 454584 
variable size             218103808 
database buffers       25165824 
redo buffers              667648

It is same as show sga in sql *plus with the exeption that it also show the total.

Code:

 
sql> show sga; 
total system global area 244391864 bytes 
fixed size                     454584 bytes 
variable size                 218103808 bytes 
database buffers           25165824 bytes 
redo buffers                 667648 bytes

krishnaindia2007 · #**152** (**permalink**) 10-04-2008

Dynamic performanc views - II

II.v$sgastat
It constains detailed information on the system global area or contains information on sga segment sizes

Code:

 
sql> select * 
    2 from v$sgastat; 
pool          name             bytes 
----------- -------------------------- ---------- 
                 fixed_sga                          454584 
                 buffer_cache                     25165824 
                 log_buffer                         656384 
shared pool errors                                9612 
shared pool enqueue                            171860 
shared pool kgk heap                            3756 
shared pool kqr m po                            304168 
shared pool kqr s po                             62212 
.................... 
Shared pool message pool freequeue       665792 
shared pool ksxr pending messages que   841036 
shared pool event statistics per sess     1913520 
43 rows selected.

Query to know freespace in sga

Code:

 
sql> select * from v$sgastat w
here name = 'free memory'; 
pool            name                      bytes 
----------- -------------------------- ---------- 
shared pool free memory              151228400 
large pool   free memory              8388608 
java pool    free memory              33554432

krishnaindia2007 · #**153** (**permalink**) 10-04-2008

Dynamic performanc views - III

V$instance :- It displays information of the current instance.

Code:

SQL>  SELECT   instance_name,
  2                  version,
  3                  status,
  4                  instance_role
  5      FROM     v$instance;

INSTANCE_NAME    VERSION           STATUS       INSTANCE_ROLE
---------------------------------------------------------------
orcl            9.2.0.6.0          OPEN        PRIMARY_INSTANCE

Instance_role can be used to determine if an instance is an active instance (primary instance) or a secondary instance (in a standby environment).

krishnaindia2007 · #**154** (**permalink**) 10-04-2008

Dynamic performanc views - IV

V$BGPROCESS :- This process describes the background processes.

V$BH:- This is a parallel server view. This view gives the status and number of pings for every buffer in the SGA.

V$BUFFER_POOL :- This view displays information about all buffer pools available for the instance.

krishnaindia2007 · #**155** (**permalink**) 10-04-2008

Control File Related Views

V$CONTROL FILE :- This view lists the names of the control files.

Code:

SQL> SELECT name 
  2  FROM   v$controlfile;

NAME
-------------------------------------------
E:\ORA9IDB\ORADATA\ORCL\CONTROL01.CTL
E:\ORA9IDB\ORADATA\ORCL\CONTROL02.CTL
E:\ORA9IDB\ORADATA\ORCL\CONTROL03.CTL

V$CONTROLFILE_RECORD_SECTION :- This view displays information about the control file record sections.

krishnaindia2007 · #**156** (**permalink**) 10-04-2008

Choosing Between Having and Where

Where puts condition on the table rows.
Having puts condition on grouped results.

The processing order is as follows.
1. Select rows with WHERE
2. Divide rows into sets with GROUP BY
3. Calculate accurate values for each group.
4. Eliminate unwanted group result rows with having.

Any rows you can remove with where rather than having make your query more efficient.

Ex:- Wrong Statement

Code:

SQL> SELECT job, count(*)
  2  FROM   emp
  3  GROUP BY job
  4  HAVING job<>'SALESMAN';

JOB        COUNT(*)
--------- ---------
ANALYST           2
CLERK             3
MANAGER           4
PRESIDENT         1

Better Statement

Code:

SQL> SELECT job, count(*) 
  2  FROM   emp
  3  WHERE  job<>'SALESMAN'
  4  GROUP BY job;

JOB        COUNT(*)
--------- ---------
ANALYST           2
CLERK             3
MANAGER           4
PRESIDENT         1

krishnaindia2007 · #**157** (**permalink**) 10-04-2008

Indexes and Like operator

you can take advantage of an index with like as long as you provide the first character in the pattern.

Code:

 
sql> select ename, job, sal 
   2 from emp 
   3 where ename like 'M%'; 
ename job sal 
---------- --------- --------- 
martin salesman 1663.75 
miller clerk 1730.3 

execution plan 
---------------------------------------------------------- 
0 select statement optimizer=choose 
1 0 table access (by index rowid) of 'emp' 
2 1  index (range scan) of 'ename_idx' (non-unique)

Optimizer does not use index if you start pattern with wildcard.

Code:

 
sql> select ename, job, sal 
    2 from emp 
    3 where ename like '%M%'; 

ename job sal 
---------- --------- --------- 
martin salesman 1663.75 
adams clerk 1464.1 
james clerk 1264.45 
miller clerk 1730.3 

execution plan 
--------------------------------------------- 
0 select statement optimizer=choose 
1 0 table access (full) of 'emp'

krishnaindia2007 · #**158** (**permalink**) 10-04-2008

Indexes and Comparing columns in the same table

Comparing the columns in the same table makes an index useless.

Code:

 
sql> select empno, ename, job, mgr 
   2 from emp 
   3 where empno = mgr; 

empno ename job mgr 
--------- ---------- --------- --------- 
7944 krishna manager 7944 

execution plan 
----------------------------------------------- 
0 select statement optimizer=choose 
1 0 table access (full) of 'emp'

It will consider index when empno column compared to a string constant.

Code:

 
sql> select empno, ename, job, mgr 
   2 from emp 
   3 where empno = 7944; 

empno ename job mgr 
--------- ---------- --------- --------- 
7944 krishna manager 7944 

execution plan 
--------------------------------------------------------- 
0 select statement optimizer=choose 
1 0 table access (by index rowid) of 'emp' 
2 1 index (unique scan) of 'empno_pk' (unique)

krishnaindia2007 · #**159** (**permalink**) 10-04-2008

Indexes and between operator

Between operator will consider indexes if there index on a column.
Between is treated as a pair of comparison operators.

Code:

 
sql> select * 
   2 from emp 
   3 where empno between 7900 and 7999; 

empno ename job mgr hiredate sal comm deptno 
--------- ---------- --------- --------- --------- 
7900 james clerk 7698 03-dec-81 1264.45 30 
7902 ford analyst 7566 03-dec-81 3993 20 
7934 miller clerk 7782 23-jan-82 1730.3 10 

execution plan 
---------------------------------------------------------- 
0 select statement optimizer=choose 
1 0 table access (by index rowid) of 'emp' 
2 1 index (range scan) of 'empno_pk' (unique)

krishnaindia2007 · #**160** (**permalink**) 10-05-2008

SPFILE and PFILE

SPFILE stands for server parameter file.
Initialisation parameter file init.ora is also called as pfile.

These files hold setup parameters that defines attirbutes of instance.

PFFILE is a text based file where are SPFILE is a binary file. We can't modify SPFILE using editors.

The only way of modifying paramters in spfile is using ALTER SYSTE SET/RESET command.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Hi Geeks! Another Freak here to join you :)	itcoolgirl	Get Together	1	12-12-2007 03:17 AM
Hey Geeks!!!	maverick_dude	Get Together	1	12-12-2007 03:12 AM
Geeks talk has 5000+ threads.	debasisdas	Site News & Announcements	0	07-31-2007 10:37 AM
GeekInterview Pager - communicate better with Geeks community	admin	Site News & Announcements	1	03-01-2007 06:32 AM
Welcome to Geeks Talk	admin	Site News & Announcements	0	05-13-2006 02:10 PM

Geeks - Tip of the Day

Geeks - Tip of the Day

Similar Threads