Loading ...
Sorry, an error occurred while loading the content.

how to transfer tables from one tablespace to another

Expand Messages
  • zahhus277
    Dear How will transfer tables (one of them table has a field item of long row data type ) from one tablsspace to another tablespace. Regards Zaheer Hussain
    Message 1 of 3 , Apr 1, 2004
    • 0 Attachment
      Dear

      How will transfer tables (one of them table has a field item of "
      long row data type ") from one tablsspace to another tablespace.

      Regards

      Zaheer Hussain
    • Umair Malik
      Hi Zaheer, In your table if the long raw datatype exist than you can t move the tablespace from one to another. Use blob datatype instead of long raw. User
      Message 2 of 3 , Apr 1, 2004
      • 0 Attachment
        Hi Zaheer,
        In your table if the long raw datatype exist than you can't move the
        tablespace from one to another.
        Use blob datatype instead of long raw.
        User alter table command to move table from one tablespace to another
        tablespace.
        First query and check your current table tablespace from
        Sql>select * from user_tables;
        This will result you the table with their tablespace.
        Than if your users have privillige of alter any table than run the
        command from your user
        Sql>alter table <table_name> move tablespace <TBS>;
        Else use sys user and run this command
        Sql>alter table scott.<table_name> move tablespace <TBS>;


        Kindest Regards,

        Umair Ijaz Malik
        Support Team Leader,
        Senior Support Analyst,
        Ora-Tech Systems (PVT) LTD.
        Phone : 111-672253 Ext.219
        Email : umairmalik@... , mudiiius@...
        Cell : 0333-2191980

        -----Original Message-----
        From: zahhus277 [mailto:zahhus277@...]
        Sent: Thursday, April 01, 2004 4:07 PM
        To: PkOug@yahoogroups.com
        Subject: [PkOug] how to transfer tables from one tablespace to another

        Dear

        How will transfer tables (one of them table has a field item of "
        long row data type ") from one tablsspace to another tablespace.

        Regards

        Zaheer Hussain




        Yahoo! Groups Links
      • saqibz@hotmail.com
        Just in case you are wondering how to convert the long raw/long column to lob columns follow this process 1. table with original long raw column that has to be
        Message 3 of 3 , Apr 1, 2004
        • 0 Attachment
          Just in case you are wondering how to convert the long raw/long column to
          lob columns
          follow this process
          1. table with original long raw column that has to be moved to another
          tablespace
          create table t1 (f1 number(2) primary key, f2 long raw);
          2. create another table by using the to_lob() function to convert the long
          data type to lob.
          create table t2 as select f1, to_lob(f2) f2 from t1;
          3. Rename the original table to some temp table so that you can still have
          the original in case you want it
          rename table t1 to t1_old;
          4. Rename the new long converted table to the original table
          rename table t2 to t1;
          5. Move the table to the desired tablespace
          alter table t1 move tablespace <users>;

          The above process is valid for long raw and long data types. Also note that
          the lob data types are preferred over long as there can be more than one lob
          (nclob, clob, blob,bfile) data types on one table as opposed to a single
          long type column.

          Regards,

          Saqib Zulfiqar
          IS Manager, FTS group

          ----- Original Message -----
          From: "Umair Malik" <umairmalik@...>
          To: <PkOug@yahoogroups.com>
          Sent: Thursday, April 01, 2004 5:21 PM
          Subject: RE: [PkOug] how to transfer tables from one tablespace to another


          > Hi Zaheer,
          > In your table if the long raw datatype exist than you can't move the
          > tablespace from one to another.
          > Use blob datatype instead of long raw.
          > User alter table command to move table from one tablespace to another
          > tablespace.
          > First query and check your current table tablespace from
          > Sql>select * from user_tables;
          > This will result you the table with their tablespace.
          > Than if your users have privillige of alter any table than run the
          > command from your user
          > Sql>alter table <table_name> move tablespace <TBS>;
          > Else use sys user and run this command
          > Sql>alter table scott.<table_name> move tablespace <TBS>;
          >
          >
          > Kindest Regards,
          >
          > Umair Ijaz Malik
          > Support Team Leader,
          > Senior Support Analyst,
          > Ora-Tech Systems (PVT) LTD.
          > Phone : 111-672253 Ext.219
          > Email : umairmalik@... , mudiiius@...
          > Cell : 0333-2191980
          >
          > -----Original Message-----
          > From: zahhus277 [mailto:zahhus277@...]
          > Sent: Thursday, April 01, 2004 4:07 PM
          > To: PkOug@yahoogroups.com
          > Subject: [PkOug] how to transfer tables from one tablespace to another
          >
          > Dear
          >
          > How will transfer tables (one of them table has a field item of "
          > long row data type ") from one tablsspace to another tablespace.
          >
          > Regards
          >
          > Zaheer Hussain
          >
          >
          >
          >
          > Yahoo! Groups Links
          >
          >
          >
          >
          >
          >
          >
          >
          >
          >
          > Yahoo! Groups Links
          >
          >
          >
          >
          >
          >
        Your message has been successfully submitted and would be delivered to recipients shortly.