Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Quick Tip: Add a Datafile to a TABLESPACE using SQL Developer

1 Min Read

I’m lazy.

I don’t like having to look up the directory name/structure when creating a new datafile.

I don’t even like having to copy and paste it.

So I avoid all of that, by using the CREATE LIKE dialog.

View > DBA

Add a connection.

Open.

Storage.

Datafiles.

Find one in the tablespace you want to add space for.

Right-click.

Lazy isn't bad.
Lazy isn’t bad.

Now just fill in the details, or change things up as needed.

All I need to do here is put in the file name.
All I need to do here is put in the file name.

Can’t Figure Out Which Datafile belongs to What Tablespace?

Try this trick. Hopefully your datafiles have some sort of familiar naming standard, but in case they don’t…

Most of the objects in the tree have these summary pages available. And it's a grid, so you can filter, re-order columns, export them to Excel, etc.
Most of the objects in the tree have these summary pages available. And it’s a grid, so you can filter, re-order columns, export them to Excel, etc.

People always ask me how long to create a blog post?

In this case, about 10 minutes. And the real part of the post is about 75 words. Does that make this post less than valuable? You tell me.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

11 Comments

  1. Rich Soule Reply

    “Maybe add option checkbox for OMF and generate the DDL appropriately.”

    This please!

    As I make the move to multitenant databases and ACFS, I’m finding OMF even more important.

  2. Martin Berger Reply

    Oracle recommends a “trick” how (not) to take care in environments with many files: “Oracle Managed Files”
    http://docs.oracle.com/database/122/ADMIN/using-oracle-managed-files.htm#ADMIN11486
    In this case the spfile parameter DB_CREATE_FILE_DEST can do the trick – you can leave the filename empty (at least in the SQL Statement – never tried in SQL Developer). No need to take care at all, as Oracle manages the filename for you. In this case Oracle will also do the cleanup for you if you drop the datafile 🙂

    • thatjeffsmith Post Author

      +100

      But guess how many DBAs ignore this trick? Too many. And we need to support those on both sides of the fence.

    • Kv

      Or where you have 100s of files on 10s of different paths and you are scrapping around for a bit of disk in a file system to squeeze another one in.

      Sometimes it’s not ‘missing a trick’ it’s shoveling shit from the bottom of a well, in the dark, without a ladder……or a shovel !

  3. KV Reply

    Lazy is good but in a database with 100s of data files that are badly named you need to look at the tablespace first to see which ones are for the tablespace in question then go to data file list to right click to do create like (so you know you are being consistently crap with the filenaming – don’t ask!!!) If a prefill of ‘create like’ could be there when you do add data file from the tablespace section it would save a click and a scroll to go back to this create like option off the data file section or what I tend to do is go to edit one, copy the path and name then go to add data file (initiated from the tablespace section) then paste it twice and edit it. It’s just seems a bit cumbersome. But maybe I’m missing a trick. I’ve also found using the add box locks up my screen whereas if I copy the sql from the sql tab to the worksheet I can still work with other connections whilst the add data file runs in the background. That’s not an issue but again, maybe I’m missing a trick and there’s a way to make it run in the background from the box rather than pasting to the worksheet? (I love these blog posts, they are really helping me learn a lot about the product and I’m getting more from it as a result of them, thanks)

    • thatjeffsmith Post Author

      That’s what I do, Edit Tablespace, copy existing path, paste in new one.

      But I don’t copy filename, I just observe the existing on the left panel and type in the new one.

      I don’t know how to fix badly named datafiles.

      If a prefill of ‘create like’ could be there when you do add data file from the tablespace section – not a bad idea, I’ll add it to the list.

    • KV

      Brilliant double click tip thanks, I knew I was missing a trick! (I already added the prefill to ‘your list’ via the requests site, I’ve been putting a few things on there but I am half expecting more tips and tricks like this one to pop up that solve the problems in a different way – thanks so much)

    • Biju

      Not able to create tablespaces with OMF data files. If I leave the directory blank, the data file is getting created under ORACLE_HOME/dbs (expected result) instead of the ASM diskgroup. I cannot leave the NAME blank as it is mandatory in create tablespace option.

      For add data files option, SQLDEV lets me leave the name and directory as blank, but the DDL generated is
      ALTER TABLESPACE “XXTS1″
      ADD DATAFILE ”
      SIZE 100M
      The DATAFILE ” clause makes the statement to fail.

      Maybe add option checkbox for OMF and generate the DDL appropriately.

      Able to create file on ASM diskgroup if I provide the diskgroup name in the directory column, though the file name generated in DDL does not look right.

      DDL generated:
      CREATE TABLESPACE XXTSTEST1
      DATAFILE
      ‘+DBPATCH/FILE1.DBF’ SIZE 104857600;

      The file created is not OMF, it is on ASM though.

Write A Comment