I am Md.Samiuzzaman Khan (Tamim) work as Technical Consultant having 5+ years of experience in Oracle Database Administration and Development, Unix/Linux administration, System Design and Integration, Search Engine Optimization (SEO), Mobile Content Management System (SMPP and Web Services), Java and .Net (C#) base Application Development also in Project Management(Agile/Scrum).

ERROR: ORA-00257: archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.alter database flashback on

There is two possible way to solution.
1. without increasing DB_RECOVERY_FILE_DEST_SIZE.
2. by increasing DB_RECOVERY_FILE_DEST_SIZE.

Without increasing DB_RECOVERY_FILE_DEST_SIZE.
1. Check whether the database is in archive log mode and automatic archiving is enabled.

 SQL> archive log list;
 Database log mode              Archive Mode
 Automatic archival             Enabled
 Archive destination            USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence     29
 Next log sequence to archive   31
 Current log sequence           31

2. If archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by:

SQL> show parameter db_recovery_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\oracle\product\10.2.0/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

Check what the value for db_recovery_file_dest_size.

3. Find the space used in flash recovery area by using following SQL:

col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999
col round(space_used/1048576) heading “Space Used (MB)” format 99999
col name format a40
select name, round(space_limit/1048576) As space_limit,round(space_used/1048576) As space_used
from v$RECOVERY_FILE_DEST;

4. if SPACE_USED is equal to SPACE_LIMIT of db_recovery_file_dest, move the archive logs to different destination.

5. Archive all the log files

SQL> alter system archive log all;

6. Just switch the logs to verify

 SQL> alter system switch logfile;

7. DB_RECOVERY_FILE_DEST_SIZE is to delete (archive log) files from DB_RECOVERY_FILE_DEST if you are sure you have backups and the archived logs are no longer necessary.

 
 $rman target /
 RMAN>delete archivelog until time 'SYSDATE-1';
 or,
 RMAN>delete archivelog all;

By increasing DB_RECOVERY_FILE_DEST_SIZE.
1. See the path of flash recovery area.

 SQL> show parameter db_recovery_file_dest;

2. Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;
Sytem Altered.

CreateFile error 32 when trying set file time

Today I am going to install Oracle 10g Client and ODAC11gR2 (Oracle Data Provider .Net) in Microsoft Windows XP Professional Service Pack 2. I have got following error message in console.

CreateFile error 32 when trying set file time

After searching I have found that this error message for my Antivirus. In my case the anti virus is Kaspersky. After disable my antivirus I am able to install Oracle Client with out any Warning or Error message

ORA-00439: feature not enabled: Flashback Database

Some feature is not enabled in oracle database standard edition, as a result we can not use flashback feature in Oracle Database if you are use in standard edition of oracle.

Following Error Message show in Oracle 10g standard edition

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

Version of Oracle

SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Features are available in standard edition

SQL> SELECT Parameter,Value FROM V$OPTION Where Value = 'TRUE';
PARAMETER                                                        VALUE                                                           
----------------------------------------------------------------------
Objects                                                          TRUE                                                            
Connection multiplexing                                          TRUE                                                            
Connection pooling                                               TRUE                                                            
Database queuing                                                 TRUE                                                            
Incremental backup and recovery                                  TRUE                                                            
Instead-of triggers                                              TRUE                                                            
Parallel load                                                    TRUE                                                            
Proxy authentication/authorization                               TRUE                                                            
Plan Stability                                                   TRUE                                                            
Transparent Application Failover                                 TRUE                                                            
Sample Scan                                                      TRUE                                                            
Java                                                             TRUE                                                            
OLAP Window Functions                                            TRUE

So flashback database is disabled in standard edition or in standard one  edition. To be able to flashback on you have to use enterprise edition.

ORA-01775: looping chain of synonyms

Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself.

Action: Change one synonym definition so that it applies to a base table or view and retry the operation.

For example, the following definitions are circular: CREATE SYNONYM s1 for s2;
CREATE SYNONYM s2 for s3
CREATE SYNONYM s3 for s1
or
CREATE SYNONYM s4 for s4

Check your synonyms with:
select * from all_synonyms where synonym_name = ‘name’ or table_name = ‘name’;

SELECT table_owner, table_name, db_link
FROM dba_synonyms
WHERE owner = 'PUBLIC'
AND synonym_name = <<synonym name>>

select owner, synonym_name, connect_by_iscycle CYCLE
from dba_synonyms
where connect_by_iscycle > 0
connect by nocycle prior table_name = synonym_name
and prior table_owner = owner
union
select 'PUBLIC', synonym_name, 1
from dba_synonyms
where owner = 'PUBLIC'
and table_name = synonym_name
and (table_name, table_owner) not in (select object_name, owner from dba_objects
where object_type != 'SYNONYM')

RemoteOperationException:ERROR:Wrong Password for User

Problem in Oracle 10g database on Windows 2000/2003 Professional/Enterprise error message  “RemoteOperationException:ERROR:Wrong Password for User”

Solutions

1. go to Control Panel -> Administrative Tools.

2. Click on Local Security Policy.

3. Local Policies, User Rights Assignment.

4. Double click, ‘Log on as a batch job‘.

5. Add your local user that you are supplying for the Host Credentials.

 

 

Listener Commands

current_listener  
Syntax : set current_listener <listener_name>
LSNRCTL> set current_listener LISTENER
LSNRCTL> show current_listener

displaymode 
Syntax : set displaymode <RAW | COMPAT | NORMAL | VEBOSE>
LSNRCTL> show displaymode
Service display mode is NORMAL
LSNRCTL> set displaymode VERBOSE

inbound_connect_timeout 
Syntax : set inbound_connect_timeout <value>
LSNRCTL> set inound_connect_timeout 0

log_directory     
Syntax : set log_directory <directory_path>
LSNRCTL> set log_directory c:\listner_log

log_file    
Syntax : set log_file <file_name>
LSNRCTL> set log_file listner.log

log_status  
Syntax : set log_status < ON | OFF>
LSNRCTL> set log_status ON

password    
Syntax : set password
LSNRCTL> set password
Password:

rawmode     
Syntax : set rawmode <ON | OFF>
LSNRCTL> set rawmode OFF

save_config_on_stop     
Syntax : set save_config_on_stop <ON | OFF>
LSNRCTL> set save_config_on_stop ON

startup_waittime  
Syntax : set startup_waittime <value>
LSNRCTL> set startup_waittime 0

trc_directory     
Syntax : set trc_directory <directory path>
LSNRCTL> set trc_directory c:\listner_log

trc_file    
Syntax : set trc_level <file_name>
LSNRCTL> set trc_file listener.trc
trc_file    
Syntax : set trc_level <file_name>
LSNRCTL> set trc_file listener.trc

trc_level set trc_level <value>

Value	Description
0 – 1	off
2 – 3	err
4 – 5	user
6 – 14	admin
15	dev
16 – 99	support

LSNRCTL> set trc_level 0

Services view the current listener services

lsnrctl services

Start the listener  lsnrctl start

Stop the listener  lsnrctl stop

Show status lsnrctl status

Listener version lsnrctl version

RESTRICTED SESSION

SQL> select logins from v$instance;
LOGINS
----------
ALLOWED

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.

SQL> select logins from v$instance;
LOGINS
----------
RESTRICTED

$ sqlplus <username>/<password>@<serviceid>
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 24 09:26:48 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
System altered.

‘alter system enable restricted session‘ does not “kick out” user automatically. It just blocks new user who does not have the ‘restrict session’ privilege to login, similar to ‘ALTER SYSTEM QUIESCE RESTRICTED’, which blocks new non-dba users. However, I can scarely see the instances when non-dba users have been granted to the ‘restrict session’ privilege.

ALTER SYSTEM QUIESCE RESTRICTED;

Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active. For example, If a user issues a SQL query in an attempt to force an inactive session to become active, the query will appear to be hung. When the database is later unquiesced, the session is resumed, and the blocked action is processed.

The following statement restores the database to normal operation:

ALTER SYSTEM UNQUIESCE;

All non-DBA activity is allowed to proceed. In an Oracle Real Application Clusters environment, this statement is not required to be issued from the same session, or even the same instance, as that which quiesced the database. If the session issuing the ALTER SYSTEM UNQUIESCE statement terminates abnormally, then the Oracle Database server ensures that the unquiesce operation completes.

The ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance. The column values has one of these values:

• NORMAL: Normal unquiesced state.
• QUIESCING: Being quiesced, but some non-DBA sessions are still active.
• QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

Kill all oracle sessions

rem ##################################################################
rem Filename:   kill_all_oracle_sessions.sql
rem Purpose:    Kill all oracle sessions for a user
rem Date:       23-Feb-2011
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################

set pagesize 0
spool kill_all.sql
select 'alter system kill session '''||sess.sid||', '||sess.serial#||''';' from v$session sess where username = '<User Name>';
spool off
spool kill_all.log
@kill_all.sql
spool off

To run the script connect oracle as a system user then run the file kill_all_oracle_sessions.sql

@kill_all_oracle_sessions.sql

Monitoring SGA (Free Memory) Using v$sgastat

In this article, we will discuss how to get monitor free memory in System Global Area (SGA) in Oracle 10g. The System Global Area (SGA) Memory Structures in data. The SGA is read/write.  A system global area is a group of shared memory areas that dedicated to an Oracle database instance. For example Large Pool, Shared Pool, Java Pool etc.

SQL> SHOW SGA;
Total System Global Area  608174080 bytes
Fixed Size                  2006160 bytes
Variable Size             176161648 bytes
Database Buffers          423624704 bytes
Redo Buffers                6381568 bytes

Let’s use v$sgastat parameter in this article. The v$sgastat parameter displays detailed information on the system global area (SGA).

SQL> DESC v$sgastat;
Name                                      Null?    Type
----------------------------------------- -------- ----------------
POOL                                               VARCHAR2(12)
NAME                                               VARCHAR2(26)
BYTES                                              NUMBER

Let’s have a short explanation of all column of the v$sgastat parameter:

POOL: Designates the pool in which the memory in NAME resides:

• Shared Pool: Memory is allocated from the shared pool.

• Large Pool: Memory is allocated from the large pool.

• Java Pool: Memory is allocated from the Java pool.

• Streams Pool: Memory is allocated from the Streams pool.

NAME: The name column means system global area component name.

BYTES: The memory size in bytes.

Now we will use a simple SQL to see the free memory in SGA.

SQL> Select POOL, Round(bytes/1024/1024,0) Free_Memory_In_MB
  2  From V$sgastat
  3  Where Name Like '%free memory%';
POOL         FREE_MEMORY_IN_MB
------------ -----------------
shared pool                 22
large pool                   3
java pool                    4
streams pool                 8

Step by Step Oracle Enterprise Manager Configuration

In this article, we will discuss monitoring configuration in Oracle Enterprise Manager.

Go to http://<hostname&gt;:<port-number>/em then specifying the system username and password after that we will click Login button.

N.B: Make sure enterprise manager console is running. to check it, issue following command.

$ emctl status dbconsole

The home page is displaying. After that we will click Monitoring Configuration under the Related Links.

The Configure Database Instance: Properties page is displaying. We will enter the correct password in the Monitor Password field and click Test Connection.

The Database Instance Configuration Result page is displaying. After some time next page is displaying.

The Configure Database Instance: Review page is displaying. We will click Submit button.

The Database Instance Configuration Result page is displaying. We will click OK button, after that we will go to Home page of the Oracle Database.

 

2010 in review

The stats helper monkeys at WordPress.com mulled over how this blog did in 2010, and here’s a high level summary of its overall blog health:

The Blog-Health-o-Meter™ reads Fresher than ever.

Crunchy numbers

The average container ship can carry about 4,500 containers. This blog was viewed about 17,000 times in 2010. If each view were a shipping container, your blog would have filled about 4 fully loaded ships.

 

In 2010, there were 32 new posts, growing the total archive of this blog to 67 posts. There were 12 pictures uploaded, taking up a total of 3mb. That’s about a picture per month.

The busiest day of the year was October 13th with 164 views. The most popular post that day was Use oradim in Linux.

Where did they come from?

The top referring sites in 2010 were google.co.in, en.wordpress.com, google.com, facebook.com, and yandex.ru.

Some visitors came searching, mostly for pl/sql lock timer, oracle parameterized view, oradim linux, oracle 11g oradim unix, and db_writer_processes.

Attractions in 2010

These are the posts and pages that got the most views in 2010.

1

Use oradim in Linux November 2009

2

Oracle 10g Enterprise Manager January 2010

3

Parameterized View in Oracle April 2010
1 comment

4

ER Diagram of HR System November 2009

5

Partition Table In Oracle January 2010

Shared Memory Realm does not exist

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Solution :

check ORACLE_SID has value if not exist then set ORACLE_SID

For Windows Environment

set ORACLE_SID=orcl 
echo %ORACLE_SID% 
orcl 

For Linux Environment

$ export ORACLE_SID=orcl

Now You need to Manually startup the database

sqlplus "sys/password as sysdba" 
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area  574619648 bytes
Fixed Size                  1250236 bytes
Variable Size             192941124 bytes
Database Buffers          377487360 bytes
Redo Buffers                2940928 bytes
Database mounted.
Database opened.
SQL> exit

Basic Concept of Performance Tuning in Oracle

Scope of Performance Tuning

There is four main area for Performance Tuning.

1. SQL Tuning          – Responsibility of the Developer
2. Database Tuning     – Responsibility of the Database Administrator
3. System Tuning       – Responsibility of the System Administrator
4. Network Tuning      – Responsibility of the Network / LAN / WAN Administrator.

SQL Tuning

1. Find the problem to a single SQL

You may be lucky and know already the exact SQL causing the problem. If so, move straight on to the second step. Otherwise, click on the link above for help on finding the problem SQL.

2. Analyze the SQL to determine the nature of the problem

Most performance problems are quite common and easy to fix. This section will describe some of these and how to spot them, and then go on to describe a more general analysis method.

3. Fix the problem.

Almost every performance problem has a solution; it’s just that some are more complex than others. In order of increasing complexity and expense, such fixes include:

• Analyze the underlying table to give Oracle’s Cost Based Optimizer the information it needs to resolve the SQL efficiently.
• Add one or more hints to the SQL to encourage or discourage certain execution plans.
• Minor changes to the SQL to encourage or discourage certain execution plans.
• Restructure a poorly designed SQL that cannot be resolved efficiently.
• Alter the underlying infrastructure. eg. Add or change (or even remove!) indexes; introduce clusters, partitions or index-organised tables; denormalize tables; use materialized views. Note that these actions venture outside the scope of this document, and should only be performed with the prior permission of (and preferably assistance from) the DBA and/or System Architect.
• Refer the problem to the database administrator. Possible solutions here would include tuning the Oracle instance, restructuring or moving tablespaces, or archiving old data.
• Refer the problem to the System Adminstrator. Possible solutions may include reconfiguration of existing hardware, or acquisition of new hardware.

Database Tuning

For optimum performance an Oracle database should be regularly tuned. Only tune a database after it has been up and running for a little while.

• Tuning the cache hit ratio
• Tuning the library cache
• Tuning the log buffer
• Tuning buffer cache hit ratio
• Tuning sorts
• Tuning rollback segments
• Identifying missing indexes
• Identifying index fragmentation
• Identifying free list contention
• Identify significant reparsing of SQL
• Reducing database fragmentation
• Rebuilding indexes
• Reduce thrashing or poor system performance (or how to un-tune oracle?!)

System Tuning(Operating System)

Tune your operating system according to your operating system documentation. For Windows platforms, the default settings are usually sufficient. However, the Solaris and Linux platforms usually need to be tuned appropriately. The following sections describe issues related to operating system performance:

• Basic OS Tuning Concepts
• Solaris Tuning Parameters
• Linux Tuning Parameters
• HP-UX Tuning Parameters
• Windows Tuning Parameters
• Other Operating System Tuning Information

Details

Network Tuning

Network tuning is the performance optimization and tuning of SQL*Net based on an arbitrary UNP which could be TCP/IP, SPX/IP or DECnet. SQL*Net performance can be maximized by synchronization with tunable parameters of the UNP, for example, buffer size.SQL*Net transaction performance can be divided into components of connect time and query time, where
Total SQL*Net (Net8) Transaction Time = Connect Time + Query Time 

Connect time can be maximized by calibration of tunable parameters of SQL*Net and the UNP when designing and implementing networks.

SQL*Net Performance
For this discussion, SQL*Net performance and tuning analysis is based on two categories:

• SQL*Net performance
• SQL*Net tuning

Details1 

Details2

DB_WRITER_PROCESSES

Setting the DB_WRITER_PROCESSES parameter (Background process DBWR) to a value greater than one is supported starting with the Oracle 8.0 release.

Multiple DBWR processes are mainly used to simulate asynchronous I/O when the operating system does not support it.  Since Windows NT and Windows 2000 use asynchronous I/O by default, using multiple DBWR processes may not necessarily improve performance.  Increasing this parameter is also likely to have minimal effect on single-CPU systems.  Increasing this parameter could, in fact, reduce performance on systems where the CPU’s are already over burdened.  In cases where the main performance bottleneck is that a single DBWR process cannot keep up with the work load, then increasing the value for DB_WRITER_PROCESSES may improve performance.

When increasing DB_WRITER_PROCESSES it may also be necessary to increase the DB_BLOCK_LRU_LATCHES parameter, as each DBWR process requires an LRU latch.

How to change db_writer_processes parameter

SQL> show parameter db_writer_processes
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_writer_processes                  integer     1

SQL> alter system set db_writer_processes=2 scope=spfile sid='*';

System altered.

Now restart the database to take the change effect.

 
SQL> show parameter db_writer_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_writer_processes                  integer     2

“The number of DBWR should be two times the number of CPUs (vary 1 or 2 depending on idle state of CPUs during production/test run)”

DB_WRITER_PROCESSES = 2 * number of CPU

Default value of DB_WRITER_PROCESSES is 1 or CPU_COUNT / 8, whichever is greater. If the number of processor groups is less than 36 but greater than the number of DB writer processes, then the number of DB writer processes is adjusted to be a multiple of the number of processor groups. If the number of DB writer processes is greater than or equal to the number of processor groups, then there is no adjustment.

Range of value in Oracle 10g- 1 to 20 but in Oracle 11g – 1 to 36.

Convert Number to Word

SQL> select to_char(to_date(5358452,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(5358452,'J'),'JSP')
----------------------------------------------------------------------
FIVE MILLION THREE HUNDRED FIFTY-EIGHT THOUSAND FOUR HUNDRED FIFTY-TWO

Restriction: It can conversion from 1 to 5373484 and it can not conversion decimal number.

A function for convert number to word

create or replace
function spell_number( p_number in number )
   return varchar2
   as
       type myArray is table of varchar2(255);
       l_str    myArray := myArray( '',
                              ' thousand '    , ' million ',
                              ' billion '     , ' trillion ',
                              ' quadrillion ' , ' quintillion ',
                              ' dont read '   , ' septillion ',
                              ' octillion '   , ' nonillion ',
                              ' decillion '   , ' undecillion ',
                              ' duodecillion ');

       l_num   varchar2(50) default trunc( p_number );
       l_return varchar2(4000);
   begin
       for i in 1 .. l_str.count
       loop
          exit when l_num is null;

           if ( substr(l_num, length(l_num)-2, 3) <> 0 )
           then
              l_return := to_char(
                              to_date(
                               substr(l_num, length(l_num)-2, 3),
                                 'J' ),
                          'Jsp' ) || l_str(i) || l_return;
           end if;
           l_num := substr( l_num, 1, length(l_num)-3 );
       end loop;

       return l_return;
   end;
 /

Example

SQL> Select spell_number( 1234567899 ) From dual;

SPELL_NUMBER(1234567899)
------------------------
One billion Two Hundred Thirty-Four million 
Five Hundred Sixty-Seven thousand Eight Hundred 
Ninety-Nine

Oracle & Iron Man – Great Combination

http://www.oracle.com/us/ironman2/index.html

Some useful Linux Command for DBA

ls: List files
cp: Copy files
mv: Move and rename files
mkdir: Make a directory
alias: Define command macros
rm: Remove files and directories
more: Page through output
head: Show beginning of file contents
tail: Show end of file contents
df: Display filesystem space usage
du: Display directory disk space usage
cat: Show and concatenate files
grep: Search for patterns in files
chmod: Change permissions of files
chown: Change owner of files
zip: Compress and package files together
gedit: A WYSIWYG text editor
export: Make environment settings global
ps: List running processes
touch: Change file time stamps
id: Show information about the current user
sudo: Execute commands as another user

Standard Measurement Tools

•    Top Resource Consumers: top

•    System Activity Reporter: sar

•    Virtual Memory Statistics: vmstat

•    I/O Statistics: iostat

•    System Log files: /var/log/messages

Linux Tools

•    X-based tools: xosview

•    The /proc virtual file system

•    Free and used memory: free

Tools for monitoring and tuning CPU include:

•    top

•    pstree and free

•    vmstat

•    Syntax: vmstat <interval> <count>

•    Example : # vmstat 2 5

•    mpstat –p All

•    sar –u

•    Syntax: #sar -B <frequency> <count>

#sar -R <frequency> <count>

•    Example : #sar -B 2 3

#sar -R 2 3

•    xosview

•    xload

•    System Monitor

Measuring Total Memory

•    top

•    free

•    cat /proc/meminfo

Monitoring and Tuning I/O

•    /proc file system
•    sar -d
•    I/O statistics by device [iostat –d]
Syntax : iostat -d <interval> <count>
Eample : #iostat -d 2 2
•    I/O activity by partition
iostat –d -p <interval> <count>
•    vmstat
•    xosview

How to use trigger to check database constant

We can use trigger to check database constant and customize error message. For example we have two tables name Department and Employee. I have written two trigger on Update and Delete on Department table, which will responsible to check referential integrity constraint and customize the error message.

Creating Tables Department and Employee

CREATE TABLE DEPARTMENT
  (
    Department_Id          Number(*,0) Not Null Enable,
    Department_Name        Varchar2(45 Byte) Not Null Enable,
    Department_Description Varchar2(500 Byte),
    Primary Key (Department_Id)
  );

CREATE TABLE EMPLOYEE
  (
    Employee_Name        Varchar2(45 Byte) Not Null Enable,
    Employee_Ssn         Varchar2(45 Byte) Not Null Enable,
    Employee_Phone       Varchar2(45 Byte) Not Null Enable,
    Employee_Cellular    Varchar2(45 Byte) Not Null Enable,
    Employee_Description Varchar2(500 Byte),
    Department_Id        Number(*,0) Not Null Enable,
    Primary Key (Employee_Ssn) ,
    Foreign Key (Department_Id) References Department (Department_Id) Enable
  );

Insert some data on Department and Employee Table

Insert Into Department (Department_Id, Department_Name, Department_Description)
Values ('1001', 'Tecnical', 'Tecnical Department');
Insert Into Department (Department_Id, Department_Name, Department_Description)
Values ('1002', 'Merketing', 'Merketing Department');

Insert Into EMPLOYEE (Employee_Name, Employee_Ssn, Employee_Phone, Employee_Cellular, Department_Id)
Values ('Tamim', '100001', '880175307713', '880175307713', '1001');

Trigger on Delete of Department

Create Or Replace Trigger Td_Department
  After Delete on Department
  FOR EACH row
    DECLARE numrows INTEGER;
  BEGIN
    SELECT COUNT(*) INTO numrows
    FROM Employee
    WHERE  Employee.Department_ID = :old.Department_ID;
    IF (numrows> 0) THEN
      raise_application_error( -20001, 'Cannot DELETE Department because Employee exists.' );
    END IF;
  END;
  /

Test a Delete SQL Statement on Department Table

  Delete
  From Department
  Where Department_Id = 1001 

  /*
  SQL Error: ORA-20001: Cannot DELETE Department because Employee exists.
  */

Trigger on Update of Department

Create Or Replace Trigger Tu_Department
  After Update On Department
  For Each Row
    Declare Numrows Integer;
  Begin
    IF (:old.Department_ID <> :new.Department_ID) THEN
      Select Count(*) Into Numrows   
      FROM Employee
      Where Employee.Department_Id = :Old.Department_Id;
      IF (numrows > 0) THEN
        raise_application_error( -20005, 'Cannot UPDATE Department because Employee exists.' );
      END IF;
    END IF;
  END;
  /

Test a Update SQL Statement on Department Table

  Update Department
  Set Department_Id = 1003
  Where Department_Id = 1001 

  /*
  SQL Error: ORA-20005: Cannot UPDATE Department because Employee exists.
  */

Auditing Table Data using Trigger

Create New User in Oracle

CREATE User <UserName> IDENTIFIED BY <Passsword>;

Grant User necessary privileges

GRANT CREATE session TO <username>;

GRANT CREATE TABLE TO <username>;

GRANT CREATE TRIGGER TO <username>;

ALTER USER <username> QUOTA UNLIMITED ON <TablespaceName>

Create a Transactional Table

CREATE TABLE AD_DURATIONS
  (
      Ad_Duration_Cn    Varchar2(10 Byte)     Not Null Enable,
      Ad_Start_Date     Date Not              Null Enable,
      Ad_End_Date       Date Not              Null Enable,
      Modified_By       Varchar2(10 Byte),
      Modified_Date     Date                  Default Sysdate,
      Constraint CJ_AD_DURATIONS_PK Primary Key (AD_DURATION_CN)
  );

Insert value in the AD_DURATIONS Table

Insert Into Ad_Durations (Ad_Duration_Cn, Ad_Start_Date,
                              Ad_End_Date, Modified_By)
Values ('10001', To_Date('26-JUL-10', 'DD-MON-RR'),
                              To_Date('30-JUL-10', 'DD-MON-RR'), 'Tamim');

Insert Into Ad_Durations (Ad_Duration_Cn, Ad_Start_Date,
                              Ad_End_Date, Modified_By)
Values ('10002', To_Date('31-JUL-10', 'DD-MON-RR'),
                              To_Date('05-Aug-10', 'DD-MON-RR'), 'Khan');

Create a Log Table for AD_DURATIONS Data

Here Modified_By, Modified_Date and Action is Audit Column.

CREATE TABLE AD_DURATIONS_LOG
  (
      Ad_Duration_Cn    Varchar2(10 Byte),
      Ad_Start_Date     Date,
      Ad_End_Date       Date,
      Modified_By       Varchar2(20 Byte),
      Modified_Date     Timestamp (6),
      Action            Varchar2(20 Byte)
  );

Create a Trigger on AD_DURATIONS table

Create Or Replace TRIGGER AD_DURATIONS_LOG
AFTER DELETE OR UPDATE ON AD_DURATIONS
FOR EACH ROW
Begin
  If Updating Then
      Insert Into Ad_Durations_Log(Ad_Duration_Cn, Ad_Start_Date,
                  Ad_End_Date, Modified_By,Modified_Date,Action)
      Values (:Old.Ad_Duration_Cn, :Old.Ad_Start_Date,:Old.Ad_End_Date,
                  :Old.Modified_By,:Old.Modified_Date,'Update');
  Elsif Deleting Then
      Insert Into Ad_Durations_Log(Ad_Duration_Cn, Ad_Start_Date,
                  Ad_End_Date, Modified_By,Modified_Date,Action)
      Values (:Old.Ad_Duration_Cn, :Old.Ad_Start_Date, :Old.Ad_End_Date,
                  :Old.Modified_By,:Old.Modified_Date,'Delete');
  End If;
END;

Update on AD_DURATIONS Table

Update Ad_Durations
Set Ad_End_Date = To_Date('31-JUL-10', 'DD-MON-RR')
Where Ad_Duration_Cn = 10001;

Update from AD_DURATIONS Table

Delete From Ad_Durations
Where Ad_Duration_Cn = 10002;

Now Selecting data from AD_DURATIONS_LOG Table.

Select * From Ad_Durations_Log;

CONTROLFILE AUTO BACKUP

RMAN is one of the very useful utility provided by Oracle for backup and recovery Purpose. Oracle online backups were introduced with Version 6, where tablespace must be kept in backup mode in order to take online backups.

RMAN has following default parameters and its default values:

RMAN> show all;
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 'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S
NCFORCL.ORA'; # default

Or

RMAN>  SHOW CONTROLFILE AUTOBACKUP;

RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;

By default CONTROLFILE AUTOBACKUP is OFF. But it is strongly recommended enabling CONTROLFILE AUTOBACKUP ON.

Advantage:

• RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.
• Restore the RMAN repository contained in the control file when the control file is lost and you have no recovery catalog. You do not need a recovery catalog or target database control file to restore the control file auto backup.
• Control file auto backup can keep track of add a data file, resize, increase/decrease the size of data files or etc.
• If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current server parameter file (if used to start up the database) in one of two circumstances: when a successful backup must be recorded in the RMAN repository, and when a structural change to the database affects the contents of the control file which therefore must be backed up

To perform control file auto backup on issue the following command

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

ORA-19809: limit exceeded for recovery files

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 28 10:25:39 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from tab;
select * from tab
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
 

Shutdown the database first

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Start the database

SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  1250356 bytes
Variable Size             180358092 bytes
Database Buffers          411041792 bytes
Redo Buffers                7135232 bytes
Database mounted.
ORA-16038: log 2 sequence# 135 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1:
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'

Problem: The value db_recovery_file_dest_size is not enough for generate archive log

          
SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
db_recovery_file_dest_size           big integer 2G

Solution: Increase the value of db_recovery_file_dest_size following way.

 
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;

System altered.

Now open the database.

SQL> alter database open;

Database altered.

Cancel-Based Recovery

A cancel-based recovery is a type of user-managed incomplete recovery that is performed by specifying the UNTIL CANCEL clause with the RECOVER command (a SQL*Plus command that is used to recover a database). The UNTIL CANCEL clause specifies that the recovery process will continue until the user manually cancels the recovery process issuing the CANCEL command.

In a cancel-based incomplete recovery, the recovery process proceeds by prompting the user with the suggested archived redo log files’ names. The recovery process stops when the user specifies CANCEL instead of specifying an archived redo log file’s name. If the user does not specify CANCEL, the recovery process automatically stops when all the archived redo log files have been applied to the database.

A cancel-based recovery is usually performed when the requirement is to recover up to a particular archived redo log file. For example, if one of the archived redo log files required for the complete recovery is corrupt or missing, the only option is to recover up to the missing archived redo log file.

Recovery Scenario	Preferred Recovery Method
Some important table is dropped	Oracle Time-based Recovery based Recovery
Some bad data is committed in a table	Oracle Time-based Recovery based Recovery
Lost archive log results in failure of  complete recovery	Oracle Cancel-based Recovery
Backup control file does not know anything about the arhivelogs	Oracle Cancel-based Recovery
All unarchived Redo Logs and datafiles are lost	Oracle Cancel-based Recovery
Recovery is needed up to a specific archived log file	Oracle Cancel-based Recovery
Recovery through Resetlogs when media failure occurs before backup completion.	Oracle Change-based Recovery
A Tablespace is dropped	Recovery with a backup control file

1. Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:

sqlplus '/ AS SYSDBA'

2. Start a new instance and mount the database:

STARTUP MOUNT

3. Begin cancel-based recovery by issuing the following command:

RECOVER DATABASE UNTIL CANCEL

If you are using a backup control file with this incomplete recovery, then specify the USING BACKUP CONTROLFILE option in the RECOVER command.

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

4. Oracle applies the necessary redo log files to reconstruct the restored datafiles. Oracle supplies the name it expects to find from LOG_ARCHIVE_DEST_1 and requests you to stop or proceed with applying the log file. Note that if the control file is a backup, then you must supply the names of the online logs if you want to apply the changes in these logs.

5. Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:

CANCEL

Oracle returns a message indicating whether recovery is successful. Note that if you cancel recovery before all the datafiles have been recovered to a consistent SCN and then try to open the database, you will get an ORA-1113 error if more recovery is necessary for the file. You can query V$RECOVER_FILE to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery.

6. Open the database in RESETLOGS mode. You must always reset the online logs after incomplete recovery or recovery with a backup control file. For example, enter:

ALTER DATABASE OPEN RESETLOGS;

For More Detail please visit here

SQL Tuning Advisor in Oracle 10g

In Oracle 10g, the SQL tuning process has been automated by introducing a new manageability feature call Automatic SQL Tuning. This feature is designed to work equally well for OLTP and Data Warehouse workloads. Unlike existing tools, automatic SQL Tuning id preformed in the database server by the Oracle query optimizer itself, running in special mode. When running in this mode, the Oracle query optimizer is referred to us the Automatic Tuning Optimizer.

Create a Table to Test a SQL

Create Table TestEmp
       As Select * from Scott.emp;

Write a SQL that you need to tune.

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM TESTEMP
ORDER by SAL;

Execute DBMS_STATS Package for Gather Table Satieties

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(
  3    ownname          => 'TEST', 
  4    tabname          => 'TESTEMP',
  5    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  6    method_opt       => 'FOR ALL COLUMNS SIZE AUTO'
  7    );
  8  END;
  9  /
PL/SQL procedure successfully completed.

How to use dbms_sqltune Package

CREATE OR REPLACE PROCEDURE P_SQL_TUNING (
        p_sql CLOB
  )
AS
  v_task VARCHAR2(30);
BEGIN
  /* Drop the task in case we are re-running... */
  BEGIN
      DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'sql_tuning_task');
      EXCEPTION
         WHEN OTHERS THEN -- ORA-13605
            NULL;
  END;
 
  /* Create a SQL Tuning task for our SQL... */
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                   sql_text    => p_sql,
                   time_limit  => 1,
                   scope       => 'COMPREHENSIVE',
                   task_name   => 'sql_tuning_task',
                   description => 'Demo of DBMS_SQLTUNE'
                   );          
  /* Execute the task... */
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sql_tuning_task');
  
  /* We want to run this again... */
  ROLLBACK;
END P_SQL_TUNING;

Execute p_Sql_Tuning Procedure

SQL> exec P_SQL_TUNING('SELECT emp.EMPNO, emp.ENAME, emp.JOB,
                        emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO
                        FROM TESTEMP emp,TESTEMP empmgr
                        Where emp.empno=empmgr.mgr
                        ORDER by SAL'
                       );
 
PL/SQL procedure successfully completed.

Tuning Result

SQL> set long 80000
SQL> col recs format a90
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task') AS result
     FROM   DUAL;
 
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sql_tuning_task
Tuning Task Owner                 : TEST
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1
Completion Status                 : COMPLETED
Started at                        : 06/14/2010 12:24:18
Completed at                      : 06/14/2010 12:24:18
Number of Index Findings          : 1
 
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID     : grfz43h6vdf3d
SQL Text   : SELECT emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE,
             emp.SAL, emp.COMM, emp.DEPTNO FROM TESTEMP emp,TESTEMP empmgr
             Where emp.empno=empmgr.mgr ORDER by SAL
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.
 
  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index TEST.IDX$$_041C0001 on TEST.TESTEMP('MGR');
 
  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original
-----------
Plan hash value: 719480185
 
-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    13 |   533 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |         |    13 |   533 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN         |         |    13 |   533 |     7  (15)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TESTEMP |    13 |    52 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TESTEMP |    14 |   518 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP"."EMPNO"="EMPMGR"."MGR")
   3 - filter("EMPMGR"."MGR" IS NOT NULL)
 
