Archive

Posts Tagged ‘SQL’

Compile Invalid SYNONYM Oracle

January 11th, 2010 4 comments

Happy New Year!
Wish you all the great stuff you have been dreaming about to come true in 2010!

I haven’t posted for a long time but my excuse is the busy season at work. At the end of the day I am finding a minute to share a simple but useful hint on generating the COMPILE SQL for invalid PUBLIC SYNONYM objects.

Every time you drop and recreate an object such as a SEQUENCE, the PUBLIC SYNONYM for that objects invalidates (if such exists). In order to generate the COMPILE commands you could use SQL similar to the following two examples:

Read more…

Categories: Database, Oracle Tags: , , , ,

Drop multiple tables in Oracle

April 23rd, 2009 13 comments

I just noticed that the Oracle instance that I am working with has the SMP_% tables added from the Oracle Enterprise Manager. Since I don’t need them I decided to drop them. Here is my sql that generates the delete statements:

select ‘drop table ‘||table_name||’;’ from user_tables where table_name like ‘SMP_%’;

or..

Read more…

Categories: Oracle Tags: ,

Oracle alter table change column datatype

February 17th, 2009 Comments off

Let’s say you had a field CITIZEN in table called EMPLOYEE that was created as VARCHAR2(10) but this has to be changed to VARCHAR2(3). The syntax to change the the column datatype in Oracle is as follows:


ALTER TABLE table_name MODIFY ( column_name VARCHAR2(3));


If there is existing column data you should first export that data and then import it after the datatype change. Make sure the exported data will be suitable for import upon the change (You can’t import a row with CITIZEN column of 5 after you have changed the column to VARCHAR2(3)).

Categories: Database, Oracle Tags: , ,