Ask Ershad : May I Help Your Administration(Oracle+MSSQL Server+MySQL) ?

DIFF utility: Find Differences Between Files

2010-04-19T21:49:00.004+06:00

Working as a DBA , you may encounter situations like to compare two files to check whether the contents are same or not.
diff will surely make your life easy.Believe IT or ....

$ diff init.ora init1.ora

Check the output showing the differences in the files:
16,17c16,17
< sga_max_size=900M
< sga_target=700M
---
> sga_max_size=800M
> sga_target=600M
19a20
> # sort_area_size=0

here, < refer to file1 & > refer to file 2.
And , c stands for change
a stands for append (looks like file 2 has one more line & that is 20 which shows contents "# sort_area_size=0"

There is another tag like d (beside c & a) which stands for delete.

$ diff init.ora init1.ora > change.diff
To convert init.ora to init1.ora, use the "patch" command with the difference report output:
$ patch init.ora change.diff

We can even use "sdiff" utility too.
Ex: $ sdiff init.ora init1.ora

This will show the changes in more readable format.

Note: can also use "diff3" utility to compare differences between three files.

Find Differences Between Directories:

Possible guys!!!!

$ diff /oracle/scripts /u01/oracle/scripts

thanks... have a nice day.

Reference to vi editor

2010-04-19T21:20:00.010+06:00

hi,
Can check a somewhat huge reference for vi editor. Very much helps to DBA guys.

$ vi filename

Some Helpful vi Command-Line Startup Options:
Option ==> Action
vi ==> Starts editing session in memory.
vi ==> Starts session and opens the specified file.
vi * ==> Opens first file that matches the wildcard pattern. Use :n to navigate to the next matched file.
view ==> Opens file in read-only mode.
vi -R ==> Opens file in read-only mode.
vi -r ==> Recovers file and recent edits after abnormal abort from editing session (like a system crash).
vi +n ==> Opens file at specified line number n.
vi + ==> Opens file at the last line.
vi +/ ==> Opens file at first occurrence of specified string pattern.

Common Techniques to Enter vi Insert Mode:
Enter Insert Command ==> Action
i ==> Insert text in front of the cursor.
a ==> Insert text after the cursor.
I ==> Insert text at the beginning of the line.
A ==> Insert text at the end of the line.
o ==> Insert text below the current line.
O ==> Insert text above the current line.

Useful vi Exit Commands
Exit Command ==> Action
:wq ==> Save and exit.
ZZ ==> Save and exit.
:x ==> Save and exit.
:w ==> Save the current edits without exiting.
:w! ==> Override file protections and save.
:q ==> Exit the file.
:q! ==> Exit without saving.
:n ==> Edit next file.
:e! ==> Return to previously saved version.

Common Navigation Commands

Command ==> Action
j (or down arrow) ==> Move down a line.
k (or up arrow) ==> Move up a line.
h (or left arrow) ==> Move one character left.
l (or right arrow) ==> Move one character right.
Ctrl+f (or Page Down) ==> Scroll down one screen.
Ctrl+b (or Page Up) ==> Scroll up one screen.
1G ==> Go to first line in file.
G ==> Go to last line in file.
nG ==> Go to n line number.
H ==> Go to top of screen.
L ==> Go to bottom of screen.
w ==> Move one word forward.
b ==> Move one word backward.
0 ==> Go to start of line.
$ ==> Go to end of line.

Common Options for Copying, Deleting, and Pasting Text
Option ==> Action
yy ==> Yank (copy) the current line.
nyy ==> Yank (copy) n number of lines.
p ==> Put yanked line(s) below the cursor.
P ==> Put yanked line(s) above the cursor.
x ==> Delete the character that the cursor is on.
X ==> Delete the character to the left of the cursor.
dw ==> Delete the word the cursor is currently on.
dd ==> Delete current line of text.
ndd ==> Delete n lines of text
D ==> Delete to the end of the current line.

Common Options for Changing Text
Option ==> Action
r ==> Replace the character that the curser is on with the next character you type.
~ ==> Change the case of a character.
cc ==> Delete the current line and insert text.
C ==> Delete to the end of the line and insert text.
c$ ==> Delete to the end of the line and insert text.
cw ==> Delete to the end of the word and insert text.
R ==> Type over the characters in the current line.
s ==> Delete the current character and insert text.
S ==> Delete the current line and insert text.

Common Options for Text Searching

Option ==> Action
/ ==> Search forward for a string.
? ==> Search backward for a string.
n ==> Repeat the search forward.
N ==> Repeat the search backward.
f ==> Search forward for a character in the current line.
F ==> Search backward for a character in the current line.

:set number ==> Displaying Line Numbers
u ==> Undoing a Command

Thanks to GUYS behind "Linux Recipes for Oracle DBAs" for this summary.

Select the LAST N rows from a table in Oracle

2010-04-18T23:58:00.000+06:00

From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the LAST N or BOTTOM N rows. Examples:

Get the bottom 10 employees based on their salary

SELECT ename, sal
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal) sal_rank
FROM emp )
WHERE sal_rank <= 10;

Select the employees getting the lowest 10 salaries

SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
FROM emp )
WHERE sal_dense_rank <= 10;

For Oracle 8i and above, one can get the bottom N rows using an inner-query with an ORDER BY clause:

SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1)
WHERE ROWNUM < 10;

Use this workaround for older (8.0 and prior) releases:

SELECT *
FROM my_table a
WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol <= a.maxcol)
ORDER BY maxcol;

Thanks.

Select the TOP N rows from a table in Oracle

2010-04-18T23:46:00.004+06:00

Below is the examples to find the top 5 employees based on their salary.

Option 1: Using RANK()

SELECT employee_name, salary
FROM ( SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) salary_rank
FROM employee )
WHERE salary_rank <= 5;

Option 2: Using Dense_Rank()

SELECT employee_name, salary
FROM ( SELECT employee_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_dense_rank
FROM employee )
WHERE salary_dense_rank <= 5;

Option 3: Using inner query
This is an example of using an inner-query with an ORDER BY clause:

SELECT *
FROM (SELECT * FROM employee ORDER BY salary DESC)
WHERE ROWNUM < 5;

Option 4: Using count distinct combination

SELECT *
FROM employee e
WHERE 5 >= (SELECT COUNT(DISTINCT salary)
FROM employee b
WHERE b.salary >= e.salary)
ORDER BY salary DESC;

Thanks.

Controlling the Autotrace Report in sql*plus

2010-04-18T23:44:00.001+06:00

In sql*plus with AUTOTRACE command you can see the execution plan of successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements which is useful for monitoring and tuning the performance of these statements.

You can control the report by setting the AUTOTRACE system variable.

Following is the available AUTOTRACE settings.

1)SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default. Result of a query will be displayed on the console as usual.

2)SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows the optimizer execution path along with query output and note but does not show statistics.

3)SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows the SQL statement execution statistics along with query output but does not display execution plan.

4)SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics along with query output.

5)SET AUTOTRACE TRACEONLY: Report displays both optimizer execution path as well as execution statistics but does not display query output. If STATISTICS is enabled, query data is still fetched, but not printed.

Thanks.

All About V$ views

2010-04-18T23:33:00.002+06:00

This can help a lot to an Oracle DBA to get info about different views:

Advisors: Information related to cache advisors

V$PGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE_HISTOGRAM
V$MTTR_TARGET_ADVICE
V$PX_BUFFER_ADVICE
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$JAVA_POOL_ADVICE
V$STREAMS_POOL_ADVICE (10.2)
V$SGA_TARGET_ADVICE (10.2)
V$ADVISOR_PROGRESS (10.2)

ASM

V$ASM_ALIAS (10.1)
V$ASM_CLIENT(10.1)
V$ASM_DISK(10.1)
V$ASM_DISK_STAT(10.2)
V$ASM_DISKGROUP(10.1)
V$ASM_DISKGROUP_STAT(10.2)
V$ASM_FILE(10.1)
V$ASM_OPERATION(10.1)
V$ASM_TEMPLATE(10.1)

Backup/recovery Information related to database backups and recovery including last backup,archive logs,state of files for backup,and recovery