2- Using New Indices
--------------------
Plan hash value: 2840960280
 
--------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |    13 |   533 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |                |    13 |   533 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS      |                |    13 |   533 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TESTEMP        |    14 |   518 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | IDX$$_041C0001 |     1 |     4 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."EMPNO"="EMPMGR"."MGR")
       filter("EMPMGR"."MGR" IS NOT NULL)

Clean up

The DBMS_SQLTUNE package provides APIs to remove the objects we created as follows.

SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tuning_task');
 
PL/SQL procedure successfully completed.

Note:

The Following error message means user have no permission to user Advisor privilege

ORA-13616: The current user <User Name> has not been granted the ADVISOR privilege.

Solutions:

Grant ADVISOR to <User Name>;

How to Clear Buffer Cash and Shared Pool in Oracle 10g

Flush buffer cache to clear recently cached data blocks

SQL> alter system flush buffer_cache;
System altered.

Flush shared pool to clear recently created SQL parse and execution plan

SQL> alter system flush shared_pool;
System altered.

What is the Difference between “clear buffer” and “alter system flush buffer_cache”

Clear Buffer SQL* Plus Command which is use to clear the SQL*Plus screen and the screen buffer.

Syntax:
CLEAR {BREAKS|BUFFER|COLUMNS|COMPUTES|SCREEN|SQL TIMING}

SQL> clear buffer
buffer cleared

Example:
SQL> Select empno, ename from Scott.emp Where ename='KING';
     EMPNO ENAME
---------- ----------
      7839 KING
SQL> list;
  1* Select empno, ename from Scott.emp Where ename='KING'
SQL> clear buffer;
buffer cleared
SQL> list
SP2-0223: No lines in SQL buffer.

But alter system flush buffer_cache use to clear data buffer cash in SGA.

Full Database Backup Script

rem ######################################################################
rem Filename:   FullDBBackup.sql
rem Purpose:    Generate script to do a simple on-line database backup.
rem Notes:      Adjust the copy_cmnd and copy_dest variables and run from
rem             sqlplus. Uncomment last few lines to do the actual backup.
rem ######################################################################
set serveroutput on
set trimspool on
set line 500
set head off
set feed off
spool backup.cmd
declare
  copy_cmnd constant varchar2(30) := 'cp';            -- Use "ocopy" for NT
  copy_dest constant varchar2(30) := '/u02/backup/';  -- C:\BACKUP\ for NT
  dbname  varchar2(30);
  logmode varchar2(30);
begin
  select name, log_mode
  into   dbname, logmode
  from   sys.v_$database;

  if logmode <> 'ARCHIVELOG' then
    raise_application_error(-20000, 'ERROR: Database must be in ARCHIVELOG mode!!!');
    return;
  end if;
  dbms_output.put_line('spool backup.'||dbname||'.'|| to_char(sysdate, 'ddMonyy')||'.log');
  --Loop through tablespaces
  for c1 in (select tablespace_name ts from sys.dba_tablespaces where CONTENTS <> 'TEMPORARY')
  loop
    dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');
    --Loop through tablespaces' data files
    for c2 in (select file_name fil
               from   sys.dba_data_files
               where  tablespace_name = c1.ts)
      loop
        dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);
      end loop;
      dbms_output.put_line('alter tablespace '||c1.ts||' end backup;');
    end loop;
  -- Backup controlfile and switch logfiles
  dbms_output.put_line('alter database backup controlfile to trace;');
  dbms_output.put_line('alter database backup controlfile to '||''''||
  copy_dest||'control.'||dbname||'.'||
  to_char(sysdate,'DDMonYYHH24MI')||''''||';');
  dbms_output.put_line('alter system switch logfile;');
  dbms_output.put_line('spool off');
end;
/
spool off
set head on
set feed on
set serveroutput off
-- Unremark/uncomment the following line to run the backup script
-- @backup.cmd
-- exit

Grant Select/Execute on Database Objects

rem ##################################################################
rem Filename:   GrantSelectOnView.sql
rem Purpose:    Grant Select on Tables/View to the role
rem Date:       09-May-2010
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################
SELECT 'Grant Select on  '||object_name || ' to '||'<Role/User Name>' || ';'
FROM ALL_OBJECTS
Where object_type = '<VIEW/TABLE>'
  and OWNER = '<USER_NAME>';

rem ##################################################################
rem Filename:   GrantExecuteOnPackage.sql
rem Purpose:    Grant Execute on Package/Function/Procedure to the Role
rem Date:       09-May-2010
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################
SELECT 'Grant Execute on  '||object_name || ' to '||'<Role/User Name>' || ';'
FROM ALL_OBJECTS
Where object_type = '<PACKAGE/FUNCTION/PROCEDURE>'
  and OWNER = '<USER_NAME>';

Create Public Synonym

rem ##################################################################
rem Filename:   CreatePublicSynonym.sql
rem Purpose:    Create Public Synonym for a User Objects
rem Date:       09-May-2010
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################

SELECT 'Create or replace public synonym  '||object_name || ' for '
                               || OWNER || '.' || object_name || ';'
FROM ALL_OBJECTS
Where object_type  in ('VIEW','PACKAGE')
and OWNER = '<USER_NAME>';

How delete duplicate rows from a Table

Creating Table and Insert some sample Data

CREATE TABLE PROC_DATA_LOG
  (
    CN      VARCHAR2(20 BYTE),
    A_DATA  VARCHAR2(400 BYTE)
  );
Select * from PROC_DATA_LOG;

CN                   A_DATA                                          
-------------------- ------------
1                    Test Data 1
1                    Test Data 1
2                    Test Data 2
3                    Test Data 3
3                    Test Data 3
4                    Test Data 4
5                    Test Data 5

SQL to delete the duplicate rows

DELETE FROM PROC_DATA_LOG  
WHERE ROWID NOT IN (
                    SELECT MAX (ROWID)
                    FROM PROC_DATA_LOG
                    GROUP BY CN
                   );

All duplicate row base on CN is deleted

Select * from PROC_DATA_LOG;

CN                   A_DATA                                                   
-------------------- ------------
1                    Test Data 1
2                    Test Data 2
3                    Test Data 3
4                    Test Data 4
5                    Test Data 5

Delete Duplicate Rows using Analytic functions

DELETE FROM PROC_DATA_LOG
WHERE ROWID IN ( 
        SELECT ROWID
        FROM (
              SELECT
              ROW_NUMBER() OVER (PARTITION BY CN ORDER BY CN) rnk
              FROM   PROC_DATA_LOG
             )
        WHERE rnk>1
        );

Parameterized View in Oracle

There is nothing like parameterize view in Oracle, but you can use parameterize view in oracle following way. There is two possible option, You can use the User Environment variable to manage a session variable (dbms_application_info package), another way is write a package to get and set value parameterize view in the global variable or sys_context, later on a view can use the value from, which called so called parameterized view.

Example:

Creating Table

CREATE TABLE APPLICATIONS (
    APPLICATION_CN             VARCHAR2 (10 BYTE) NOT NULL ENABLE,
    APPLICATION_NAME           VARCHAR2 (20 BYTE) NOT NULL ENABLE,
    APPLICATION_DESCRIPTION    VARCHAR2 (200 BYTE),
    APPLICATION_STATUS         VARCHAR2 (5 BYTE) DEFAULT 'True'
  );

Inserting Data in to the table

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10001','ERP','Enterprise resource Planning ','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10002','Oracle ERP','Oracle Enterprise resource Planning ','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10003','Web Base Application','Web Base Application','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10004','Desktop Application','Desktop Application','True');

Commit;

Create a Package

create or replace
PACKAGE TestParam_VW_PKG AS
 PROCEDURE P_SET_APPLICATION_CN (  
    p_Application_CN            VARCHAR2
  );

  FUNCTION F_GET_APPLICATION_CN RETURN VARCHAR2;
END TestParam_VW_PKG;

Create a Package body for Get and Set operation

 
create or replace
PACKAGE BODY TestParam_VW_PKG AS
  g_Application_CN            VARCHAR2(10);
  PROCEDURE P_SET_APPLICATION_CN (  
    p_Application_CN          VARCHAR2
  ) AS
  BEGIN
    g_Application_CN := p_Application_CN;
  END;

  FUNCTION F_GET_APPLICATION_CN RETURN VARCHAR2
  IS
  BEGIN
    RETURN g_Application_CN;
  END;
END TestParam_VW_PKG;

Create a View base on the Packege

CREATE OR REPLACE FORCE VIEW APPLICATION_VW ("APPLICATION_CN", "APPLICATION_NAME", "APPLICATION_DESCRIPTION", "APPLICATION_STATUS")
AS
  SELECT APPLICATION_CN,
    APPLICATION_NAME,
    APPLICATION_DESCRIPTION,
    APPLICATION_STATUS
  FROM  APPLICATIONS
  WHERE APPLICATION_CN = TestParam_VW_PKG.F_GET_APPLICATION_CN;

How to use the view

SQL> Exec TESTPARAM_VW_PKG.P_SET_APPLICATION_CN('10001');

PL/SQL procedure successfully completed.

SQL> SELECT APPLICATION_CN, APPLICATION_NAME, APPLICATION_DESCRIPTION, APPLICATION_STATUS
FROM APPLICATION_VW ;
APPLICATION_CN    APPLICATION_NAME     APPLICATION_DESCRIPTION        APPLICATION_STATUS
---------------   -------------------- -----------------------------  ------------------
10001             ERP                  Enterprise resource Planning   True

Database system development life cycle

Database planning is the management activities that allow the stages of the database system development lifecycle to be realized as efficiently and effectively as possible.

System Definition involves identifying the scope and boundaries of the database system, including its major user views. A user view can represent a job role or business application area.

Requirements collection and analysis is the process of collecting and analyzing information about the organization that is to be supported by the database system, and using this information to identify the requirements for the new system.

Requirements collection and analysis is a preliminary stage to database design. The amount of data gathered depends on the nature of the problem and the policies of the organization. Identifying the required functionality for a database system is a critical activity, as systems with inadequate or incomplete functionality will annoy the users, and may lead to rejection or underutilization of the system. However, excessive functionality can also be problematic as it can overcomplicate a system, making it difficult to implement, maintain, use, and learn.

Database design is the process of creating a design that will support the organization’s mission statement and mission objectives for the required database system. This stage includes the logical and physical design of the database.

DBMS selection

The aim of DBMS selection is to select a system that meets the current and future requirements of the organization, balanced against costs that include the purchase of the DBMS product and any additional software/hardware, and the costs associated with changeover and training.

Database Design Life Cycle

Application design involves designing the user interface and the application programs that use and process the database. This stage involves two main activities: transaction design and user interface design.

• Transaction Design

An action, or series of actions, carried out by a single user or application program that accesses or changes the content of the database.

There are three main types of transactions:

• retrieval transactions
• update transactions
• mixed transactions

• User interface design

In addition to designing how the required functionality is to be achieved, we have to design an appropriate user interface for the database system. This interface should present the required information in a user-friendly way.

Prototyping involves building a working model of the database system, which allows the designers or users to visualize and evaluate the system.

There are two prototyping strategies in common use today:

• Requirements prototyping and
• Evolutionary prototyping.

Requirements prototyping uses a prototype to determine the requirements of a proposed database system and once the requirements are complete the prototype is discarded. While evolutionary prototyping is used for the same purposes, the important difference is that the prototype is not discarded but with further development becomes the working database system.

Implementation is the physical realization of the database and application designs.

Data conversion and loading involves transferring any existing data into the new database and converting any existing applications to run on the new database.

Testing is the process of running the database system with the intent of finding programming errors.

Operational maintenance is the process of monitoring and maintaining the system following installation.

The process of monitoring and maintaining the database system following installation.

In this stage, the database system now moves into a maintenance stage, which involves the following activities:

• Monitoring the performance of the database system. If the performance falls below an acceptable level, the database may need to be tuned or reorganized.

• Maintaining and upgrading the database system (when required). New requirements are incorporated into the database system through the preceding stages of the lifecycle.

For More detail please read the book Database Solutions A step-by-step guide to building databases

A Tutorial on Database Design concept please click here.