Drop Database in Oracle 10g

It has become easier to drop a database from Oracle 10g. Use the DROP DATABASE command to drop the database. It removes the datafiles, redologs, controlfiles and init parameter files.

$ sqlplus / as sysdba
SQL> Shutdown immediate;
SQL> Startup Mount Exclusive Restrict;
SQL> Drop Database;

Thanks

Recompile Invalid Objects

Script to find the invalid objects in the database

SQL> Select * From dba_objects where status = 'INVALID';

To Recompile Invalid Objects

Method 1: With access to SYS user

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

Method 2: Without access to SYS user (Cannot validate SYS objects)

SQL> Exec DBMS_UTILITY.compile_schema('SYSTEM');

Method 3: Manual Script

SQL> select 'ALTER ' OBJECT_TYPE ' ' OWNER '.' OBJECT_NAME
' COMPILE;'from dba_objects where status = 'INVALID'and object_type in ('PACKAGE','FUNCTION','PROCEDURE', 'VIEW', 'TRIGGER', 'SYNONYM');

SQL> Select 'ALTER PACKAGE' ' ' OWNER '.' OBJECT_NAME ' COMPILE BODY;' From dba_objects where status = 'INVALID'
And object_type in ('PACKAGE BODY');

SQL> Select 'ALTER MATERIALIZED VIEW' ' ' OWNER '.' OBJECT_NAME ' COMPILE;' From dba_objects where status = 'INVALID'
and object_type in ('UNDEFINED');

Thanks

Basic database Info

To get the basic database information such as the database name, dbid, creation time, archiving information etc query V$DATABASE view.

SQL> Select * From V$DATABASE;

To view the oracle version query V$VERSION view.

SQL> Select * From V$VERSION;

Will DDL statement commit automatically

I have read in some books that issuing ddl statement will commit the transaction automatically irrespective of the success of the DDL statement.

I performed a small test and came to a conclusion that,

• If ddl statement is executed successfully, the transaction is committed automatically.
• If ddl statement fails due to logical error, the transaction is committed automatically.
• If ddl statement fails due to syntax error, the transaction is neither committed nor rolled back, the transaction is still open.

Details of the test performed

SQL> create table testddl ( a number, b date);
Table created.
SQL> insert into testddl values (1, sysdate);
1 row created.
SQL> insert into testddl values (2, sysdate);
1 row created.
Not committed.
SQL> alter table testddl add constraint a_uq unique (a);
Table altered.
Automatically commits - ddl statement succeed.

SQL> rollback;
Rollback complete.

SQL> select * from testddl;
A B
-- --------
1 10-SEP-08
2 10-SEP-08

SQL> insert into testddl values (3, sysdate);
1 row created.
SQL> insert into testddl values (4, sysdate);
1 row created.
not committed

SQL> alter table testddl add constraint b_fk b references test (b);
alter table testddl add constraint b_fk b references test (b)
*
ERROR at line 1:ORA-01430: column being added already exists in table

Error but automatically commits. DDL Failed due to logical error.

SQL> rollback;
Rollback complete.

SQL> select * from testddl;
A B
-- --------
1 10-SEP-08
2 10-SEP-08
3 10-SEP-08
4 10-SEP-08

SQL> insert into testddl values (5, sysdate);
1 row created.
SQL> insert into testddl values (6, sysdate);
1 row created.
not committed

SQL> alter table testddl drp constraint a_uq;
alter table testddl drp constraint a_uq
*
ERROR at line 1:ORA-01735: invalid ALTER TABLE option

Error. ddl failed due to syntax error.Will not commit automatically.

SQL> rollback;
Rollback complete.

SQL> select * from testddl;
A B
-- --------
1 10-SEP-08
2 10-SEP-08
3 10-SEP-08
4 10-SEP-08

This is how the ddl statement is processed.
DDL parse
If success
commit
execute ddl
commit
end if
else
do nothing.

Hope you have enjoyed...

dblinks...compile problem in forms6i

I faced a bizarre problem while using database link in forms6i. I created a public database link. When I queried through sql*plus I was able to retrieve the data. That means everything was fine with tnsnames.ora and the network. But when I issued the same statement in the forms6i, i was not able to compile it.When i tried to compile (Ctl + k) and (Ctl + Shift +K) the form builder was closed without throwing any error message. I created synonym for the database link, but still the problem persists. Our database is in 9i and the remote database is in 10g.I thought it could be due to the version problem. I created a view for that statement, and finally i was able to compile it.

So if any of you are facing such problem, one solution is to create a view for the statement that uses database link.If any of you had faced such error and got a solution please do share with me.

Primary Key with Null Value...Amazing

Who said Primary key column cannot have null values...Here is a method you can create primary key with null values...

Create a table with null values.

create table tbl_null (a number);
insert into tbl_null values (null);
insert into tbl_null values (null);
insert into tbl_null values (null);
insert into tbl_null values (null);
commit;

select * from tbl_null;
4 rows selected

Now create a table with primary key column having null values..

Create table tbl_pri_null ( a primary key) as select * from tbl_null;
Table created

desc tbl_pri_null
Name - A
Null ? - Not Null
Type - Number

Select * from tbl_pri_null;
4 rows selected
All null values.....

Thats it. Folks!
Try and have fun....