V$ARCHIVE
V$ARCHIVED_LOG
V$ARCHIVE_DEST
V$ARCHIVE_DEST_STATUS
V$ARCHIVE_GAP
V$ARCHIVE_PROCESSES
V$BACKUP
V$BACKUP_ASYNC_IO
V$BACKUP_CORRUPTION
V$BACKUP_DATAFILE
V$BACKUP_DEVICE
V$BACKUP_PIECE
V$BACKUP_REDOLOG
V$BACKUP_SET
V$BACKUP_SYNC_IO
V$BLOCK_CHANGE_TRACKING
V$COPY_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION
V$DATABASE_INCARNATION
V$DATAFILE_COPY
V$DELETED_OBJECT
V$FAST_START_SERVERS
V$FAST_START_TRANSACTIONS
V$INSTANCE_RECOVERY
V$MTTR_TARGET_ADVICE
V$PROXY_ARCHIVEDLOG
V$PROXY_DATAFILE
V$RMAN_CONFIGURATION
V$RECOVERY_FILE_STATUS
V$RECOVERY_LOG
V$RECOVERY_PROGRESS
V$RECOVERY_STATUS
V$RECOVER_FILE
V$BACKUP_ARCHIVELOG_DETAILS(10.2)
V$BACKUP_ARCHIVELOG_SUMMARY(10.2)
V$BACKUP_CONTROLFILE_DETAILS(10.2)
V$BACKUP_CONTROLFILE_SUMMARY(10.2)
V$BACKUP_COPY_DETAILS(10.2)
V$BACKUP_COPY_SUMMARY(10.2)
V$BACKUP_FILES(10.1)
V$BACKUP_PIECE_DETAILS(10.2)
V$BACKUP_SET_DETAILS(10.2)
V$BACKUP_SET_SUMMARY(10.2)
V$BACKUP_SPFILE
V$BACKUP_SPFILE_DETAILS(10.2)
V$BACKUP_SPFILE_SUMMARY(10.2)
V$DATAFILE_HEADER
V$FLASH_RECOVERY_AREA_USAGE(10.2)
V$FLASHBACK_DATABASE_LOG(10.1)
V$FLASHBACK_DATABASE_STAT(10.1)
V$OBSOLETE_BACKUP_FILES
V$OFFLINE_RANGE
V$PROXY_ARCHIVELOG_DETAILS(10.2)
V$PROXY_ARCHIVELOG_SUMMARY(10.2)
V$PROXY_COPY_DETAILS(10.2)
V$PROXY_COPY_SUMMARY(10.2)
V$RECOVERY_FILE_DEST(10.1)
V$RESTORE_POINT(10.2)
V$RMAN_BACKUP_JOB_DETAILS(10.2)
V$RMAN_BACKUP_SUBJOB_DETAILS(10.2)
V$RMAN_BACKUP_TYPE(10.2)
V$RMAN_ENCRYPTION_ALGORITHMS(10.2)
V$RMAN_OUTPUT(10.1)
V$RMAN_STATUS(10.1)
V$UNUSABLE_BACKUPFILE_DETAILS(10.2)

Caches Information related to the various caches, including objects, library, cursors, and the dictionary

V$ACCESS
V$BUFFER_POOL
V$BUFFER_POOL_STATISTICS
V$DB_CACHE_ADVICE
V$DB_OBJECT_CACHE
V$JAVA_POOL_ADVICE
V$LIBRARYCACHE
V$LIBRARY_CACHE_MEMORY
V$PGASTAT
V$PGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE_HISTOGRAM
V$ROWCACHE
V$ROWCACHE_PARENT
V$ROWCACHE_SUBORDINATE
V$SESSION_CURSOR_CACHE
V$SGA
V$SGASTAT
V$SGA_CURRENT_RESIZE_OPS
V$SGA_DYNAMIC_COMPONENTS
V$SGA_DYNAMIC_FREE_MEMORY
V$SGA_RESIZE_OPS
V$SGAINFO
V$SHARED_POOL_ADVICE
V$SHARED_POOL_RESERVED
V$SYSTEM_CURSOR_CACHE
V$SUBCACHE
V$JAVA_LIBRARY_CACHE_MEMORY(10.1)
V$PROCESS_MEMORY(10.2)
V$SGA_TARGET_ADVICE(10.2)

Cache Fusion/RAC

V$ACTIVE_INSTANCES
V$BH
V$CLUSTER_INTERCONNECTS(10.2)
V$CONFIGURED_INTERCONNECTS(10.2)
V$CR_BLOCK_SERVER
V$CURRENT_BLOCK_SERVER(10.1)
V$GC_ELEMENT
VGCSHVMASTER_INFO
V$GCSPFMASTER_INFO
V$GES_BLOCKING_ENQUEUE
V$GES_CONVERT_LOCAL
V$GES_CONVERT_REMOTE
V$GES_ENQUEUE
V$GES_LATCH
V$GES_RESOURCE
V$GES_STATISTICS
V$HVMASTER_INFO
V$INSTANCE_CACHE_TRANSFER
V$RESOURCE_LIMIT

Control files Information related to instance control files

V$CONTROLFILE
V$CONTROLFILE_RECORD_SECTION

Cursors/SQL statements Information related to cursors and SQL statements, including the open cursors, statistics, and actual SQL text

V$OPEN_CURSOR
V$SQL
V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES
V$SQL_BIND_DATA
V$SQL_BIND_METADATA
V$SQL_CURSOR
V$SQL_OPTIMIZER_ENV
V$SQL_PLAN
V$SQL_PLAN_STATISTICS
V$SQL_PLAN_STATISTICS_ALL
V$SQL_REDIRECTION
V$SESSION_CURSOR_CACHE
V$SQL_SHARED_CURSOR
V$SQL_SHARED_MEMORY
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
V$SQL_WORKAREA_HISTOGRAM
V$SYSTEM_CURSOR_CACHE V$MUTEX_SLEEP(10.2)
V$MUTEX_SLEEP_HISTORY(10.2) (for those dubious shared latches)
V$SQL_BIND_CAPTURE(10.1)
V$SQL_JOIN_FILTER(10.2)
V$SQL_AREA_PLAN_HASH(10.2)
V$SQLSTATS(10.2)
V$SYS_OPTIMIZER_ENV(10.1)
V$VPD_POLICY

Database instances Information related to the actual database instance

V$ACTIVE_INSTANCES
V$BGPROCESS
V$DATABASE
V$INSTANCE
V$PROCESS
V$SGA
V$SGASTAT
V$BLOCKING_QUIESCE(10.2)
V$CLIENT_STATS(10.1)
RAC Views: V$BH
V$ACTIVE_INSTANCES

Direct Path Operations Information related to the SQL*Loader direct load option

V$LOADISTAT
V$LOADPSTAT

Distributed/Heterogeneous Services

V$DBLINK
V$GLOBAL_TRANSACTION
V$GLOBAL_BLOCKED_LOCKS
V$HS_AGENT
V$HS_PARAMETER
V$HS_SESSION

Fixed view Information related to the v$ tables themselves

V$FIXED_TABLE
V$FIXED_VIEW_DEFINITION
V$INDEXED_FIXED_COLUMN

General General information related to various system information

V$DBPIPES
V$CONTEXT
V$GLOBALCONTEXT
V$LICENSE
V$OPTION
V$RESERVED_WORDS
V$TIMER
V$TIMEZONE_NAMES
V$TYPE_SIZE
V$_SEQUENCES
V$VERSION
V$DB_TRANSPORTABLE_PLATFORM(10.2)
V$TRANSPORTABLE_PLATFORM(10.1)
V$SCHEDULER_RUNNING_JOBS(10.2)

I/O Information related to I/O, including files and statistics

V$DBFILE
V$FILESTAT
V$WAITSTAT
V$TEMPSTAT
V$FILE_HISTOGRAM(10.1)
V$FILEMETRIC(10.1)
V$FILEMETRIC_HISTORY(10.1)
V$SYSAUX_OCCUPANTS(10.1)
V$TABLESPACE
V$TEMP_HISTOGRAM(10.1)
V$TEMP_SPACE_HEADER
V$TEMPFILE
V$TEMPSEG_USAGE

Latches/locks Information related to latches and locks

V$ENQUEUE_LOCK
V$ENQUEUE_STAT
V$EVENT_NAME
V$FILE_CACHE_TRANSFER
V$GLOBAL_BLOCKED_LOCKS
V$LATCH
V$LATCHHOLDER
V$LATCHNAME
V$LATCH_CHILDREN
V$LATCH_MISSES
V$LATCH_PARENT
V$LOCK
V$LOCKED_OBJECT
V$RESOURCE
V$RESOURCE_LIMIT
V$TRANSACTION_ENQUEUE
V$_LOCK
V$_LOCK1
V$ENQUEUE_STATISTICS

FOLLOWING ARE RAC VIEWS:

V$CR_BLOCK_SERVER
V$GCSHVMASTER_INFO
V$GCSPFMASTER_INFO
V$GC_ELEMENT
V$GES_BLOCKING_ENQUEUE
V$GES_ENQUEUE
V$HVMASTER_INFO
V$GES_LATCH
V$GES_RESOURCES

Log Miner Information related to Log Miner

V$LOGMNR_CALLBACK
V$LOGMNR_CONTENTS
V$LOGMNR_DICTIONARY
V$LOGMNR_LATCH
V$LOGMNR_LOGFILE
V$LOGMNR_LOGS
V$LOGMNR_PARAMETERS
V$LOGMNR_PROCESS
V$LOGMNR_REGION

V$LOGMNR_SESSION
V$LOGMNR_STATS
V$LOGMNR_TRANSACTION
V$LOGMNR_DICTIONARY_LOAD(10.2)

Metrics Information related to Metrics (ALL New in 10g!)

V$METRICNAME
V$SERVICEMETRIC
V$EVENTMETRIC
V$FILEMETRIC
V$FILEMETRIC_HISTORY
V$SERVICEMETRIC_HISTORY
V$SESSMETRIC
V$SYSMETRIC
V$SYSMETRIC_HISTORY
V$SYSMETRIC_SUMMARY
V$THRESHOLD_TYPES
V$WAITCLASSMETRIC
V$WAITCLASSMETRIC_HISTORY

Multithreaded/shared servers Information related to multithreaded and parallel servers, including connections, queues,dispatchers, and shared servers

V$CIRCUIT
V$DISPATCHER
V$DISPATCHER_RATE
V$QUEUE
V$QUEUEING_MTH
V$REQDIST
V$SHARED_SERVER
V$SHARED_SERVER_MONITOR
V$DISPATCHER_CONFIG(10.1)

Object Usage Information related to object use and dependencies

V$OBJECT_DEPENDENCY
V$OBJECT_USAGE

Overall system Information related to the overall system performance

V$GLOBAL_TRANSACTION
V$SHARED_POOL_RESERVED
V$RESUMABLE
V$SORT_SEGMENT
V$TEMPSEG_USAGE
V$STATNAME
V$SYS_OPTIMIZER_ENV
V$SYS_TIME_MODEL
V$SYSSTAT
V$SYSTEM_CURSOR_CACHE
V$SYSTEM_EVENT
V$TEMPFILE
V$TEMPORARY_LOBS
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_SPACE_HEADER
V$TRANSACTION
V$ALERT_TYPES(10.1)
V$EVENT_HISTOGRAM(10.1)
V$OSSTAT(10.1)
V$SYSTEM_WAIT_CLASS(10.1)
V$TEMP_HISTOGRAM(10.1)
V$XML_AUDIT_TRAIL

Parallel Query Information related to the Parallel Query option

V$EXECUTION
V$PARALLEL_DEGREE_LIMIT_MTH
V$PQ_SESSTAT
V$PQ_SLAVE
V$PQ_SYSSTAT
V$PQ_TQSTAT
V$PX_PROCESS
V$PX_PROCESS_SYSSTAT
V$PX_SESSION
V$PX_SESSTAT

Parameters Information related to various Oracle parameters, including initialization and NLS per session

V$NLS_PARAMETERS
V$NLS_VALID_VALUES
V$OBSOLETE_PARAMETER
V$PARAMETER
V$PARAMETER2
V$SPPARAMETER
V$SYSTEM_PARAMETER
V$SYSTEM_PARAMETER2
V$PARAMETER_VALID_VALUES(10.2)

Redo logs Information related to redo logs, including statistics and history

V$LOG
V$LOGFILE
V$LOGHIST
V$LOG_HISTORY
V$THREAD (RAC related)

Replication and materialized views Information related to replication and materialized views

V$MVREFRESH
V$REPLPROP
V$REPLQUEUE

Resource Manager Information related to resource management

V$ACTIVE_SESSION_POOL_MTH
V$ACTIVE_SESSION_POOL_HISTORY
V$RSRC_CONS_GROUP_HISTORY(10.2)
V$RSRC_CONSUMER_GROUP
V$RSRC_CONSUMER_GROUP_CPU_MTH
V$RSRC_PLAN
V$RSRC_PLAN_CPU_MTH
V$RSRC_PLAN_HISTORY(10.2)
V$RSRC_SESSION_INFO(10.2)

Rollback segments And Undo Information on rollback segments, including statistics and transactions

V$ROLLNAME
V$ROLLSTAT
V$TRANSACTION
V$UNDOSTAT

Security/privileges Information related to security

V$ENABLEDPRIVS
V$PWFILE_USERS
V$VPD_POLICY
V$WALLET(10.2)
V$XML_AUDIT_TRAIL(10.2)

Sessions (includes some replication information and heterogeneous services) Information related to a session, including object access, cursors, processes, and statistics

V$ACTIVE_SESSION_HISTORY
V$MYSTAT
V$PROCESS
V$SESS_TIME_MODEL
V$SESSION
V$SESSION_CONNECT_INFO
V$SESSION_CURSOR_CACHE
V$SESSION_EVENT
V$SESSION_LONGOPS
V$SESSION_OBJECT_CACHE
V$SESSION_WAIT
V$SESSION_WAIT_CLASS
V$SESSION_WAIT_HISTORY
V$SESSTAT
V$SESS_IO
V$SES_OPTIMIZER_ENV
V$SESSMETRIC
and V$CLIENT_STATS
and V$TSM_SESSIONS(10.2)

Services (all new for 10.1)

V$ACTIVE_SERVICES
V$SERV_MOD_ACT_STATS
V$SERVICE_EVENT
V$SERVICE_STATS
V$SERVICE_WAIT_CLASS
V$SERVICES

Sorting Information related to sorting

V$SORT_SEGMENT
V$TEMPSEG_USAGE
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_HISTOGRAM(10.1)
V$TEMP_SPACE_HEADER
V$TEMPFILE
V$TEMPSTAT

Standby databases (Data Guard) Information related to standby databases

V$DATAGUARD_STATUS
V$LOGSTDBY
V$LOGSTDBY_STATS
V$MANAGED_STANDBY
V$STANDBY_LOG
V$DATAGUARD_CONFIG(10.1)
V$DATAGUARD_STATS(10.2)
V$LOGSTDBY_PROCESS(10.2)
V$LOGSTDBY_PROGRESS(10.2)
V$LOGSTDBY_STATE(10.2)
V$LOGSTDBY_TRANSACTION(10.2)

File mapping interface Information related to file mapping

V$MAP_COMP_LIST
V$MAP_ELEMENT
V$MAP_EXT_ELEMENT
V$MAP_FILE
V$MAP_FILE_EXTENT
V$MAP_FILE_IO_STACK
V$MAP_LIBRARY
V$MAP_SUBELEMENT

Streams Information related to streams

V$AQ
V$STREAMS_APPLY_COORDINATOR
V$STREAMS_APPLY_READER
V$STREAMS_APPLY_SERVER
V$STREAMS_CAPTURE
V$BUFFERED_PUBLISHERS(10.1)
V$BUFFERED_QUEUES(10.1)
V$BUFFERED_SUBSCRIBERS(10.1)
V$PROPAGATION_RECEIVER(10.1)
V$PROPAGATION_SENDER(10.1)
V$RULE(10.1)
V$RULE_SET(10.1)
V$RULE_SET_AGGREGATE_STATS(10.1)
V$STREAMS_TRANSACTION(10.2)

Statistics Information related to statistics in general

V$SEGMENT_STATISTICS
V$SEGSTAT
V$SEGSTAT_NAME
V$STATISTICS_LEVEL
V$STATNAME
V$WAITSTAT

Transactions Information related to transactions in general

V$GLOBAL_TRANSACTION
V$LOGSTDBY_TRANSACTION
V$RESUMABLE
V$STREAMS_TRANSACTION
V$TRANSACTION
V$TRANSACTION_ENQUEUE

Note: In Oracle 10.1 and above, the V$LOCK_TYPE dynamic performance view summarizes all implemented lock types

MTV ENJOY....

Top SQL query by cpu for MSSQL Server

2010-04-18T23:29:00.001+06:00

Check the fantastic query below to find the 25 top most cpu intensive queries for a SQL Server Instance.

select top 25 * from (SELECT pa.value AS dbid,
COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value as int))+'*') AS DBNAME,
SUBSTRING(text,
-- starting value for substring
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
-- ending value for substring
CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END + 1
) AS TSQL, total_worker_time,execution_count,
total_worker_time/execution_count avg_cpu,total_physical_reads/execution_count avg_physical_read,
total_elapsed_time/execution_count avg_elapsed_time,total_logical_reads/execution_count avg_logical_read
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE attribute = 'dbid') a where a.dbid>4 order by avg_cpu desc;

Let me know any problem on execution.
Thanks.

Understanding Execution Plan Statistics

2010-04-18T23:22:00.001+06:00

Below is the output of an execution plan statistics.
SQL> set autot trace statistics
SQL> select * from tab;

107 rows selected.

Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
898 consistent gets
0 physical reads
0 redo size
3459 bytes sent via SQL*Net to client
458 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed

You sometimes want to know what these fields indicates. Below is the details of these fields.

1)recursive calls: For processing of sql statements oracle database maintain internal tables. Sometimes for processing sql statements change is needed in the internal tables and sometimes not. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.

2)db block gets: Number of times a CURRENT block was requested.

3)consistent gets: Number of times a consistent read was requested for a block. This is called the logical reads indicates for processing of a query how many blocks needs to be accessed.

4)physical reads: Total number of data blocks read from disk. This number equals the value of "physical reads direct" + all reads into buffer cache.

5)redo size: For processing of a query total amount of redo generated in bytes.

6)bytes sent through SQL*Net to client: Total number of bytes sent to the client from the foreground processes.

7)bytes received through SQL*Net from client: Total number of bytes received from the client over Oracle Net.

8)SQL*Net round-trips to/from client: Total number of Oracle Net messages sent to and received from the client.
9)sorts (memory):Number of sort operations that were performed completely in memory and did not require any disk writes.

10)sorts (disk):Number of sort operations that required at least one disk write.

11)rows processed: Number of rows processed during the operation.

Thanks.

Type of constraints in oracle

2010-04-18T00:32:00.000+06:00

Constraint in oracle imposes rule that restrict the values in a oracle database. There are six types of constraint in oracle database and all these constraints except not null constraint can be declared in two ways.

A brief description of these six types of constraints are listed below.

1)Not Null Constraint: If I define a field not null then value must be inserted in to that column.

2)Unique Constraint: If I define a column or set of column to be unique then it prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

3)Primary Key Constraint: Primary key constraint is the combination of not null constraint and unique constraint. In a single declare it satisfy both constraint.

4)Foreign Key Constraint: Foreign key constraint requires values in one table to match values in another table.

5)Check Constraint: Check constraint imposes restriction of values based on specified condition.

6)REF Constraint: A REF constraint lets you further describe the relationship between the REF column and the object it references.

Constraint related queries: http://askershad.blogspot.com/2010/04/dbaconstraints-userconstraints.html

Thanks.

dba_constraints/ user_constraints/ all_constraints queries

2010-04-18T00:19:00.005+06:00

Simply list of constranits along with the constraint_type you can achieve querying from dba_constraints/ user_constraints/ all_constraints.

SQL> select owner, constraint_name, constraint_type from dba_constraintswhere owner='&Owner_name' and table_name='&Tab_name';

Output:

In the column constraint_type there may have values C,P,U,R,V and O which means,
1) C :check constraint on a table
2) P :primary key
3) U :unique key
4) R :referential integrity
5) V :with check option, on a view
6) O :with read only, on a view

You can get constraints along with their columns and position by querying from DBA_CONS_COLUMNS/ USER_CONS_COLUMNS/ ALL_CONS_COLUMNS.

SQL> col owner for a10
col constraint_name for a27
col table_name for a25
col column_name for a23
set pages 100
SQL> select * from dba_cons_columns where table_name='&Tab_name' and owner='&Owner_name' order by constraint_name, position;

By joining both two views you can get a list of constraints , their type, column_name, column position and their reference constraint name, reference table name in the constraint by,

SQL> col r_owner for a10
SQL> Select c.constraint_name, cc.column_name, c.r_owner,c.r_constraint_name,c.constraint_type,cc.position, r.table_namefrom dba_constraints c JOIN dba_cons_columns cc ON(c.table_name=cc.table_name AND c.owner=cc.ownerAND c.constraint_name=cc.constraint_name) LEFT JOIN dba_constraints rON(c.r_constraint_name=r.constraint_name AND r.constraint_type in ('P','U') ) where c.table_name='&Tab_name' and c.owner='&Owner_name' order byconstraint_name, position;

Thanks.

Oracle: Restore points

2009-02-22T15:31:00.008+06:00

Restore point is nothing but a name given to a specified point of a database status.It can define the status of a database on a specific time or SCN number.So, that when we flashback database , we can mention the name of the restore point in stead of a specific date and time or a SCN number .

Oracle will automatically flashback the database to that specific restore point. It is a mapping of SCN number to a restore point name. So, when we flashback database to a restore point we do, nothing but restoring to a specific SCN.

There are two different type of restore point:

a) Normal restore point or nonguaranteed restore point
b) Guaranteed restore point

a) To create Normal restore point do the following:

sql> create restore point ershad;
[I have given the restore point name as "ershad"]

So, when required , we can flashback the database upto this restore point.But, CONDITIONS APPLY....

whether database can be flashbacked upto the restore point depends on whether you have the flashback logs in the Flash Recovery Area or not.If you have, you can restore upto that point.But , it is not Guaranteed every time.so, you may face the following error while trying to flashback to a normal restore point.

ORA-38729: Not enough flashback database log data to do FLASHBACK

This error means the database does not have the flashback logs needed to go back to the restore point or time or SCN that you’ve specified

***Check db_flashback_retention_target for more on flashback logs retention period.***

b) Guaranteed restore point:
This will guarantee that at any point in time you can flashback the database to the restore point. As , Oracle will make sure the flashback logs will not be deleted until that restore point , whatever the case is.

sql> create restore point askershad guarantee flashback database;
[I have given the restore point name as "askershad"]

Note: As Guaranteed restore point assures flashback logs will not be deleted.So Be Careful about the space consumed in Flash recovery area.And create this only when you require.like , you need to check your database behaviour with a new application and you are going to revert back after the test. And please DROP the restore point after your job is done.Because,for a Guaranteed restore point, the associated flashback logs will never be deleted until you drop the restore point. And not doing so may end up no space in flash recovery area and cause a recovery writer(RVWR) process failure which leads to database instance failure......

TO CHECK RESTORE POINTS:

sql> select * from v$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME NAME
------- -------------------------------- ------ ------------------- ----------- -------
916841 --------11----------------------YES-----3981312----------22-FEB-09-- ASKERSHAD
916831-------- 11---------------------- NO-----------0-----------22-FEB-09---ERSHAD

SCN--> To which SCN the restore point is mapped
DATABASE_INCARNATION#-->This column displays the incarnation of the database
when this restore point was created
GUARANTEE_FLASHBACK_DATABASE-->whether it is guaranteed or normal restore point
STORAGE_SIZE-->What is the flashback log size***
TIME-->To what TIME the restore point is mapped
NAME-->Name of the restored point

