Error Description
Whenever I try to rebuild index online then it fails with message ORA-00604: error occurred at recursive SQL level 1 along with ORA-01450: maximum key length (3215) exceeded. Below is the scenario.
SQL> create table tab1(a varchar2(3000),b varchar2(2000));
Table created.
SQL> create index tab1_I on tab1(a,b);
Index created.
SQL> alter index tab1_I rebuild online;
alter index tab1_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
Let's now create one table with column length 3000+199=3199 bytes and see what happens.
SQL> create table tab3(a varchar2(3000),b varchar2(199));
Table created.
SQL> create index tab3_I on tab3(a,b);
Index created.
Try to rebuild it online and it works.
SQL> alter index tab3_I rebuild online;
Index altered.
Now just add extra 1 bytes on column b. And whenever we try to rebuild it online it will fail.
SQL> alter table tab3 modify b varchar2(200);
Table altered.
SQL> alter index tab3_I rebuild online;
alter index tab3_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded
Cause of the Problem
When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)
The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.
So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.
Solution of the Problem
The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.
2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.
3)Rebuild the index without online clause. That is
ALTER INDEX index_name REBUILD;
Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.
Thursday, September 25, 2008
ORA-00997: illegal use of LONG datatype
Error Description
Whenever you try to do some illegal operations of LONG datatype it fails with error ORA-00997: illegal use of LONG datatype.
SQL> alter table a move ;
alter table a move
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
Cause of the Problem
The usage of LONG datatype has several restrictions. One of the restriction is, if a table contain long datatype column then it can't be used to move by using ALTER TABLE. In order to see list of restrictions just have a look at LONG Datatype in Oracle
Solution of the Problem
Don't use any LONG datatype in any table. If you have LONG datatype you must convert it to either BLOB/CLOB datatype using TO_LOB function. The conversion process is discussed on How to Convert Long data type to Lob
Whenever you try to do some illegal operations of LONG datatype it fails with error ORA-00997: illegal use of LONG datatype.
SQL> alter table a move ;
alter table a move
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
Cause of the Problem
The usage of LONG datatype has several restrictions. One of the restriction is, if a table contain long datatype column then it can't be used to move by using ALTER TABLE. In order to see list of restrictions just have a look at LONG Datatype in Oracle
Solution of the Problem
Don't use any LONG datatype in any table. If you have LONG datatype you must convert it to either BLOB/CLOB datatype using TO_LOB function. The conversion process is discussed on How to Convert Long data type to Lob
Wednesday, September 24, 2008
Root login fails with Permission denied on Unix
Problem Description
Using ssh whenever you try to login to another machine as a root user it failed with message Permission denied (gssapi-keyex,gssapi-with-mic,publickey,keyboard-interactive) like below.
Arju@debian:~$ ssh root@saturn
Password:
Password:
Password:
Permission denied (gssapi-keyex,gssapi-with-mic,publickey,keyboard-interactive).
Though I have provided the correct password but it fails. On server I can connect as other OS user rather than root and su as root user with password works. But from network it does not.
Cause of The Problem
In the machine to which you try to login within the script /etc/ssh/sshd_config parameter PermitRootLogin defines whether root user can ssh to the system or not.
Solution of The Problem
In order to see the contents of /etc/ssh/sshd_config you can issue,
$cat /etc/ssh/sshd_config
To see the settings of PermitRootLogin issue,
$ cat /etc/ssh/sshd_config |grep PermitRootLogin
PermitRootLogin no
As it is set to no so using ssh root connection is not accepted. In order to permit root login through network you have have to change this to yes from no after logging to the machine as a root user.
1)$su
password
#vi /etc/ssh/sshd_config
PermitRootLogin yes
2)Then restart the ssh daemon. On linux you can do it by,
#service sshd restart
On Solaris machine do it by,
# svcadm restart ssh
3)Then from another machine try to connect to the machine using root user.
Arju@debian:~$ ssh root@saturn
Password:
Last login: Fri Apr 25 11:15:34 2008
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
You have new mail.
#
And that's all.
Using ssh whenever you try to login to another machine as a root user it failed with message Permission denied (gssapi-keyex,gssapi-with-mic,publickey,keyboard-interactive) like below.
Arju@debian:~$ ssh root@saturn
Password:
Password:
Password:
Permission denied (gssapi-keyex,gssapi-with-mic,publickey,keyboard-interactive).
Though I have provided the correct password but it fails. On server I can connect as other OS user rather than root and su as root user with password works. But from network it does not.
Cause of The Problem
In the machine to which you try to login within the script /etc/ssh/sshd_config parameter PermitRootLogin defines whether root user can ssh to the system or not.
Solution of The Problem
In order to see the contents of /etc/ssh/sshd_config you can issue,
$cat /etc/ssh/sshd_config
To see the settings of PermitRootLogin issue,
$ cat /etc/ssh/sshd_config |grep PermitRootLogin
PermitRootLogin no
As it is set to no so using ssh root connection is not accepted. In order to permit root login through network you have have to change this to yes from no after logging to the machine as a root user.
1)$su
password
#vi /etc/ssh/sshd_config
PermitRootLogin yes
2)Then restart the ssh daemon. On linux you can do it by,
#service sshd restart
On Solaris machine do it by,
# svcadm restart ssh
3)Then from another machine try to connect to the machine using root user.
Arju@debian:~$ ssh root@saturn
Password:
Last login: Fri Apr 25 11:15:34 2008
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
You have new mail.
#
And that's all.
Crash Recovery Fails With ORA-27067
Problem Symptoms
After performing shutdown abort whenever I try to start my database it fails to perform crash recovery. Below is from alert log.
Wed May 21 07:11:43 2008
Errors in file /ora/udump/arju_ora_12424.trc:
ORA-01115: IO error reading block from file 2 (block # 11546)
ORA-01110: data file 2: '/ora/data/data01/arju_undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1245184
Cause of the Problem
This is oracle bug. It fired whenever someone gave very large insert operation and during that time issued shutdown abort.
Solution of the Problem
Just perform normal recovery. To do so,
1)$sqlplus / as sysdba
2)startup mount;
3)recover database;
4)alter database open;
After performing shutdown abort whenever I try to start my database it fails to perform crash recovery. Below is from alert log.
Wed May 21 07:11:43 2008
Errors in file /ora/udump/arju_ora_12424.trc:
ORA-01115: IO error reading block from file 2 (block # 11546)
ORA-01110: data file 2: '/ora/data/data01/arju_undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1245184
Cause of the Problem
This is oracle bug. It fired whenever someone gave very large insert operation and during that time issued shutdown abort.
Solution of the Problem
Just perform normal recovery. To do so,
1)$sqlplus / as sysdba
2)startup mount;
3)recover database;
4)alter database open;
Install flashplayer fails with Please enter the installation path
After downloading flashplayer from site http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash whenever I try to install flashplayer on my system it always ask me "Please enter the installation path of the Mozilla, Netscape,or Opera browser". It gave me a lots of pain.
1)Logon as a root user.
$su
Password:
2)Untar the file that you download from the site above.
#tar -xvf tar -xvf install_flash_player_9_linux.tar.gz
3)Run the installer.
debian:# ./flashplayer-installer
Copyright(C) 2002-2006 Adobe Macromedia Software LLC. All rights reserved.
Adobe Flash Player 9 for Linux
Adobe Flash Player 9 will be installed on this machine.
You are running the Adobe Flash Player installer as the "root" user.
Adobe Flash Player 9 will be installed system-wide.
Support is available at http://www.adobe.com/support/flashplayer/
To install Adobe Flash Player 9 now, press ENTER.
To cancel the installation at any time, press Control-C.
NOTE: Please exit any browsers you may have running.
Press ENTER to continue...
Please enter the installation path of the Mozilla, Netscape,
or Opera browser (i.e., /usr/lib/mozilla):
WARNING: Please enter a valid installation path.
4)At this stage press CTRL+C and have a look at your mozilla installation directory. It by default take directory as /usr/lib/mozilla. Based on system you use it may vary. Like /usr/lib/firefox-2.0.0.3 or others. In my system to see I used,
debian:# ls /usr/lib/moz*
/usr/lib/mozilla:
libflashplayer.so plugins
/usr/lib/mozilla-firefox:
components plugins
/usr/lib/mozilla-snapshot:
plugins
So my installation directory is /usr/lib/mozilla-firefox. I have to provide this path whenever it prompts.
5)Again start the installer and provide this path.
debian:# ./flashplayer-installer
Please enter the installation path of the Mozilla, Netscape,
or Opera browser (i.e., /usr/lib/mozilla): /usr/lib/mozilla-firefox
WARNING: The Adobe Flash Player binary is a symbolic link.
The installer will replace this symbolic link with the actual binary.
----------- Install Action Summary -----------
Adobe Flash Player 9 will be installed in the following directory:
Browser installation directory = /usr/lib/mozilla-firefox
Proceed with the installation? (y/n/q): y
Installation complete.
At this stage your flashplayer installation is completed and in order to affect restart your browser.
1)Logon as a root user.
$su
Password:
2)Untar the file that you download from the site above.
#tar -xvf tar -xvf install_flash_player_9_linux.tar.gz
3)Run the installer.
debian:# ./flashplayer-installer
Copyright(C) 2002-2006 Adobe Macromedia Software LLC. All rights reserved.
Adobe Flash Player 9 for Linux
Adobe Flash Player 9 will be installed on this machine.
You are running the Adobe Flash Player installer as the "root" user.
Adobe Flash Player 9 will be installed system-wide.
Support is available at http://www.adobe.com/support/flashplayer/
To install Adobe Flash Player 9 now, press ENTER.
To cancel the installation at any time, press Control-C.
NOTE: Please exit any browsers you may have running.
Press ENTER to continue...
Please enter the installation path of the Mozilla, Netscape,
or Opera browser (i.e., /usr/lib/mozilla):
WARNING: Please enter a valid installation path.
4)At this stage press CTRL+C and have a look at your mozilla installation directory. It by default take directory as /usr/lib/mozilla. Based on system you use it may vary. Like /usr/lib/firefox-2.0.0.3 or others. In my system to see I used,
debian:# ls /usr/lib/moz*
/usr/lib/mozilla:
libflashplayer.so plugins
/usr/lib/mozilla-firefox:
components plugins
/usr/lib/mozilla-snapshot:
plugins
So my installation directory is /usr/lib/mozilla-firefox. I have to provide this path whenever it prompts.
5)Again start the installer and provide this path.
debian:# ./flashplayer-installer
Please enter the installation path of the Mozilla, Netscape,
or Opera browser (i.e., /usr/lib/mozilla): /usr/lib/mozilla-firefox
WARNING: The Adobe Flash Player binary is a symbolic link.
The installer will replace this symbolic link with the actual binary.
----------- Install Action Summary -----------
Adobe Flash Player 9 will be installed in the following directory:
Browser installation directory = /usr/lib/mozilla-firefox
Proceed with the installation? (y/n/q): y
Installation complete.
At this stage your flashplayer installation is completed and in order to affect restart your browser.
Monday, September 22, 2008
Database startup fails with ORA-27302: failure occurred at: sskgpsemsper
Error Description
Whenever I try to start my database it fails with ORA-27302: failure occurred at: sskgpsemsper as following.
RMAN> STARTUP FORCE NOMOUNT PFILE='/backup03/webkey/testinitdb.ora';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 09/23/2008 00:45:51
RMAN-04014: startup failed: ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper
Cause of the problem
The error may mislead you. Though it indicates No space left on device but whenever I issue df -h on my OS there is enough space. The problem happened because of short of semaphores setting in the OS.
Solution of the problem
Solution 1)
Increase number of semaphores on operating system. You set semmns 32767 .
To make the setting permanent make an entry in /etc/sysctl.conf file on linux system.
sem = semmsl semmns semopm semmni
kernel.sem = 256 32768 100 228
The details is in How to configure(modify,see) Kernel Parameters in Linux
Solution 2)
Change the initialization parameter processes to a lower one in the initialization file and then startup the database. In my initialization file processes was set to 555. Whenever I start my database it fails with above error. I then reduced it to by 55 and it started my database successfully.
CREATE PFILE='1.pfile' FROM SPFILE;
edit pfile and set Processes parameter to lower value
STARTUP PFILE='1.pfile';
If you use spfile to startup your database you can use a trick to start your database which is discussed on,
http://arjudba.blogspot.com/2008/09/how-to-avoid-of-recreating-pfile-or.html.
Related Documents
Startup fails with ORA-27102: out of memory Solaris-AMD64 Error
Whenever I try to start my database it fails with ORA-27302: failure occurred at: sskgpsemsper as following.
RMAN> STARTUP FORCE NOMOUNT PFILE='/backup03/webkey/testinitdb.ora';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 09/23/2008 00:45:51
RMAN-04014: startup failed: ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper
Cause of the problem
The error may mislead you. Though it indicates No space left on device but whenever I issue df -h on my OS there is enough space. The problem happened because of short of semaphores setting in the OS.
Solution of the problem
Solution 1)
Increase number of semaphores on operating system. You set semmns 32767 .
To make the setting permanent make an entry in /etc/sysctl.conf file on linux system.
sem = semmsl semmns semopm semmni
kernel.sem = 256 32768 100 228
The details is in How to configure(modify,see) Kernel Parameters in Linux
Solution 2)
Change the initialization parameter processes to a lower one in the initialization file and then startup the database. In my initialization file processes was set to 555. Whenever I start my database it fails with above error. I then reduced it to by 55 and it started my database successfully.
CREATE PFILE='1.pfile' FROM SPFILE;
edit pfile and set Processes parameter to lower value
STARTUP PFILE='1.pfile';
If you use spfile to startup your database you can use a trick to start your database which is discussed on,
http://arjudba.blogspot.com/2008/09/how-to-avoid-of-recreating-pfile-or.html.
Related Documents
Startup fails with ORA-27102: out of memory Solaris-AMD64 Error
Sunday, September 21, 2008
ORA-14120: incompletely specified partition bound for a DATE column
In order to create partitioning table of FORM_AT whenever I try to create a temporary partitioned table name FORM_AT_PART it fails with ORA-14120: incompletely specified partition bound for a DATE column error as below.
SQL>CREATE TABLE "FORM_AT_PART"
( "ID" NUMBER NOT NULL ENABLE,
"AT" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-06','DD-MON-YY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-06','DD-MON-YY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-06','DD-MON-YY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-07','DD-MON-YY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-07','DD-MON-YY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-07','DD-MON-YY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-07','DD-MON-YY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-08','DD-MON-YY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-08','DD-MON-YY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
*
ERROR at line 7:
ORA-14120: incompletely specified partition bound for a DATE column
Cause of The Problem
There may be several causes behind the problem. One reason is you don't use TO_DATE conversion of date column while range partition and you specified date format inside partitioning column does not match with the NLS_DATE_FORMAT setting.
Another reason is starting with 8.0.3, Oracle insists that a partition bound for a DATE
partitioning column is fully specified. Fully specified indicates that it will at least contain day, month and year with 4 digits. Here inside to_date conversion I used 2 digits of date and hence error appears. Instead of '01-JUN-08' we have to use '01-JUN-2008'.
Solution of The Problem
Along with to_date conversion use 4 digits of year for a DATE partitioning column in oracle.
After fixing format it look like below,
SQL>CREATE TABLE "FORM_AT_PART"
( "ID" NUMBER NOT NULL ENABLE,
"AT" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
Table created.
SQL>CREATE TABLE "FORM_AT_PART"
( "ID" NUMBER NOT NULL ENABLE,
"AT" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-06','DD-MON-YY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-06','DD-MON-YY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-06','DD-MON-YY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-07','DD-MON-YY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-07','DD-MON-YY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-07','DD-MON-YY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-07','DD-MON-YY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-08','DD-MON-YY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-08','DD-MON-YY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
partition fa_part1 values less than (to_date('01-JAN-06','DD-MON-YY')) tablespace DATA02,
*
ERROR at line 7:
ORA-14120: incompletely specified partition bound for a DATE column
Cause of The Problem
There may be several causes behind the problem. One reason is you don't use TO_DATE conversion of date column while range partition and you specified date format inside partitioning column does not match with the NLS_DATE_FORMAT setting.
Another reason is starting with 8.0.3, Oracle insists that a partition bound for a DATE
partitioning column is fully specified. Fully specified indicates that it will at least contain day, month and year with 4 digits. Here inside to_date conversion I used 2 digits of date and hence error appears. Instead of '01-JUN-08' we have to use '01-JUN-2008'.
Solution of The Problem
Along with to_date conversion use 4 digits of year for a DATE partitioning column in oracle.
After fixing format it look like below,
SQL>CREATE TABLE "FORM_AT_PART"
( "ID" NUMBER NOT NULL ENABLE,
"AT" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
Table created.
How to make partitioning in Oracle more Quickly
As the table size grows and full table scans happens more frequently then there is no alternative than partitioning in oracle. Partition greatly enhance performance of a query. I will start my partitioning system simply with an example.
In my database the following query took 30~35 seconds to complete. After partitioning the performance increased amazingly and it then took only 1 second.
My query was,
SELECT DISTINCT fs.type, fs.id
, fs.pid, fs.cid
, fs.cr_id, fs.created_date
FROM summary fs where fs.id in
(select fa.id from forms fa where fa.sar in
(select la.sar from login la where la.login_id=1 and fa.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') and fs.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') )
)and mode=0;
After seeing above query I decide to make range partition on column created_date both in summary table and forms table.
Below is the list of procedures of making partition of forms table
1)Get a creation script of the original Table.
I get it by using DBMS_METADATA package.
SQL>SET LONG 99999
SQL>SELECT DBMS_METADATA.GET_DDL('TABLE','FORMS') FROM DUAL;
CREATE TABLE "PROD"."FORMS"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA"
2)Get the creation script of the associated Indexes of the table.
SQL> select dbms_metadata.get_ddl('INDEX',index_name) from dba_indexes where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------
CREATE INDEX "PROD"."FA_DATE" ON "PROD"."FORMS" ("CREATED_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
CREATE INDEX "PROD"."TF_SAR_I" ON "PROD"."FORMS" ("SAR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
3)Get the creation script of the associated Constraints.
SQL> select dbms_metadata.get_ddl('CONSTRAINT',constraint_name)from dba_constraints where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME)
--------------------------------------------------------------------------------
ALTER TABLE "PROD"."FORMS" MODIFY ("ID" NOT NULL ENABLE)
ALTER TABLE "PROD"."FORMS" MODIFY ("CREATED_DATE" NOT NULL ENABLE)
4)Now create a temporary Partitioned Table
Define the range of date to make a partitioned table. Here I made 11 partitions. It's structure will be same as of FORMS table. I named here of partition table as FORMS_PART.
CREATE TABLE "FORMS_PART"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
5)Load data into the temporary partitioned table.
In order to minimize to load time greatly I gave append and nologging hints. Append hints will not generate undo data and nologging hint will generate a minimal redo log. So my data load will be faster.
insert /*+APPEND NOLOGGING */ into FORMS_PART select * from forms;
78474831 rows created.
6) After load data into paritioned table rename original table to a new one. Here I gave it a name from FORMS to FROMS_BAK. You later can dropped it. Also rename the temporary partitioned table to original table name.
SQL> rename forms to forms_bak;
Table renamed.
SQL> rename forms_part to forms;
Table renamed.
7)Rebuild the index as well as create constraints if any.
alter index FA_DATE rebuild;
alter index TF_SAR_I rebuild;
8)Gather partitioned table statistics.
EXEC DBMS_STATS.gather_table_stats('PROD', 'FORMS', cascade => TRUE);
Now have a test your original query and compare performance with the one not partitioned.
In my database the following query took 30~35 seconds to complete. After partitioning the performance increased amazingly and it then took only 1 second.
My query was,
SELECT DISTINCT fs.type, fs.id
, fs.pid, fs.cid
, fs.cr_id, fs.created_date
FROM summary fs where fs.id in
(select fa.id from forms fa where fa.sar in
(select la.sar from login la where la.login_id=1 and fa.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') and fs.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') )
)and mode=0;
After seeing above query I decide to make range partition on column created_date both in summary table and forms table.
Below is the list of procedures of making partition of forms table
1)Get a creation script of the original Table.
I get it by using DBMS_METADATA package.
SQL>SET LONG 99999
SQL>SELECT DBMS_METADATA.GET_DDL('TABLE','FORMS') FROM DUAL;
CREATE TABLE "PROD"."FORMS"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA"
2)Get the creation script of the associated Indexes of the table.
SQL> select dbms_metadata.get_ddl('INDEX',index_name) from dba_indexes where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------
CREATE INDEX "PROD"."FA_DATE" ON "PROD"."FORMS" ("CREATED_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
CREATE INDEX "PROD"."TF_SAR_I" ON "PROD"."FORMS" ("SAR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA02"
3)Get the creation script of the associated Constraints.
SQL> select dbms_metadata.get_ddl('CONSTRAINT',constraint_name)from dba_constraints where owner='PROD' and table_name='FORMS';
DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME)
--------------------------------------------------------------------------------
ALTER TABLE "PROD"."FORMS" MODIFY ("ID" NOT NULL ENABLE)
ALTER TABLE "PROD"."FORMS" MODIFY ("CREATED_DATE" NOT NULL ENABLE)
4)Now create a temporary Partitioned Table
Define the range of date to make a partitioned table. Here I made 11 partitions. It's structure will be same as of FORMS table. I named here of partition table as FORMS_PART.
CREATE TABLE "FORMS_PART"
( "ID" NUMBER NOT NULL ENABLE,
"SAR" VARCHAR2(32) NOT NULL ENABLE,
"CREATED_DATE" TIMESTAMP (3) NOT NULL ENABLE
)partition by range (created_date)
(
partition fa_part1 values less than (to_date('01-JAN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part2 values less than (to_date('01-JUN-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part3 values less than (to_date('01-SEP-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part4 values less than (to_date('01-DEC-2006','DD-MON-YYYY')) tablespace DATA02,
partition fa_part5 values less than (to_date('01-MAR-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part6 values less than (to_date('01-JUN-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part7 values less than (to_date('01-SEP-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part8 values less than (to_date('01-DEC-2007','DD-MON-YYYY')) tablespace DATA02,
partition fa_part9 values less than (to_date('01-MAR-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part10 values less than (to_date('01-JUN-2008','DD-MON-YYYY')) tablespace DATA02,
partition fa_part11 values less than (maxvalue) tablespace DATA02);
5)Load data into the temporary partitioned table.
In order to minimize to load time greatly I gave append and nologging hints. Append hints will not generate undo data and nologging hint will generate a minimal redo log. So my data load will be faster.
insert /*+APPEND NOLOGGING */ into FORMS_PART select * from forms;
78474831 rows created.
6) After load data into paritioned table rename original table to a new one. Here I gave it a name from FORMS to FROMS_BAK. You later can dropped it. Also rename the temporary partitioned table to original table name.
SQL> rename forms to forms_bak;
Table renamed.
SQL> rename forms_part to forms;
Table renamed.
7)Rebuild the index as well as create constraints if any.
alter index FA_DATE rebuild;
alter index TF_SAR_I rebuild;
8)Gather partitioned table statistics.
EXEC DBMS_STATS.gather_table_stats('PROD', 'FORMS', cascade => TRUE);
Now have a test your original query and compare performance with the one not partitioned.
Set Date format inside RMAN environment
Suppose inside rman environment I got the following output.
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 558.80M DISK 00:01:14 08-SEP-08
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20080908T035835
Piece Name: /oracle/app/oracle/product/10.2.0/db_1/flash_recovery_area/ARJU/backupset/
2008_09_08/o1_mf_nnndf_TAG20080908T035835_4d9pscwz_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/system01.dbf
2 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/undotbs01.dbf
3 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/sysaux01.dbf
4 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/users01.dbf
.
.
.
Here I get date as 08-SEP-08 which only shows date, month and year. The exact minute, hour and second are not displayed here. In order to get output as hour,minute second along with above output I have to set NLS_DATE_FORMAT. Note that this need to be set in OS level. Suppose in my bash shell, I have set
-bash-3.00$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
Ans now it returns as I wanted.
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
1 Full 558.80M DISK 00:01:14 08-SEP-08 03:59:49
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20080908T035835
Piece Name: /oracle/app/oracle/product/10.2.0/db_1/flash_recovery_area/ARJU/backupset/
2008_09_08/o1_mf_nnndf_TAG20080908T035835_4d9pscwz_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------ ----
1 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/system01.dbf
2 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/undotbs01.dbf
3 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/sysaux01.dbf
4 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/users01.dbf
On windows you have to set as,
>set NLS_DATE_FORMAT=MON DD, YYYY HH24:MI:SS
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 558.80M DISK 00:01:14 08-SEP-08
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20080908T035835
Piece Name: /oracle/app/oracle/product/10.2.0/db_1/flash_recovery_area/ARJU/backupset/
2008_09_08/o1_mf_nnndf_TAG20080908T035835_4d9pscwz_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/system01.dbf
2 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/undotbs01.dbf
3 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/sysaux01.dbf
4 Full 1030282 08-SEP-08 /oradata2/arjudba/arjudba/arju/users01.dbf
.
.
.
Here I get date as 08-SEP-08 which only shows date, month and year. The exact minute, hour and second are not displayed here. In order to get output as hour,minute second along with above output I have to set NLS_DATE_FORMAT. Note that this need to be set in OS level. Suppose in my bash shell, I have set
-bash-3.00$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
Ans now it returns as I wanted.
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
1 Full 558.80M DISK 00:01:14 08-SEP-08 03:59:49
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20080908T035835
Piece Name: /oracle/app/oracle/product/10.2.0/db_1/flash_recovery_area/ARJU/backupset/
2008_09_08/o1_mf_nnndf_TAG20080908T035835_4d9pscwz_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------ ----
1 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/system01.dbf
2 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/undotbs01.dbf
3 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/sysaux01.dbf
4 Full 1030282 08-SEP-08 03:58:35 /oradata2/arjudba/arjudba/arju/users01.dbf
On windows you have to set as,
>set NLS_DATE_FORMAT=MON DD, YYYY HH24:MI:SS
Saturday, September 20, 2008
How to Disable and Enable all constraints using SQL
I have written two scripts. These scripts will disable and enable of entire constraints of ARJU schema. However if you want to enable of disable another schema then change the AND t.owner='ARJU' line inside script and change text ARJU.
Disable constraints of entire ARJU schema
set feedback off
set verify off
set echo off
prompt This script is going to disable constraints ....
set termout off
set pages 500
set heading off
set linesize 150
spool cons_disable.sql
select 'spool constraint_disable.log;' from dual;
select 'ALTER TABLE ARJU.'||c.table_name||' DISABLE CONSTRAINT '||constraint_name||' CASCADE;'
from dba_constraints c, dba_tables t
where c.table_name = t.table_name
AND t.owner='ARJU';
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@cons_disable.sql;
exit
/
Enable Constraints of Entire ARJU Schema
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool cons_enable.sql
select 'spool cons_enable.log;' from dual;
select 'ALTER TABLE ARJU.'||c.table_name||
' ENABLE CONSTRAINT '||constraint_name||' ;'
from dba_constraints c, dba_tables u
where c.table_name = u.table_name AND u.owner='ARJU';
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@cons_enable;
exit
/
Related Documents
ORA-02297: cannot disable constraint -dependencies exist
Type of constraint in oracle
How to know list of constraints and reference constraints in a table
Disable constraints of entire ARJU schema
set feedback off
set verify off
set echo off
prompt This script is going to disable constraints ....
set termout off
set pages 500
set heading off
set linesize 150
spool cons_disable.sql
select 'spool constraint_disable.log;' from dual;
select 'ALTER TABLE ARJU.'||c.table_name||' DISABLE CONSTRAINT '||constraint_name||' CASCADE;'
from dba_constraints c, dba_tables t
where c.table_name = t.table_name
AND t.owner='ARJU';
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@cons_disable.sql;
exit
/
Enable Constraints of Entire ARJU Schema
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool cons_enable.sql
select 'spool cons_enable.log;' from dual;
select 'ALTER TABLE ARJU.'||c.table_name||
' ENABLE CONSTRAINT '||constraint_name||' ;'
from dba_constraints c, dba_tables u
where c.table_name = u.table_name AND u.owner='ARJU';
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@cons_enable;
exit
/
Related Documents
ORA-02297: cannot disable constraint -dependencies exist
Type of constraint in oracle
How to know list of constraints and reference constraints in a table
Audit Trigger Activity in Oracle
Auditing a trigger activity or SQL inside trigger is no different than auditing normal SQL. In our business environment it was required to audit triggering event whenever the SQL statement inside trigger does unsucessful execution.
We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger.
Here is a test. Inside test schema I have made an example.
Connect as test user and create three tables.
SQL> conn test/test
Connected.
SQL> create table test(a number, b varchar2(4), c varchar2(8));
Table created.
SQL> create table test1(a number, b varchar2(3), c varchar2(3));
Table created.
SQL> create table test2(a number, b varchar2(3), c varchar2(3));
Table created.
2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2.
SQL> create or replace trigger test_t before insert on test for each row begin
insert into test1 values(:new.a,:new.b,:new.c);
update test2 set b=:new.b where a=:new.a;
end;
/
Trigger created.
3)Set the audit_trail parameter to DB, EXTENDED so that we can get full text of SQL.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;
System altered.
4)Just enter one row in test2 table. It is nothing but to see whether trigger can update test2 table.
SQL> insert into test2 values(1,'A','B');
1 row created.
SQL> commit;
Commit complete.
5)As audit_trail is static parameter. So in order to effect this parameter connect as sysdba and do a shutdown and startup.
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 574619648 bytes
Fixed Size 1250236 bytes
Variable Size 197135428 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.
SQL> conn test/test
Connected.
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB, EXTENDED
6)Enable audit on test table for each insert operation whenever not successfully done.
SQL> audit insert on test.test by access whenever not successful;
Audit succeeded.
SQL> insert into test values(1,'Tes','T2');
1 row created.
SQL> commit;
Commit complete.
7)In the dba_audit_trail view no data as insert sucessful.
SQL> select username,sql_text from dba_audit_trail;
no rows selected
SQL> select * from test1;
A B C
---------- --- ---
1 Tes T2
SQL> select * from test2;
A B C
---------- --- ---
1 Tes B
8)A failure in insert operation and trigger will fire and ba_audit_trail view will be populated.
SQL> insert into test values(2,'Test','Test2');
insert into test values(2,'Test','Test2')
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST1"."B" (actual: 4, maximum:3)
ORA-06512: at "TEST.TEST_T", line 2
ORA-04088: error during execution of trigger 'TEST.TEST_T'
Related Documents
About Audit_trail Parameter
Configure and Administer Database Auditing
We can achive our goal by simply audit the SQL for which trigger fires. Additionally you may also wish to audit the SQL statements inside trigger.
Here is a test. Inside test schema I have made an example.
Connect as test user and create three tables.
SQL> conn test/test
Connected.
SQL> create table test(a number, b varchar2(4), c varchar2(8));
Table created.
SQL> create table test1(a number, b varchar2(3), c varchar2(3));
Table created.
SQL> create table test2(a number, b varchar2(3), c varchar2(3));
Table created.
2)Create the trigger. It will fire before insert operation done on table test. Then it will insert these value into table test1 and update the table test2.
SQL> create or replace trigger test_t before insert on test for each row begin
insert into test1 values(:new.a,:new.b,:new.c);
update test2 set b=:new.b where a=:new.a;
end;
/
Trigger created.
3)Set the audit_trail parameter to DB, EXTENDED so that we can get full text of SQL.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;
System altered.
4)Just enter one row in test2 table. It is nothing but to see whether trigger can update test2 table.
SQL> insert into test2 values(1,'A','B');
1 row created.
SQL> commit;
Commit complete.
5)As audit_trail is static parameter. So in order to effect this parameter connect as sysdba and do a shutdown and startup.
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.
Total System Global Area 574619648 bytes
Fixed Size 1250236 bytes
Variable Size 197135428 bytes
Database Buffers 373293056 bytes
Redo Buffers 2940928 bytes
Database mounted.
Database opened.
SQL> conn test/test
Connected.
SQL> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB, EXTENDED
6)Enable audit on test table for each insert operation whenever not successfully done.
SQL> audit insert on test.test by access whenever not successful;
Audit succeeded.
SQL> insert into test values(1,'Tes','T2');
1 row created.
SQL> commit;
Commit complete.
7)In the dba_audit_trail view no data as insert sucessful.
SQL> select username,sql_text from dba_audit_trail;
no rows selected
SQL> select * from test1;
A B C
---------- --- ---
1 Tes T2
SQL> select * from test2;
A B C
---------- --- ---
1 Tes B
8)A failure in insert operation and trigger will fire and ba_audit_trail view will be populated.
SQL> insert into test values(2,'Test','Test2');
insert into test values(2,'Test','Test2')
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST1"."B" (actual: 4, maximum:3)
ORA-06512: at "TEST.TEST_T", line 2
ORA-04088: error during execution of trigger 'TEST.TEST_T'
SQL> select username,sql_text from dba_audit_trail;
USERNAME SQL_TEXT
-------------- --------------------------
TEST insert into test values(2,'Test','Test2')
Related Documents
About Audit_trail Parameter
Configure and Administer Database Auditing
Query to check whether user has datapump privilege.
Check for whether user has full database export/import privilege
You can check whether user has privilege to export or import the full database using data pump. To export full database using data pump user must have EXP_FULL_DATABASE role or dba role and to import full database using data pump user must have IMP_FULL_DATABASE or dba role.
In order to see whether user has these privilege or not you can query,
which indicates user ARJU,FARUK,MOMIN,SYS,SYSTEM can do both full data pump export and full datapump import operation. And user Test only has datapump export operation. Note that DBA role includes both EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.
Check for user has create table or create session privilege
In order to see whether a user has CREATE TABLE or CREATE SESSION privilege, query from dba_sys_privs table. Following is the check whether user TEST has these privilege or not.
Check for granted privileges on directory objects
Suppose we want to check whether user TEST has privilege on directory objects query,
Check for default tablespace on a user
In order to check what is the default tablespace of user TEST query,
From output we can see user TEST has quota on system tablespace 10M and he already used 65K.
Related Documents
Minimum privilege needed to take data pump export
You can check whether user has privilege to export or import the full database using data pump. To export full database using data pump user must have EXP_FULL_DATABASE role or dba role and to import full database using data pump user must have IMP_FULL_DATABASE or dba role.
In order to see whether user has these privilege or not you can query,
SQL>SET lines 100
COL privilege FOR a50
SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY 1,2;
GRANTEE GRANTED_ROLE DEF
------------------------------ ------------------------------ ---
ARJU DBA YES
DBA EXP_FULL_DATABASE YES
DBA IMP_FULL_DATABASE YES
FARUK DBA YES
MOMIN DBA YES
SYS DBA YES
SYS EXP_FULL_DATABASE YES
SYS IMP_FULL_DATABASE YES
SYSMAN DBA YES
SYSTEM DBA YES
TEST EXP_FULL_DATABASE YES
which indicates user ARJU,FARUK,MOMIN,SYS,SYSTEM can do both full data pump export and full datapump import operation. And user Test only has datapump export operation. Note that DBA role includes both EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.
Check for user has create table or create session privilege
In order to see whether a user has CREATE TABLE or CREATE SESSION privilege, query from dba_sys_privs table. Following is the check whether user TEST has these privilege or not.
SQL>SET lines 100 pages 100
COL privilege FOR a40
SELECT grantee, privilege
FROM dba_sys_privs
WHERE (grantee IN ('TEST', 'PUBLIC')
OR grantee IN (SELECT granted_role FROM dba_role_privs
WHERE grantee IN ('TEST', 'PUBLIC')))
AND privilege IN ('CREATE SESSION', 'CREATE TABLE')
ORDER BY 1,2;
If user test have it then output will be like below.
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
TEST CREATE SESSION
TEST CREATE TABLE
Check for granted privileges on directory objects
Suppose we want to check whether user TEST has privilege on directory objects query,
SET lines 110
COL privilege FOR a12
COL grantee FOR a25
COL owner FOR a25
SELECT p.grantee, p.privilege, p.owner, d.directory_name
FROM dba_tab_privs p, dba_directories d
WHERE p.table_name=d.directory_name
AND (grantee IN ('TEST', 'PUBLIC')
OR grantee IN (SELECT granted_role FROM dba_role_privs
WHERE grantee IN ('TEST', 'PUBLIC')))
ORDER BY 4,3,2;
A typical output is
GRANTEE PRIVILEGE OWNER DIRECTORY_NAME
-------------------- ---------- -------------------- ------------------------------
TEST READ SYS D
TEST WRITE SYS D
EXP_FULL_DATABASE READ SYS DATA_PUMP_DIR
EXP_FULL_DATABASE WRITE SYS DATA_PUMP_DIR
Check for default tablespace on a user
In order to check what is the default tablespace of user TEST query,
SQL>select default_tablespace from dba_users where username='TEST';
Check for tablespace quota
To check whether user TEST has quota on which tablespaces query,
SET lines 100 numwidth 12
SELECT q.username, q.tablespace_name, q.bytes, q.max_bytes
FROM dba_ts_quotas q, dba_users u
WHERE q.username=u.username AND q.username in ('TEST');
USERNAME TABLESPACE_NAME BYTES MAX_BYTES
------------------------------ ------------------------------ ------------ ------------
TEST SYSTEM 65536 10485760
From output we can see user TEST has quota on system tablespace 10M and he already used 65K.
Related Documents
Minimum privilege needed to take data pump export
Minimum privilege needed to take data pump export
In your organization you may assign a user who is only responsible to take data pump export. Suppose everyday evening he will be responsible to take a logical backup of the database.
The minimum privilege need to perform data pump export operation is given below.
1)Create Session privilege. This is required to logon to database.
2)Create Table privilege. This is required as while doing export operation he needs to create a master table.
3)Read and write permission on a valid database directory. Or Create Directory privilege.
4)Sufficient tablespace quota on the user's default tablespace. As master table need to be created while data pump export operation.
In addition to above 4 privileges it is needed to grant EXP_FULL_DATABASE to the intended user if he might need the following things
- to run a full database Export or
- to run a transport_tablespace job or
- to run an Export DataPump job with the TRACE parameter or
- to run an operation that exports a different schema.
Now suppose I want to grant minimum privilege to user Dump_User to perform data pump export.
With minimum privilege granted to him you can create user named Dump_User as below.
Way 1:
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'Give an OS directory here that already exist in database and OS user has permission on it';
Like, CREATE OR REPLACE DIRECTORY datapump_dir AS 'C:\'
GRANT create session, create table TO DUMP_USER IDENTIFIED BY a ;
ALTER USER DUMP_USER default tablespace users;
GRANT read, write ON DIRECTORY datapump_dir TO DUMP_USER;
ALTER USER DUMP_USER QUOTA unlimited ON users;
or:
Way 2: Using Role,
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'full_pre_existing_directory_path_here';
CREATE ROLE expdp_role;
GRANT create session, create table TO expdp_role;
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
GRANT expdp_role TO DUMP_USER;
ALTER USER DUMP_USER DEFAULT ROLE all;
ALTER USER DUMP_USER default tablespace users;
ALTER USER DUMP_USER QUOTA unlimited ON users;
Related Documents
Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE
Privileges Required to Create Views
Public Privileges in Oracle
How to make global read only user
The minimum privilege need to perform data pump export operation is given below.
1)Create Session privilege. This is required to logon to database.
2)Create Table privilege. This is required as while doing export operation he needs to create a master table.
3)Read and write permission on a valid database directory. Or Create Directory privilege.
4)Sufficient tablespace quota on the user's default tablespace. As master table need to be created while data pump export operation.
In addition to above 4 privileges it is needed to grant EXP_FULL_DATABASE to the intended user if he might need the following things
- to run a full database Export or
- to run a transport_tablespace job or
- to run an Export DataPump job with the TRACE parameter or
- to run an operation that exports a different schema.
Now suppose I want to grant minimum privilege to user Dump_User to perform data pump export.
With minimum privilege granted to him you can create user named Dump_User as below.
Way 1:
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'Give an OS directory here that already exist in database and OS user has permission on it';
Like, CREATE OR REPLACE DIRECTORY datapump_dir AS 'C:\'
GRANT create session, create table TO DUMP_USER IDENTIFIED BY a ;
ALTER USER DUMP_USER default tablespace users;
GRANT read, write ON DIRECTORY datapump_dir TO DUMP_USER;
ALTER USER DUMP_USER QUOTA unlimited ON users;
or:
Way 2: Using Role,
CONNECT system/a
CREATE OR REPLACE DIRECTORY datapump_dir AS 'full_pre_existing_directory_path_here';
CREATE ROLE expdp_role;
GRANT create session, create table TO expdp_role;
GRANT read, write ON DIRECTORY my_dir TO expdp_role;
GRANT expdp_role TO DUMP_USER;
ALTER USER DUMP_USER DEFAULT ROLE all;
ALTER USER DUMP_USER default tablespace users;
ALTER USER DUMP_USER QUOTA unlimited ON users;
Related Documents
Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE
Privileges Required to Create Views
Public Privileges in Oracle
How to make global read only user
Expdp fails with ORA-01950 and ORA-01536
Error Description
Whenever I run expdp the export process fails with ORA-01950 or ORA-01536.
SQL> host expdp test/t full=y dumpfile=a.dmp directory=d
Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 15:54:18
Copyright (c) 2003, 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
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'
Or,
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01536: space quota exceeded for tablespace 'USERS'
Cause of the Problem While exporting database objects a master table is created in the user's default tablespace. In above example in first case it is SYS_EXPORT_FULL_05 and in second case SYS_EXPORT_SCHEMA_01 under test schema. If user does not have quota on default tablespace then export operation fails.
Solution of the Problem
Check the default tablespace of user test by issuing,
SQL> select default_tablespace from dba_users where username='TEST';
DEFAULT_TABLESPACE
------------------------------
USERS
Solution 1)Grant sufficient quota to the default tablespace on user's default tablespace who is performing export operation. To do it as a dba user grant quota to test user in this example as is shown above error.
SQL>conn system/a
SQL>ALTER USER test QUOTA unlimited ON users;
If you don't want unlimited quota you can still give less for example 10M to default tablespace on user test like,
SQL>ALTER USER test QUOTA 10M ON users;
Solution 2)If user has quota on another tablespace then you can change the user default tablespace to another tablespace of test user.
To do this issue query from dba_ts_quotas to see on which tablespace user has sufficient quota,
As on tabespace data user has quota you can then change user default tablespace by,
SQL>ALTER user test default tablespace DATA;
Now you can able to perform export operation as test user.
Related Documents
Data pump export fails with ORA-39000, ORA-31641,ORA-27038
Export data to a previous time or point-in-time
Whenever I run expdp the export process fails with ORA-01950 or ORA-01536.
SQL> host expdp test/t full=y dumpfile=a.dmp directory=d
Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 15:54:18
Copyright (c) 2003, 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
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'
Or,
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_SCHEMA_01"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01536: space quota exceeded for tablespace 'USERS'
Cause of the Problem While exporting database objects a master table is created in the user's default tablespace. In above example in first case it is SYS_EXPORT_FULL_05 and in second case SYS_EXPORT_SCHEMA_01 under test schema. If user does not have quota on default tablespace then export operation fails.
Solution of the Problem
Check the default tablespace of user test by issuing,
SQL> select default_tablespace from dba_users where username='TEST';
DEFAULT_TABLESPACE
------------------------------
USERS
Solution 1)Grant sufficient quota to the default tablespace on user's default tablespace who is performing export operation. To do it as a dba user grant quota to test user in this example as is shown above error.
SQL>conn system/a
SQL>ALTER USER test QUOTA unlimited ON users;
If you don't want unlimited quota you can still give less for example 10M to default tablespace on user test like,
SQL>ALTER USER test QUOTA 10M ON users;
Solution 2)If user has quota on another tablespace then you can change the user default tablespace to another tablespace of test user.
To do this issue query from dba_ts_quotas to see on which tablespace user has sufficient quota,
SQL> select tablespace_name,username,bytes, max_bytes from dba_ts_quotas where username='TEST';
TABLESPACE_NAME USERNAME BYTES MAX_BYTES
------------------------------ ------------------------------ ---------- ----------
DATA TEST 65536 10485760
As on tabespace data user has quota you can then change user default tablespace by,
SQL>ALTER user test default tablespace DATA;
Now you can able to perform export operation as test user.
Related Documents
Data pump export fails with ORA-39000, ORA-31641,ORA-27038
Export data to a previous time or point-in-time
Expdp fails with ORA-31626, ORA-31633, ORA-06512, ORA-01031
Error Description
Whenever I run the expdp command to export only my schema objects it fails with ORA-31626, ORA-31633, ORA-06512 and ORA-01031.
A screenshot is below from my console.
SQL> host expdp test/t full=y directory=d dumpfile=a.dmp
Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 14:58:53
Copyright (c) 2003, 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
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01031: insufficient privileges
Cause of the Problem
As ORA-01031 indicates the test user that is performing data pump export does not have enough privilege.
In order to see the current privilege assigned to the user issue,
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
BACKUP ANY TABLE
SELECT ANY TABLE
SELECT ANY SEQUENCE
EXECUTE ANY PROCEDURE
CREATE ANY DIRECTORY
EXECUTE ANY TYPE
ADMINISTER RESOURCE MANAGER
RESUMABLE
SELECT ANY DICTIONARY
READ ANY FILE GROUP
11 rows selected.
Solution of the Problem
In order to do export operation at a minimum level the user must have the create table system privilege. But with above output we see the user does not have "create table" privilege. Do in order to avoid problem we can have different solution.
Solution 1)As a DBA user grant create table privilege to the user who will perform data pump export operation.
SQL> conn system/a
SQL> grant create table to test;
Then as a test user perform your operation.
SQL> host expdp test/t full=y directory=d dumpfile=a.dmp
Solution 2)Run the export operation as a different user who has the create table privilege. For example run the export operation as a system user.
Like,
SQL>host expdp system/a full=y directory=d dumpfile=a.dmp
Related Documents
Data pump export fails with ORA-39000, ORA-31641,ORA-27038
Export data to a previous time or point-in-time
Whenever I run the expdp command to export only my schema objects it fails with ORA-31626, ORA-31633, ORA-06512 and ORA-01031.
A screenshot is below from my console.
SQL> host expdp test/t full=y directory=d dumpfile=a.dmp
Export: Release 10.2.0.1.0 - Production on Saturday, 20 September, 2008 14:58:53
Copyright (c) 2003, 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
ORA-31626: job does not exist
ORA-31633: unable to create master table "TEST.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01031: insufficient privileges
Cause of the Problem
As ORA-01031 indicates the test user that is performing data pump export does not have enough privilege.
In order to see the current privilege assigned to the user issue,
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
BACKUP ANY TABLE
SELECT ANY TABLE
SELECT ANY SEQUENCE
EXECUTE ANY PROCEDURE
CREATE ANY DIRECTORY
EXECUTE ANY TYPE
ADMINISTER RESOURCE MANAGER
RESUMABLE
SELECT ANY DICTIONARY
READ ANY FILE GROUP
11 rows selected.
Solution of the Problem
In order to do export operation at a minimum level the user must have the create table system privilege. But with above output we see the user does not have "create table" privilege. Do in order to avoid problem we can have different solution.
Solution 1)As a DBA user grant create table privilege to the user who will perform data pump export operation.
SQL> conn system/a
SQL> grant create table to test;
Then as a test user perform your operation.
SQL> host expdp test/t full=y directory=d dumpfile=a.dmp
Solution 2)Run the export operation as a different user who has the create table privilege. For example run the export operation as a system user.
Like,
SQL>host expdp system/a full=y directory=d dumpfile=a.dmp
Related Documents
Data pump export fails with ORA-39000, ORA-31641,ORA-27038
Export data to a previous time or point-in-time
Thursday, September 18, 2008
EM Daemon is not running
On windows whenever I try to start my dbconsole in one time during process it shows me message EM Daemon is not running.
First I invoke status command to see whether my dbconcole is started or not by,
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is not running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
So it is not running. To start it on windows you can fix it by,
Right click on my computer icon> Click Manage> Select Services and Application>Select Services>On the right side From a list a Name select OracleDBConsolearju where arju is the name of database> Right click on OracleDBConsolearju and click start.
or in other OS along with windows you can start with
$emctl start dbconsole
Now again see the status.
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
EM Daemon is not running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
The status has been changed now and it says EM Daemon is not running. In order to fix it you have to start the listener. To start listener you may follow,
Right click on my computer icon> Click Manage> Select Services and Application> Select Services> On the right side From a list a Name select OracleOraDb10g_home1TNSListener> Right click on OracleOraDb10g_home1TNSListener and click start.
Or in other OS along with windows you can start with,
$lsnrctl start [Listener_name]
Now let us see the status.
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
Related Documents
Export and Import from Enterprise Manager
Login to Dbconsole, Authentication failed!null Returned
First I invoke status command to see whether my dbconcole is started or not by,
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is not running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
So it is not running. To start it on windows you can fix it by,
Right click on my computer icon> Click Manage> Select Services and Application>Select Services>On the right side From a list a Name select OracleDBConsolearju where arju is the name of database> Right click on OracleDBConsolearju and click start.
or in other OS along with windows you can start with
$emctl start dbconsole
Now again see the status.
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
EM Daemon is not running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
The status has been changed now and it says EM Daemon is not running. In order to fix it you have to start the listener. To start listener you may follow,
Right click on my computer icon> Click Manage> Select Services and Application> Select Services> On the right side From a list a Name select OracleOraDb10g_home1TNSListener> Right click on OracleOraDb10g_home1TNSListener and click start.
Or in other OS along with windows you can start with,
$lsnrctl start [Listener_name]
Now let us see the status.
C:\Documents and Settings\Queen>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://Queen:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory E:\oracle\product\10.2.0\db_1/QUEEN_arju/sysman/log
Related Documents
Export and Import from Enterprise Manager
Login to Dbconsole, Authentication failed!null Returned
Type of constraint in oracle
Constraint in oracle imposes rule that restrict the values in a oracle database. There are six types of constraint in oracle database and all these constraints except not null constraint can be declared in two ways. A brief description of these six types of constraints are listed below.
1)Not Null Constraint: If I define a field not null then value must be inserted in to that column.
2)Unique Constraint: If I define a column or set of column to be unique then it prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
3)Primary Key Constraint: Primary key constraint is the combination of not null constraint and unique constraint. In a single declare it satisfy both constraint.
4)Foreign Key Constraint: Foreign key constraint requires values in one table to match values in another table.
5)Check Constraint: Check constraint imposes restriction of values based on specified condition.
6)REF Constraint: A REF constraint lets you further describe the relationship between the REF column and the object it references.
Related Documents
How to Disable and Enable all constraints using SQL
ORA-02297: cannot disable constraint -dependencies exist
Type of constraint in oracle
How to know list of constraints and reference constraints in a table
1)Not Null Constraint: If I define a field not null then value must be inserted in to that column.
2)Unique Constraint: If I define a column or set of column to be unique then it prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
3)Primary Key Constraint: Primary key constraint is the combination of not null constraint and unique constraint. In a single declare it satisfy both constraint.
4)Foreign Key Constraint: Foreign key constraint requires values in one table to match values in another table.
5)Check Constraint: Check constraint imposes restriction of values based on specified condition.
6)REF Constraint: A REF constraint lets you further describe the relationship between the REF column and the object it references.
Related Documents
How to Disable and Enable all constraints using SQL
ORA-02297: cannot disable constraint -dependencies exist
Type of constraint in oracle
How to know list of constraints and reference constraints in a table
How we can minimize our database recovery time greatly
Now a days storage space is no matter in terms of data availability. Everybody always wants or expects the least downtime if any problem happens in the database. Suppose one of my datafile got corrupted. How quickly we can recover data. An efficient backup strategy should always satisfy least downtime in case of any possible problems. In this topic I will give an idea about how we can minimize our recovery time.
We know with RMAN we can take two types of backup. 1)Backupset and 2)Image copy.
By default whenever we give backup database command then RMAN create backupset. We can also take image copy of backup by giving command as BACKUP AS COPY DATABASE. If flash recovery area is enabled then those backups reside in flash recovery area and maintained there based on retention policy of RMAN setting.
Let's now think about the scenario. We have terabytes size of database and one datafile of 100G is corrupted. Now if we would took our backup as backupset then our approach would be:
1)Take the tablespace containing affected datafile offline.
2)Restore the datafile from backupset.
3)Recover the datafile.
4)Make the tablespace online.
Between these four steps step 2 consumes most of the time. If the backupset contains in slow media then restore operation can take more than a day if datafile size is near 100G.
We can minimize our recovery time from days to seconds easily. If we use image copy (bit by bit copy or OS copy of datafile) can we can acheive our goal.
To take an image copy backup of database through RMAN just issue,
RMAN>run {
backup as copy
database;
}
With this command RMAN will create the copies of the datafiles in the Flash Recovery Area with an Oracle-generated name such as o1_mf_users_2rqnthy_.dbf for users tablespace.
Suppose now our users tablespace datafile got corrupted. Then our procedure will be following which will minimize our recovery time greatly.
1)Just get a list of affected file_id by querying,
SQL>file_id from users tablespace by,
select file_id, file_name
from dba_data_files
where tablespace_name = 'USERS';
2)Connect to RMAN and take the affected tablespace offline.
$rman target /
RMAN>sql 'alter tablespace users offline';
3)Switch the affected datafile to the copy in the FRA. We get the file_id of affected datafile in step 1. Suppose we get file_id 4 is corrupted. Then our RMAN command will just point to the image copy of file_id 4 in the flash recovery area.
Which is,
RMAN> switch datafile 4 to copy;
4)Recover the datafile.
RMAN> recover datafile 4;
5)Make tablespace online.
RMAN> sql 'alter tablespace users online';
This way of recovering a datafile just saves restoring time as we did in case of backup of backupsets.
After making tablespace online if you check the location of file_id 4 you will see location is pointed to flash recovery area.
You can check it by,
SQ>select name from v$datafile where file# = 4;
Recovery area may contain slow disk storage. If you want to get back your datafile as of original location then just do the following task.
1. Make an image copy of the datafile at the original location.
RMAN> backup as copy datafile 4 format '/oradata/PROD/users01.dbf';
2. Take the tablespace offline.
RMAN> sql 'alter tablespace users offline';
3. Switch the datafile to the "copy" (Here "copy" points to the original location).
RMAN> switch datafile 4 to copy;
4. Recover the tablespace.
RMAN> recover datafile 4;
or RMAN>recover tablespace users;
5. Place the tablespace online.
RMAN> sql 'alter tablespace users online';
In above example I just shown an example of using flash recovery area and RMAN. However if you don't use RMAN and or flash recovery area then still you can achieve the same goal.
We know with RMAN we can take two types of backup. 1)Backupset and 2)Image copy.
By default whenever we give backup database command then RMAN create backupset. We can also take image copy of backup by giving command as BACKUP AS COPY DATABASE. If flash recovery area is enabled then those backups reside in flash recovery area and maintained there based on retention policy of RMAN setting.
Let's now think about the scenario. We have terabytes size of database and one datafile of 100G is corrupted. Now if we would took our backup as backupset then our approach would be:
1)Take the tablespace containing affected datafile offline.
2)Restore the datafile from backupset.
3)Recover the datafile.
4)Make the tablespace online.
Between these four steps step 2 consumes most of the time. If the backupset contains in slow media then restore operation can take more than a day if datafile size is near 100G.
We can minimize our recovery time from days to seconds easily. If we use image copy (bit by bit copy or OS copy of datafile) can we can acheive our goal.
To take an image copy backup of database through RMAN just issue,
RMAN>run {
backup as copy
database;
}
With this command RMAN will create the copies of the datafiles in the Flash Recovery Area with an Oracle-generated name such as o1_mf_users_2rqnthy_.dbf for users tablespace.
Suppose now our users tablespace datafile got corrupted. Then our procedure will be following which will minimize our recovery time greatly.
1)Just get a list of affected file_id by querying,
SQL>file_id from users tablespace by,
select file_id, file_name
from dba_data_files
where tablespace_name = 'USERS';
2)Connect to RMAN and take the affected tablespace offline.
$rman target /
RMAN>sql 'alter tablespace users offline';
3)Switch the affected datafile to the copy in the FRA. We get the file_id of affected datafile in step 1. Suppose we get file_id 4 is corrupted. Then our RMAN command will just point to the image copy of file_id 4 in the flash recovery area.
Which is,
RMAN> switch datafile 4 to copy;
4)Recover the datafile.
RMAN> recover datafile 4;
5)Make tablespace online.
RMAN> sql 'alter tablespace users online';
This way of recovering a datafile just saves restoring time as we did in case of backup of backupsets.
After making tablespace online if you check the location of file_id 4 you will see location is pointed to flash recovery area.
You can check it by,
SQ>select name from v$datafile where file# = 4;
Recovery area may contain slow disk storage. If you want to get back your datafile as of original location then just do the following task.
1. Make an image copy of the datafile at the original location.
RMAN> backup as copy datafile 4 format '/oradata/PROD/users01.dbf';
2. Take the tablespace offline.
RMAN> sql 'alter tablespace users offline';
3. Switch the datafile to the "copy" (Here "copy" points to the original location).
RMAN> switch datafile 4 to copy;
4. Recover the tablespace.
RMAN> recover datafile 4;
or RMAN>recover tablespace users;
5. Place the tablespace online.
RMAN> sql 'alter tablespace users online';
In above example I just shown an example of using flash recovery area and RMAN. However if you don't use RMAN and or flash recovery area then still you can achieve the same goal.
Monday, September 15, 2008
How to Download Patchset or Opatch from metalink
Update:
If you are using latest support.oracle.com then after login to metalink Dashboard
- Click on Patches and Updates tab
- On the left sidebar under "Oracle Server/Tools" click on "Latest Patchsets".
- A new window will appear.
- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.
- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.
- You will go the download page. From the download page you can also change your platform and patchset version.
To download patchset as well as logon to metalink you must need a MetaLink account. To register for MetaLink, you will need a valid Support Identifier (CSI). To have an account go to https://metalink.oracle.com/ and select Register For MetaLink under First Time Users. Then give your CSI number Support Identifier Country and click proceed. After that follow instruction as indicated.
After you have your metalink account you can downlaod patchset or Opatch from metalink. Steps are stated below.
1)Login to metalink with username and password.
2)Beside quick find drop down menu select Patch Number.
3)Beside this box there is another box where you can give the patch number if you know the patch number previous. It is an integer number. For example,
-If you try to get patchset 10.2.0.3 then give number as 5337014 and click go. In order to know the patchset number just have a look at, List of Patchset number in metalink
-New pop up windows will appear. Enter again username and password.
-Select you platform from drop down menu and click download.
-You have finished download in this stage.
4)If you don't know patch number and you wish to find it out then simply click go.
5)Patches & Updates page will appear. From it you can proceed as you like.
If you are using latest support.oracle.com then after login to metalink Dashboard
- Click on Patches and Updates tab
- On the left sidebar under "Oracle Server/Tools" click on "Latest Patchsets".
- A new window will appear.
- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.
- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.
- You will go the download page. From the download page you can also change your platform and patchset version.
To download patchset as well as logon to metalink you must need a MetaLink account. To register for MetaLink, you will need a valid Support Identifier (CSI). To have an account go to https://metalink.oracle.com/ and select Register For MetaLink under First Time Users. Then give your CSI number Support Identifier Country and click proceed. After that follow instruction as indicated.
After you have your metalink account you can downlaod patchset or Opatch from metalink. Steps are stated below.
1)Login to metalink with username and password.
2)Beside quick find drop down menu select Patch Number.
3)Beside this box there is another box where you can give the patch number if you know the patch number previous. It is an integer number. For example,
-If you try to get patchset 10.2.0.3 then give number as 5337014 and click go. In order to know the patchset number just have a look at, List of Patchset number in metalink
-New pop up windows will appear. Enter again username and password.
-Select you platform from drop down menu and click download.
-You have finished download in this stage.
4)If you don't know patch number and you wish to find it out then simply click go.
5)Patches & Updates page will appear. From it you can proceed as you like.
ORA-07445: exception encountered: core dump SIGSEGV
Problem Symptoms
Whenever I check my alert log file I got the text as
Errors in file /var/opt/oracle/admin/udump/orastdby_ora_31795.trc
ORA-07445: exception encountered: core dump [_intel_fast_memcmp()+38] [SIGSEGV] [Address not mapped to object] [0x0] [] []
After checking the trace file I got,
Call Stack Trace shows:
ksedst ksedmp ssexhd intel_fast_memcmp
Recent Changes of The Database
We have changes the CURSOR_SHARING parameter to SIMILAR from EXACT.
Cause of The Problem
This is oracle bug. Bug number 4456646. When the cursor sharing parameter is not set to EXACT this bug may fire. This occur while literal replacement when there are empty string literals in use. This bug happened in oracle version 10.2.0.1. In our environment it was RHL linux 32 bit production server.
Solution of The Problem
Way 1:
As a workaround disable literal replacement. You can do it by,
ALTER SYSTEM SET cursor_sharing='EXACT' SCOPE=both;
if you use spfile.
Way 2:
This bug is fixed in 10.2.0.2 patchset. So, apply 10.2.0.2 patchset in order to fix the prolem.
Way 3:
Apply One-off patch. Download one-off patch 4456646 from metalink and apply if that is avialable for your OS. For Linux x86 and linux x86-64 only available currently.
Whenever I check my alert log file I got the text as
Errors in file /var/opt/oracle/admin/udump/orastdby_ora_31795.trc
ORA-07445: exception encountered: core dump [_intel_fast_memcmp()+38] [SIGSEGV] [Address not mapped to object] [0x0] [] []
After checking the trace file I got,
Call Stack Trace shows:
ksedst ksedmp ssexhd intel_fast_memcmp
Recent Changes of The Database
We have changes the CURSOR_SHARING parameter to SIMILAR from EXACT.
Cause of The Problem
This is oracle bug. Bug number 4456646. When the cursor sharing parameter is not set to EXACT this bug may fire. This occur while literal replacement when there are empty string literals in use. This bug happened in oracle version 10.2.0.1. In our environment it was RHL linux 32 bit production server.
Solution of The Problem
Way 1:
As a workaround disable literal replacement. You can do it by,
ALTER SYSTEM SET cursor_sharing='EXACT' SCOPE=both;
if you use spfile.
Way 2:
This bug is fixed in 10.2.0.2 patchset. So, apply 10.2.0.2 patchset in order to fix the prolem.
Way 3:
Apply One-off patch. Download one-off patch 4456646 from metalink and apply if that is avialable for your OS. For Linux x86 and linux x86-64 only available currently.
Related Documents
How to skip a tablespace for restore operation
Sometimes we want to restore full database except a certain tablespace. That is we need to skip a tablespace for restore operation. May be that the tablespace only contains tempoarary data and hence not useful for us. Or we are performing some database point in time recovery operation and we want to omit a big tablespace as the tablespace does not contain data needed for our requirement. In this way we can minimize our restore operation time.
With the RESTORE DATABASE command we perform all datafiles restore operation except those that are offline or read-only.
Note that RESTORE DATABASE does not work same as BACKUP DATABASE. With command BACKUP DATABASE, RMAN backs up datafiles along with controlfiles and spfile. But with RESTORE COMMAND operation, RMAN only restores datafiles.
To omit a certain tablespace for restore operation use RESTORE DATABASE SKIP TABLESPACE tablespace_name. Suppose I want to omit restore of indexed tablespace INDX01,INDX02 and INDX01_16K . Then my restore command will be,
RMAN>RESTORE DATABASE SKIP TABLESPACE INDX01,INDX02,INDX01_16K;
If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP option of ALTER DATABASE DATAFILE ... OFFLINE when taking the datafiles that belong to the tablespace offline before the restore. The DROP option indicates that RMAN does not intend to recover these files and intends to drop their tablespaces from the database after the database is opened again. In other words, FOREVER indicates that RMAN never intends to do anything with the skipped tablespaces again.
Suppose you want to skip forever to restore tablespace EXAMPLE,INDX01 and INDX02 then your command will be,
RMAN>RESTORE DATABASE SKIP FOREVER TABLESPACE EXAMPLE, INDX01, INDX02;
With the RESTORE DATABASE command we perform all datafiles restore operation except those that are offline or read-only.
Note that RESTORE DATABASE does not work same as BACKUP DATABASE. With command BACKUP DATABASE, RMAN backs up datafiles along with controlfiles and spfile. But with RESTORE COMMAND operation, RMAN only restores datafiles.
To omit a certain tablespace for restore operation use RESTORE DATABASE SKIP TABLESPACE tablespace_name. Suppose I want to omit restore of indexed tablespace INDX01,INDX02 and INDX01_16K . Then my restore command will be,
RMAN>RESTORE DATABASE SKIP TABLESPACE INDX01,INDX02,INDX01_16K;
If you specify SKIP FOREVER TABLESPACE, then RMAN specifies the DROP option of ALTER DATABASE DATAFILE ... OFFLINE when taking the datafiles that belong to the tablespace offline before the restore. The DROP option indicates that RMAN does not intend to recover these files and intends to drop their tablespaces from the database after the database is opened again. In other words, FOREVER indicates that RMAN never intends to do anything with the skipped tablespaces again.
Suppose you want to skip forever to restore tablespace EXAMPLE,INDX01 and INDX02 then your command will be,
RMAN>RESTORE DATABASE SKIP FOREVER TABLESPACE EXAMPLE, INDX01, INDX02;
Related Documents
How to Discover find DBID
How to catalog and uncatalog a backup to RMAN repository
Catalog Backup
Whenever we take any backup through RMAN, in the repository information of the backup is recorded. The RMAN respository can be either controlfile or recovery catalog. However if I take a backup through OS command then RMAN does not aware of that and hence recorded are not reflected in the repository. This is also true whenever we create a new controlfile or a backup taken by RMAN is transferred to another place using OS command then controlfile/recovery catalog does not know about the prior backups of the database. So in order to restore database with a new created controlfile we need to inform RMAN about the backups taken before so that it can pick one to restore.
This task can be done by catalog command in RMAN. With catalog command it can
-Add information of backup pieces and image copies in the repository that are on disk.
-Record a datafile copy as a level 0 incremental backup in the RMAN repository.
-Record of a datafile copy that was taken by OS.
But CATALOG command has some restrictions. It can't do the following.
-Can't catalog a file that belong to different database.
-Can't catalog a backup piece that exists on an sbt device.
Examples of Catalog command
1)Catalog an archive log: To catalog two archived logs named /oracle/oradata/arju/arc001_223.arc and /oracle/oradata/arju/arc001_224.arc the command is,
RMAN>CATALOG ARCHIVELOG '/oracle/oradata/arju/arc001_223.arc', '/oracle/oradata/arju/arc001_224.arc';
2)Catalog a file copy as an incremental backup: To catalog datafile copy '/oradata/backup/users01.dbf' as an incremental level 0 backup your command will be,
RMAN>CATALOG DATAFILECOPY '/oradata/backup/users01.dbf' LEVEL 0;
Note that this datafile copy was taken backup either using the RMAN BACKUP AS COPY command, or by using operating system utilities in conjunction with ALTER TABLESPACE BEGIN/END BACKUP.
3)Catalog multiple copies in a directory: To catalog all valid backups from directory /tmp/backups issue,
RMAN>CATALOG START WITH '/tmp/backups' NOPROMPT;
4)Catalog files in the flash recovery area: To catalog all files in the currently enabled flash recovery area without prompting the user for each one issue,
RMAN>CATALOG RECOVERY AREA NOPROMPT;
5)Catalog backup pieces: To catalog backup piece /oradata2/o4jccf4 issue,
RMAN>CATALOG BACKUPPIECE '/oradata2/o4jccf4';
Uncatalog Backup
In many cases you need to uncatalog command. Suppose you do not want a specific backup or copy to be eligible to be restored but also do not want to delete it.
To uncatalog all archived logs issue,
RMAN>CHANGE ARCHIVELOG ALL UNCATALOG;
To uncataog tablespace USERS issue,
RMAN>CHANGE BACKUP OF TABLESPACE USERS UNCATALOG;
To uncatalog a backuppiece name /oradata2/oft7qq issue,
RMAN>CHANGE BACKUPPIECE '/oradata2/oft7qq' UNCATALOG;
Whenever we take any backup through RMAN, in the repository information of the backup is recorded. The RMAN respository can be either controlfile or recovery catalog. However if I take a backup through OS command then RMAN does not aware of that and hence recorded are not reflected in the repository. This is also true whenever we create a new controlfile or a backup taken by RMAN is transferred to another place using OS command then controlfile/recovery catalog does not know about the prior backups of the database. So in order to restore database with a new created controlfile we need to inform RMAN about the backups taken before so that it can pick one to restore.
This task can be done by catalog command in RMAN. With catalog command it can
-Add information of backup pieces and image copies in the repository that are on disk.
-Record a datafile copy as a level 0 incremental backup in the RMAN repository.
-Record of a datafile copy that was taken by OS.
But CATALOG command has some restrictions. It can't do the following.
-Can't catalog a file that belong to different database.
-Can't catalog a backup piece that exists on an sbt device.
Examples of Catalog command
1)Catalog an archive log: To catalog two archived logs named /oracle/oradata/arju/arc001_223.arc and /oracle/oradata/arju/arc001_224.arc the command is,
RMAN>CATALOG ARCHIVELOG '/oracle/oradata/arju/arc001_223.arc', '/oracle/oradata/arju/arc001_224.arc';
2)Catalog a file copy as an incremental backup: To catalog datafile copy '/oradata/backup/users01.dbf' as an incremental level 0 backup your command will be,
RMAN>CATALOG DATAFILECOPY '/oradata/backup/users01.dbf' LEVEL 0;
Note that this datafile copy was taken backup either using the RMAN BACKUP AS COPY command, or by using operating system utilities in conjunction with ALTER TABLESPACE BEGIN/END BACKUP.
3)Catalog multiple copies in a directory: To catalog all valid backups from directory /tmp/backups issue,
RMAN>CATALOG START WITH '/tmp/backups' NOPROMPT;
4)Catalog files in the flash recovery area: To catalog all files in the currently enabled flash recovery area without prompting the user for each one issue,
RMAN>CATALOG RECOVERY AREA NOPROMPT;
5)Catalog backup pieces: To catalog backup piece /oradata2/o4jccf4 issue,
RMAN>CATALOG BACKUPPIECE '/oradata2/o4jccf4';
Uncatalog Backup
In many cases you need to uncatalog command. Suppose you do not want a specific backup or copy to be eligible to be restored but also do not want to delete it.
To uncatalog all archived logs issue,
RMAN>CHANGE ARCHIVELOG ALL UNCATALOG;
To uncataog tablespace USERS issue,
RMAN>CHANGE BACKUP OF TABLESPACE USERS UNCATALOG;
To uncatalog a backuppiece name /oradata2/oft7qq issue,
RMAN>CHANGE BACKUPPIECE '/oradata2/oft7qq' UNCATALOG;
How to make global read only user
In many cases we need to make a read only user for the entire data dictionary and for all the tables of all schemas. Suppose in order to analysis the entire schema or to monitor performance of the database you might give only select privilege of the entire schema of a user as well as on the dictionary objects of database. But that user will not be able to delete or alter or modify anything of other schema objects. If your goal is make just read only user of a schema then have a look at
http://arjudba.blogspot.com/2008/09/create-read-only-user-for-schema.html
You can simply achive this goal by giving him three permission.
1)Create Session: With this privilege he will be able to logon in the the database.
2)Select any Table: With this privilege he will be able to select any table schema rather than SYS objects. Though if O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.
3)Select any dictionary: If O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then this privilege is not required to select data dictionary objects or in other word SYS objects. If O7_DICTIONARY_ACCESSIBILITY parameter is set to false then this privilege is required to give select permission on the data dictionary objects.
Following is an example. User arju will be globally read only user.
SQL> create user arju identified by a;
User created.
SQL> grant create session,select any dictionary,select any table to arju;
Grant succeeded.
Related Documents
Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE
What is O7_DICTIONARY_ACCESSIBILITY?
A user can do work in his schema with only Create Session Privilege.
Create Read only user for a Schema
http://arjudba.blogspot.com/2008/09/create-read-only-user-for-schema.html
You can simply achive this goal by giving him three permission.
1)Create Session: With this privilege he will be able to logon in the the database.
2)Select any Table: With this privilege he will be able to select any table schema rather than SYS objects. Though if O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.
3)Select any dictionary: If O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then this privilege is not required to select data dictionary objects or in other word SYS objects. If O7_DICTIONARY_ACCESSIBILITY parameter is set to false then this privilege is required to give select permission on the data dictionary objects.
Following is an example. User arju will be globally read only user.
SQL> create user arju identified by a;
User created.
SQL> grant create session,select any dictionary,select any table to arju;
Grant succeeded.
Related Documents
Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE
What is O7_DICTIONARY_ACCESSIBILITY?
A user can do work in his schema with only Create Session Privilege.
Create Read only user for a Schema
Sunday, September 14, 2008
Screen -A very useful unix tool to work with remote system
Sometimes on remote server I have to ssh and run some long running operations. Like I have to take a dump on USA server while I am in Bangladesh, or have to backup or restore operation on remote server. If disk is slow and/or database size is big then this process may take hours after hours.
During this time if my session terminates for example network down, power outage then my whole process abnormally shutdown and I have to start from first again.
Unix screen tool in this case really makes our life easy.
screen tool will permit remote monitoring of program execution progress with automatic protection against disconnection. It is a so-called terminal multiplexer, which allows us to create, shuffle, share, and suspend command line sessions within one window. It provides protection against disconnections and the flexibility
to retrieve command line sessions remotely.
You can download the screen tool from Http GNU Screen.
After downloading you can install it by,
tar -zxvf screen-4.0.2.tar.gz
cd screen-4.0.2
./configure && make
make install
Here 4.0.2 is the version number. Note that version 4.0.3 is available now.
After you download and install you can work with it.
The working steps are,
1. log in remote system where you do work usually via secure shell.
2. start screen. Just write screen on console.
3. run your long running operation here. Like take a dump of do any backup or restore operation or anything that need much times.
4. detach the screen session, using Ctrl+a or using Ctrl+d, and
5. log out.
The session continues working in the background, contained within the screen session. If we want to revisit the session to check its progress, then
1. log in that remote system via secure shell,
2. start screen -r, which in fact recalls the unattached session,
3. examine the saved buffer; scrolling around, copying and pasting as necessary,
4. detach the screen session, using Ctrl+a , Ctrl+d, and
5. log out.
Related Documents
How to change/configure IP Address on Linux/ Fedora /Ubuntu
Copy files between Unix and Windows with rcp
Different ways to take Screenshot on Linux
How to change the hostname in Linux
Memory Usage of Solaris Operating System
During this time if my session terminates for example network down, power outage then my whole process abnormally shutdown and I have to start from first again.
Unix screen tool in this case really makes our life easy.
screen tool will permit remote monitoring of program execution progress with automatic protection against disconnection. It is a so-called terminal multiplexer, which allows us to create, shuffle, share, and suspend command line sessions within one window. It provides protection against disconnections and the flexibility
to retrieve command line sessions remotely.
You can download the screen tool from Http GNU Screen.
After downloading you can install it by,
tar -zxvf screen-4.0.2.tar.gz
cd screen-4.0.2
./configure && make
make install
Here 4.0.2 is the version number. Note that version 4.0.3 is available now.
After you download and install you can work with it.
The working steps are,
1. log in remote system where you do work usually via secure shell.
2. start screen. Just write screen on console.
3. run your long running operation here. Like take a dump of do any backup or restore operation or anything that need much times.
4. detach the screen session, using Ctrl+a or using Ctrl+d, and
5. log out.
The session continues working in the background, contained within the screen session. If we want to revisit the session to check its progress, then
1. log in that remote system via secure shell,
2. start screen -r, which in fact recalls the unattached session,
3. examine the saved buffer; scrolling around, copying and pasting as necessary,
4. detach the screen session, using Ctrl+a , Ctrl+d, and
5. log out.
Related Documents
How to change/configure IP Address on Linux/ Fedora /Ubuntu
Copy files between Unix and Windows with rcp
Different ways to take Screenshot on Linux
How to change the hostname in Linux
Memory Usage of Solaris Operating System
How to move LOB data to another tablespace
We know with the ALTER TABLE .. MOVE clause we can relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment.
If you want to make no other changes to the table other than rebuilding it then your statement is simply,
SQL>ALTER TABLE table_name MOVE;
Or if you want to move it to another tablespace then specify,
SQL>ALTER TABLE table_name MOVE TABLESPACE tablespace_name;
With this statement it does not affect any of the lob segments associated with the lob columns in the table.
If you want to move only lob segment to a new tablespace then your command will be,
ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name);
Along with the log segment you can also move the table as well as storage attribute of table and log by following query,
ALTER TABLE table_name MOVE
TABLESPACE new_tablespace STORAGE(new_storage)
LOB (lobcol) STORE AS
(TABLESPACE new_tablespace STORAGE (new_storage));
If you want to move all the lobs contained in a tablespace of a particular user then you can follow .
Let's have a look lob column_name and table_name of the specified tablespace of owner ARJU.
Now execute the script move_table.scr after modifying it.
SQL>@move_table.scr
If you want to make no other changes to the table other than rebuilding it then your statement is simply,
SQL>ALTER TABLE table_name MOVE;
Or if you want to move it to another tablespace then specify,
SQL>ALTER TABLE table_name MOVE TABLESPACE tablespace_name;
With this statement it does not affect any of the lob segments associated with the lob columns in the table.
If you want to move only lob segment to a new tablespace then your command will be,
ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name);
Along with the log segment you can also move the table as well as storage attribute of table and log by following query,
ALTER TABLE table_name MOVE
TABLESPACE new_tablespace STORAGE(new_storage)
LOB (lobcol) STORE AS
(TABLESPACE new_tablespace STORAGE (new_storage));
If you want to move all the lobs contained in a tablespace of a particular user then you can follow .
Let's have a look lob column_name and table_name of the specified tablespace of owner ARJU.
SQL> col COLUMN_NAME format a20
SQL> col TABLE_NAME format a20
SQL> select owner, table_name, column_name from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name='USERS' and segment_type='LOBSEGMENT' and owner='ARJU');
OWNER TABLE_NAME COLUMN_NAME
------------------------------ -------------------- --------------------
ARJU TEST_LONG_LOB B
ARJU LOB_TAB COL2_LOB
ARJU LOB_TAB2 COL3
ARJU LOB_TAB2 COL2_LOB
set pagesize 0
set heading off
set lines 130
set feedback off
set verify off
set echo off
set termout off
spool move_table.scr
select 'alter table '||owner||'.'||table_name ||' move lob (' ||column_name||')' ||
'store as (tablespace DATA02);' from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name='USERS' and owner='ARJU' and segment_type='LOBSEGMENT');
spool off
Now execute the script move_table.scr after modifying it.
SQL>@move_table.scr
Saturday, September 13, 2008
Implicitly Assigned temp tablespace changes after database default tablespace change
In this post I have shown if you change your database default temporary tablespace then implicitly assigned users to previous temporary tablespace automatically assigned to the new database default temporary tablespace but the users to whom temporary tablespaces are explicitly assigned they are not shifted.
Let's start by seeing database default temporary tablespace which is temp4.
SQL> select PROPERTY_VALUE from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE'; 2
PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP4
Now if you create user then that user will be automatically assigned to temporary tablespace temp4 if we don't assign any. We create two users named Test1 and Test2. Create Test1 with no temp clause but create test2 with temp clause to temporary tablespace temp.
SQL> create user test1 identified by t;
User created.
SQL> create user test2 identified by t temporary tablespace temp;
User created.
As TEST1 is not assigned any tablespace, so database default temporary tablespace is assigned to it. We can see it by,
Now create a new temporary tablespace temp3.
SQL> create temporary tablespace temp3 tempfile '/oradata2/temp3.dbf' size 10M;
Tablespace created.
Let's chnage the database default temporary tablespace to temp3.
SQL> alter database default temporary tablespace temp3;
Database altered.
Related Documents
ORA-12906 cannot drop default temporary tablespace
Drop Temporary Tablespace Hangs
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.
Let's start by seeing database default temporary tablespace which is temp4.
SQL> select PROPERTY_VALUE from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE'; 2
PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP4
Now if you create user then that user will be automatically assigned to temporary tablespace temp4 if we don't assign any. We create two users named Test1 and Test2. Create Test1 with no temp clause but create test2 with temp clause to temporary tablespace temp.
SQL> create user test1 identified by t;
User created.
SQL> create user test2 identified by t temporary tablespace temp;
User created.
As TEST1 is not assigned any tablespace, so database default temporary tablespace is assigned to it. We can see it by,
SQL> select username, temporary_tablespace from dba_users where username in ('TEST1','TEST2');
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TEST1 TEMP4
TEST2 TEMP
Now create a new temporary tablespace temp3.
SQL> create temporary tablespace temp3 tempfile '/oradata2/temp3.dbf' size 10M;
Tablespace created.
Let's chnage the database default temporary tablespace to temp3.
SQL> alter database default temporary tablespace temp3;
Database altered.
Now we will see TEST1 temporary tablespace change but TEST2 temporary tablespace does not.
SQL> select username, temporary_tablespace from dba_users where username in ('TEST1','TEST2');
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TEST1 TEMP3
TEST2 TEMP
Related Documents
ORA-12906 cannot drop default temporary tablespace
Drop Temporary Tablespace Hangs
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.
ORA-12906 cannot drop default temporary tablespace
In this post I have shown how to solve the error ORA-12906: cannot drop default temporary tablespace.
Let's have a look about temporary tablespace assigned to users ARJU, PROD and SCOTT.
So These users are assigned to TEMP temporary tablespace.
Now have a look at database default tablespace.
SQL> select PROPERTY_VALUE from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP
Whenever you try to drop database default tablespace it fails with error ORA-12906.
SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
In order to solve this problem you must assign database default tenporary tablespace to a new one.
To do this create a new tempoary tablespace temp2 and then assign it to database default tablespace.
To create a new one,
SQL> create temporary tablespace temp2 tempfile '/oradata2/temp2.dbf' size 10M;
Tablespace created.
To make this new one to database default temporary tablespace,
SQL> alter database default temporary tablespace temp2;
Database altered.
After chaging database default temporary tablespace you will notice all user's temporary tablespace are also changed.
You can easily drop by,
SQL> drop tablespace temp;
Tablespace dropped.
Related Documents
Free space in Temporary Tablespace
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.
Let's have a look about temporary tablespace assigned to users ARJU, PROD and SCOTT.
SQL> select username, temporary_tablespace from dba_users where username in ('ARJU','SCOTT','PROD');
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
ARJU TEMP
PROD TEMP
SCOTT TEMP
So These users are assigned to TEMP temporary tablespace.
Now have a look at database default tablespace.
SQL> select PROPERTY_VALUE from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP
Whenever you try to drop database default tablespace it fails with error ORA-12906.
SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
In order to solve this problem you must assign database default tenporary tablespace to a new one.
To do this create a new tempoary tablespace temp2 and then assign it to database default tablespace.
To create a new one,
SQL> create temporary tablespace temp2 tempfile '/oradata2/temp2.dbf' size 10M;
Tablespace created.
To make this new one to database default temporary tablespace,
SQL> alter database default temporary tablespace temp2;
Database altered.
After chaging database default temporary tablespace you will notice all user's temporary tablespace are also changed.
SQL> select username, temporary_tablespace from dba_users where username in ('ARJU','SCOTT','PROD');
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
ARJU TEMP2
PROD TEMP2
SCOTT TEMP2
You can easily drop by,
SQL> drop tablespace temp;
Tablespace dropped.
Related Documents
Free space in Temporary Tablespace
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.
Friday, September 12, 2008
All Purge commands in Oracle -Delete an object permanently
Whenever we drop any objects other than objects reside in system tablespace using DROP command the objects became unavailable and reside in recyclebin. In fact they reside in the same tablespace where it was and thus does not free any space in the tablespace. In order to remove the objects permanently from the recyclebin we have to use PURGE command. With PURGE command we can do,
•Remove a table or index from your recycle bin and release all of the space associated with the object, or
•To remove the entire recycle bin, or
•To remove part of all of a dropped tablespace from the recycle bin.
However during drop you may wish not to put objects in recyclebin. With DROP command you may wish to drop it permanently not residing in recyclebin. Like,
SQL> create table t(a number);
Table created.
SQL> purge table t;
purge table t
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN
As PURGE command is only to drop objects from recyclebin so purge t fails with ORA-38307.
In order to drop t along with recyclebin use,
SQL> drop table t purge;
Table dropped.
To see the all contents of the recyclebin query from DBA_RECYCLEBIN. A user can see his own schma recylebin by querying from USER_RECYCLEBIN. A synonym of USER_RECYCLEBIN is RECYCLEBIN.
The list of Purge Commands are listed below.
1)Purge Table/Index:
•Use
PURGE TABLE table_name
to purge table and to purge index use
PURGE INDEX index_name.
To purge test table which reside in recyclebin now or in in other which was dropped previously by DROP TABLE TEST command and now in recyclebin issue,
PURGE TABLE TEST;
•In the name you can specify either the original user-specified name or the system-generated name in the recylebin.
•While purging objects from recylebin if you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database purges the object that has been in the recycle bin the longest.
•System generated name in recylebin are unique. So if you purge an object with system generated name specified object is purged.
•When the database purges a table, all table partitions, LOBs and LOB partitions,
indexes, and other dependent objects of that table are also purged.
2)PURGE RECYCLEBIN: To delete/remove objects of current users from the recylebin use PURGE RECYCLEBIN. Other schema users recyclebin objects will be intact. Command is
SQL>PURGE RECYCLEBIN;
3)PURGE DBA_RECYCLEBIN: To clean up all user's recylebin you can use PURGE DBA_RECYCLEBIN statement. Only user having SYSDBA system privilege can do this task. Command is simply,
SQL>CONN / AS SYSDBA
SQL>PURGE DBA_RECYCLEBIN;
4)PURGE TABLESPACE tablespace_name: With PURGE TABLESPACE tablespace clause you can purge all objects residing in the specified tablespace from the recycle bin. To clean up all objects that reside in recylebin of USERS tablespace use,
SQL>PURGE TABLESPACE USERS;
5)PURGE TABLESPACE tablespace USER username: With this clause you can reclaim space in a tablespace for a specified user. The specified user's objects from the specified tablespace recyclebin will be cleaned up. This operation is useful when a particular user is running low on disk quota for the specified tablespace.
To clean up recylebin of user ARJU from TABLESPACE USERS use,
SQL> PURGE TABLESPACE USERS USER ARJU;
Related Documents
Drop Table in Oracle
Flashback Table and Flashback Drop
•Remove a table or index from your recycle bin and release all of the space associated with the object, or
•To remove the entire recycle bin, or
•To remove part of all of a dropped tablespace from the recycle bin.
However during drop you may wish not to put objects in recyclebin. With DROP command you may wish to drop it permanently not residing in recyclebin. Like,
SQL> create table t(a number);
Table created.
SQL> purge table t;
purge table t
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN
As PURGE command is only to drop objects from recyclebin so purge t fails with ORA-38307.
In order to drop t along with recyclebin use,
SQL> drop table t purge;
Table dropped.
To see the all contents of the recyclebin query from DBA_RECYCLEBIN. A user can see his own schma recylebin by querying from USER_RECYCLEBIN. A synonym of USER_RECYCLEBIN is RECYCLEBIN.
The list of Purge Commands are listed below.
1)Purge Table/Index:
•Use
PURGE TABLE table_name
to purge table and to purge index use
PURGE INDEX index_name.
To purge test table which reside in recyclebin now or in in other which was dropped previously by DROP TABLE TEST command and now in recyclebin issue,
PURGE TABLE TEST;
•In the name you can specify either the original user-specified name or the system-generated name in the recylebin.
•While purging objects from recylebin if you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database purges the object that has been in the recycle bin the longest.
•System generated name in recylebin are unique. So if you purge an object with system generated name specified object is purged.
•When the database purges a table, all table partitions, LOBs and LOB partitions,
indexes, and other dependent objects of that table are also purged.
2)PURGE RECYCLEBIN: To delete/remove objects of current users from the recylebin use PURGE RECYCLEBIN. Other schema users recyclebin objects will be intact. Command is
SQL>PURGE RECYCLEBIN;
3)PURGE DBA_RECYCLEBIN: To clean up all user's recylebin you can use PURGE DBA_RECYCLEBIN statement. Only user having SYSDBA system privilege can do this task. Command is simply,
SQL>CONN / AS SYSDBA
SQL>PURGE DBA_RECYCLEBIN;
4)PURGE TABLESPACE tablespace_name: With PURGE TABLESPACE tablespace clause you can purge all objects residing in the specified tablespace from the recycle bin. To clean up all objects that reside in recylebin of USERS tablespace use,
SQL>PURGE TABLESPACE USERS;
5)PURGE TABLESPACE tablespace USER username: With this clause you can reclaim space in a tablespace for a specified user. The specified user's objects from the specified tablespace recyclebin will be cleaned up. This operation is useful when a particular user is running low on disk quota for the specified tablespace.
To clean up recylebin of user ARJU from TABLESPACE USERS use,
SQL> PURGE TABLESPACE USERS USER ARJU;
Related Documents
Drop Table in Oracle
Flashback Table and Flashback Drop
Subscribe to:
Posts (Atom)