Skip to Content
avatar image
Former Member

Backup desired tables in oracle

How to backup desired tables in oracle?

I guess we can create a new tablespace and move all the desired tables and back it up by using this query

ALTER TABLESPACE <TABLESPACE_NAME> BEGIN BACKUP

please post your suggestions

Regards,

Pavan

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    May 06, 2014 at 10:35 AM

    Hi Pavan,

    You can go with exp/imp options or you can create a duplicate table likean e.g


    create table table_backup as select * from <table_name> ;


    Regards,

    Gaurav

    Add comment
    10|10000 characters needed characters exceeded

    • Dear Pavan,

      If you have space issues, backink up the table would not help you. Don't you mean reorganization, to gain some more freespace?

      By executing the above command (create table ...) you would create the new table (table_backup) in the users own default_tablespace. That means you would double the spacce required for the same data.

      Reorganizing the table into a newly created tablespace with BRSPACE would achieve you less extents allocated for the same object, means less space is allocated.

      So,l suggest you to use BRSPACE (which internally uses Oracle commands and features) to 'move' the table into a new tablespace.

      For more detailed info and to see some example commands, please check the below note:

      646681 Reorganization of tables with BRSPACE

  • May 06, 2014 at 10:32 AM

    Hello

    Your scenario does not really make sense. You do not need to copy tables to a new tablespace for 'backuping' them.Taking a backup of single tablespace is not really useful either as it cannot be restored on it's own as it is a part of a database and should be aligned with the DB change number (SCN).

    What are you willing to do, backup specific tables only, for which reason ?

    Regards

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 07, 2014 at 04:00 AM

    Hi,

    Safest and recommended option is to take a full online_consistent/offline backup before you perform any critical activity.

    If space is the constraint, then you can take backups into tapes which doesn't require space at your storage.

    Regards,

    Nick Loy

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi,

      Backing up your data is to ensure that it is used as a fall back solution.

      In this case even if you take a backup of particular table (by creating a new table), it can't be restored in later stage as online operations will not be captured in your backed up table.

      As you said - It is a big table, even to create new table then you need equal amount of space (double in size).

      For your situation - If you have equal amount of space on disk, then just create copy table by executing above command (mentioned in above replies) which will take it's own time, then perform your activity (users shouldn't access system during the activity to ensure that no online transactions which hits this particular table) and then release your system. As a worst case solution you can swap the table names if required.

      Regards,

      Nick Loy

  • avatar image
    Former Member
    Jul 18, 2014 at 09:13 AM

    Hello Gurus,

    I came to know that the desired tables can be backed up by creating a transport request.

    The generated data and cofiles can be copied any where and importing the transport request will get back the tables to database.

    I believe this is a better and simple option.

    Please suggest your comments..

    Regards,

    Pavan

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi,

      Just create a transport request and include objects (there you can add R3TR TABL <TABLE NAME>).

      Or go to table an search the menu option "Transport", it automatically creates a transport request.

      Regards,

      Nick Loy