Helpful Oracle hints I've found and saved

How to find all the Oracle Datafiles in a Database

I was going to modify the database today by resizing all the datafiles because I need space on the server (for our dev box).

There may be better ways to do this, and that's what the comment box at the bottom of this page is for, but with the extremely tight space limitations, and limited time frame to complete this, I needed a quick way to do it.

So, I followed this procedure:

  1. Exported all data using "exp" – Oracle's Export Utility
  2. Dropped the application's Oracle user to delete all the data out of the DB using the following command:
  3. drop user myuser cascade;

  4. Got a list of the tablespaces and their current sizes using TOAD and "print screen" 🙂 (This was just for reference),
    Or, use the following SQL:
  5. select * from dba_tablespaces;

  6. Got a list of the current sizes of the datafiles and what tablespaces they were linked to. Here's the SQL:
  7. select * from dba_data_files;

Don't you just LOVE those "DBA_" tables?

Leave a Reply

Powered by WordPress | Designed by Elegant Themes

Pin It on Pinterest

Share This