Link to home
Create AccountLog in
Avatar of pkchhabra
pkchhabraFlag for India

asked on

storage clause of create table

WHAT ARE THE DEFAULT STORAGE VALUES IT TAKES WHILE CREATING ANY TABLE OR MATERIALIZED VIEW?
ASKER CERTIFIED SOLUTION
Avatar of jwahl
jwahl
Flag of Austria image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
So to add what jwahl said , that is why it is so important that the tablespace be created with the "SEGMENT SPACE MANAGEMENT AUTO ".

In out company, we create tablespace for data in this way:

Example:
CREATE  TABLESPACE "UAMS_DATA" DATAFILE '/cust/oravl09/ORACLE/PCTIOL/dbf/uams_data01.dbf' SIZE 126M AUTOEXTEND ON NEXT 125M MAXSIZE 5001M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;


And index tablespace in this way:

CREATE  TABLESPACE "UAMS_INDX" DATAFILE '/cust/oravl35/ORACLE/PCTIOL/dbf/uams_indx01.dbf' SIZE 41M AUTOEXTEND ON NEXT 40M MAXSIZE 2001M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

The default storage values are the storage values of the tablespace you will place the table or the View.
Avatar of Sean Stuber
Sean Stuber

and, if you do not specify a tablespace it will be the default tablespace of the user that owns the objects.

SELECT u.username, u.default_tablespace, tablespace_name, initial_extent, next_extent, extent_management,
       allocation_type
  FROM dba_tablespaces t, dba_users u
 WHERE u.username = 'YOUR_TABLE_OWNER_NAME'
     AND t.tablespace_name = u.default_tablespace;
Just addition to LindaC comments... SEGMENT SPACE MANAGEMENT AUTO  can only be added if tablespace is locally managed. I am assuming that you are using locally managed tablespaces in your environment. Goto this site: http://www.psoug.org/library.html for more details on storage management and full examples on how to create tablespaces using various options.