Drop tables for a user (A Workaround)

I am not an expert in database and hence i got stuck when I had to drop around hundreds of tables for a user in a database (Oracle 10g in my case). The tables were having complicated relationships and hence they were having dependencies on other tables maintained by constraints. But then I got a very nice trick to do the same. Below mentioned are the steps for the same:

Log on to the database as the user whose tables has to be dropped and run the following command,

select ‘drop table ‘ || tname || ‘cascade constraints;’ from tab ;

The output of this script will be the list of drop table commands which will also drop the constraints. For example if there are two tables say customer and account, the output will be like:

drop table customer cascade constraints;

drop table account cascade constraints;

Now the script for dropping all the tables is ready. This script will drop all the tables. However this script could be hand-coded but if you have hundreds of tables this trick will help you. You can also modify the command to write the output in a .sql file so that you can directly execute it.

However this is of course a workaround but this had solved my problem.

Cheers,

Kamlesh

Advertisements

Getting a file from classpath

In file handling we most of the times need to locate a file from classpath. Though locating a file is not a rocket science but still I have seen people getting stuck doing this and ultimately hardcoding the file path at the required place.

Following is a code snippet which locates a file from classpath and returns its absolute path.

ClassLoader loader = Thread.currentThread().getContextClassLoader();
URL url = loader.getResource(“name_of_file.extension”);
String file = url.getFile().replace(“%20″, ” “);//this is required as URL treats space //as %20.

Some of the IO APIs of Java takes URI also as input, so URL can be converted to URI using url.toURI() method.

– Kamlesh

Small but important things must be kept handy

Hi,

I am back on my favorite space after a long break. It has been a year or more I guess since I have not written any posts on this blog. I had been busy in learning lots and lots of stuff and now I feel I have few things which may prove helpful for technocrats like us. There are times when you get stuck in something which are small but tricky. You even find the solution but due to time crunch or some other reason you forget to keep a note of that. And when after a long time you face the same problem or someone else faces the problem then you realize that it would have been better if you had saved that particular solution. This often happens to me and I have to waste my time and effort to reinvent the wheel. So I have decided to put those kind of solutions on this blog so that it can be reused at any point of time.

Suggestions and contributions to this blog are welcome.

– Kamlesh