2 Replies Latest reply on Dec 5, 2012 12:45 PM by mdecker77

    Changing phisycal attribute for a sub partition

    Orna Weisman

      oracle version : on Solaris

      I have a table with subpartitions and I need to reduce the initial extent of the subpartition without dropping and recreating .
      I can do that on a table level and on a partition level using :

      alter table xxx move storage (initial 65k);
      alter table XXX move partition YYY storage (initial 65k);

      But I cannot do a similar thing for subpartition

      alter table xxx move subpartition yyy storage (initial 65k);

      ORA-14160: this physical attribute may not be specified for a table subpartition

      Any way to accpmplish this without dropping and recreating the table ?

      This is needed as part of a huge data structure copy .
      I get the structure from a production DB and I am importing them ( using datapump ) to development.
      I need to "shrink" the tables because the initial extents defined are big and there are so many of them that the copied schema, thoufght empty of data is taking many GB of space.
      I managed to "shrink " everything , only subpartitioned tables are still occupying a lot of space

      Any advise ?

        • 1. Re: Changing phisycal attribute for a sub partition
          Orna Weisman
          Some more info :

          I am using LMT in both production and test
          but when I export and import ( datapump ) - the initial extents are not 'adjusting' themselves to the new tablespace in test.

          So the original tablespace in production have a 100MG initial and next extent and the target tablespace to which I remap during the import has 65k initial/next extent - but nevertheless - the tables are getting created with the old extent sizes of 100MG.

          The only way I found to fix this is to actually run alter table commands on the empty tables after import, and that works fine for everything other then subpartitions.

          • 2. Re: Changing phisycal attribute for a sub partition
            You can use TRANSFORM parameter of datapump import and have it igore the STORAGE clause contained in the datapump dumpfile but use the tablespace defaults instead.