Different Ways to Generate Unique ID

We had a requirement to generate 32 character alpha numeric unique IDs. Many options came up - here is the summary of options we considered.

Java UUID:

With Java 1.5 UUID class was introduced. This would generate universally unique identifier (UUID). A UUID represents a 128-bit value. randomUUID() method in UUID class will generate 128-bit universally unique value every time the method is called. This is hash-based values separated by dashes. Java API available here: http://docs.oracle.com/javase/6/docs/api/index.html?java/util/UUID.html

Database Hash:

If system design and architecture allows the unique ID generation on database side, then database hash can be used to create unique IDs.

Composite Key in Database:

If there is a way to combine various non unique keys to form a primary key on database side, this might work as an option as well.

Apache Commons:

randomAlphanumeric(int) in RandomStringUtils class takes length as an arguments and generates alpha numeric values. API documentation is here: http://commons.apache.org/proper/commons-lang/javadocs/api-2.6/org/apache/commons/lang/RandomStringUtils.html

Conclusion:

What worked for us in first phase of implementation was an approach where we used the system provided columns to generate a composite key.

Later the requirement changed to use alphanumeric values at which point in time we used RandomStringUtils provided by Apache Commons. However, it came with an overhead to check the generated ID against the database. However, this didn't have impact on performance given the benchmarks. 

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;

/