2 Replies Latest reply: Nov 7, 2012 4:30 PM by 954124 RSS

    How to add space in tablespace?

    954124
      HI,

      How can I add 180M space in SALES_DETAIL_TS_IDX tablespace?

      I mean Do I need to add another datafile with size of 180M or Do I need to resize anyone of datafile listed below?

      If Resize, than how much size I should resize for?

      Tablespace Size (MB) Free (MB) % Free % Used
      ------------------------------ ---------- ---------- ---------- ----------
      SALES_DETAIL_TS 122001.563 32000 26 74

      SQL> Select File_Name,Tablespace_Name,File_ID From dba_data_files Where Tablespace_Name = 'SALES_DETAIL_TS_IDX' Order by File_ID Desc;

      FILE_NAME
      --------------------------------------------------------------------------------
      TABLESPACE_NAME FILE_ID
      ------------------------------ ----------
      /d01/u05/oradata/PSRDM/sales_detail_ts_idx_27.dbf
      SALES_DETAIL_TS_IDX 1016

      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_26.dbf
      SALES_DETAIL_TS_IDX 978

      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_25.dbf
      SALES_DETAIL_TS_IDX 975


      FILE_NAME
      --------------------------------------------------------------------------------
      TABLESPACE_NAME FILE_ID
      ------------------------------ ----------
      /d01/u10/oradata/PSRDM/sales_detail_ts_idx_21.dbf
      SALES_DETAIL_TS_IDX 908

      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_20.dbf
      SALES_DETAIL_TS_IDX 837

      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_19.dbf
      SALES_DETAIL_TS_IDX 836


      FILE_NAME
      --------------------------------------------------------------------------------
      TABLESPACE_NAME FILE_ID
      ------------------------------ ----------
      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_18.dbf
      SALES_DETAIL_TS_IDX 835

      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_17.dbf
      SALES_DETAIL_TS_IDX 834

      /d01/u12/oradata/PSRDM/sales_detail_ts_idx_16.dbf
      SALES_DETAIL_TS_IDX 217


      FILE_NAME
      --------------------------------------------------------------------------------
      TABLESPACE_NAME FILE_ID
      ------------------------------ ----------
      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_15.dbf
      SALES_DETAIL_TS_IDX 216

      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_14.dbf
      SALES_DETAIL_TS_IDX 204

      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_13.dbf
      SALES_DETAIL_TS_IDX 203


      FILE_NAME
      --------------------------------------------------------------------------------
      TABLESPACE_NAME FILE_ID
      ------------------------------ ----------
      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_12.dbf
      SALES_DETAIL_TS_IDX 202

      /d01/u12/oradata/PSRDM/sales_detail_ts_idx_24.dbf
      SALES_DETAIL_TS_IDX 156

      /d01/u12/oradata/PSRDM/sales_detail_ts_idx_23.dbf
      SALES_DETAIL_TS_IDX 155


      FILE_NAME
      --------------------------------------------------------------------------------
      TABLESPACE_NAME FILE_ID
      ------------------------------ ----------
      /d01/u10/oradata/PSRDM/sales_detail_ts_idx_22.dbf
      SALES_DETAIL_TS_IDX 145

      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_21.dbf
      SALES_DETAIL_TS_IDX 137

      /d01/u15/oradata/PSRDM/sales_detail_ts_idx_11.dbf
      SALES_DETAIL_TS_IDX 125


      FILE_NAME
      --------------------------------------------------------------------------------
      TABLESPACE_NAME FILE_ID
      ------------------------------ ----------
      /d01/u12/oradata/PSRDM/sales_detail_ts_idx_10.dbf
      SALES_DETAIL_TS_IDX 124

      /d01/u12/oradata/PSRDM/sales_detail_ts_idx_09.dbf
      SALES_DETAIL_TS_IDX 123

      /d01/u08/oradata/PSRDM/sales_detail_ts_idx_08.dbf
      SALES_DETAIL_TS_IDX 122


      FILE_NAME
      --------------------------------------------------------------------------------
      TABLESPACE_NAME FILE_ID
      ------------------------------ ----------
      /d01/u08/oradata/PSRDM/sales_detail_ts_idx_07.dbf
      SALES_DETAIL_TS_IDX 121

      /d01/u07/oradata/PSRDM/sales_detail_ts_idx_06.dbf
      SALES_DETAIL_TS_IDX 120

      /d01/u07/oradata/PSRDM/sales_detail_ts_idx_05.dbf
      SALES_DETAIL_TS_IDX 119


      FILE_NAME
      --------------------------------------------------------------------------------
      TABLESPACE_NAME FILE_ID
      ------------------------------ ----------
      /d01/u06/oradata/PSRDM/sales_detail_ts_idx_04.dbf
      SALES_DETAIL_TS_IDX 118

      /d01/u06/oradata/PSRDM/sales_detail_ts_idx_03.dbf
      SALES_DETAIL_TS_IDX 117

      /d01/u05/oradata/PSRDM/sales_detail_ts_idx_02.dbf
      SALES_DETAIL_TS_IDX 116


      FILE_NAME
      --------------------------------------------------------------------------------
      TABLESPACE_NAME FILE_ID
      ------------------------------ ----------
      /d01/u05/oradata/PSRDM/sales_detail_ts_idx_01.dbf
      SALES_DETAIL_TS_IDX 115

      /d01/u04/oradata/PSRDM/sales_detail_ts_idx_27.dbf
      SALES_DETAIL_TS_IDX 63

      df -kh

      Size Used Free
      u12zp/u12 392G 347G 44G 89% /d01/u12
      u13zp/u13 392G 340G 51G 87% /d01/u13
      u08zp/u08 392G 317G 74G 82% /d01/u08
      u09zp/u09 392G 348G 44G 89% /d01/u09
      u06zp/u06 392G 318G 74G 82% /d01/u06
      u11zp/u11 392G 350G 42G 90% /d01/u11
      u04zp/u04 392G 324G 68G 83% /d01/u04
      u05zp/u05 392G 349G 43G 89% /d01/u05
      u07zp/u07 392G 331G 60G 85% /d01/u07
      u10zp/u10 392G 342G 50G 88% /d01/u10
      u15zp/u15 392G 324G 67G 83% /d01/u15
      u16zp/u16 392G 275G 116G 71% /d01/u16
      u14zp/u14 392G 316G 76G 81% /d01/u14
      u90zp/u90 392G 1.2G 391G 1% /d01/u90


      Thanks in advance.

      Edited by: 951121 on Nov 7, 2012 11:52 AM
        • 1. Re: How to add space in tablespace?
          rcc50886
          select file_name, bytes/(1024*1024*1024), autoextensiable, maxsize/(1024*1024)  from dba_data_files where tablespace_name='SALES_DETAIL_TS_IDX' ;
          find the datafile that is not reached the maximum size (32GB if its 8k block) and resize it to match your requirement.
          alter database datafile '<absolute_file_name>' resize <size>;
          or else you can add new datafile. blow is the simple add datafile statement:
          alter tbalespace add datafile '<absolute_file_name>' size <size>;
          • 2. Re: How to add space in tablespace?
            954124
            Thank you so much for your help. Now I got it.