Oracle Invalid Objects

Here is a quick query for Oracle users to identify all invalid objects:

SELECT owner,object_name,object_type
FROM dba_objects WHERE status='INVALID'

Deleting Objects from a Schema in Oracle

Here is a script that would come handy if you need to delete all the objects in a schema. Please ensure you are NOT logged in as System / Sys. Log in as the user for whose schema all the objects need to be dropped. Hope this helps.

--

prompt >>>

prompt >>> dropping it all..................

prompt >>>

--

begin

declare

cursor c1 is

select table_name, constraint_name from user_constraints where constraint_type = 'R';

cursor c2 is

select table_name, constraint_name from user_constraints where constraint_name not like 'SYS_IL%';

cursor c3 is

select index_name from user_indexes where index_name not like 'SYS_IL%';

cursor c4 is

select table_name, constraint_name from user_constraints where constraint_type = 'P';

cursor c5 is

select index_name from user_indexes where index_type = 'NORMAL' and uniqueness = 'NONUNIQUE';

cursor c6 is

select table_name from user_tables;

cursor c7 is

select object_name from user_objects where object_type = 'PROCEDURE';

cursor c8 is

select sequence_name from user_sequences;

v_sql varchar2(512);

begin

for c8_rec in c8 LOOP

v_sql := 'drop sequence '||c8_rec.sequence_name;

execute immediate v_sql;

END LOOP;

for c1_rec in c1 LOOP

v_sql := 'alter table '||c1_rec.table_name||' drop constraint '||c1_rec.constraint_name;

execute immediate v_sql;

END LOOP;

for c2_rec in c2 LOOP

v_sql := 'alter table '||c2_rec.table_name||' drop constraint '||c2_rec.constraint_name||' drop index';

execute immediate v_sql;

END LOOP;

for c3_rec in c3 LOOP

v_sql := 'drop index '||c3_rec.index_name;

execute immediate v_sql;

END LOOP;

for c4_rec in c4 LOOP

v_sql := 'alter table '||c4_rec.table_name||' drop constraint '||c4_rec.constraint_name||' drop index';

execute immediate v_sql;

END LOOP;

for c5_rec in c5 LOOP

v_sql := 'drop index '||c5_rec.index_name;

execute immediate v_sql;

END LOOP;

for c6_rec in c6 LOOP

v_sql := 'drop table '||c6_rec.table_name||' cascade constraints';

execute immediate v_sql;

END LOOP;

for c7_rec in c7 LOOP

v_sql := 'drop procedure '||c7_rec.object_name;

execute immediate v_sql;

END LOOP;

end;

end;

/

Useful SQL commands

A – How to create a Tablespace

Please log as “system” (with its valid password) using SqlPlus.

-- Create a 1GB tablespace from scratch.

CREATE TABLESPACE  LOGGING DATAFILE 'X:\full\path\to\XXXX.DBF' SIZE 1000M EXTENT MANAGEMENT LOCAL;

-- Change existing tablespace name.

ALTER TABLESPACE  RENAME TO ;

-- Change default user tablespace.

ALTER USER  DEFAULT TABLESPACE  QUOTA UNLIMITED ON ;

B – How to create a user for the Tablespace

CREATE USER  IDENTIFIED BY  DEFAULT TABLESPACE  TEMPORARY TABLESPACE temp;

GRANT SELECT ANY TABLE TO ;

GRANT UNLIMITED TABLESPACE TO ;

GRANT UPDATE ANY TABLE TO ;

GRANT RESOURCE TO ;

GRANT CONNECT TO ;

GRANT CREATE SESSION TO ;

grant query rewrite to ;

C – How to export your own database:

Exp system/@<instance> file= owner= log=explogfile.log

Parameters:

 - Oracle user name.

 - Oracle instance name.

 - System password.

 - Your database dump (like myDump.dmp).

Tech Jokes

Here they go... :-) Hope you like 'em!
----------
1. An SQL query walks into a bar and sees two tables. It walks up to them and says "Can I join you?" Tables say, "Sure, if you have the key?"

2. A Session Bean walks into a bar and asks for a Jack Daniels on the rocks. Bartender says, "I'm afraid I can't serve you any alcohol", and the Session Bean says, "why not? Are you discriminating against Session Beans?" The bartender replies "well, look at the state you're in".