FAQ
What database objects are created in the default tablespace?

If I create a table, is both the index and the table created in the default
tabalespace?

Platform is Oracle 9.2

John Dunn

Search Discussions

  • David Sharples at Sep 27, 2005 at 4:25 am
    yes, unless you specify explicitly the tablespace which you want it to go in

    On 9/27/05, John Dunn wrote:
    If I create a table, is both the index and the table created in the default
    tabalespace?
  • Niall Litchfield at Sep 27, 2005 at 4:30 am
    any object that requires physical storage will be created in the default
    tablespace unless otherwise specified
    (TABLE,INDEX,CLUSTER,MATERIALIZED VIEW etc), logical objects
    (views,procedures etc) get stored in the data dictionary and so reside in
    objects in the SYSTEM tablespace.
    cheers
    Niall
    On 9/27/05, John Dunn wrote:

    What database objects are created in the default tablespace?

    If I create a table, is both the index and the table created in the
    default
    tabalespace?

    Platform is Oracle 9.2



    John Dunn

    --
    http://www.freelists.org/webpage/oracle-l
    --
    Niall Litchfield
    Oracle DBA
    http://www.niall.litchfield.dial.pipex.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Kirtikumar Deshpande at Sep 27, 2005 at 8:06 am
    tablespace unless otherwise specified
    .... and the owner of such objects must have proper privileges to create those in other
    tablespaces.

    Cheers!

    Kirti

    Niall Litchfield wrote:
    any object that requires physical storage will be created in the default
    tablespace unless otherwise specified
    (TABLE,INDEX,CLUSTER,MATERIALIZED VIEW etc), logical objects
    (views,procedures etc) get stored in the data dictionary and so reside in
    objects in the SYSTEM tablespace.
    cheers
    Niall
    On 9/27/05, John Dunn wrote:

    What database objects are created in the default tablespace?

    If I create a table, is both the index and the table created in the
    default
    tabalespace?

    Platform is Oracle 9.2



    John Dunn

    --
    http://www.freelists.org/webpage/oracle-l


    --
    Niall Litchfield
    Oracle DBA
    http://www.niall.litchfield.dial.pipex.com


    Yahoo! Mail - PC Magazine Editors' Choice 2005
    http://mail.yahoo.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Lou Fangxin at Sep 27, 2005 at 9:31 pm
    Maybe Oracle will add a default index tablespacce to user.
    On 9/27/05, Kirtikumar Deshpande wrote:

    tablespace unless otherwise specified
    .... and the owner of such objects must have proper privileges to create
    those in other
    tablespaces.

    Cheers!

    - Kirti

    --- Niall Litchfield wrote:
    any object that requires physical storage will be created in the default
    tablespace unless otherwise specified
    (TABLE,INDEX,CLUSTER,MATERIALIZED VIEW etc), logical objects
    (views,procedures etc) get stored in the data dictionary and so reside in
    objects in the SYSTEM tablespace.
    cheers
    Niall
    On 9/27/05, John Dunn wrote:

    What database objects are created in the default tablespace?

    If I create a table, is both the index and the table created in the
    default
    tabalespace?

    Platform is Oracle 9.2



    John Dunn

    --
    http://www.freelists.org/webpage/oracle-l


    --
    Niall Litchfield
    Oracle DBA
    http://www.niall.litchfield.dial.pipex.com



    __________________________________
    Yahoo! Mail - PC Magazine Editors' Choice 2005
    http://mail.yahoo.com
    --
    http://www.freelists.org/webpage/oracle-l
    --
    Welcome, Fangxin.Lou from China
    MYDUL, alternative to DUL !!!
    Home: http://www.anysql.net/en/
    Mydul: http://www.anysql.net/en/mydul.html

    --
    http://www.freelists.org/webpage/oracle-l
  • David Sharples at Sep 28, 2005 at 2:31 am
    why would it want to do that?
    On 9/28/05, Lou Fangxin wrote:

    Maybe Oracle will add a default index tablespacce to user.
    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Sep 28, 2005 at 3:12 am
    I can see it now
    create user athene identified by minerva
    default table tablespace table_data
    default index tablespace indx_data
    default cluster tablespace cluster_data
    default lob tablespace lob_data
    default mview tablespace mview_data
    default object tablespace object_data
    default spatial tablespace spatial_data
    temporary tablespace temp;

    I'm sure I've missed something.
    On 9/28/05, David Sharples wrote:

    why would it want to do that?
    On 9/28/05, Lou Fangxin wrote:

    Maybe Oracle will add a default index tablespacce to user.
    --
    Niall Litchfield
    Oracle DBA
    http://www.niall.litchfield.dial.pipex.com

    --
    http://www.freelists.org/webpage/oracle-l
  • Stephen booth at Sep 28, 2005 at 2:26 pm

    On 28/09/05, David Sharples wrote:
    why would it want to do that?


    On 9/28/05, Lou Fangxin wrote:

    Maybe Oracle will add a default index tablespacce to user.
    I seem to recall that an enhancement request was submitted for this
    some time ago.

    There seem to be different schools of thought on whether indexes
    should be in the same tablespace as the tables, different tablespaces
    but on the same disks/volumes or different tablespaces on different
    disks/volumes.

    I seem to recall reading something by Connor where he debunked the
    myth that putting tables and indexes on separate disks improves
    performance because Oracle can read the index and the table without
    having to move the disk heads back and forth. It doesn't work that
    way, Oracle doesn't read them in parralell it reads the index and then
    the table. What I read did seem to only consider a single
    transaction.

    My gut feeling is that, like many things in Oracle (and IT in
    general), it depends on your app. There may be some apps that would
    benefit from indexes being stored separately to tables, one possible
    example that comes to mind is where you have a large number of
    similtaneous queries by a key value that are widely dispersed accross
    a large table (e.g. queries to an inventory table from an ordering
    system where products are selected by a product ID which is the
    primary key). Due to there being a large number queries all running
    at the same time there's a good chance of the table and it's index
    being read at the same time albeit by different processes.

    Stephen

    --
    It's better to ask a silly question than to make a silly assumption.
    --
    http://www.freelists.org/webpage/oracle-l
  • Dennis Williams at Sep 28, 2005 at 5:19 pm
    Stephen,
    one possible
    example that comes to mind is where you have a large number of
    similtaneous queries by a key value that are widely dispersed accross
    Or you have one process running that accesses an index and a table.
    Sound remarkably like benchmarking? My personal theory is that is
    where this practice originated. People doing benchmarking would split
    the table and index to different devices, got a good perfomance
    increase, then that technique migrated into the database lore.

    As has been pointed out, most Oracle databases have many processes,
    many tables and indexes.

    Dennis Williams

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 27, '05 at 4:14a
activeSep 28, '05 at 5:19p
posts9
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase