I asked this question over at https://stackoverflow.com/questions/52068320/creating-a-table-space-sql-developer, but there was no traction on the question, i thought i might try my luck over here.

I'm creating a tablespace to allocate some tables I've made. I'm new to SQL and i'm not exactly sure what my next step is to create the tablespace.

I understood and found that in order for me create a non-clustered table space i need to:

  1. Calculate the total block header size.
  2. Calculate the available data space per data block.
  3. Calculate the space used per row.
  4. Calculate the total number of rows that will fit in a data block.

I've calculated these figures for each table i have, but i'm clueless on the next step after this.

How do I use my calculated values to create a tablespace?

  • 2
    There's currently no need to calculate anything, just make sure you have enough space on disk. The tablespace itself hasn't got a size, it's the datafiles you assign to it that take space on disk. Nowadays, you can assign just one datafile, and tell Oracle that it's "autoextend" and the maximum size it may grow to (size of the disk?). Have a look at Oracle's documentation, it's quite good: docs.oracle.com/cd/B28359_01/server.111/b28318/…. Look for "bigfile". – Gerard H. Pille Aug 29 '18 at 6:47

Since you quoted SQL Developer in your question -

View > DBA

Add your connection.

Go to Storage, Tablespaces.

Right-click, 'Create New'

Fill in the details - and I agree with Gerard Pile's comment to your question - you don't need to worry about what you're worrying about. Just pick a directory, file name, and intial file size, and how you want it to grow, with an optional max file size.

enter image description here

You could theoretically override the database's DB_BLOCK_SIZE on the 'Properties' page, but that's generally not necessary.

If you want to see the SQL that's generated by the dialog, click on the 'DDL' page of the popup, otherwise, just click 'Ok.'

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.