Resetting password for System user in Oracle Express Edition

If you are running Oracle XE for your own development purpose and by any chance forgotten the password of the "system" user here are the simple steps to resolve the problem

Linux:
export ORACLE_SID=XE
export ORACLE_HOME=
sqlplus / as sysdba
SQL> alter user system identified by ;

Windows:
set ORACLE_SID=XE
set ORACLE_HOME= (E.g. This will look something like - "D:\oraclexe\app\oracle\product\10.2.0\server")
sqlplus / as sysdba
SQL> alter user system identified by ;

Hope this helps...

Ora-02429 cannot drop index used for enforcement of unique/primary key

There are multiple reasons why Ora-02429 error may happen. I encountered this problem while deleting tablespace dedicated to indexes in the product we are using.

One may want to try following options:

1. Use "cascade constraints" e.g. drop tablespace [name of ts] cascade constraints;
2. check what objects are in this tablespace and move these to some where else and drop the tablespace.
3. alter index [indexname] rebuild [tablespacename]; (this is because, rowid's will change when a table is moved/re-org)
4. If you are trying to delete tablespace which is still referenced by any user; you need to make sure either users are removed (recursively) and that there are no data related to any user in there. Delete the user (Schema) with all the objects and it should free up any references.

For me the last option worked!

Helpful SQL Scripts

There may come a time for database developers to perform bulk operations e.g. compile all the stored procedures or rebuild all the indices. This might be required esp. when you are supporting a product and have to upgrade it.

I had to do this with help of oracle XE instance with only a web interface and here is the solution I developed.

Recompiling All Procedures:

begin
declare
cursor c_proc is
select object_name from user_objects where object_type = 'PROCEDURE';
v_sql varchar2(512);

begin
for c_proc_rec in c_proc LOOP
v_sql := 'ALTER PROCEDURE 'c_proc_rec.object_name' COMPILE';
execute immediate v_sql;
END LOOP;

end;
end;

Rebuilding all indexes:
This might be required after inserting thousands of rows as part of product upgrade. Here is the script that I used.

begin
begin
declare
cursor c_idx is
select index_name from user_indexes where index_type = 'NORMAL' and uniqueness = 'NONUNIQUE';
v_sql varchar2(512);

begin
for c_idx_rec in c_idx LOOP
v_sql := 'Alter index 'c_idx_rec.index_name' rebuild';

execute immediate v_sql;
END LOOP;
end;
end;

Hope this helps!

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;

/