Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I am creating temporary/permanent tablespace on Oracle. As I am going to create an application that is transactional there will be traffic. I bumped into concept called tablespace in oracle. What kind practial things should I consider while creating tablespace? Should I create temporary table or should I use default temp tablespace? Are there any resource that I can study except theory?

I have though to create tablespace as:

 CREATE TEMPORARY TABLESPACE OPTIMADATA_TEMP TEMPFILE
    '/opt/app/oracle/oradata/orcl/test01.dbf' SIZE 1024M reuse autoextend on,
    '/opt/app/oracle/oradata/orcl/test02.dbf' SIZE 1024M
    reuse
    autoextend on
    extent management local;

Do I create multiple files or Single file is sufficient?

CREATE TABLESPACE OPTIMADATA DATAFILE '/opt/app/oracle/oradata/orcl/test.dbf' size 2000M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL;

Do I always do Autoextend on ??

share|improve this question
How much data are you talking about? 100s of rows? millions? billions? – Phil Oct 2 '12 at 5:49
@phil billions or more as data starts to aggregate – Kitex Oct 2 '12 at 8:37
The Oracle Concepts Guide will clear up most if not all of these questions. docs.oracle.com/cd/E11882_01/server.112/e25789/… – Leigh Riffel Oct 2 '12 at 12:48
1  
why not use the default tablespace until you can show there is a problem? When you create a database the Oracle default settings are quite reasonable and it makes it much easier to administer a database created this way. Said another way "If it's not broken, don't fix it." – kevinsky Oct 2 '12 at 15:16

1 Answer

up vote 1 down vote accepted

While creating a tablespace, you need to consider few things like

i) Type of the tablespace either permanent or temporary or undo (default is permanent)

ii) Extent management whether it is managed locally or dictionary based

iii) Segment space management whether it is automatically managed or manually managed

Always you can use temporary tablespace since it is the best practice. You can create multiple files and mainly it depends on your application.

We have two types of tablespaces like small file and big file. Small file tablespaces can have multiple datafiles. Each file with maximum size of 1024 bytes. Big file tablespaces should contain only one file with maximum size of 8 Hexabyte.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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