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.
No comments:
Post a Comment