•The data dictionary tables are the first objects created in Oracle database.
•They must be created and resided in SYSTEM tablespace.
•Whenever you create your database using dbca (Database Configuration Assistant) Oracle automatically creates the data dictionary.
•But if you create database manually which is described as in Manually Create an Oracle Database then to build or create data dictionary objects, you must run several scripts, while connecting to database as a user with SYSDBA privileges.
Here a list of scripts that you need to run to build data dictionary objects. You can get and run these scripts from $ORACLE_HOME/rdbms/admin/
1)catalog.sql: Creates the data dictionary and public synonyms for many of its views. It also grants PUBLIC access to the synonyms.
2)catproc.sql: This script builds all data dictionary objects required for PL/SQL.
3)catclust.sql: This script creates Real Application Clusters data dictionary views.
To know the additional scripts just have a look at,
Addiional Data Dictionary Scripts
Saturday, July 26, 2008
Tuesday, July 22, 2008
Process and Runtime Limits
1)Instances per database
Maximum number of cluster database instances per database: Operating system-dependent
2)Locks
Row-level: Unlimited
Distributed Lock Manager: Operating system dependent
3)SGA size
Maximum value: Operating system-dependent;
For 32 bit OS, 2GB - 4GB.
For 64 bit OS, >4GB.
4)Advanced Queuing Processes
Maximum per instance: 10
5)Job Queue Processes
Maximum per instance: 1000
6)I/O Slave Processes
Maximum per background process (DBWR, LGWR, ....): 15
Maximum per Backup session: 15
7)Sessions
Maximum per instance: 32 KB; Restricted by the PROCESSES and SESSIONS initialization parameters.
8)Global Cache Service Processes
Maximum per instance: 10
9)Shared Servers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
10)Dispatchers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
11)Parallel Execution Slaves
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
12)Backup Sessions
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
Related Document
Physical Database Limits in oracle
Datatype Limits in Oracle
Logical Database Limits in Oracle
Maximum number of cluster database instances per database: Operating system-dependent
2)Locks
Row-level: Unlimited
Distributed Lock Manager: Operating system dependent
3)SGA size
Maximum value: Operating system-dependent;
For 32 bit OS, 2GB - 4GB.
For 64 bit OS, >4GB.
4)Advanced Queuing Processes
Maximum per instance: 10
5)Job Queue Processes
Maximum per instance: 1000
6)I/O Slave Processes
Maximum per background process (DBWR, LGWR, ....): 15
Maximum per Backup session: 15
7)Sessions
Maximum per instance: 32 KB; Restricted by the PROCESSES and SESSIONS initialization parameters.
8)Global Cache Service Processes
Maximum per instance: 10
9)Shared Servers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
10)Dispatchers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
11)Parallel Execution Slaves
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
12)Backup Sessions
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
Related Document
Physical Database Limits in oracle
Datatype Limits in Oracle
Logical Database Limits in Oracle
Logical Database Limits in Oracle
1)CREATE MATERIALIZED VIEW definition size Limit
Maximum size: 64K Bytes
2)GROUP BY clause size Limit
Maximum length: Must fit within a single database block.
3)Indexes Limit
Maximum per table: Unlimited
Total size of indexed column: 75% of the database block size minus some overhead
4)Columns Limit
Maximum Per table: 1000
Maximum Per index (or clustered index): 32
Maximum Per bitmapped index: 30
5)Constraints
Maximum per column: Unlimited
6)Subqueries
Maximum levels of subqueries in a SQL statement in the FORM clause of top-level query: Unlimited
Maximum levels of subqueries in a SQL statement in the WHERE clause: 255
7)Partitions
Maximum length of linear partitioning key: 4 KB - overhead
Maximum number of columns in partition key: 16 columns
Maximum number of partitions allowed per table or index: 1024K - 1
8)Rows
Maximum number per table: Unlimited
9)Stored Packages
Maximum size: Limits typically range from 2000 to 3000 lines of code.
10)Trigger Cascade Limit
Maximum value: Operating system-dependent, typically 32
11)Users and Roles
Maximum: 2,147,483,638
12)Tables
Maximum per clustered table: 32 tables
Maximum per database: Unlimited
Related Documents
Physical Database Limits in oracle
Datatype Limits in Oracle
Maximum size: 64K Bytes
2)GROUP BY clause size Limit
Maximum length: Must fit within a single database block.
3)Indexes Limit
Maximum per table: Unlimited
Total size of indexed column: 75% of the database block size minus some overhead
4)Columns Limit
Maximum Per table: 1000
Maximum Per index (or clustered index): 32
Maximum Per bitmapped index: 30
5)Constraints
Maximum per column: Unlimited
6)Subqueries
Maximum levels of subqueries in a SQL statement in the FORM clause of top-level query: Unlimited
Maximum levels of subqueries in a SQL statement in the WHERE clause: 255
7)Partitions
Maximum length of linear partitioning key: 4 KB - overhead
Maximum number of columns in partition key: 16 columns
Maximum number of partitions allowed per table or index: 1024K - 1
8)Rows
Maximum number per table: Unlimited
9)Stored Packages
Maximum size: Limits typically range from 2000 to 3000 lines of code.
10)Trigger Cascade Limit
Maximum value: Operating system-dependent, typically 32
11)Users and Roles
Maximum: 2,147,483,638
12)Tables
Maximum per clustered table: 32 tables
Maximum per database: Unlimited
Related Documents
Physical Database Limits in oracle
Datatype Limits in Oracle
Physical Database Limits in oracle
1)Data Block Size Limit
Minimum: 2048 bytes; must be a multiple of operating system physical block size
Maximum: Operating system dependent; Not more than 32 KB
2)Data Blocks Limit
Minimum in initial extent of a segment: 2 data blocks
Maximum per datafile: Platform dependent; typically power(2,22) - 1 blocks
3)Controlfiles Limit
Number of control files: 1 minimum; 2 or more (on separate devices) strongly recommended.
Size of a control file: Maximum of 20,000 * (database block size)
4)Datafiles Limit
Maximum per tablespace: 1022
Maximum per database: 65533
Also, limited by the DB_FILES initialization parameter for a particular instance.
5)Database extents Limit
Maximum per dictionary managed tablespace: 4 GB * physical block size.
Maximum per locally managed (uniform) tablespace: 2 GB * physical block size.
6)Database file size Limit
Limited by maximum operating system file size.
7)MAXEXTENTS Limit
Default value: Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
Maximum: Unlimited
8)Redo Log Files Limit
Maximum number of logfiles: Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement.
Maximum number of logfiles per group: Unlimited
9)Redo Log File Size Limit
Minimum size: 50 KB
Maximum size: Operating system limit.
10)Tablespaces:
Maximum number per database: 65533
11)Bigfile Tablespaces
Number of blocks: power(2,32)
12)Smallfile Tablespaces:
Number of blocks: power(2,22)
13)External Tables file
Maximum size: OS dependent.
Minimum: 2048 bytes; must be a multiple of operating system physical block size
Maximum: Operating system dependent; Not more than 32 KB
2)Data Blocks Limit
Minimum in initial extent of a segment: 2 data blocks
Maximum per datafile: Platform dependent; typically power(2,22) - 1 blocks
3)Controlfiles Limit
Number of control files: 1 minimum; 2 or more (on separate devices) strongly recommended.
Size of a control file: Maximum of 20,000 * (database block size)
4)Datafiles Limit
Maximum per tablespace: 1022
Maximum per database: 65533
Also, limited by the DB_FILES initialization parameter for a particular instance.
5)Database extents Limit
Maximum per dictionary managed tablespace: 4 GB * physical block size.
Maximum per locally managed (uniform) tablespace: 2 GB * physical block size.
6)Database file size Limit
Limited by maximum operating system file size.
7)MAXEXTENTS Limit
Default value: Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
Maximum: Unlimited
8)Redo Log Files Limit
Maximum number of logfiles: Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement.
Maximum number of logfiles per group: Unlimited
9)Redo Log File Size Limit
Minimum size: 50 KB
Maximum size: Operating system limit.
10)Tablespaces:
Maximum number per database: 65533
11)Bigfile Tablespaces
Number of blocks: power(2,32)
12)Smallfile Tablespaces:
Number of blocks: power(2,22)
13)External Tables file
Maximum size: OS dependent.
Char, Varchar2, Long etc Datatype Limits in Oracle
1)BFILE:
Maximum size: 4 GB-1 which is power(2,32)-1 bytes.
Maximum size of the file name: 255 characters
Maximum size of the directory name: 30 characters
Maximum number of open BFILEs: Limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which itself is limited by the maximum number of open files the OS will allow.
2)BLOB:
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter/ LOB Chunk size.
So, if db_block_size=8024K then maximum size=32T
SQL> select 4*1024*1024*1024*8*1024/1024/1024/1024/1024 from dual;
4*1024*1024*1024*8*1024/1024/1024/1024/1024
-------------------------------------------
32
As database block size vary from 2K to 32K so BLOB size can vary from 8TB to 128TB.
(8 TB to 128 TB)
Number of LOB columns per table: Limited by the maximum number of columns per table where maximum can be 1000.
3)CHAR:
Maximum size: 2000 bytes
Minimum and Default Size: 1 byte
4)CHAR VARYING
Maximum size: 4000 bytes
5)CLOB
Same as BLOB in the range of 4T to 128T.
6)Literals (characters or numbers in SQL or PL/SQL)
Maximum size: 4000 characters
7)LONG
Maximum size: 2 GB - 1
8)NCHAR
Maximum size: 2000 bytes
9)NCHAR VARYING
Maximum size: 4000 bytes
10)NCLOB
Same as BLOB in the range of 4T to 128T
11)NUMBER
Maximum size: 999...(in this way 38 9s) * power(10,125)
Minimum size: -999...(in this way 38 9s) *power(10,125)
12)RAW
Maximum size: 2000 bytes
13)VARCHAR
Maximum size: 4000 bytes
Minimum size: 1 byte.
14)VARCHAR2
Maximum size: 4000 bytes
Minimum size: 1 byte.
Maximum size: 4 GB-1 which is power(2,32)-1 bytes.
Maximum size of the file name: 255 characters
Maximum size of the directory name: 30 characters
Maximum number of open BFILEs: Limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which itself is limited by the maximum number of open files the OS will allow.
2)BLOB:
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter/ LOB Chunk size.
So, if db_block_size=8024K then maximum size=32T
SQL> select 4*1024*1024*1024*8*1024/1024/1024/1024/1024 from dual;
4*1024*1024*1024*8*1024/1024/1024/1024/1024
-------------------------------------------
32
As database block size vary from 2K to 32K so BLOB size can vary from 8TB to 128TB.
(8 TB to 128 TB)
Number of LOB columns per table: Limited by the maximum number of columns per table where maximum can be 1000.
3)CHAR:
Maximum size: 2000 bytes
Minimum and Default Size: 1 byte
4)CHAR VARYING
Maximum size: 4000 bytes
5)CLOB
Same as BLOB in the range of 4T to 128T.
6)Literals (characters or numbers in SQL or PL/SQL)
Maximum size: 4000 characters
7)LONG
Maximum size: 2 GB - 1
8)NCHAR
Maximum size: 2000 bytes
9)NCHAR VARYING
Maximum size: 4000 bytes
10)NCLOB
Same as BLOB in the range of 4T to 128T
11)NUMBER
Maximum size: 999...(in this way 38 9s) * power(10,125)
Minimum size: -999...(in this way 38 9s) *power(10,125)
12)RAW
Maximum size: 2000 bytes
13)VARCHAR
Maximum size: 4000 bytes
Minimum size: 1 byte.
14)VARCHAR2
Maximum size: 4000 bytes
Minimum size: 1 byte.
Monday, July 21, 2008
Benefits and Considerations of Using Bigfile tablespaces
Benefits of Bigfile Tablespaces
•In a database there can have maximum 65533 data files. Database is limited by the datafiles to 65533. In other way we can say in a database there can have maximum 65533 tablespaces because each tablespace must include at least one file.
Smallfile tablespace can contain up to 1024 files, but bigfile tablespaces contain only single file that can be 1024 times larger than a smallfile tablespace. Since database is limited by the total tablespace 65533 or in other word by the total data files 65533 and since bigfile tablespace's datafile can be 1024 times larger than of smallfile tablespace maximum size of database can largely expanded if you use bigfile tablespace.
The detail calculation of overall database size with the bigfile tablespace and smallfile tablespace are discussed in Overall Database Size
•With the use of bigfile tablespace you can reduce the number of datafiles of a ultra large tablespace and thus we can manage the database well. You can also adjust parameters to reduce the SGA space required for datafile information and the size of the control file.
•If you use Oracle-managed files to name the datafile, bigfile tablespaces make datafiles completely transparent for users as bigfile tablespaces contain only one datafile.
Considerations with Bigfile Tablespaces
•We should consider to use bigfile tablespace with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.
•We should not use bigfile tablespace on a system that does not support striping because of negative implications for parallel execution and RMAN backup parallelization.
•We should not use bigfile tablespace if there is free space problem on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.
•We should not use bigfile tablespace on the system that impose limit to large file sizes.
•Overall, performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tablespaces instead of smallfile tablespaces. Though in case of corrupted datafile restore time of the datafile is more than of smallfile tablespace case.
Related Documents
Overall Database Size
•In a database there can have maximum 65533 data files. Database is limited by the datafiles to 65533. In other way we can say in a database there can have maximum 65533 tablespaces because each tablespace must include at least one file.
Smallfile tablespace can contain up to 1024 files, but bigfile tablespaces contain only single file that can be 1024 times larger than a smallfile tablespace. Since database is limited by the total tablespace 65533 or in other word by the total data files 65533 and since bigfile tablespace's datafile can be 1024 times larger than of smallfile tablespace maximum size of database can largely expanded if you use bigfile tablespace.
The detail calculation of overall database size with the bigfile tablespace and smallfile tablespace are discussed in Overall Database Size
•With the use of bigfile tablespace you can reduce the number of datafiles of a ultra large tablespace and thus we can manage the database well. You can also adjust parameters to reduce the SGA space required for datafile information and the size of the control file.
•If you use Oracle-managed files to name the datafile, bigfile tablespaces make datafiles completely transparent for users as bigfile tablespaces contain only one datafile.
Considerations with Bigfile Tablespaces
•We should consider to use bigfile tablespace with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.
•We should not use bigfile tablespace on a system that does not support striping because of negative implications for parallel execution and RMAN backup parallelization.
•We should not use bigfile tablespace if there is free space problem on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.
•We should not use bigfile tablespace on the system that impose limit to large file sizes.
•Overall, performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tablespaces instead of smallfile tablespaces. Though in case of corrupted datafile restore time of the datafile is more than of smallfile tablespace case.
Related Documents
Overall Database Size
Overview of Extents and when extents are allocated
Extents is made up of a number of contiguous data blocks. One or more extents make a segment. When in a segment there is no space i.e segment is full, then to allow more space oracle allocates a new extent in that segment.
When Extents Are Allocated
When you create a table or index then oracle automatically creates an initial extent with a number of specified data blocks (with how many data blocks will be discussed later on) under the table's/ index's data segment.
In this case though no rows are inserted into the table or index but yet extents are allocated and reserve for the table's or index's rows.
Now, if the data blocks of a segment's initial extent become full and more space is required to hold new data, then oracle automatically allocates an incremental extent for that segment.
In this way, subsequent increment extents are allocated whenever data blocks of a segment's extent full.
When Extents Are Deallocated
With the Segment Advisor oracle automatically advice on which schema objects or on which tablespaces has space available for reclamation based on the level of space fragmentation within the object.
To access Segment Advisor from enterprise manager home go to Related Links> Select Advisor Central>Select Segment Advisor.
You can reclaim space manually of LB table by
alter table "LB" enable row movement;
alter table "LB" shrink space;
In general the extents of a segment do not return to the tablespace until you drop the schema objects using DROP TABLE or DROP CLUSTER statement.
The exception is following,
•TRUNCATE...DROP STORAGE statement.
•ALTER TABLE table_name DEALLOCATE UNUSED;
•Periodically, Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.
When Extents Are Allocated
When you create a table or index then oracle automatically creates an initial extent with a number of specified data blocks (with how many data blocks will be discussed later on) under the table's/ index's data segment.
In this case though no rows are inserted into the table or index but yet extents are allocated and reserve for the table's or index's rows.
Now, if the data blocks of a segment's initial extent become full and more space is required to hold new data, then oracle automatically allocates an incremental extent for that segment.
In this way, subsequent increment extents are allocated whenever data blocks of a segment's extent full.
When Extents Are Deallocated
With the Segment Advisor oracle automatically advice on which schema objects or on which tablespaces has space available for reclamation based on the level of space fragmentation within the object.
To access Segment Advisor from enterprise manager home go to Related Links> Select Advisor Central>Select Segment Advisor.
You can reclaim space manually of LB table by
alter table "LB" enable row movement;
alter table "LB" shrink space;
In general the extents of a segment do not return to the tablespace until you drop the schema objects using DROP TABLE or DROP CLUSTER statement.
The exception is following,
•TRUNCATE...DROP STORAGE statement.
•ALTER TABLE table_name DEALLOCATE UNUSED;
•Periodically, Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.
Overview of Oracle Data Blocks
•Oracle data block is smallest unit of data that oracle use to store data.
•Oracle store data in the datafile in terms of oracle data block.
•As we know each operating system has a block size. Oracle data block is not same as operating system block size. In fact oracle block size is multiple of OS block size.
•How much of a standard oracle block size will be is defined by the DB_BLOCK_SIZE initialization parameter.
•We can also define up to five non standard block sizes.
•Data block format is discussed in Oracle data block format
•How we can choose non standard block size is discussed in Choose of a non standard Blocksize
•Oracle store data in the datafile in terms of oracle data block.
•As we know each operating system has a block size. Oracle data block is not same as operating system block size. In fact oracle block size is multiple of OS block size.
•How much of a standard oracle block size will be is defined by the DB_BLOCK_SIZE initialization parameter.
•We can also define up to five non standard block sizes.
•Data block format is discussed in Oracle data block format
•How we can choose non standard block size is discussed in Choose of a non standard Blocksize
More Internet Explorer Keyboard Shortcuts
Shortcuts to Access Address Box
Note that if internet explorer's tab are enabled then for only the first tab these shortcuts work well. For subsequent tabs these shortcuts do not work.
1)ALT+D: These two combination select the text in address box so that you can give new address or edit existing address in the address box.
2)F4: It displays a list of addresses that you have in your address box.
3)CTRL+LEFT ARROW: When in the Address box, move the cursor left to the next logical break in the address (period or slash or hypen etc). First press F4 and then press CTRL+LEFT ARROW.
4)CTRL+RIGHT ARROW: When in the Address box, move the cursor right to the next logical break in the address (period or slash or hypen etc). First press F4 and then press CTRL+RIGHT ARROW.
5)CTRL+ENTER: In the address box after typing address when you press these two kwy combination it automatically adds www. at the begining and .com at the end. Suppose press ALT+D and simply type google and then press CTRL+ENTER and then observe it will
go as www.google.com
6)UP ARROW: Move forward through the list of AutoComplete matches.
7)DOWN ARROW: Move back through the list of AutoComplete matches.
Shortcuts to Access Favourites
1)CTRL+D: Add the current page to your favorites.
2)CTRL+B: Open the Organize Favorites dialog box.
3)ALT+UP ARROW: Move selected item up in the Favorites list in the Organize Favorites dialog box.
4)ALT+DOWN ARROW: Move selected item down in the Favorites list in the Organize Favorites dialog box
Shortcut to print preview
Press ALTER+P and then v in order to come print preview page. In the print preview page you can apply following shortcut keys.
1)ALT+P:Set printing options and print the page.
2)ALT+U: Change paper, headers and footers, orientation, and margins for this page.
3)ALT+HOME: Display the first page that to be printed.
4)ALT+LEFT ARROW: Display the previous page that to be printed.
5)ALT+A: Type the page number that you want displayed.
6)ALT+RIGHT ARROW: Display the next page that to be printed.
7)ALT+END: Display the last page that to be printed.
8)ALT+-:Zoom out.
9)ALT++:Zoom in.
10)ALT+Z: Display a list of zoom percentages.
11)ALT+C: Close Print Preview.
Related Documents:
Natural Windows Keyboard Shortcut
General Windows keyboard Shortcut.
Dialog Box and Accessibility Keyboard Shortcut
Note that if internet explorer's tab are enabled then for only the first tab these shortcuts work well. For subsequent tabs these shortcuts do not work.
1)ALT+D: These two combination select the text in address box so that you can give new address or edit existing address in the address box.
2)F4: It displays a list of addresses that you have in your address box.
3)CTRL+LEFT ARROW: When in the Address box, move the cursor left to the next logical break in the address (period or slash or hypen etc). First press F4 and then press CTRL+LEFT ARROW.
4)CTRL+RIGHT ARROW: When in the Address box, move the cursor right to the next logical break in the address (period or slash or hypen etc). First press F4 and then press CTRL+RIGHT ARROW.
5)CTRL+ENTER: In the address box after typing address when you press these two kwy combination it automatically adds www. at the begining and .com at the end. Suppose press ALT+D and simply type google and then press CTRL+ENTER and then observe it will
go as www.google.com
6)UP ARROW: Move forward through the list of AutoComplete matches.
7)DOWN ARROW: Move back through the list of AutoComplete matches.
Shortcuts to Access Favourites
1)CTRL+D: Add the current page to your favorites.
2)CTRL+B: Open the Organize Favorites dialog box.
3)ALT+UP ARROW: Move selected item up in the Favorites list in the Organize Favorites dialog box.
4)ALT+DOWN ARROW: Move selected item down in the Favorites list in the Organize Favorites dialog box
Shortcut to print preview
Press ALTER+P and then v in order to come print preview page. In the print preview page you can apply following shortcut keys.
1)ALT+P:Set printing options and print the page.
2)ALT+U: Change paper, headers and footers, orientation, and margins for this page.
3)ALT+HOME: Display the first page that to be printed.
4)ALT+LEFT ARROW: Display the previous page that to be printed.
5)ALT+A: Type the page number that you want displayed.
6)ALT+RIGHT ARROW: Display the next page that to be printed.
7)ALT+END: Display the last page that to be printed.
8)ALT+-:Zoom out.
9)ALT++:Zoom in.
10)ALT+Z: Display a list of zoom percentages.
11)ALT+C: Close Print Preview.
Related Documents:
Natural Windows Keyboard Shortcut
General Windows keyboard Shortcut.
Dialog Box and Accessibility Keyboard Shortcut
Archiving not possible: No primary destinations
Error Description:
Today I got a very interesting problem. The developer told me that they can't connect to database. It says ORA-00257: archiver error. Then I log on to the database and it was ok. I asked developer it is fine. After some time developers again asked me they are having the same problem. I then look for alert log and saw the following stack of message.
ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)
Cause of The Problem:
Unknown yet. Possibly hit oracle bug.
Solution of The Problem:
At first seems I guessed there is space issue and look for following thing as in described,
ORA-00257: archiver error. Connect internal only, until freed.
Step1: Look for archival destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1203
Next log sequence to archive 1205
Current log sequence 1205
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata1
db_recovery_file_dest_size big integer 50G
I also checked for settings whether LOG_ARCHIVE_DEST_10 is implicitly set or not. Yet it was set.
SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_10';
DEST_NAME
--------------------------------------------------------------------------------
DESTINATION
--------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_10
USE_DB_RECOVERY_FILE_DEST
Step2: Let's check space in flash recovery area.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oradata1
5.3687E+10 32381952 0 2
So it was fine. I suddenly look for alert log and it was changed to
ORA-16038: log 2 sequence# 1160 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/oradata1/arju/ARJU/redo02.log'
I immediately try to clear the logfile as it is described in ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
^C alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
But no hope. It stopped and in alert log again it shows error message Archiving not possible: No primary destinations.
Step3: I set LOG_ARCHIVE_DEST_9 explicitly to DB_RECOVERY_FILE_DEST
With the following statement whenever I explicitly set LOG_ARCHIVE_DEST_9 to use DB_RECOVERY_FILE_DEST then the error gone.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
In the alert log I put tail -f and wanted to see that status. Immediately status becomes as follows,
Cleared LOG_ARCHIVE_DEST_10 parameter default value
Mon Jul 21 05:52:04 2008
ALTER SYSTEM SET log_archive_dest_9='LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;
Mon Jul 21 05:52:04 2008
Archiver process freed from errors. No longer stopped
And error gone. I tested with alter system switch logfile and it went normal.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
I don't know what happened. Possibly this is oracle bug. Later I explicitly set log_archive_dest_9 to use LOCATION USE_DB_RECOVERY_FILE_DEST and unset log_archive_dest_9 and it went normal.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='';
System altered.
Related Documents
ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
ORA-00257: archiver error. Connect internal only, until freed.
Today I got a very interesting problem. The developer told me that they can't connect to database. It says ORA-00257: archiver error. Then I log on to the database and it was ok. I asked developer it is fine. After some time developers again asked me they are having the same problem. I then look for alert log and saw the following stack of message.
ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)
Cause of The Problem:
Unknown yet. Possibly hit oracle bug.
Solution of The Problem:
At first seems I guessed there is space issue and look for following thing as in described,
ORA-00257: archiver error. Connect internal only, until freed.
Step1: Look for archival destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1203
Next log sequence to archive 1205
Current log sequence 1205
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata1
db_recovery_file_dest_size big integer 50G
I also checked for settings whether LOG_ARCHIVE_DEST_10 is implicitly set or not. Yet it was set.
SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_10';
DEST_NAME
--------------------------------------------------------------------------------
DESTINATION
--------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_10
USE_DB_RECOVERY_FILE_DEST
Step2: Let's check space in flash recovery area.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oradata1
5.3687E+10 32381952 0 2
So it was fine. I suddenly look for alert log and it was changed to
ORA-16038: log 2 sequence# 1160 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/oradata1/arju/ARJU/redo02.log'
I immediately try to clear the logfile as it is described in ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
^C alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
But no hope. It stopped and in alert log again it shows error message Archiving not possible: No primary destinations.
Step3: I set LOG_ARCHIVE_DEST_9 explicitly to DB_RECOVERY_FILE_DEST
With the following statement whenever I explicitly set LOG_ARCHIVE_DEST_9 to use DB_RECOVERY_FILE_DEST then the error gone.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
In the alert log I put tail -f and wanted to see that status. Immediately status becomes as follows,
Cleared LOG_ARCHIVE_DEST_10 parameter default value
Mon Jul 21 05:52:04 2008
ALTER SYSTEM SET log_archive_dest_9='LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;
Mon Jul 21 05:52:04 2008
Archiver process freed from errors. No longer stopped
And error gone. I tested with alter system switch logfile and it went normal.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
I don't know what happened. Possibly this is oracle bug. Later I explicitly set log_archive_dest_9 to use LOCATION USE_DB_RECOVERY_FILE_DEST and unset log_archive_dest_9 and it went normal.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='';
System altered.
Related Documents
ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
ORA-00257: archiver error. Connect internal only, until freed.
Sunday, July 20, 2008
Logminer fails with ORA-01284, ORA-00308, ORA-27047
Problem Description:
Whenever you try to run the logminer in the mining database (different from source database. Source database is one in which redo or archived redo logs are generated and mining database is one in which you try to analysis those logs) to add logfile using DBMS_LOGMNR.ADD_LOGFILE it generates error ORA-01284, ORA-00308, ORA-27047.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/export/home/oracle/o1_mf_1_61856_48637xkh_.arc', -
OPTIONS => DBMS_LOGMNR.NEW);
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/o1_mf_1_61856_48637xkh_.arc', OPTIONS => DBMS_LOGMNR.NEW); END;
*
ERROR at line 1:
ORA-01284: file /export/home/oracle/o1_mf_1_61856_48637xkh_.arc cannot be
opened
ORA-00308: cannot open archived log
'/export/home/oracle/o1_mf_1_61856_48637xkh_.arc'
ORA-27047: unable to read the header block of file
Additional information: 2
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 1
But if you see in your mining database hard disk the archived redo log file actually exist.
SQL> !ls -l /export/home/oracle/o1_mf_1_61856_48637xkh_.arc
-rwxrwxrwx 1 oracle oinstall 24671232 Jul 21 00:38 /export/home/oracle/o1_mf_1_61856_48637xkh_.arc
Cause of The Problem:
To be able to run oracle logminer there are several requirements. If the requirements are not satisfy logminer will not run successfully. One of the requirement to run logminer is both the source database and the mining database must be running on the same hardware platform.
The database block sizes of the analyzing instance and the log source database must also be the same.
If they are different then logminer will not work and working with it will produce error ORA-01284, ORA-00308, ORA-27047.
Solution of The problem:
Use the mining database as the same hardware platform as of source database. If you don't have same platform then it is not possible. In that case you can use source database for mining.
Whenever you try to run the logminer in the mining database (different from source database. Source database is one in which redo or archived redo logs are generated and mining database is one in which you try to analysis those logs) to add logfile using DBMS_LOGMNR.ADD_LOGFILE it generates error ORA-01284, ORA-00308, ORA-27047.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/export/home/oracle/o1_mf_1_61856_48637xkh_.arc', -
OPTIONS => DBMS_LOGMNR.NEW);
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/o1_mf_1_61856_48637xkh_.arc', OPTIONS => DBMS_LOGMNR.NEW); END;
*
ERROR at line 1:
ORA-01284: file /export/home/oracle/o1_mf_1_61856_48637xkh_.arc cannot be
opened
ORA-00308: cannot open archived log
'/export/home/oracle/o1_mf_1_61856_48637xkh_.arc'
ORA-27047: unable to read the header block of file
Additional information: 2
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 1
But if you see in your mining database hard disk the archived redo log file actually exist.
SQL> !ls -l /export/home/oracle/o1_mf_1_61856_48637xkh_.arc
-rwxrwxrwx 1 oracle oinstall 24671232 Jul 21 00:38 /export/home/oracle/o1_mf_1_61856_48637xkh_.arc
Cause of The Problem:
To be able to run oracle logminer there are several requirements. If the requirements are not satisfy logminer will not run successfully. One of the requirement to run logminer is both the source database and the mining database must be running on the same hardware platform.
The database block sizes of the analyzing instance and the log source database must also be the same.
If they are different then logminer will not work and working with it will produce error ORA-01284, ORA-00308, ORA-27047.
Solution of The problem:
Use the mining database as the same hardware platform as of source database. If you don't have same platform then it is not possible. In that case you can use source database for mining.
How to Load or copy data from SQL Server or excel to Oracle
If you want any software and automatic conversion to migrate non oracle database to oracle then you can use Oracle SQL Developer Migration Workbench which can be used to migrate Microsoft Access, Microsoft SQL Server, MySQL and Sybase databases to Oracle.
However you wish to load a table sample data from non-oracle to oracle database. You may wish to do the task manually. Below is the manual procedure by which you can load data to oracle. It explain also if you have data in a flat file then how you can can able to load it into your oracle database.
Though at first time it may seem to you a difficult one but in this post I will try to make it easy. The steps involved to copy SQL Server data to an oracle database is given below. However this procedure is also applied if you want to import data from an excel flat file to an oracle database.
Step 1: Export Data to a CSV file:
You have to proceed table by table if you want to copy data from SQL Server database to Oracle.
For each table data you have to export data to a flat file and convert it to a CSV file.
Don't bother with .CSV or name CSV extension. It is nothing just abbreviate form of Comma Separated Values. If you save a normal text file with the comma between the record parts then that file is a CSV file.
You can directly export data in a CSV file from SQL SERVER database or just export data to a flat file and then convert it to a CSV file.
Export data to a flat file is discussed in http://arjudba.blogspot.com/2008/05/how-to-export-data-to-flat-file.html. Now open this file with excel and from excel file you can easily convert to a CSV file. To do it just open the excel file and >click file manu and >select save as. A pop up window will be displayed. Go to Save as Type section and select .CSV extention and click on save button. You now have got the .CSV file and you have finished step 1.
It will be more easily if you can directly export data to a CSV format. Easily you can do by separating column value of the table with an extension.
Like, you want to copy or load emp table from sql server to oracle.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NUMBER
EMP_NAME VARCHAR2(10)
DEPT_NO NUMBER
SQL> select emp_no ||','||emp_name ||','||dept_no from emp;
EMP_NO||','||EMP_NAME||','||DEPT_NO
--------------------------------------------------------------------------------
1,ddd,10
2,aaa,11
3,bbb,10
Save the output to a emp.csv file.
Step 2: Create a Control file:
In this are emp.csv is called datafile where data of the table to be loaded exists. Never mix with datafile of oracle with this emp.csv. This one is SQL*Loader datafile and oracle datafile are of .dbf extension. After successfully creating data file create a control file. Also don't mix this control file with database control file. This control file instructs SQL*loader how to load data.
Here is the control file. In my other posts of my blog I will go detail with it.
LOAD DATA
INFILE '/export/home/oracle/emp.dat'
INTO TABLE emp
FIELDS TERMINATED BY ','
(emp_no CHAR(2), emp_name CHAR(10), dept_no CHAR(2))
Note that whether datatype is number or varchar2 in control file it is specified as CHAR.
I save the control file as emp.ctl
Step3: Go to oracle database and create the emp table.
I created as below.
CREATE TABLE ARJU.EMP
( EMP_NO NUMBER,
EMP_NAME VARCHAR2(10),
DEPT_NO NUMBER
)TABLESPACE USER_TBS;
Table created.
Step 4: Invoke SQL*Loader and load data.
Copy datafile, control file to the oracle database and invoke sqlldr to load data.
$sqlldr arju/a control=/export/home/oracle/emp.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 20 06:21:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 3
Let's check logfile if any error.
bash-3.00$ cat emp.log
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 20 06:21:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: /export/home/oracle/emp.ctl
Data File: /export/home/oracle/emp.dat
Bad File: /export/home/oracle/emp.bad
.
.
Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Step 5: See the data from database and You are done:
SQL> select * from emp;
EMP_NO EMP_NAME DEPT_NO
---------- ---------- ----------
1 ddd 10
2 aaa 11
3 bbb 10
However you wish to load a table sample data from non-oracle to oracle database. You may wish to do the task manually. Below is the manual procedure by which you can load data to oracle. It explain also if you have data in a flat file then how you can can able to load it into your oracle database.
Though at first time it may seem to you a difficult one but in this post I will try to make it easy. The steps involved to copy SQL Server data to an oracle database is given below. However this procedure is also applied if you want to import data from an excel flat file to an oracle database.
Step 1: Export Data to a CSV file:
You have to proceed table by table if you want to copy data from SQL Server database to Oracle.
For each table data you have to export data to a flat file and convert it to a CSV file.
Don't bother with .CSV or name CSV extension. It is nothing just abbreviate form of Comma Separated Values. If you save a normal text file with the comma between the record parts then that file is a CSV file.
You can directly export data in a CSV file from SQL SERVER database or just export data to a flat file and then convert it to a CSV file.
Export data to a flat file is discussed in http://arjudba.blogspot.com/2008/05/how-to-export-data-to-flat-file.html. Now open this file with excel and from excel file you can easily convert to a CSV file. To do it just open the excel file and >click file manu and >select save as. A pop up window will be displayed. Go to Save as Type section and select .CSV extention and click on save button. You now have got the .CSV file and you have finished step 1.
It will be more easily if you can directly export data to a CSV format. Easily you can do by separating column value of the table with an extension.
Like, you want to copy or load emp table from sql server to oracle.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NUMBER
EMP_NAME VARCHAR2(10)
DEPT_NO NUMBER
SQL> select emp_no ||','||emp_name ||','||dept_no from emp;
EMP_NO||','||EMP_NAME||','||DEPT_NO
--------------------------------------------------------------------------------
1,ddd,10
2,aaa,11
3,bbb,10
Save the output to a emp.csv file.
Step 2: Create a Control file:
In this are emp.csv is called datafile where data of the table to be loaded exists. Never mix with datafile of oracle with this emp.csv. This one is SQL*Loader datafile and oracle datafile are of .dbf extension. After successfully creating data file create a control file. Also don't mix this control file with database control file. This control file instructs SQL*loader how to load data.
Here is the control file. In my other posts of my blog I will go detail with it.
LOAD DATA
INFILE '/export/home/oracle/emp.dat'
INTO TABLE emp
FIELDS TERMINATED BY ','
(emp_no CHAR(2), emp_name CHAR(10), dept_no CHAR(2))
Note that whether datatype is number or varchar2 in control file it is specified as CHAR.
I save the control file as emp.ctl
Step3: Go to oracle database and create the emp table.
I created as below.
CREATE TABLE ARJU.EMP
( EMP_NO NUMBER,
EMP_NAME VARCHAR2(10),
DEPT_NO NUMBER
)TABLESPACE USER_TBS;
Table created.
Step 4: Invoke SQL*Loader and load data.
Copy datafile, control file to the oracle database and invoke sqlldr to load data.
$sqlldr arju/a control=/export/home/oracle/emp.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 20 06:21:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 3
Let's check logfile if any error.
bash-3.00$ cat emp.log
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 20 06:21:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: /export/home/oracle/emp.ctl
Data File: /export/home/oracle/emp.dat
Bad File: /export/home/oracle/emp.bad
.
.
Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Step 5: See the data from database and You are done:
SQL> select * from emp;
EMP_NO EMP_NAME DEPT_NO
---------- ---------- ----------
1 ddd 10
2 aaa 11
3 bbb 10
Numeric REMAINDER ROUND SIGN SIN SINH SQRT TAN TANH TRUNC function
1)REMAINDER
The function REMAINDER holds the syntax REMAINDER(n2,n1) and returns the remainder of n2 divided by n1. This function is similar to MOD function except that MOD uses FLOOR in its formula, whereas REMAINDER uses ROUND.
Example:
SQL> SELECT REMAINDER(11,3) FROM DUAL;
REMAINDER(11,3)
---------------
-1
SQL> SELECT MOD(11,3) FROM DUAL;
MOD(11,3)
----------
2
2)ROUND (number)
The function ROUND has the syntax ROUND({n}[,integer]).
It returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places. The argument integer can be negative to round off digits left of the decimal point.
Example:SQL> SELECT ROUND(11.283,1) FROM DUAL;
ROUND(11.283,1)
---------------
11.3
SQL> SELECT ROUND(11.283,-1)FROM DUAL;
ROUND(11.283,-1)
----------------
10
SQL> SELECT ROUND(11.283)FROM DUAL;
ROUND(11.283)
-------------
11
3)SIGN
The SIGN function returns the sign of a numeric datatype or datatype that can be implicitly converted to numeric datatype.
In case of numeric datatype,
If value <0 argument="0">0 then SIGN function returns 1.
In case of binary float or binary double it returns -1 if n<0>=0 or n=NaN
SQL> SELECT SIGN(0f) FROM DUAL;
SIGN(0F)
----------
1
As 0f is float so SIGN returns 1.
SQL> SELECT SIGN(-11) FROM DUAL;
SIGN(-11)
----------
-1
SQL> SELECT SIGN(0) FROM DUAL;
SIGN(0)
----------
0
4)SIN
The function SIN takes a value in radians and returns the sine value of the argument.
To get SIN value of 30 degree,
SQL> SELECT SIN(30 * 3.14159265359/180) FROM DUAL;
SIN(30*3.14159265359/180)
-------------------------
.5
5)SINH
SINH returns the hyperbolic sine of n.
SQL> SELECT SINH(2) FROM DUAL;
SINH(2)
----------
3.62686041
6)SQRT
SQRT returns the square root of n.
SQL> SELECT SQRT(256) FROM DUAL;
SQRT(256)
----------
16
7)TAN
TAN returns the tangent of n where n is expressed in radians.
To get tangent of 45 degree,
SQL> SELECT TAN(45 * 3.14159265359/180) FROM DUAL;
TAN(45*3.14159265359/180)
-------------------------
1
8)TANH
TANH returns the hyperbolic tangent of n.
Example:
SQL> SELECT TANH(1) FROM DUAL;
TANH(1)
----------
.761594156
9)TRUNC (number)
TRUNC (number) holds the syntax TRUNC({n2}[,n1]) where {} indicates mandatory option and [] indicate optional option.
It returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
Example:SQL> SELECT TRUNC(13.59,1) FROM DUAL;
TRUNC(13.59,1)
--------------
13.5
SQL> SELECT TRUNC(13.59,-1) FROM DUAL;
TRUNC(13.59,-1)
---------------
10
SQL> SELECT TRUNC(13.59,0) FROM DUAL;
TRUNC(13.59,0)
--------------
13
The function REMAINDER holds the syntax REMAINDER(n2,n1) and returns the remainder of n2 divided by n1. This function is similar to MOD function except that MOD uses FLOOR in its formula, whereas REMAINDER uses ROUND.
Example:
SQL> SELECT REMAINDER(11,3) FROM DUAL;
REMAINDER(11,3)
---------------
-1
SQL> SELECT MOD(11,3) FROM DUAL;
MOD(11,3)
----------
2
2)ROUND (number)
The function ROUND has the syntax ROUND({n}[,integer]).
It returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places. The argument integer can be negative to round off digits left of the decimal point.
Example:SQL> SELECT ROUND(11.283,1) FROM DUAL;
ROUND(11.283,1)
---------------
11.3
SQL> SELECT ROUND(11.283,-1)FROM DUAL;
ROUND(11.283,-1)
----------------
10
SQL> SELECT ROUND(11.283)FROM DUAL;
ROUND(11.283)
-------------
11
3)SIGN
The SIGN function returns the sign of a numeric datatype or datatype that can be implicitly converted to numeric datatype.
In case of numeric datatype,
If value <0 argument="0">0 then SIGN function returns 1.
In case of binary float or binary double it returns -1 if n<0>=0 or n=NaN
SQL> SELECT SIGN(0f) FROM DUAL;
SIGN(0F)
----------
1
As 0f is float so SIGN returns 1.
SQL> SELECT SIGN(-11) FROM DUAL;
SIGN(-11)
----------
-1
SQL> SELECT SIGN(0) FROM DUAL;
SIGN(0)
----------
0
4)SIN
The function SIN takes a value in radians and returns the sine value of the argument.
To get SIN value of 30 degree,
SQL> SELECT SIN(30 * 3.14159265359/180) FROM DUAL;
SIN(30*3.14159265359/180)
-------------------------
.5
5)SINH
SINH returns the hyperbolic sine of n.
SQL> SELECT SINH(2) FROM DUAL;
SINH(2)
----------
3.62686041
6)SQRT
SQRT returns the square root of n.
SQL> SELECT SQRT(256) FROM DUAL;
SQRT(256)
----------
16
7)TAN
TAN returns the tangent of n where n is expressed in radians.
To get tangent of 45 degree,
SQL> SELECT TAN(45 * 3.14159265359/180) FROM DUAL;
TAN(45*3.14159265359/180)
-------------------------
1
8)TANH
TANH returns the hyperbolic tangent of n.
Example:
SQL> SELECT TANH(1) FROM DUAL;
TANH(1)
----------
.761594156
9)TRUNC (number)
TRUNC (number) holds the syntax TRUNC({n2}[,n1]) where {} indicates mandatory option and [] indicate optional option.
It returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
Example:SQL> SELECT TRUNC(13.59,1) FROM DUAL;
TRUNC(13.59,1)
--------------
13.5
SQL> SELECT TRUNC(13.59,-1) FROM DUAL;
TRUNC(13.59,-1)
---------------
10
SQL> SELECT TRUNC(13.59,0) FROM DUAL;
TRUNC(13.59,0)
--------------
13
Numeric EXP FLOOR LN LOG MOD NANVL POWER functions
1)EXP
The EXP function holds the syntax EXP(n) and returns e raised to the nth power value of argument n.
The value of e is 2.71828183 ...
Example:
SQL> SELECT EXP(3) FROM DUAL;
EXP(3)
----------
20.0855369
Almost same as,
SQL> SELECT POWER(2.71828183,3) FROM DUAL;
POWER(2.71828183,3)
-------------------
20.085537
2)FLOOR
The function FLOOR hold the format FLOOR(n) and it returns largest integer equal to or less than n.
Example:
SQL> SELECT FLOOR(11.8) from dual;
FLOOR(11.8)
-----------
11
SQL> SELECT FLOOR(11.001) from dual;
FLOOR(11.001)
-------------
11
3)LN The function LN holds the syntax LN(n) and it returns the natural logarithm of n, where n>0.
SQL> select ln(2.71828183) from dual;
LN(2.71828183)
--------------
1
Which is similar to log(e,e) where e=2.71828183.
SQL> select log(2.71828183,2.71828183) from dual;
LOG(2.71828183,2.71828183)
--------------------------
1
4)LOG The function LOG use the syntax LOG(n2,n1) and returns the logarithm, base n2, of n1. The base n1 can be any positive value other than 0 or 1 and n2 can be any positive value.
Example:
SQL>Select log(3,9) from dual;
LOG(3,9)
----------
2
5)MOD The function MOD holds the syntax MOD(n2,n1) and it returns the remainder of n2 divided by n1. It returns n2 if n1=0 (zero ).
SQL> SELECT MOD(25,6) FROM DUAL;
MOD(25,6)
----------
1
SQL> SELECT MOD(0,2) FROM DUAL;
MOD(0,2)
----------
0
SQL> SELECT MOD(10,0) FROM DUAL;
MOD(10,0)
----------
10
6)NANVL The NANVL function holds the syntax NANVL(n2,n1) and this function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. NaN indicates not a number. If the value stored in a table is not a number then this function instructs oracle to return alternative value n1 if the input value n2 is NaN (not a number). If n2 is not NaN, then Oracle returns n2.
The following example will clear you.
SQL> CREATE TABLE nan(a binary_float, b binary_double);
Table created.
SQL> insert into nan values('NaN','nan');
1 row created.
SQL> select * from nan;
A B
---------- ----------
Nan Nan
Now I wants if values are not a number then it will return zerop and for that you can use NANVL function.
SQL> SELECT NANVL(a,0), NANVL(b,0) from nan;
NANVL(A,0) NANVL(B,0)
---------- ----------
0 0
7)POWER The POWER function holds the syntax POWER(n2,n1). It returns n2 raised to the n1 power. The n2 is called base and n1 is the exponent. If n2 is negative, then n1 must be an integer.
Example:
SQL> SELECT POWER(-2,3) FROM DUAL;
POWER(-2,3)
-----------
-8
SQL> SELECT POWER(2,-3) FROM DUAL;
POWER(2,-3)
-----------
.125
The EXP function holds the syntax EXP(n) and returns e raised to the nth power value of argument n.
The value of e is 2.71828183 ...
Example:
SQL> SELECT EXP(3) FROM DUAL;
EXP(3)
----------
20.0855369
Almost same as,
SQL> SELECT POWER(2.71828183,3) FROM DUAL;
POWER(2.71828183,3)
-------------------
20.085537
2)FLOOR
The function FLOOR hold the format FLOOR(n) and it returns largest integer equal to or less than n.
Example:
SQL> SELECT FLOOR(11.8) from dual;
FLOOR(11.8)
-----------
11
SQL> SELECT FLOOR(11.001) from dual;
FLOOR(11.001)
-------------
11
3)LN The function LN holds the syntax LN(n) and it returns the natural logarithm of n, where n>0.
SQL> select ln(2.71828183) from dual;
LN(2.71828183)
--------------
1
Which is similar to log(e,e) where e=2.71828183.
SQL> select log(2.71828183,2.71828183) from dual;
LOG(2.71828183,2.71828183)
--------------------------
1
4)LOG The function LOG use the syntax LOG(n2,n1) and returns the logarithm, base n2, of n1. The base n1 can be any positive value other than 0 or 1 and n2 can be any positive value.
Example:
SQL>Select log(3,9) from dual;
LOG(3,9)
----------
2
5)MOD The function MOD holds the syntax MOD(n2,n1) and it returns the remainder of n2 divided by n1. It returns n2 if n1=0 (zero ).
SQL> SELECT MOD(25,6) FROM DUAL;
MOD(25,6)
----------
1
SQL> SELECT MOD(0,2) FROM DUAL;
MOD(0,2)
----------
0
SQL> SELECT MOD(10,0) FROM DUAL;
MOD(10,0)
----------
10
6)NANVL The NANVL function holds the syntax NANVL(n2,n1) and this function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. NaN indicates not a number. If the value stored in a table is not a number then this function instructs oracle to return alternative value n1 if the input value n2 is NaN (not a number). If n2 is not NaN, then Oracle returns n2.
The following example will clear you.
SQL> CREATE TABLE nan(a binary_float, b binary_double);
Table created.
SQL> insert into nan values('NaN','nan');
1 row created.
SQL> select * from nan;
A B
---------- ----------
Nan Nan
Now I wants if values are not a number then it will return zerop and for that you can use NANVL function.
SQL> SELECT NANVL(a,0), NANVL(b,0) from nan;
NANVL(A,0) NANVL(B,0)
---------- ----------
0 0
7)POWER The POWER function holds the syntax POWER(n2,n1). It returns n2 raised to the n1 power. The n2 is called base and n1 is the exponent. If n2 is negative, then n1 must be an integer.
Example:
SQL> SELECT POWER(-2,3) FROM DUAL;
POWER(-2,3)
-----------
-8
SQL> SELECT POWER(2,-3) FROM DUAL;
POWER(2,-3)
-----------
.125
Subscribe to:
Posts (Atom)
Tag Cloud
10.2g
10g
11g
11gR2
Abasa
About Oracle
Administration
Adsense
Alerts
Archival
ASM
ASP.Net
Audit
Audit Vault
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Mining
Data Pump
Data Type
Database Administration
Database Vault
DBConsole
Developer
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Facebook
Firefox
Firmware
Flashback
Forum
Functions
Games
Globalization Support
Grid Control
Hardware
History
HTML
IE
Import
Indexes
initializaion parameter
initialization parameter
Installation
Internals
Internet
Interview
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Mobile
Money
Multimedia
MySQL
Net Services
Network
OCP
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
Photos
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quiz
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Social Marketing
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
System Analysis
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Upgradation
Utilities
Version
Views
Vmware
Windows
Wordpress
XML