***look here , The normal restored point does not have a size mentioned. As it requires the flashback logs from the point of SCN number. If logs are not there,it is not possible to flashback. The guaranteed one will keep the logs so, the size of logs belong to it and assures the flashback.

TO DROP RESTORE POINTS:

SQL> drop restore point ershad;(for both types the command is same)

More Topic:

FLASHBACK DATABASE TO A RESTORE POINT

Thanks,
Have a nice day..........

Oracle:flashback database to earlier time from SQL prompt

2009-02-22T15:09:00.003+06:00

In the last post we have seen how to flashback a database using RMAN utility. And i have discussed about the pros and cons of flashback database also. To review please check:

HOW TO FLASHBACK DATABASE USING RMAN UTILITY

Now , we will see what are the options available to flashback database from SQL*plus utility.

being in the sql prompt also you can do several flashback of database .Available options are:
1) flashback to a specific date and time
2) flashback to a specific SCN number
3) flashback to a named restore point

In Detail::

1) flashback to a specific date and time:

sql>connect sys/e@lion as sysdba
sql>shutdown immediate
sql>startup mount
sql>flashback database to timestamp to_date('14-FEB-2007 11:30:00','DD-MON-YYYY HH24:MI:SS');

Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.

sql>alter database open resetlogs;

2) flashback to a specific SCN number:

sql>connect sys/e@lion as sysdba

Check the current SCN number first:
sql> select current_scn from v$database;

CURRENT_SCN
-----------
916022

Then,
sql>shutdown immediate
sql>startup mount
sql> flashback database to scn 915000;
Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.

sql>alter database open resetlogs;

3) flashback to a named restore point:

sql>connect sys/e@lion as sysdba
sql>shutdown immediate
sql>startup mount
then issue the following with the restore point name you want to flashback. In my case, it is "ershad"

sql>flashback database to restore point ershad;

Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.

sql>alter database open resetlogs;

Topic explained:
WHAT IS RESTORE POINT
HOW TO CREATE RESTORE POINT

HOW to check the maximum flashback possible of a database

2009-02-14T16:27:00.002+06:00

If you want to check how far back you can flashback the database.please do following:
sql> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
sql> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- -------------------- ---------------- -------------- ----------------------------------------------------------------------------------------------
907191---- 14-FEB-2009 12:37:20----- 1440------ 8192000--------73728

so we can flashback database to any time in betweeen upto 14-feb-2009 till 12:37:20 or upto the scn number 907191. but the condition is , we should have the flashback logs available and the archived logs are also available. Because flashback feature uses archive logs to undo the changes.

columns of V$FLASHBACK_DATABASE_LOG:
OLDEST_FLASHBACK_SCN-->The oldest SCN to which you can flash back the database.
OLDEST_FLASHBACK_TIME-->The earliest time to which you can flash back the database.
RETENTION_TARGET-->The initialization parameter db_flashback_retention_target determines how long the flashback logs are retained,values measurement is in minutes.
FLASHBACK_SIZE-->Current size (in bytes) of the flashback data.
ESTIMATED_FLASHBACK_SIZE--> Estimated size of flashback data needed for the current target retention(will be discussed shortly)

DB_FLASHBACK_RETENTION_TARGET
This parameter value decides how long the flashback logs should be kept. remember undo_retention parameter, it behaves the same way. It will keep the flashback logs for that much time but still conditions apply.....
as it depends on the size of flash recovery area also.DB_RECOVERY_FILE_DEST_SIZE decides the size of flashback recovery area. So, DB_FLASHBACK_RETENTION_TARGET will try to keep the flashback logs until the time , if it has sufficient space. If not , FRA will delete the oldest logs to keep new one.the reverse of this, if space is enough , it can keep logs more than the time mentioned in DB_FLASHBACK_RETENTION_TARGET parameter.

in my query to v$flashback_database_log you can see FLASHBACK_SIZE is 8192000(which is 8 MB almost) and ESTIMATED_FLASH_SIZE is 73728(which is 72 KB).So, as of my retention parameter i require 72 kb of space and compare to that i have 8MB. So it will be well enough . But on the real time active database,the reverse is almost always true.

HOW TO ESTIMATE FLASHBACK LOG SIZE

thanks,
have a nice day

Oracle:flashback a database to earlier time using RMAN

2009-02-14T12:35:00.054+06:00

we can flashback an Oracle database with following features:

1) flashback to a specific date and time
2) flashback to a specific SCN number
3) flashback to a named restore point
4) flashback to last resetlogs option

first of all, we need to enable flashback and properly size the flash recovery area so that flashback logs are created and we can flashback our database to a previous stage.
to do that please check the following:

HOW TO ENABLE FLASHBACK
HOW TO CONFIGURE FLASH RECOVERY AREA

now, it is configure properly and we can flashback our database as required.

One more information you may need, that is how far back you can flashback the database.
follow the link for this,MAXIMUM FLASHBACK POSSIBLE FOR ORACLE DATABASE

1)Flashback to a specific date and time:(steps)
RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount
RMAN> flashback database to time 'sysdate-50/60/24'; (doing a flashback to 50 minutes from sysdate)

OUTPUT:
Starting flashback at 14-FEB-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished flashback at 14-FEB-09

at this point, you may need to check whether database has been flashbackED to the point you want.
to do that, open the database in read only mode and check the data.

RMAN> sql 'alter database open read only';

and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;

If answer is no, you can redo the procedure to flashback to a diiferent TIME. that is,shutdown database,startup mount,flashback to the desired TIME,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.

RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........

if you want to mention a specific time then:

RMAN> flashback database to time "to_date('13-FEB-2009 11:00:00','DD-MON-YYYY hh24:mi:ss')";

2) flashback to a specific SCN number
first check the current scn of the database by issuing following statement:
connect as sysdba
sql> select current_scn from v$database;

CURRENT_SCN
-----------
909158

that means we can flashback to a SCN prior to this SCN only.

RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount

now, think i want to flashback till SCN number--

RMAN> flashback database to scn 909100;

Starting flashback at 22-FEB-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished flashback at 22-FEB-09

at this point, you may need to check whether database has been flashback to the point you want.
to do that, open the database in read only mode and check the data.
RMAN> sql 'alter database open read only';

Now , check this,
sys@LION> select current_scn from v$database;

CURRENT_SCN
-----------
909100

and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;

If answer is no, you can redo the procedure to flashback to a diiferent SCN. that is,shutdown database,startup mount,flashback to the desired SCN ,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.

RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........

3.Flashback to a Restore Point:
we can flashback a database to a restore point also.
to do that,
RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount
then issue the following with the restore point name you want to flashback. In my case, it is "ershad"

RMAN> flashback database to restore point ershad;

at this point, you may need to check whether database has been flashback to the point you want.
to do that, open the database in read only mode and check the data.
RMAN> alter database open read only;

and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;

If answer is no, you can redo the procedure to flashback to a diiferent restore point. that is,shutdown database,startup mount,flashback to the desired Restore point ,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.

RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........

WHAT IS RESTORE POINT
HOW TO CREATE RESTORE POINT

4)flashback to last resetlogs option

At last , During the flashback operation , we may end up with a mistake like:
You may have flashback to a wrong point. As we know it is still recoverable, BY opening the database in read only mode,you can check whether the operation is desired or not . if yes , open the database with resetlog options, if no, you can redo the procedure to your desired point once again.

Problem is , if you have flashback to a point and open the database with resetlogs options. And now feel that it was wrong.

Dont worry. You can still go back to the previous incarnation and recover your old state of database before resetlogs.Because, Oracle knows you expect a lot from him.
Let us check, HOW TO FLASHBACK DATABASE TO PRIOR TO LAST RESETLOGS OPTIONS:

RMAN> connect target /@lion
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> flashback database to before resetlogs;
RMAN> alter database open resetlogs;

Note: When we do a resetlogs, oracle will record the SCN . So the previous operation is nothing but flashing back to a previous SCN. We can check the last resetlogs SCN from the following query:
SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
911255

