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
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
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.
Last edited by krishnaindia2007; 09-18-2008 at 10:36 PM.
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.
How to export data based on a query?
EXP file=f1.dmp log=f1.log tables=y query=\"WHERE DEPTNO=10\"
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.
Last edited by susarlasireesha; 10-01-2008 at 02:42 AM.
Sireesha
sequence related queries
last number selected from sequence
next number from sequenceCode: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
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
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.
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.
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.
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
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.
It is same as show sga in sql *plus with the exeption that it also show the total.Code:sql> select * 2 from v$sga; Name Value -------------------- ---------- fixed size 454584 variable size 218103808 database buffers 25165824 redo buffers 667648
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
Last edited by krishnaindia2007; 10-03-2008 at 11:48 PM.
Dynamic performanc views - II
II.v$sgastat
It constains detailed information on the system global area or contains information on sga segment sizes
Query to know freespace in sgaCode: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.
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
Last edited by krishnaindia2007; 10-03-2008 at 11:49 PM.
Dynamic performanc views - III
V$instance :- It displays information of the current 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).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
Last edited by krishnaindia2007; 10-03-2008 at 11:53 PM.
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.
Control File Related Views
V$CONTROL FILE :- This view lists the names of the control files.
V$CONTROLFILE_RECORD_SECTION :- This view displays information about the control file record sections.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
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
Better StatementCode: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
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
Indexes and Like operator
you can take advantage of an index with like as long as you provide the first character in the pattern.
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 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)
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'
Last edited by krishnaindia2007; 10-04-2008 at 04:45 AM.
Indexes and Comparing columns in the same table
Comparing the columns in the same table makes an index useless.
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 = mgr; empno ename job mgr --------- ---------- --------- --------- 7944 krishna manager 7944 execution plan ----------------------------------------------- 0 select statement optimizer=choose 1 0 table access (full) of 'emp'
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)
Last edited by krishnaindia2007; 10-04-2008 at 04:56 AM.
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)
Last edited by krishnaindia2007; 10-04-2008 at 05:05 AM.
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.