Quantcast
OracleBrains.Com header image 5

Entries Tagged as 'Oracle Administration'

Understanding Virtual Columns

October 13th, 2007 · 1 Comment

For last few days I was busy packing my things plus doing some important task related to my Australia trip but at last got time to write about something new in Oracle 11g called Virtual Columns. First lets go through one situation, we have a sales table with Gross_Amount and Discount_Amount. Now Net_Amount := Gross_Amount [...]

[Read more →]

Tags: Oracle 11g New Features · Oracle Administration · Oracle Database · Virtual Objects in Oracle

creating UNIQUE index on a table with existing non unique values.

May 7th, 2007 · 2 Comments

Today I came up with this challenge of creating UNIQUE index on a table with exiting non unique value. After lot of r&d I came up with following very interested solution: I am using scott schema to show my solution. SQL> create table test as select deptno from emp; Table created. SQL> select deptno from [...]

[Read more →]

Tags: Oracle Administration · Oracle Database

Oracle 11g (Beta) New Features

April 22nd, 2007 · No Comments

After what I find yesterday (Enhancements in Oracle 11g PL/SQL), I research more about Oracle 11g. Found out few more things about it. Database 11g beta includes 482 new features designed to address a range of database issues, from the need to retain more information to data compression to the handling of parallel upgrades, said [...]

[Read more →]

Tags: Oracle 11g New Features · Oracle Administration · Oracle Database

Understanding Asynchronous COMMIT

March 7th, 2007 · No Comments

Before Oracle 10g Release 2, Life cycle of a transaction was as follows: – Transaction Starts When User Issues the First DML, Oracle generates redo entries corresponding to the DML and write it to buffer in memory. – While user is issuing DML, Oracle generates redo entries corresponding to the DML and write it to [...]

[Read more →]

Tags: Oracle Administration · Oracle Database

SYSDATE and CHECK Constraint

January 30th, 2007 · 1 Comment

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 [...]

[Read more →]

Tags: Oracle Administration · Oracle Database

Understanding the Recycle Bin

January 22nd, 2007 · 5 Comments

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 [...]

[Read more →]

Tags: Oracle Administration · Oracle Database

Detecting Corruption Using the ANALYZE TABLE command

January 9th, 2007 · 3 Comments

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 [...]

[Read more →]

Tags: Oracle Administration · Oracle Database

Setting Listener Password

January 2nd, 2007 · No Comments

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 [...]

[Read more →]

Tags: Oracle Administration · Oracle Database

Starting Listener on a different port

January 1st, 2007 · No Comments

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 [...]

[Read more →]

Tags: Oracle Administration · Oracle Database

Initializing column with its default value?

December 30th, 2006 · No Comments

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 [...]

[Read more →]

Tags: Oracle Administration · Oracle Database · SQL and PL/SQL