Note: Assume the current SCN is 25444. And I have flashback logs Till 22999. So, we can flashback to any point in between this.and more important , backward and forward both are possible in this range till you reach your expected point. I mean to say , once you flashback to 23888 and found it is not right and you need a bit forward. So, you can then flashback to 24000(which is actually going forward as of your last operation).You can flashback as many times in your range till the flashback logs are available and you have not opened it with resetlogs option.

More:
FLASHBACK A DATABASE FROM SQL PROMPT

MAXIMUM FLASHBACK POSSIBLE

Thanks a lot.....

Have a nice day

ORA-38759: Database must be mounted by only one instance and not open

2009-02-14T12:11:00.004+06:00

i have received an error like the following while trying to enable flashback on my database.

sql> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

Reason is: you cannot enable flashback feature when the database is in open stage.
sql>select status from v$instance;

STATUS
------------
OPEN

solution:
do the following:

sql>shutdown immediate;
sql> startup mount;
sql> alter database flashback on;
sql> alter database open;

But, we can disable flashback feature , when the database is in open state too.

To check whether flashback feature is enable or not:
sql> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

Oracle 10G/11G: Flashback features and example
Oracle 10G/11G: Different flashback options

Thanks,
have a nice day..................

Oracle flashback feature

2009-02-14T11:16:00.009+06:00

Oracle flashback feature:
In ORACLE 10G, a new feature introduced named as FLASHBACK.actually in ORACLE 9i, also there was a feature called FLASHBACK QUERIES. Dont get confused about it.... think like the full featured FLASHBACK is from 10G. in 9i, it had only one option available compared to 10G flashback options.In 10G, FLASHBACK refers as one tool which supports different types of data recovery

flashback features:

i) flashback a database to prior to present time
ii) flashback a table to prior to present time
iii) flashback/rollback a drop table statement(means rollback a table which is dropped)

i) FLASHBACK DATABASE:
earlier till 9i, if we need to go to previous time of a database then we need to do incomplete recovery(DBPITR). That is restore a backup previous to the time you want to recover and apply archived logs till the time you want to recover, then open the database with resetlogs option.10G onwards, we can flashback our database to a previous point in time. Flashback feature uses flashback logs(additional logs to be created for this feature) to do the flashback. Flashback logs will maintain the block changes information and this will be refered to , when you want to flashback your database.Oracle will read the flashback logs and undo the changed blocks to roll it backward.

Note: The entire database will be rolled back. you cannot perform flashback on individual tables or tablespaces.

ii) FLASHBACK a table data to prior time:
this was introduced in Oracle9i as flashback query feature. Remember, read consistancy....snapshot too old error .we can have read consistency till the data was not committed by another transaction.If transaction gets rollback the undo will be applied again. If commited you will see the newest data. Till the transaction going on you can see the read consistent data from undo. And so, When the change is committed, the need for the past image is gone, but it’s not discarded till there is a need of undo space for other transactions.So, the concept of flashback a table is why not to use the undo to get a table to a previous stage.yes we can do that.but only till the undo is available.
and we can resize the undo also depending on how much back image of a table i want.........

iii) Rollback/restore a dropped table:
we know DDL statements connot be rollback. Means if you drop a table,that is gone. But this works only till oracle 9i.
in 10G,you can restore a table which was dropped by a ddl commands.

Check whether flashback is enabled or not in oracle 10G:

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

If it shows no means flashback is not enabled

How to enable flashback in oracle 10G

step 1: Database should be in archive log mode.(HOW TO ENABLE /DISABLE ARCHIVE LOG)
step 2: FLASH RECOVERY AREA must be configured.(HOW TO CONFIGURE FLASH RECOVERY AREA)
step 3:
sql> conn sys/**@lion as sysdba
sql>startup mount
sql>alter database flashback on;
sql> alter database open;

Note: enabling flashback generates flashbacks logs which tracks the changes to data blocks. Flash recovery area is the only place where it stores flashback logs. So, flash recovery area must to be configured.Secondly, having only flashback logs will not help to flashback database ,it needs the archive logs to be applied to undo the blocks.so,the database must be in archive log mode.Remember one more thing, sizing the flash recovery area is very much important.
if you size it inadequetly it may end up having no space in FRA, so,database may hang,or will not startup and you may receive the following errors :(because it may be the location for your rman backup storage and archived logs also. so, no space here means you are in trouble)

Check the errors you may receive if flashback recovery area lack of space:
1) You see a message in the alert log similar to the following:
Can not open flashback thread because there is no more space in flash recovery area

2)If the database has aborted earlier because of flashback errors and you attempt to
start it, you get the following error:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

we will talk about how we can solve this issues in separate discussion. Let us come back to the discussion here.

Using flashback:
HOW to flashback a DATABASE

Other available options:

What should be the size of FRA(flash recovery area)
How to disable flashback

HOW TO GET ORACLE DATABASE IDENTIFIER (DBID)

2009-02-14T10:44:00.004+06:00

DBID stands for database identifier. Which is a unique identifier for each oracle database running.we can get the DBID from the v$database view.

sql>select DBID from v$database;

DBID
----------
396604920

so, above command will show you the unique database identifier of your database which is stored in your CONTROL files.You may need to know your Database DBID on some recovery scenarios.one example is , you may need to restore your control files from rman backup as you have lost all of your control files.There are lot of conditions apply in recovering a control file depending on the type of backup taken and the location of backup.We will discuss about this
in the control file recovery situations documentation afterward .But, while recovering the control file you may end up here:

RMAN> connect target /@lion
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
OUTPUT asking about DBID:

RMAN-06495: must explicitly specify DBID with SET DBID command

this is where this article stands firm.........
Now , you have to set DBID first then try the above recovery stunts.Now ,it becomes a challenging task as we know DBID is stored in control file which we have already lost........

So, is there a way out???
Yes, Oracle can do almost everything........

Let us check the following way to know what is my database DBID

We can get ORACLE DATABASE DBID from :
a) a rman autobackup file.(backup taken with control file autobackup on parameter)
b) we can configure alert log file to store DBID regularly
c) we can retrieve DBID from a file dump
d) we can get DBID from rman output

In detail:

a)DBID FROM CONTROL FILE AUTOBACKUP:
If you choose to take rman control file autobackup on, then mention the format parameter with %F, so that it will generate the file name as %F, this %F parameter includes the DBID in the filename.

ex: %F maintains the c-IIIIIIIIII-YYYYMMDD-QQ format .
where c indicates that it is a control file backup
IIIIIIIIII indicates DBID
YYYYMMDD indicates the date backup was created
QQ indicates a hex sequence number to make the filename unique on the same day

b)GET DBID FROM ALERT LOG FILE:
We can make sure that my database DBID is written to alert log file on regular basis with the help of a package named DBMS_SYSTEM .
To do that, include the following to your regular backup job or script:

COL dbid NEW_VALUE hold_dbid
SELECT dbid FROM v$database;
exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));

after running this code, check your alert log now. you will get an entry like:
DBID: 396604920

c)DBID FROM A FILE DUMP:
Other way is, if any of the physical files(datafiles,logfiles,even archived log files) are available, we can extract the DBID to a trace file. More importantly, we dont need the database to be mounted for this:
example:
sql> connect /@lion as sysdba
sql>startup nomount;
sql>alter system dump datafile 'D:\lion\data\SYSTEM.DBF' block min 1 block max 10;

Now search the in the trace file generated in user_dump_dest location with the string 'Db ID'. and you will get an entry like:
Db ID=396604920=0x17a3b5f8, Db Name='LION'

we can use the same syntax to make a dump of redo log files/archived log files as :

SQL> alter system dump logfile '';

d)DBID from RMAN output:
whenever you will connect to your target database rman utility, it will show the DBID as follows on the post connection output. see the following:(for this your database should be at least mounted,when you connect RMAN)
Rman> connect target /@lion
connected to target database: LION (DBID=396604920, not open)

So, you can view your previously rman output log files to get the DBID , as you surely have executed rman scripts earlier.

Rman uses DBID to identify a database uniquely. So, in control file recovery scenario,if your backups are not in default location or you dont have recovery catalog then rman cannot have the information , that which backup piece is appropriate for restore. So, it requieres DBID.

Thanks,

have a nice day..............

Oracle: Create database manually (in windows/linux)

2009-02-10T10:57:00.012+06:00

Let us check a demonstration of HOW TO CREATE AN ORACLE DATABASE MANUALLY:

Note: Demonstration is on oracle 10G version. but the same will do for other versions also...almost....

step 1: let us create the location for our database:
My database name will be 'DEMO' and the location is 'd:\demo'
under d:\demo folder, i will create folder named......

d:\demo\data (will store all of my datafiles)
d:\demo\pfile (will be my pfile location)
d:\demo\adump (will be my audit_dump_dest location)
d:\demo\bdump (will be my background_dump_dest location)
d:\demo\cdump (will be my core_dump_dest location)
d:\demo\udump (will be my user_dump_dest location)
if you want you can have another folder for flash recovery area (Oracle 10G onwards)

step 2: now , let us copy a sample pfile from the existing running database and place it into "d:\demo\pfile" folder.

CHECK THE SAMPLE PFILE

In the sample file do the required changes mentioned in "BLUE" color text and save the file.

Note: remember the value of undo_tablespace parameter in pfile,as we need to provide the same name for undo_tablespace when we will create the database.

step 3: now we need to create the password file. we will use the orapwd utility for this.
you will find this utility in $ORACLE_HOME\bin directory.
so, lets proceed...

c:\ershad>orapwd file=E:\oracle\product\10.2.0\db_1\database\pwddemo.ora password=tiger entries=5

here we have created a password file named pwddemo.ora under my $oracle_home. The password is tiger and maximum 5 user will be allowed to login as sysdba.

What is a password file
Create password file with ORAPWD utility

step 4: update your listener.ora and tnsnames.ora with only the BLUE colored text here:
(copy & paste)
==>in tnsnames.ora file:

# tnsnames.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

tiger =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.12.242)(PORT = 1521))
)
(CONNECT_DATA =
(server=dedicated)
(SERVICE_NAME = tiger)
)
)

demo=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = YOUR HOSTNAME/IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =demo)
)
)

==>in listener.ora file:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba2)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = e:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = tiger)
(ORACLE_HOME =e:\oracle\product\10.2.0\db_1)
(SID_NAME = tiger)
)
(SID_DESC =
(GLOBAL_DBNAME = demo)
(ORACLE_HOME =e:\oracle\product\10.2.0\db_1)
(SID_NAME = demo)
)
)

step 5: you need to create the service:

C:\ershad>oradim -new -sid demo -pfile d:\demo\pfile\initdemo.ora -startmode A

step 6: connect to the database:
C:\ershad>set oracle_sid=demo

C:\ershad>sqlplus / as sysdba

sql> select status from v$instance;

STATUS
------------
STARTED

after connecting to database, if it shows "connect to an idle instance" , then you need to manually start up the database using the following:
sql>startup nomount pfile=d:\demo\pfile\initdemo.ora

step 7: run the create database script :
just copy the following into a file and run the file in sql prompt as follows...............

content of create_database_script file:

create database demo
logfile group 1 ('d:\demo\data\redo1.log') size 100M,
group 2 ('d:\demo\data\redo2.log') size 100M,
group 3 ('d:\demo\data\redo3.log') size 100M
datafile 'd:\demo\data\system.dbf' size 100M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile 'd:\demo\data\sysaux.dbf' size 100M autoextend on next 10M maxsize unlimited
undo tablespace undodemo datafile 'd:\demo\data\undotbs1.dbf' size 100M
default temporary tablespace temptiger tempfile 'd:\demo\data\temp01.dbf' size 100M;

so now,
sql>@d:\create_database_script
Database created.

Now, You need to run two scripts to create the dynamic views and enable pl/sql packages
put the database in oepn mode and run the following scripts:
location of scripts are '$ORACLE_HOME\RDBMS\ADMIN'

sql> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catalog.sql
sql> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catproc.sql

Your manually created database is ready to use now.

HOW TO CREATE ORACLE DATABASE MANUALLY IN LINUX

perform all the steps previous except step 5. as in unix/linux ,no need to create the database service.

thanks,
have a nice day...........................

Oracle: A sample pfile

2009-02-10T10:17:00.002+06:00

let us check a sample pfile here, database name is 'demo' :

##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=""
db_name=demo

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=d:\demo\bdump
core_dump_dest=d:\demo\cdump
user_dump_dest=d:\demo\udump

###########################################
# File Configuration
###########################################
control_files=("d:\demo\data\control01.ctl", "d:\demo\data\control02.ctl", "d:\demo\data\control03.ctl")

###########################################
# Job Queues
###########################################
job_queue_processes=10

###########################################
# Miscellaneous
###########################################
compatible=10.2.0.1.0

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# SGA Memory
###########################################
sga_target=571473920

###########################################
# Security and Auditing
###########################################
audit_file_dest=d:\demo\adump
remote_login_passwordfile=EXCLUSIVE

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=demoXDB)"

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=189792256

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDODEMO

thanks,
have a nice day..............

exp/imp vs expdp/impdp or datapump export/import feature or difference between datapump and original export/import

2009-02-10T09:52:00.003+06:00

prior to oracle 10G, the logical backup was taken through EXP/IMP utility, which was the basic export/import utility till oracle 9i. Oracle has come with more powerful logical backup tool in oracle 10G. As we know about datapump export/import utility.

let us check the feature which enhances the datapump export/import over the original export/import

expdp/impdp(datapump) vs exp/imp(basic one)

1) Impdp/Expdp uses parallel execution rather than a single stream of execution. this improved the task performance a lot.

2) In Data Pump, expdp full=y and then impdp schemas=HR is same as of expdp schemas=HR and then impdp full=y where in original export/import does not always behave the same.

3) in datapump Expdp/Impdp access files on the server rather than on the client.

4) Data Pump represents metadata information in the dump file set as XML documents rather than as simple DDL commands.

5) Datapump Impdp/Expdp has self-tuning abilities. Tuning parameters that were used in original Export and Import, such as RECORDLENGTH and BUFFER are not supported by
Data Pump Export and Import.Not required is the perfect term to define.

6) Datapump Expdp/Impdp operates on a group of files called a dump file set rather than on
a single sequential dump file as exp/imp

7) Oracle data pump does not supports Sequential media, such as tapes and pipes.

8) The way of transition of data between two different versions of oracle database is not same
in expdp/impdp and exp/imp utility

9) When you are importing rows into an existing table using either APPEND or TRUNCATE
using datapump, if any row violates an active constraint, the load is discontinued and no data is loaded.

Original Import will logs any rows that are in violation and continues the load process.

10) Datapump expdp/impdp generates more undo than expdp/impdp

Hope the information are useful....

thanks,
have a nice day.............

Oracle:rman output to a file/V$rman_output/V$rman_status

2009-02-08T13:38:00.004+06:00

When using rman utility , you may often need to store the rman activity log in a file. And normally we use various rman backup and recovery scripts on a scheduled time to run . so, we need to refer to log to find everything was ok at the scheduled time as i want.

Although , whenever you run rman commands , it will automatically log the detail output of rman activity in a data dictionary file named as V$rman_output.

sql> select * from v$rman_output;

but as it is a dynamic view , so the contents will be washed out on restart of the database.

so, a log file is safe to store the output of rman.let us check the following demonstration:

RMAN> spool log to 'c:\rman.log';
RMAN> backup database;
RMAN> spool off;
RMAN>

Whatever statements you have issued in between spool and spool off will have there output stored in the destination file you have mentioned.As you see there is no output of backup database command on the screen.
Let us check the log file now:

c:\>notepad 'c:\rman.log'

Spooling started in log file: c:\rman.log

Recovery Manager10.2.0.1.0

RMAN>
Starting backup at 08-FEB-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\LION\DATA\SYSTEM.DBF
input datafile fno=00002 name=D:\LION\DATA\UNDOTBS1.DBF
input datafile fno=00003 name=D:\LION\DATA\SYSAUX.DBF
input datafile fno=00004 name=C:\DATA01.DBF
input datafile fno=00005 name=C:\INDEX01.DBF
channel ORA_DISK_1: starting piece 1 at 08-FEB-09
channel ORA_DISK_1: finished piece 1 at 08-FEB-09
piece handle=E:\LION\BACKUPSET\2009_02_08\O1_MF_NNNDF_TAG20090208T150901_4RX89GOZ_.BKP tag=TAG20090208T150901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 08-FEB-09

Starting Control File and SPFILE Autobackup at 08-FEB-09
piece handle=E:\LION\AUTOBACKUP\2009_02_08\O1_MF_S_678294567_4RX8B8MK_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 08-FEB-09

RMAN>
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "off": expecting one of: "log, msglog, trace"
RMAN-01007: at line 1 column 7 file: standard input

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate"
RMAN-01007: at line 1 column 10 file: standard input

so, hope it is ok
remember one thing , if you run the same procedure again, it will overwrite the same file.
to avoid this situation try the following:
RMAN> spool log to 'c:\rman.log' append;
RMAN> backup database;
RMAN> spool off;
RMAN>

If you are running a rman script or cmdfile then also in the file you can do the same.
or you have another option like:

rman> rman target /@lion cmdfile c:\rman.txt log c:\rman.log

to avoid overwriting ................

rman> rman target /@lion cmdfile c:\rman.txt log c:\rman.log append

Note: You can check the job status of rman by quering the V$rman_status view also....

Thanks,
Have a nice day............................

Oracle:Configure flash recovery area

2009-02-08T11:41:00.004+06:00

in Oracle 10G, a major feature is flash recovery area.Flash recovery area contributes as a central repository for rman to store backups,archive logs. Even oracle recommends to keep your manual backups,archive logs,redo files to keep in FRA. We can keep control files also to this location.

Let us check what are the effective way to configure FRA(login as sysdba user):

step 1: To configure Flash recovery area , first you need to think about:
i) what will be the location
ii) what should be the size
step 2:
sql>show parameter log_archive_dest
sql>show parameter log_archive_duplex_dest
or
sql>show parameter log_archive_dest_
(check for the values for 1 to 10)
If this parameters have a value set then, you need to remove this values.
Normally we may have either log_archive_dest+log_archive_duplex_dest or log_archive_dest_1 to 10 set as archive location. Both combination are not allowed together.
any of these parameters , need to be removed to configure flash recovery area

Note: You may have this values configured as your archive log location, we will discuss about this point later

to remove the values:

sql> alter system set log_archive_duplex_dest = ' ' scope=both;
sql> alter system set log_archive_dest = ' ' scope=both;

for log_archive_dest_1 to 10 follow the same to remove the values.

step 3: Issue the following command:
sql>alter system set db_recovery_file_dest_size = 8G scope=both;
sql>alter system set db_recovery_file_dest = 'e:\oracle\flasharea' scope=both;

Note: the directory "e:\oracle\flasharea" should exists .

Now, the note earlier about archive log location.
if you have log_archive_dest as your archive log location , then change it to log_archive_dest_1 to store archive log.

check this ,
earlier location for archive log:

SQL>show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
log_archive_dest string e:\archive

now change the value:

sql> alter system set log_archive_dest = ' ' scope=both;
then,
SQL> alter system set log_archive_dest_1 = 'location=e:\archive';

Note that , With flash recovery area configured we can keep the archive log in that location(as of oracle recommendation), though if you want above is the solution for using flash recovery area as well as different archive locations for archiving redo logs.

Thanks,

Have a nice day....................

Oracle: foreign key syntax

2009-02-08T10:37:00.003+06:00

let us check the syntax of foreign key with various options:
step 1) first we will create a primary key that will be referred in foreign key constraints:

sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
);

Table created

step 2) We will configure the foreign key now.
a) foreign key with create table option
sys@LION> create table ersh_foreign_tab
(id number(10) not null,
email varchar2(32),
constraint emp_id_fk foreign key (id) references ersh_tab(emp_id)
);
Table created

b) foreign key with alter table option
sys@LION>ALTER TABLE ersh_foreign_tab
add CONSTRAINT emp_id_fk
FOREIGN KEY (id) REFERENCES ersh_tab(emp_id);
Table altered

c) Composite primary key with create table:
sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id,emp_name)
);

composite foreign key with create table option
sys@LION> create table ersh_foreign_tab
(id number(10) not null,
email varchar2(32),
constraint emp_id_fk foreign key (id,email) references ersh_tab(emp_id,email)
);

Thanks,
Have a nice day.................

Oracle: Rman show command parameters

2009-02-08T09:49:00.014+06:00

to check the rman configuration settings, SHOW is very important . there is a lot of parameter that show supports. let us check one by one:

==>RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFLION.ORA'; # default

show all will show all the rman configuration settings which include both the default settings as well as what you have configured . You can identify the default settings by "# default" at the end of every line.

==>RMAN> show controlfile autobackup;

RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

shows whether control file will be backup up automatically after any backup operation by rman.default value is OFF.

==>RMAN> show archivelog deletion policy;

RMAN configuration parameters are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

shows the archive log detention policy.

==>RMAN> show archivelog backup copies;

RMAN configuration parameters are:
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

shows the number of archivelog backup copies

==>RMAN> show auxname;

RMAN configuration parameters are:
RMAN configuration has no stored or default parameters

shows the auxiliary database information.

==>RMAN> show backup optimization;

RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION OFF; # default

shows whether backup optimization is on or off.

==>RMAN> show controlfile autobackup format;

RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

shows what will be the format of auto backed up control file .

==>RMAN> show datafile backup copies;

RMAN configuration parameters are:
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
shows how many datafile backup copies will be kept by rman

==>RMAN> show default device type;

RMAN configuration parameters are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

shows whether the default device type is disk or tape

==>RMAN> show retention policy;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

Shows policy for datafile and control file backups and copies that RMAN marks as obsolete.

==>RMAN> show encryption algorithm;

RMAN configuration parameters are:
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

shows which encryption algorithm is currently is use.

==>RMAN> show encryption for database;[or for tablespace]

RMAN configuration parameters are:
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

shows the encryption for the database or every tablespace.

More:
show exclude; (will show which tablespaces are excluded from backup)
show maxsetsize; (shows the max size of backup set. the default is unlimited)
show snapshot controlfile name; (shows the snapshot control file name)
show compression algorithm; (shows compression algorithm . default is ZLIB)
show channel for device type disk; [device type sbt](shows the channel for device type)

NOTE: If you have changed the default value of any rman parameters , you can get the parameter name and the values from v$rman_configuration view .
ex:
step 1:
RMAN> show controlfile autobackup;

RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

so, it is off now.and it is default
step 2:
sys@LION> select * from v$rman_configuration;
no rows selected

step 3:
RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Changing the default value off to on.
step 4:
sys@LION> select * from v$rman_configuration;

CONF# NAME VALUE
---------- ------------------------------ ------------------------------
1 CONTROLFILE AUTOBACKUP ON

So , hope it is clear now.

Thanks,
have a nice day....

ORA-02273: this unique/primary key is referenced by some foreign keys

2009-02-07T16:30:00.009+06:00

Problem occurs because you are trying to drop a primary constraints which has a dependent foreign key exist.
let us check the demonstration:
i will create a table with primary key:
sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
);

Now let us make a foreign key relationship depending on this table:
sys@LION> create table opt (id number(30),emp_mail varchar2(50),constraint opt_id_fk foreign key(id) references ersh_tab(emp_id));

Now i will try to drop the primary key constraints, which is emp_id_pk:
sys@LION> alter table ersh_tab drop constraint emp_id_pk;
alter table ersh_tab drop constraint emp_id_pk * ERROR at line 1: ORA-02273: this unique/primary key is referenced by some foreign keys

solution is :
first drop the foreign key constraint then the primary one :
sys@LION> alter table opt drop constraint opt_id_fk;
then,
sys@LION> alter table ersh_tab drop constraints emp_id_pk;

or to do it in one shot , you can try the following:

sys@LION> alter table ersh_tab drop constraints emp_id_pk cascade;

it will drop the dependent constraints also automatically.

Note : you can get the dependency information from dba_constraints/user_constraints/all_constraints view .

MORE ABOUT PRIMARY KEY

thanks,
Have a nice day .............

He who has never learned to obey
cannot be a good commander.
Aristotle