We cannot use SYSDATE as part of a CHECK Constraint defination.
See the example:
SQL> CREATE TABLE EVENT(
2 EVENT_ID NUMBER(5) CONSTRAINT PK_EVENT PRIMARY KEY,
3 EVENT_NAME VARCHAR2(25),
4 VENUE VARCHAR2(20),
5 START_DATE DATE CONSTRAINT CK_EVENT_START_DATE CHECK
(START_DATEAction: Completely specify the date constant or system variable.
Reason:
Note that Oracle7 is lax on DATE check constraints and this has been tightened up in Oracle8 such that a CHECK CONSTRAINT for a date column needs a fully qualified 4-digit year date and a to_date clause. This is because the interpretation of the check constraint could be changed by either a change in century or a change in NLS_DATE_FORMAT.
Eg: ALTER TABLE […]

[Read more →]

Recycle Bin is one of the new feature introduced in Oracle 10g and works a little bit like the recycle bin in Windows.
Funtional point of view when this feature is enabled, any table the user drops, dropped table and its associated objects go into the recyclebin and can be restored from the recyclebin later unless objects are purge from the recycle bin.
Technical point of view when this feature is enabled, any table that user drops, it does not actually get dropped, Instead Oracle renames the table and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name […]

[Read more →]

There are many ways to detect the block corruption in a database. One of them is to use “ANALYZE TABLE” command as follows:
SQL> ANALYZE TABLE table_name VALIDATE STRUCTURE;
This command validates the integrity of the structure of the table being analyzed. To be more precise it validates the integrity of the db blocks of the underlying table.
This command is either successful or not successful. If not successful the error ORA-01499 is thrown.
If CASCADE is specified, the command also checks its indexes.
SQL> ANALYZE TABLE table_name VALIDATE STRUCTURE CASCADE;

Bookmark this post

[…]

[Read more →]

One of the biggest loophole that an Oracle database installation can have is Oracle Listener without a password. At the time of client server this fact somehow can be overlooked as we know who our user are, but now in days of web we don’t them so its becomes very important that we secure Oracle Listener.
Setting the password for the listener can be done in following three ways:
1. Editing the listener.ora file and setting the password in it.
2. Using LSNRCTL utility.
3. Through Oracle Graphical tools such as Net Manager, Oracle Enterprise Manager and so on.
In this blog entry I will […]

[Read more →]

Today when checking “Search Keyphrases” for my site, I found out that someone wants to know about “starting tns listener on a different port”. So I though why not write about it so that in future it will be helpful to others.
I will not go in detail about physical file location (listener.ora) and will assume that the person already have such knowledge.
In Oracle the listener has a default name of LISTENER and is configured to listen on the following default protocol addresses as follows:
(address=(protocol=tcp)(host=host_name)(port=1521))
Clients configured with these addresses can connect to the listener on a given port.
By default it listen […]

[Read more →]

Today I found out this very interesting fact about default value.
While inserting or updating we can use reserver word “DEFAULT” to insert or update value of a column.
Example:
SQL> CREATE TABLE TEST1( NO VARCHAR2(10) DEFAULT ‘TEST’, NO1 VARCHAR2(10));
Table created.
SQL> INSERT INTO TEST1 VALUES(DEFAULT, DEFAULT);
1 row created.
SQL> UPDATE TEST1 SET NO=DEFAULT;
1 row updated.
Note: If DEFAULT value exist for a column it will be used or null will be used.

Bookmark this post

[…]

[Read more →]

Recently I came to know about very interesting procedure called “KSDWRT” in DBMA_SYSTEM.
It can be used to add our own custom entries to the alert log or trace file or both depending on the first parameter that we pass to this procedure.
DBMS_SYSTEM.KSDWRT(1,’Testing Writting to Trace File’);
DBMS_SYSTEM.KSDWRT(2,’Testing Writting to Alert File’);
DBMS_SYSTEM.KSDWRT(3,’Testing Writting to Trace & Alter File’);
Use 1 to write to the trace file
Use 2 to write to the alter file
Use 3 to write to both.

Bookmark this post

[…]

[Read more →]

Comment by S. Inderjeet Singh on Understanding TNS Listener
“After this database authenticates login information, the listener process redirect the client to any new available port and a session is create between client and the server on that new port.”
hi, pls clarify which port it could be
my answer:
Total available Port numbers range from 0 to 65536 and ports numbers 0 to 1024 are reserved for privileged services. Apart from these reserved port any port which is not being used can be allocated to new server process to communicate with client process.

Bookmark this post

[…]

[Read more →]

Before Oracle 9i Release 2, if we want to use the UTL_FILE package then we need to initialize UTL_FILE_DIR initialization parameter and restart the instance.
UTL_FILE_DIR = directory name with path
Note: The parameter specification UTL_FILE_DIR = * has a special meaning. This entry turns off directory access checking, and it makes any directory accessible to the UTL_FILE functions.
Not only was this, security wise there was no mechanism to protect these directories. All the users can access all the directories.
With the Oracle 9i Release 2, things have changed.
Now Instead of using the directory defined with UTL_FILE_DIR, we can create DIRECTORY schema object […]

[Read more →]

I collected some feedback about my earlier blog.
First Blog Link
Understanding Oracle Release Number Format
Second Blog Link
Understanding Oracle Release Number Format - 2
Comment From  Markus Perdrizat
The new nomenclature only works starting with 9iR2. Even 9iR1 was still 9.0.1.x instead of 9.1.0.x. The article at OracleBrains isn’t complete if it doesn’t mention that. Then again, to newcomers there’s just Oracle 10gR2 anyway, so let’s not care too much about legacy versions.

Bookmark this post

[…]

[Read more →]