Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: Increasing Tabelspace

    How do you go about increasing oracle tablespace? Do you allocate space per table or per database?

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    you increase the tablespace size by increasing the file eg:

    alter database orcl datafile 'C:\ORACLE\ORADATA\ORCL\RBS01.DBF' resize 600 M;

    OR you can add a new datafile

    alter tablespace <tablespace name> add datafile 'C:\ORACLE\ORADATA\ORCL\RBS02.DB' SIZE 2000M;

    its also a good idea to coalesce the tablespace especially if you delete records or tables a lot

    alter tablespace <tablespace name> coalesce;
    There are 10 types of people in the world, those that know Binary and those that don't.

  3. #3
    Join Date
    Jan 2004
    Posts
    9
    Thanks Robert,

    Is there anyway of seeing what the current tablespace used is for a table?

  4. #4
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by pmtnt
    Thanks Robert,

    Is there anyway of seeing what the current tablespace used is for a table?
    select tablespace_name from user_tables where table_name = 'MYTABLE'

  5. #5
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    this query should give you some usefull information

    PHP Code:
    select
      decode
    (segment_type,'INDEX',i.table_name||'.','')||SEGMENT_NAME "OBJECT",
      
    segment_type "TYPE",
      
    tablespace_name "TABLESPACE",
      
    to_char(bytes/1000000,'fm999,999,999,999,999')||' MB' "SIZE",
      
    extents,
      
    to_char(initial_extent/1000000,'fm999,999,999,999,990.0')||' MB' "INITIAL_EXT",
      
    to_char(next_extent/1000000,'fm999,999,999,999,990.0')||' MB' "next_extent",
      
    pct_increase "INC",
      
    to_char(bytes/1000000,'fm999,999,999,999,990.0')||' MB' "bytes"
    from
      user_segments
    user_ind_columns i
    where
      segment_type in 
    ('TABLE','INDEX')
      and 
    i.index_name (+) = segment_name
    order by
      segment_type
    ,
      
    lpad(extents,4,'0'DESC,
      
    bytes DESC 
    this will tell you the free space in a tablespace

    PHP Code:
    SELECT FROM dba_free_space
    WHERE tablespace_name 
    '<tablespace name>'
    ORDER BY bytes DESC 
    There are 10 types of people in the world, those that know Binary and those that don't.

  6. #6
    Join Date
    Jan 2004
    Posts
    9
    excellent, thats shown me that the table in question is using a tablespace area called 'AUDITING'.

    This ties up with the applications error messages.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •