Hi frnds i hope this video is help ful to u.
http://www.youtube.com/watch?v=3oGt0vzwcH4&playnext_from=TL&videos=7j-pisMSdjs
Wednesday, July 21, 2010
Monday, July 19, 2010
Ebook for Oracle DBA
I think this book will helpful for those who are learning the Oracle DBA
http://www.mediafire.com/?avynmnw1oxy75d9
http://www.mediafire.com/?avynmnw1oxy75d9
Most Common Questions
1 How do you verify and validate the Datafile which is backed up?
Answers
Answers
In rman list recoverable backup of database
Use RMAN Backup validate database.
It will performs a validation of the datafiles by checking for logical and physical block corruption.
No backupsets are created.
To verify that the datafile is particularly backed up or not.
Sql>select * from v$backup-->This will shows the status of a particular file.
sql>select * from v$recover_file-->This is show whether any file has to be recovered.
Sql>select * from v$backup-->This will shows the status of a particular file.
sql>select * from v$recover_file-->This is show whether any file has to be recovered.
use dbverify to validate the backups.thanks
The DBVERIFY Utility
An OS executable (dbv in $ORACLE_HOME/bin) used to verify datafiles
Works against datafiles live or backup But doesnot work on Backup sets...
Works for datafiles on raw devices but you must give it a range of blocks to look at.
In the output it refers to blocks as pages . Block 5 would be page 5.
RMAN performs its own validation when backing up so dbv is not necessary. It can be used on user backups (aka non-RMAN backups).
If a block is influx then DBV was forced to check it multiple times because it was in use.
2 How much time will it take a hotbackup if the batabase size is 60Gb?
Answers
It depends on the datafiles related to database at OS Level.
Depends upon number of servers and RAM.
Depends on the amount of data inside the database.
Its all upto the Kind of Server and No of server Parameters configured and the type of backup device
Depends! On the I/O at that time...
Hot backup involves many Steps including - taking the particular Tablespace in begin backup mode and then copying the file to Backup directory.
It all depends on the Server Configuration & I/O at that time. Generally the DB will be stored on EMC - where EMC connects to large number of Servers.
3. What is mean live dead lock? How to prevent it.?
Answers
When there r 2 or more process run at a time and want to same resource then if one process take resource and othere process want same resource then there will be dedlock occour.
A deadlock occurs when two or more users are waiting for data locked by each other(Not commited by user01).
To fix this recreate the segment with higher INITTRANS and/or PCTFREE values. This will allow more space in the data blocks for Oracle to allocate more transaction entries (24 bytes at a time) when required
4. What is the difference between recovery and restoring of the database?
Answers
Restoring a database is copying the physical files from a backup medium (disk or tape) to the appropriate file locations for database operation
Recovery is process of updating database files restored from backup w/changes made to the database since backup, typically using redo log files.(Restored backup files + changes = Recovery)
5. What are the different tools available for hot backups?
Answers
Hot backup can be done by either RMAN,User Managed Backups by puting tablespace in backup mode my OEM which does the same as the user managed backup.But the Backup depends upon the size of the database you are using . if the database size in TB the RMAN backup will take more than 10 hours to complete and if the database is critical you can' wait for long to go for so long in this case their are special backup techniques which are given by vendors like TIVOLI and Netbackup they provide BC Vol backup called Business content Volumn Sync which copies a snapshot of the primary data to another place and backsup the database from one SAN to another with in 15 min for 2 TB of database and is the preferable method for big companies.
6 How can I Export 9i data in 8i ? I mean when I Export from 9i and Import in 8i, I face errors. what sould i do?
Answers
Answers
Follow these steps
1) Install Oracle 8i Client
2) Export database from Oracle 8i Client PC
3) Import data into Oracle 8i Database.
1) Install Oracle 8i Client
2) Export database from Oracle 8i Client PC
3) Import data into Oracle 8i Database.
execute catexp.sql of 8 in oracle 9i then take export and then import in 8 database.
7. what is the difference between media recovery and crash recovery..?
Answers
Media recovery is a process to recover database from backup when physical disk failure occure.
Cash recovery is a automated process take care by oracle when instance failure occure.
Cash recovery is a automated process take care by oracle when instance failure occure.
8. what do you mean by MEDIA RECOVERY..?
Answers
Loss of tablespaces, datafiles, controlfile, tables it is media failure and we need to recover such a tablespaces, datafiles, controlfile or tablespace through latest backup
It is MEDIA recovery.
It is MEDIA recovery.
9. what is the disk migration?what isthe steps involved in diskmigration?
Answers
Disk migration is noting but migration of data from one OS dependent database to another Dependent database. The steps involved in this are
- first go to your target database and export all your data into flat files
-next in the destination database during the installation of the database it asks for data source instead of giving the data of the oracle provided give the path of the flat file you exported previously .
10. What are the steps involved in Database Startup ?
Answers
Start Instance, Mount database and open for user access
Step1 :: Read parameter file and verify parameters(Memory Initialization & Background Process started)
Step 2 :: Mount the database and Read the control file information but No verification against this data will be done at this stage.
Step 3:: Open database and before opening verify Control file Contents and synchronize Redo files and data files with the respect to Transactions and then open the database.
Step1 :: Read parameter file and verify parameters(Memory Initialization & Background Process started)
Step 2 :: Mount the database and Read the control file information but No verification against this data will be done at this stage.
Step 3:: Open database and before opening verify Control file Contents and synchronize Redo files and data files with the respect to Transactions and then open the database.
11. What are the steps involved in Database Shutdown?
Answers
Close the Database, Dismount the Database and Shutdown the
Instance.
Different types of Shutdown
There are four types of shutdown
Shutdown Normal - Normal is the default mode, here new user can't logon to the database, but the current user can start the new transaction and work till he wants, the database shutdown only after the last user logs off.
Shutdown Transactional - here new user can't logon to the database, and the current user can't start the new transaction, once the current transaction complets, database shutdown.
Shutdown Immediate - here new user can't logon to the database, user can't start the new transaction, the current transactions rolls back, and database shutdown.
Shutdown Abort - here new user can't logon to the database, user can't start new transaction, the current transaction is not rolled back, database shutdown's.
While shutting down oracle updates data file header and control file with the current checkpoint informanation, Except Shutdown Abort the checkpoint is same in datafile header and control file it means database is consistance and it is clean shutdown. If the checkpoint is not same at both locations then instance recovery required at next startup.
12. What is Restricted Mode of Instance Startup ?
Answers
By starting Instance in restricted mode it will not allow all users to access and only users with restriction privilege will be allowed to access.
This will be done time of make some data changes so that no users should be allowed to access data on time of changes happening
This will be done time of make some data changes so that no users should be allowed to access data on time of changes happening
To Enable Restricted Session
Alter system enable restricted session;
To Disable Restricted Session
Alter system disable restricted session;
To Start the Database in Restricted Mode
STARTUP RESTRICT
Alter system enable restricted session;
To Disable Restricted Session
Alter system disable restricted session;
To Start the Database in Restricted Mode
STARTUP RESTRICT
Answers
Exclusive Mode If the first instance that mounts adatabase does so in exclusive mode, only that Instance canmount the database. Parallel Mode If the first instance that mounts adatabase is started in parallel mode, other instances thatare started in parallel mode can also mount the database.14. What is Full Backup ?
Answers
A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.
15. Can Full Backup be performed when the database is open ?
Answers
Yes we can perform the database backup when database is up and running. We can make it by using user managed backups and RMAN backups.
use we can take full backup while database is open. By using hot backup. Take backup of all tablespaces using hotback and take backup on control file using alter database backup controlfile to trace; and then take backup of pfile using: create pfile from spfile; In this way Full backup can be performed when the database is open;
16. What is Partial Backup ?
Answers
A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.
17. What is On-line Redo Log?
18. What is Mirrored on-line Redo Log ?
Answers
A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.
19. What is Archived Redo Log ?
Answers
Archived Redo Log is defined as a copy of one of the filled members of an online redo log group. It is maintained when the database is in the ARCHIVELOG mode
20. What are the advantages of operating a database in ARCHIVELOG mode over operating it?
Answers
If you run in NOARCHIVELOG mode, if your database crashes, you can only recover to the point of time of your last backup.
ARCHIVELOGMODE allows you to recover to the point of the crash. Downside is it takes more diskspace and can cause the db to freeze if that disk fills up. If you have a production system with no tolerance for loss, this isn't an option.
21. What are the steps involved in Instance Recovery ?
Answers
Rolling forward to recover data that has not been recordedin data files, yet has been recorded in the on-line redolog, including the contents of rollback segments. Rolling back transactions that have been explicitly rolledback or have not been committed as indicated by the rollbacksegments regenerated in step a. Releasing any resources (locks) held by transactions inprocess at the time of the failure. Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instancefailure.
Sunday, July 18, 2010
Some of the Oracle Tips
Show Oracle Version and installed Options
The data dictionary views V$OPTION and V$VERSION can be used to get the current Oracle version and the installed options. Click here for the Script.
select banner
from sys.v$version;
select parameter
from sys.v$option
where value = 'TRUE';
select parameter
from sys.v$option
where value <> 'TRUE';
begin
dbms_output.put_line('Specific Port Information:
'||dbms_utility.port_string);
end;
/
Here is the produced Output from the Script:
Oracle Version:
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.5.0.0 - Production
TNS for 32-bit Windows: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
Installed Options:
PARAMETER
----------------------------------------------------------------
Objects
Advanced replication
Bit-mapped indexes
Connection multiplexing
Connection pooling
Database queuing
Incremental backup and recovery
Instead-of triggers
Parallel backup and recovery
Parallel execution
Parallel load
Point-in-time tablespace recovery
Fine-grained access control
N-Tier authentication/authorization
Function-based indexes
Plan Stability
Online Index Build
Coalesce Index
Managed Standby
Materialized view rewrite
Materialized view warehouse refresh
Database resource manager
Spatial
Visual Information Retrieval
Export transportable tablespaces
Transparent Application Failover
Fast-Start Fault Recovery
Sample Scan
Duplexed backups
Java
Not Installed Options:
PARAMETER
----------------------------------------------------------------
Partitioning
Parallel Server
Specific Port Information: IBMPC/WIN_NT-8.1.0
Inserting in two or more tables with SQL*Loader
If you want to load data from an ASCII or EBCDIC file into an Oracle Database -- SQL*Loader is the tool. In this article we want to show, how to distribute the rows in the file in two tables depending on a value at a specified character position in the file.
As an example, insert the row in table A if the value ra is 2 on position 7, insert the row in table B if ra is 1 (see the following figure).
You need the following SQL*Loader controlfile to accomplish this task.
OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET WE8EBCDIC500
INFILE '$BOTENFILE' "fix 86"
BADFILE '$BOTENFILE.bad'
DISCARDFILE '$BOTENFILE.dsc'
REPLACE
INTO TABLE B
WHEN ra = X'F1'
(
astrnr POSITION (1:6) INTEGER EXTERNAL,
ra POSITION (7:7) INTEGER EXTERNAL,
mc POSITION (8:8) INTEGER EXTERNAL,
astrg POSITION (9:36) CHAR,
astrgk POSITION (37:61) CHAR "UPPER(LTRIM(RTRIM(:astrgk)))",
astamm POSITION (62:71) CHAR,
aadrplz POSITION (72:77) INTEGER EXTERNAL,
astrart POSITION (78:79) INTEGER EXTERNAL,
apra POSITION (80:81) INTEGER EXTERNAL,
asprc POSITION (82:82) INTEGER EXTERNAL,
gplz POSITION (83:86) INTEGER EXTERNAL,
line SEQUENCE (1,1)
)
INTO TABLE A
WHEN ra = X'F2'
(
astrnr POSITION (1:6) INTEGER EXTERNAL,
ra POSITION (7:7) INTEGER EXTERNAL,
abzart POSITION (8:8) INTEGER EXTERNAL,
abbez POSITION (9:11) CHAR "(LTRIM(RTRIM(:abbez)))",
ahnrvn POSITION (12:15) INTEGER EXTERNAL,
ahnrva POSITION (16:17) CHAR "(LTRIM(RTRIM(:ahnrva)))",
ahnrbn POSITION (18:21) INTEGER EXTERNAL,
ahnrba POSITION (22:23) CHAR "(LTRIM(RTRIM(:ahnrba)))",
atc POSITION (24:24) CHAR,
line SEQUENCE (1,1)
)
The first line in the EBCDIC file is skipped OPTIONS (SKIP=1). The most interesting part is the WHEN clause. The attribute "ra" is the single character that has the value specified by hex-byte in the character encoding schema, such as X ' F1 ' (equivalent to 241 decimal), "X" must be uppercase. Due to National Language Support NLS requirements, hex 00 cannot be used (See Oracle 8 Utilities Guide).
How to migrate LONG fields into VARCHAR2
In Oracle7 the LONG datatype often cause difficulties, for example a table in Oracle7 can have only one LONG, LONG's couldn't replicated and more. We have seen, that in many cases simple text is inserted in these LONG fields. Due to this, we suggest to convert the LONGs into VARCHAR2 with the following simple PL/SQL script. In Oracle8 you may specify a longer VARCHAR2 (40000).
CREATE TABLE long_tab (
long_field LONG
);
ALTER TABLE long_tab ADD (char_field VARCHAR2(2000));
CREATE OR REPLACE PROCEDURE long2varchar IS
CURSOR getrowid IS
SELECT rowid, long_field
FROM long_tab;
BEGIN
FOR rec IN getrowid LOOP
UPDATE long_tab
SET char_field = rec.long_field;
END LOOP;
COMMIT;
END;
/
EXECUTE long2varchar;
Tracing other user sessions
If you want to look or trace at a certain oracle process, belonging to an Orcale User, the best way is to trace the session for this user. As a DBA, you can trace other sessions, normal users can only trace their own sessions.
1). Enable TIMED statistics
ALTER SESSION SET TIMED_STATISTICS = TRUE;
However, most Oracle systems have this INIT.ORA parameter already set to TRUE, because the performance loss is small.
2). Get SID and Serial#
SELECT s.sid,s.serial#,p.pid,p.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;
3). Stop the process
EXEC dbms_system.sql_trace_in_session(sid,serial,true);
Then use TKPROF in USER_DUMP_DEST with the generated trace file.
How to start an Oracle database with corrupted or lost dbfile ?
Help -- we cannot start the Oracle Database ... we get an error message telling us, that one datafile is lost or corrupted ... what can we do ?
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/db/test.dbf'
If the database is in ARCHIVELOG mode with a working online backup concept you are a lucky person, recover the database and everything is OK. But what can we do, if the database is in NOARCHIVELOG mode and no backup is present ?
a). If the database is still running do the following
1). Switch the damaged datafile to the RECOVER status
svrmgr> ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;
svrmgr> SELECT file#,status,bytes,name FROM v$datafile;
FILE# STATUS BYTES NAME
------- -------- --------- ------------------------------
1 SYSTEM 104857600 /u01/db/SOL3/sys/SOL3_sys1.dbf
2 RECOVER 2097152 /u01/db/test.dbf
2). Stop and Start the database to verify that the database can be started without ' test.dbf '.
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
3). DROP the tablespace to which the datafile belongs
svrmgr> DROP TABLESPACE test INCLUDING CONTENTS;
b). If the database is not running do the following
The database can only be stopped with SHUTDOWN ABORT with a damaged or lost datafile and the datafile is still in ONLINE mode. Therefore it's better to switch the datafile to the RECOVER status as shown above before stopping the database. However there is a way to switch the datafile to the RECOVER status when the database is stopped.
1). Mount the database and switch the damaged datafile to the RECOVER status
svrmgr> STARTUP MOUNT;
svrmgr> ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;
svrmgr> ALTER DATABASE OPEN;
2). DROP the tablespace to which the datafile belongs
svrmgr> DROP TABLESPACE test INCLUDING CONTENTS;
3). Stop and Start the database to verify that the database can be started without ' test.dbf '.
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
Important note
* The term ' OFFLINE DROP ' is misleading, it is not possible to drop a datafile with this command. The only purpose of this command is to startup a database with damaged or missing datafile and the databae is in NOARCHIVELOG mode.
* The command ' ALTER DATABASE DATAFILE ... OFFLINE DROP ' changes the datafile status from ONLINE to RECOVER. In this mode, the database can be started even if the datafile is not present.
* If space management (e.g. CREATE TABLE ..) occurs for this datafile, Oracle will try to allocate space in this ' dropped ' datafile and fails.
* The only way to drop a datafile is to drop the tablespace to which the datafile belongs. Unfortunately you will lose data if you don't have an actual backup.
The Mistery of Inline Views
One of the hidden features of Oracle, are the so called Inline Views (Immediate View, View on the Fly, Anonymous View). They are very useful to overcome some restrictions as:
* Users want to use Views, but have no CREATE VIEW privilege.
* A SELECT statement within CONNECT BY can select only one table.
Instead to create a view with CREATE VIEW, which needs the necessary privileges, you can specify the view directly within the FROM clause. The following example, shows how to print the number of rows from two tables in one line using an Inline View.
SELECT e.emp_count, d.dept_count
FROM (SELECT COUNT(*) emp_count FROM emp) e,
(SELECT COUNT(*) dept_count FROM dept) d;
EMP_COUNT DEPT_COUNT
--------- ----------
14 4
Another example using an Inline View gives departments' total employees and salaries as a decimal value of all the departments:
SELECT a.deptno "Department",
(a.num_emp/b.total_count)*100 "%Employees",
(a.sal_sum/b.total_sal)*100 "%Salary"
FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum
FROM emp
GROUP BY deptno) a,
(SELECT COUNT(*) total_count, SUM(sal) total_sal
FROM emp) b;
Department %Employees %Salary
---------- ---------- ---------
10 21.428571 30.146425
20 35.714286 37.4677
30 42.857143 32.385874
The next example demonstrates how to overcome the restriction, that a SELECT statement with a CONNECT BY to represent hierarchical dependencies, can specify only one table in the FROM clause. There is no possibility to enter a JOIN directly in the FROM clause. This problem can be solved with an Inline View. In the top SELECT list, all attributes must be defined, including those within the Inline View.
COLUMN employee FORMAT A12 HEADING "Chart"
COLUMN empno FORMAT 9999 HEADING "EmpNo"
COLUMN job FORMAT A10 HEADING "Job"
COLUMN dname FORMAT A10 HEADING "Dept"
COLUMN mgr FORMAT A8 HEADING "Boss"
COLUMN sal FORMAT 9999 HEADING "Salary"
COLUMN mgrno FORMAT A6 HEADING "BossNo"
SELECT employee, empno, job, d.dname, mgr, sal, mgrno
FROM dept d,
(SELECT LPAD(' ',2*(LEVEL-1)) || ename employee,
empno, job, deptno, PRIOR(ename) mgr, sal,
DECODE(LEVEL,1,NULL,PRIOR(empno)) mgrno
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH job = 'PRESIDENT') e
WHERE e.deptno = d.deptno;
Note, that the SELECT list corresponds with the Inline View (Attributtes without table alias). The departement name (d.dname) does not exist within the Inline View, it is directly selected in the table DEPT using the JOIN e.deptno = d.deptno in the WHERE clause, outside the Inline View.
Chart EmpNo Job Dept Boss Salary BossNo
------------ ----- ---------- ---------- ------ ------ ------
KING 7839 PRESIDENT ACCOUNTING 5000
JONES 7566 MANAGER RESEARCH KING 2975 7839
SCOTT 7788 ANALYST RESEARCH JONES 3000 7566
ADAMS 7876 CLERK RESEARCH SCOTT 1100 7788
FORD 7902 ANALYST RESEARCH JONE 3000 7566
SMITH 7369 CLERK RESEARCH FORD 800 7902
BLAKE 7698 MANAGER SALES KING 2850 7839
ALLEN 7499 SALESMAN SALES BLAKE 1600 7698
WARD 7521 SALESMAN SALES BLAKE 1250 7698
MARTIN 7654 SALESMAN SALES BLAKE 1250 7698
TURNER 7844 SALESMAN SALES BLAKE 1500 7698
JAMES 7900 CLERK SALES BLAKE 950 7698
CLARK 7782 MANAGER ACCOUNTING KING 2450 7839
MILLER 7934 CLERK ACCOUNTING CLARK 1300 7782
How to cache a table in the SGA ?
In order to cache a table in the SGA it has to be smaller than CACHE_SIZE_THRESHOLD as set in the init.ora file. However, the cost based analyzer doesn't take table cacheing into account when doing optimization so you may want to force the table using hints.
Check current ROLE for Database Access
When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES. You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view, for example:
SELECT role FROM session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
You may check the DB access in your application context using the following code construct.
DECLARE
HasAccess BOOLEAN := FALSE;
CURSOR cur_get_role IS
SELECT role FROM session_roles;
BEGIN
FOR role_rec IN cur_get_role LOOP
IF (UPPER(role_rec.role) IN ('ADMIN','CLERK')) THEN
HasAccess := TRUE;
END IF;
END LOOP;
IF (NOT HasAccess) THEN
RAISE_APPLICATION_ERROR
(-20020,'Sorry, you have no access to the database');
END IF;
END;
/
Eliminating duplicate records/fields from the data file
The Unix command for this is "uniq". It eliminates or reports consecutive lines that are identical. Because only adjacent lines are compared, you may have to use the sort utility before using uniq. In Example1, suppose you wanted to keep only the first entry with DEPTNO = 10. The Unix command would be:
uniq +43 example1.dat > example1.unq
The "+43" indicates that the first 43 characters in each record should be ignored for the comparison. The output file example1.unq would contain:
7782 CLARK MANAGER 7839 2572.50 0.20 10
7654 MARTIN SALESMAN 7698 1894.00 0.15 30
Counting the number of lines in the data file
Use "wc". For example:
wc -l example1.dat
3 example1.dat
which indicates that the file contains three lines.
How to remove duplicate rows from a table ?
SQL> select * from address;
NAME VORNAME BIRTH
-------- -------- ----
zahn martin 1954
zahn martin 1954
hodler kurt 1962
wyss bruno 1965
Get the Duplicates with:
SELECT name,vorname FROM address
GROUP BY name,vorname
HAVING COUNT(*) > 1;
NAME VORNAME
-------- --------
zahn martin
Delete the Duplicates with
DELETE from address A
WHERE (A.name, A.vorname, A.birth) IN
(SELECT B.name, B.vorname, B.birth FROM address B
WHERE A.name = B.name AND A.vorname = B.vorname
AND A.birth = B.birth AND A.rowid > B.rowid);
1 row deleted.
Enabling and Disabling Automatic Extension for a Datafile
You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The files increase in specified increments up to a specified maximum.
Advantage of using AUTOEXTEND ON
* Reduces the need for immediate intervention when a tablespace runs out of space.
* Ensures applications will not halt because of failures to allocate extents.
Disadvantage of using AUTOEXTEND ON
If you have no automatic mechanism to check the growth of your Oracle Datafile, ATOEXTEND ON will fillup the filesystem until 100%, then it is not easily to reclaim space. We think, that the disadvantage is greater than the advantage, due to this, check your Oracle Datafiles and reset AUTOEXTEND to OFF.
Check AUTOEXTEND on the Oracle Datafiles
To find out if a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.
SELECT file_name,autoextensible FROM dba_data_files;
FILE_NAME AUT
----------------------------------- ---
/u01/db/DIA3/sys/DIA3_sys1.dbf NO
/u01/db/DIA3/cdr/DIA3_cdr1.dbf NO
/u01/db/DIA3/cre/DIA3_cre1.dbf NO
/u01/db/DIA3/rbs/DIA3_rbs1.dbf NO
/u01/db/DIA3/usr/DIA3_users1.dbf NO
/u01/db/DIA3/tab/DIA3_tab1.dbf YES
Enabling automatic file extension
You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements:
* CREATE DATABASE
* CREATE TABLESPACE
* ALTER TABLESPACE
CREATE TABLESPACE rbs
DATAFILE '/u01/db/DIA3/rbs/DIA3_rbs1.dbf' SIZE 512064K REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
PERMANENT
ONLINE;
You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile using the SQL statement ALTER DATABASE. The following example enables automatic extension for a datafile added to the USERS tablespace:
ALTER TABLESPACE users
ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.
Disabling automatic file extension
The next example disables the automatic extension for the datafile.
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf'
AUTOEXTEND OFF;
Restrict the Number of Records
While developping database application we often have to restrict the number of records an end user can insert into a table. A simple solution is the following code fragment in the BEFORE-INSERT-FOR-EACH-ROW Trigger:
select count(*) into l_cnt from detail;
if l_cnt >= 5 then
raise_application_error(-20101,
'Maximum number of records exceeded');
end if;
To reconstruct or understand the following descriptions please download the example code ex_restr_nbrof_records.sql or consult the source code, we assume, that the maximum number of detailrecords must be limited to five.
We try to insert a 6Th record:
insert into detail values (6,'M1');
*
ERROR at line 1:
ORA-20101: Maximum number of recors reached
ORA-06512: at "SCOTT.BI_DETAIL", line 6
ORA-04088: error during execution of trigger
'SCOTT.BI_DETAIL'
So far so good. We’ll now demonstrate what happens, when we insert records from two concurrent transactions. Delete one record in order to be able to insert one record hereafter:
delete from detail where id=5;
1 row deleted.
commit;
Invoke the SQL-Plus tool and insert one record
insert into detail values (5,'M1');
1 row created.
DO NOT COMMIT !
Invoke a second session by starting a SQL-Plus again and run the statement
insert into detail values (6,'M1');
1 row created.
commit;
Commit complete.
Change to the other session and issue a commit
commit;
Commit complete.
select count(*) from detail;
COUNT(*)
----------
6
We now have 6 records. The maximum number allowed was 5 !
How to avoid this situation ?
Oracle does not support the "dirty read“ isolation level which is defined with:
"A transaction reads data written by concurrent uncommitted transaction“
A solution to solve this problem is to lock the corresponding master record:
Change the ON INSERT Trigger:
create or replace trigger bi_detail
before insert
on detail
for each row
declare
l_cnt number;
l_dummy master.id%type;
begin
-- lock the master record to avoit too many record
-- by using concurrent sessions.
select id into l_dummy from master
where id = :new.m_id for update nowait;
select count(*) into l_cnt from detail;
if l_cnt >= 5 then
raise_application_error(
-20101,'Maximum number of recors reached');
end if;
end;
Delete the 2 records to test the solution:
delete from detail where id=5;
delete from detail where id=6;
commit;
There should be 4 records now:
select count(*) from detail;
COUNT(*)
----------
4
Now we try again with the two concurrent sessions:
insert into detail values (5,'M1');
1 row created.
Change to the second session and issue:
insert into detail values (6,'M1');
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "SCOTT.BI_DETAIL", line 6
ORA-04088: error during execution of trigger
'SCOTT.BI_DETAIL'
Go back to the first session and run:
commit;
Commit complete.
Remarks
If an exact maximum number of records is specified for a table, using a lock is the only solution to avoid the above situation demonstrated. Often we want to limit the number of deteilrecords in a way, that an end user can’t claim a huge amount of disk space by inserting unbound number of records. In this case, the simple solution shown at the begin will do it.
If the table doesn’t use a foreign key constraint, you can implement an auxilliary table, insert one row and lock this row instead of the masterrecord.
The data dictionary views V$OPTION and V$VERSION can be used to get the current Oracle version and the installed options. Click here for the Script.
select banner
from sys.v$version;
select parameter
from sys.v$option
where value = 'TRUE';
select parameter
from sys.v$option
where value <> 'TRUE';
begin
dbms_output.put_line('Specific Port Information:
'||dbms_utility.port_string);
end;
/
Here is the produced Output from the Script:
Oracle Version:
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.5.0.0 - Production
TNS for 32-bit Windows: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
Installed Options:
PARAMETER
----------------------------------------------------------------
Objects
Advanced replication
Bit-mapped indexes
Connection multiplexing
Connection pooling
Database queuing
Incremental backup and recovery
Instead-of triggers
Parallel backup and recovery
Parallel execution
Parallel load
Point-in-time tablespace recovery
Fine-grained access control
N-Tier authentication/authorization
Function-based indexes
Plan Stability
Online Index Build
Coalesce Index
Managed Standby
Materialized view rewrite
Materialized view warehouse refresh
Database resource manager
Spatial
Visual Information Retrieval
Export transportable tablespaces
Transparent Application Failover
Fast-Start Fault Recovery
Sample Scan
Duplexed backups
Java
Not Installed Options:
PARAMETER
----------------------------------------------------------------
Partitioning
Parallel Server
Specific Port Information: IBMPC/WIN_NT-8.1.0
Inserting in two or more tables with SQL*Loader
If you want to load data from an ASCII or EBCDIC file into an Oracle Database -- SQL*Loader is the tool. In this article we want to show, how to distribute the rows in the file in two tables depending on a value at a specified character position in the file.
As an example, insert the row in table A if the value ra is 2 on position 7, insert the row in table B if ra is 1 (see the following figure).
You need the following SQL*Loader controlfile to accomplish this task.
OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET WE8EBCDIC500
INFILE '$BOTENFILE' "fix 86"
BADFILE '$BOTENFILE.bad'
DISCARDFILE '$BOTENFILE.dsc'
REPLACE
INTO TABLE B
WHEN ra = X'F1'
(
astrnr POSITION (1:6) INTEGER EXTERNAL,
ra POSITION (7:7) INTEGER EXTERNAL,
mc POSITION (8:8) INTEGER EXTERNAL,
astrg POSITION (9:36) CHAR,
astrgk POSITION (37:61) CHAR "UPPER(LTRIM(RTRIM(:astrgk)))",
astamm POSITION (62:71) CHAR,
aadrplz POSITION (72:77) INTEGER EXTERNAL,
astrart POSITION (78:79) INTEGER EXTERNAL,
apra POSITION (80:81) INTEGER EXTERNAL,
asprc POSITION (82:82) INTEGER EXTERNAL,
gplz POSITION (83:86) INTEGER EXTERNAL,
line SEQUENCE (1,1)
)
INTO TABLE A
WHEN ra = X'F2'
(
astrnr POSITION (1:6) INTEGER EXTERNAL,
ra POSITION (7:7) INTEGER EXTERNAL,
abzart POSITION (8:8) INTEGER EXTERNAL,
abbez POSITION (9:11) CHAR "(LTRIM(RTRIM(:abbez)))",
ahnrvn POSITION (12:15) INTEGER EXTERNAL,
ahnrva POSITION (16:17) CHAR "(LTRIM(RTRIM(:ahnrva)))",
ahnrbn POSITION (18:21) INTEGER EXTERNAL,
ahnrba POSITION (22:23) CHAR "(LTRIM(RTRIM(:ahnrba)))",
atc POSITION (24:24) CHAR,
line SEQUENCE (1,1)
)
The first line in the EBCDIC file is skipped OPTIONS (SKIP=1). The most interesting part is the WHEN clause. The attribute "ra" is the single character that has the value specified by hex-byte in the character encoding schema, such as X ' F1 ' (equivalent to 241 decimal), "X" must be uppercase. Due to National Language Support NLS requirements, hex 00 cannot be used (See Oracle 8 Utilities Guide).
How to migrate LONG fields into VARCHAR2
In Oracle7 the LONG datatype often cause difficulties, for example a table in Oracle7 can have only one LONG, LONG's couldn't replicated and more. We have seen, that in many cases simple text is inserted in these LONG fields. Due to this, we suggest to convert the LONGs into VARCHAR2 with the following simple PL/SQL script. In Oracle8 you may specify a longer VARCHAR2 (40000).
CREATE TABLE long_tab (
long_field LONG
);
ALTER TABLE long_tab ADD (char_field VARCHAR2(2000));
CREATE OR REPLACE PROCEDURE long2varchar IS
CURSOR getrowid IS
SELECT rowid, long_field
FROM long_tab;
BEGIN
FOR rec IN getrowid LOOP
UPDATE long_tab
SET char_field = rec.long_field;
END LOOP;
COMMIT;
END;
/
EXECUTE long2varchar;
Tracing other user sessions
If you want to look or trace at a certain oracle process, belonging to an Orcale User, the best way is to trace the session for this user. As a DBA, you can trace other sessions, normal users can only trace their own sessions.
1). Enable TIMED statistics
ALTER SESSION SET TIMED_STATISTICS = TRUE;
However, most Oracle systems have this INIT.ORA parameter already set to TRUE, because the performance loss is small.
2). Get SID and Serial#
SELECT s.sid,s.serial#,p.pid,p.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr;
3). Stop the process
EXEC dbms_system.sql_trace_in_session(sid,serial,true);
Then use TKPROF in USER_DUMP_DEST with the generated trace file.
How to start an Oracle database with corrupted or lost dbfile ?
Help -- we cannot start the Oracle Database ... we get an error message telling us, that one datafile is lost or corrupted ... what can we do ?
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '/u01/db/test.dbf'
If the database is in ARCHIVELOG mode with a working online backup concept you are a lucky person, recover the database and everything is OK. But what can we do, if the database is in NOARCHIVELOG mode and no backup is present ?
a). If the database is still running do the following
1). Switch the damaged datafile to the RECOVER status
svrmgr> ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;
svrmgr> SELECT file#,status,bytes,name FROM v$datafile;
FILE# STATUS BYTES NAME
------- -------- --------- ------------------------------
1 SYSTEM 104857600 /u01/db/SOL3/sys/SOL3_sys1.dbf
2 RECOVER 2097152 /u01/db/test.dbf
2). Stop and Start the database to verify that the database can be started without ' test.dbf '.
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
3). DROP the tablespace to which the datafile belongs
svrmgr> DROP TABLESPACE test INCLUDING CONTENTS;
b). If the database is not running do the following
The database can only be stopped with SHUTDOWN ABORT with a damaged or lost datafile and the datafile is still in ONLINE mode. Therefore it's better to switch the datafile to the RECOVER status as shown above before stopping the database. However there is a way to switch the datafile to the RECOVER status when the database is stopped.
1). Mount the database and switch the damaged datafile to the RECOVER status
svrmgr> STARTUP MOUNT;
svrmgr> ALTER DATABASE DATAFILE '/u01/db/test.dbf' OFFLINE DROP;
svrmgr> ALTER DATABASE OPEN;
2). DROP the tablespace to which the datafile belongs
svrmgr> DROP TABLESPACE test INCLUDING CONTENTS;
3). Stop and Start the database to verify that the database can be started without ' test.dbf '.
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
Important note
* The term ' OFFLINE DROP ' is misleading, it is not possible to drop a datafile with this command. The only purpose of this command is to startup a database with damaged or missing datafile and the databae is in NOARCHIVELOG mode.
* The command ' ALTER DATABASE DATAFILE ... OFFLINE DROP ' changes the datafile status from ONLINE to RECOVER. In this mode, the database can be started even if the datafile is not present.
* If space management (e.g. CREATE TABLE ..) occurs for this datafile, Oracle will try to allocate space in this ' dropped ' datafile and fails.
* The only way to drop a datafile is to drop the tablespace to which the datafile belongs. Unfortunately you will lose data if you don't have an actual backup.
The Mistery of Inline Views
One of the hidden features of Oracle, are the so called Inline Views (Immediate View, View on the Fly, Anonymous View). They are very useful to overcome some restrictions as:
* Users want to use Views, but have no CREATE VIEW privilege.
* A SELECT statement within CONNECT BY can select only one table.
Instead to create a view with CREATE VIEW, which needs the necessary privileges, you can specify the view directly within the FROM clause. The following example, shows how to print the number of rows from two tables in one line using an Inline View.
SELECT e.emp_count, d.dept_count
FROM (SELECT COUNT(*) emp_count FROM emp) e,
(SELECT COUNT(*) dept_count FROM dept) d;
EMP_COUNT DEPT_COUNT
--------- ----------
14 4
Another example using an Inline View gives departments' total employees and salaries as a decimal value of all the departments:
SELECT a.deptno "Department",
(a.num_emp/b.total_count)*100 "%Employees",
(a.sal_sum/b.total_sal)*100 "%Salary"
FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum
FROM emp
GROUP BY deptno) a,
(SELECT COUNT(*) total_count, SUM(sal) total_sal
FROM emp) b;
Department %Employees %Salary
---------- ---------- ---------
10 21.428571 30.146425
20 35.714286 37.4677
30 42.857143 32.385874
The next example demonstrates how to overcome the restriction, that a SELECT statement with a CONNECT BY to represent hierarchical dependencies, can specify only one table in the FROM clause. There is no possibility to enter a JOIN directly in the FROM clause. This problem can be solved with an Inline View. In the top SELECT list, all attributes must be defined, including those within the Inline View.
COLUMN employee FORMAT A12 HEADING "Chart"
COLUMN empno FORMAT 9999 HEADING "EmpNo"
COLUMN job FORMAT A10 HEADING "Job"
COLUMN dname FORMAT A10 HEADING "Dept"
COLUMN mgr FORMAT A8 HEADING "Boss"
COLUMN sal FORMAT 9999 HEADING "Salary"
COLUMN mgrno FORMAT A6 HEADING "BossNo"
SELECT employee, empno, job, d.dname, mgr, sal, mgrno
FROM dept d,
(SELECT LPAD(' ',2*(LEVEL-1)) || ename employee,
empno, job, deptno, PRIOR(ename) mgr, sal,
DECODE(LEVEL,1,NULL,PRIOR(empno)) mgrno
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH job = 'PRESIDENT') e
WHERE e.deptno = d.deptno;
Note, that the SELECT list corresponds with the Inline View (Attributtes without table alias). The departement name (d.dname) does not exist within the Inline View, it is directly selected in the table DEPT using the JOIN e.deptno = d.deptno in the WHERE clause, outside the Inline View.
Chart EmpNo Job Dept Boss Salary BossNo
------------ ----- ---------- ---------- ------ ------ ------
KING 7839 PRESIDENT ACCOUNTING 5000
JONES 7566 MANAGER RESEARCH KING 2975 7839
SCOTT 7788 ANALYST RESEARCH JONES 3000 7566
ADAMS 7876 CLERK RESEARCH SCOTT 1100 7788
FORD 7902 ANALYST RESEARCH JONE 3000 7566
SMITH 7369 CLERK RESEARCH FORD 800 7902
BLAKE 7698 MANAGER SALES KING 2850 7839
ALLEN 7499 SALESMAN SALES BLAKE 1600 7698
WARD 7521 SALESMAN SALES BLAKE 1250 7698
MARTIN 7654 SALESMAN SALES BLAKE 1250 7698
TURNER 7844 SALESMAN SALES BLAKE 1500 7698
JAMES 7900 CLERK SALES BLAKE 950 7698
CLARK 7782 MANAGER ACCOUNTING KING 2450 7839
MILLER 7934 CLERK ACCOUNTING CLARK 1300 7782
How to cache a table in the SGA ?
In order to cache a table in the SGA it has to be smaller than CACHE_SIZE_THRESHOLD as set in the init.ora file. However, the cost based analyzer doesn't take table cacheing into account when doing optimization so you may want to force the table using hints.
Check current ROLE for Database Access
When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES. You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view, for example:
SELECT role FROM session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
You may check the DB access in your application context using the following code construct.
DECLARE
HasAccess BOOLEAN := FALSE;
CURSOR cur_get_role IS
SELECT role FROM session_roles;
BEGIN
FOR role_rec IN cur_get_role LOOP
IF (UPPER(role_rec.role) IN ('ADMIN','CLERK')) THEN
HasAccess := TRUE;
END IF;
END LOOP;
IF (NOT HasAccess) THEN
RAISE_APPLICATION_ERROR
(-20020,'Sorry, you have no access to the database');
END IF;
END;
/
Eliminating duplicate records/fields from the data file
The Unix command for this is "uniq". It eliminates or reports consecutive lines that are identical. Because only adjacent lines are compared, you may have to use the sort utility before using uniq. In Example1, suppose you wanted to keep only the first entry with DEPTNO = 10. The Unix command would be:
uniq +43 example1.dat > example1.unq
The "+43" indicates that the first 43 characters in each record should be ignored for the comparison. The output file example1.unq would contain:
7782 CLARK MANAGER 7839 2572.50 0.20 10
7654 MARTIN SALESMAN 7698 1894.00 0.15 30
Counting the number of lines in the data file
Use "wc". For example:
wc -l example1.dat
3 example1.dat
which indicates that the file contains three lines.
How to remove duplicate rows from a table ?
SQL> select * from address;
NAME VORNAME BIRTH
-------- -------- ----
zahn martin 1954
zahn martin 1954
hodler kurt 1962
wyss bruno 1965
Get the Duplicates with:
SELECT name,vorname FROM address
GROUP BY name,vorname
HAVING COUNT(*) > 1;
NAME VORNAME
-------- --------
zahn martin
Delete the Duplicates with
DELETE from address A
WHERE (A.name, A.vorname, A.birth) IN
(SELECT B.name, B.vorname, B.birth FROM address B
WHERE A.name = B.name AND A.vorname = B.vorname
AND A.birth = B.birth AND A.rowid > B.rowid);
1 row deleted.
Enabling and Disabling Automatic Extension for a Datafile
You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The files increase in specified increments up to a specified maximum.
Advantage of using AUTOEXTEND ON
* Reduces the need for immediate intervention when a tablespace runs out of space.
* Ensures applications will not halt because of failures to allocate extents.
Disadvantage of using AUTOEXTEND ON
If you have no automatic mechanism to check the growth of your Oracle Datafile, ATOEXTEND ON will fillup the filesystem until 100%, then it is not easily to reclaim space. We think, that the disadvantage is greater than the advantage, due to this, check your Oracle Datafiles and reset AUTOEXTEND to OFF.
Check AUTOEXTEND on the Oracle Datafiles
To find out if a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.
SELECT file_name,autoextensible FROM dba_data_files;
FILE_NAME AUT
----------------------------------- ---
/u01/db/DIA3/sys/DIA3_sys1.dbf NO
/u01/db/DIA3/cdr/DIA3_cdr1.dbf NO
/u01/db/DIA3/cre/DIA3_cre1.dbf NO
/u01/db/DIA3/rbs/DIA3_rbs1.dbf NO
/u01/db/DIA3/usr/DIA3_users1.dbf NO
/u01/db/DIA3/tab/DIA3_tab1.dbf YES
Enabling automatic file extension
You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements:
* CREATE DATABASE
* CREATE TABLESPACE
* ALTER TABLESPACE
CREATE TABLESPACE rbs
DATAFILE '/u01/db/DIA3/rbs/DIA3_rbs1.dbf' SIZE 512064K REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
PERMANENT
ONLINE;
You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile using the SQL statement ALTER DATABASE. The following example enables automatic extension for a datafile added to the USERS tablespace:
ALTER TABLESPACE users
ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.
Disabling automatic file extension
The next example disables the automatic extension for the datafile.
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf'
AUTOEXTEND OFF;
Restrict the Number of Records
While developping database application we often have to restrict the number of records an end user can insert into a table. A simple solution is the following code fragment in the BEFORE-INSERT-FOR-EACH-ROW Trigger:
select count(*) into l_cnt from detail;
if l_cnt >= 5 then
raise_application_error(-20101,
'Maximum number of records exceeded');
end if;
To reconstruct or understand the following descriptions please download the example code ex_restr_nbrof_records.sql or consult the source code, we assume, that the maximum number of detailrecords must be limited to five.
We try to insert a 6Th record:
insert into detail values (6,'M1');
*
ERROR at line 1:
ORA-20101: Maximum number of recors reached
ORA-06512: at "SCOTT.BI_DETAIL", line 6
ORA-04088: error during execution of trigger
'SCOTT.BI_DETAIL'
So far so good. We’ll now demonstrate what happens, when we insert records from two concurrent transactions. Delete one record in order to be able to insert one record hereafter:
delete from detail where id=5;
1 row deleted.
commit;
Invoke the SQL-Plus tool and insert one record
insert into detail values (5,'M1');
1 row created.
DO NOT COMMIT !
Invoke a second session by starting a SQL-Plus again and run the statement
insert into detail values (6,'M1');
1 row created.
commit;
Commit complete.
Change to the other session and issue a commit
commit;
Commit complete.
select count(*) from detail;
COUNT(*)
----------
6
We now have 6 records. The maximum number allowed was 5 !
How to avoid this situation ?
Oracle does not support the "dirty read“ isolation level which is defined with:
"A transaction reads data written by concurrent uncommitted transaction“
A solution to solve this problem is to lock the corresponding master record:
Change the ON INSERT Trigger:
create or replace trigger bi_detail
before insert
on detail
for each row
declare
l_cnt number;
l_dummy master.id%type;
begin
-- lock the master record to avoit too many record
-- by using concurrent sessions.
select id into l_dummy from master
where id = :new.m_id for update nowait;
select count(*) into l_cnt from detail;
if l_cnt >= 5 then
raise_application_error(
-20101,'Maximum number of recors reached');
end if;
end;
Delete the 2 records to test the solution:
delete from detail where id=5;
delete from detail where id=6;
commit;
There should be 4 records now:
select count(*) from detail;
COUNT(*)
----------
4
Now we try again with the two concurrent sessions:
insert into detail values (5,'M1');
1 row created.
Change to the second session and issue:
insert into detail values (6,'M1');
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "SCOTT.BI_DETAIL", line 6
ORA-04088: error during execution of trigger
'SCOTT.BI_DETAIL'
Go back to the first session and run:
commit;
Commit complete.
Remarks
If an exact maximum number of records is specified for a table, using a lock is the only solution to avoid the above situation demonstrated. Often we want to limit the number of deteilrecords in a way, that an end user can’t claim a huge amount of disk space by inserting unbound number of records. In this case, the simple solution shown at the begin will do it.
If the table doesn’t use a foreign key constraint, you can implement an auxilliary table, insert one row and lock this row instead of the masterrecord.
Subscribe to:
Posts (Atom)