Scribd Logo
Upload

Welcome to Scribd!

  • 64 views

    Move All Database Objects From One Tablespace To Another

    Uploaded by

    Jabras Guppies
    This SQL script moves all database objects from one tablespace to another. It begins by defining variables for the source tablespace, destination tablespace, and schema owner. It then selects statements to transport the objects, alter user quotas, move tables and indexes, and rebuild indexes in the destination tablespace.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    Move All Database Objects From One Tablespace To Another

    Uploaded by

    Jabras Guppies
    64 views2 pages
    This SQL script moves all database objects from one tablespace to another. It begins by defining variables for the source tablespace, destination tablespace, and schema owner. It then selects statements to transport the objects, alter user quotas, move tables and indexes, and rebuild indexes in the destination tablespace.

    Original Description:

    Move All Database Objects From One Tablespace to Another

    Original Title

    Move All Database Objects From One Tablespace to Another

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This SQL script moves all database objects from one tablespace to another. It begins by defining variables for the source tablespace, destination tablespace, and schema owner. It then selects statements to transport the objects, alter user quotas, move tables and indexes, and rebuild indexes in the destination tablespace.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    64 views2 pages

    Move All Database Objects From One Tablespace To Another

    Uploaded by

    Jabras Guppies
    This SQL script moves all database objects from one tablespace to another. It begins by defining variables for the source tablespace, destination tablespace, and schema owner. It then selects statements to transport the objects, alter user quotas, move tables and indexes, and rebuild indexes in the destination tablespace.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    You are on page 1of 2

    Move all database objects from one tablespace to another

    =========================================================
    set echo off
    set heading off
    var tbs_source varchar2;
    var tbs_dest varchar2;
    var schema_user varchar2;
    -- '' if it isn't
    exec :tbs_source
    -- '' if it isn't
    exec :schema_user
    exec :tbs_dest :=

    relevant.
    := 'SOURCE_TBS';
    relevant.
    := 'SOURCE_USER';
    'DEST_TBS';

    select 'Transporting tablespace ' || :tbs_source || ' or user ' || :schema_user


    || ' to tablespace ' || :tbs_dest from dual;
    select 'Size: ' || to_char((sum(ext.bytes) / 1048576), '9,990.00') || ' MB'
    from dba_objects ob
    inner join (
    select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, 'TABLE' as t
    ipo from dba_tables ta
    union
    select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, 'INDEX' a
    s tipo from dba_indexes ind
    ) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
    inner join dba_extents ext on ext.segment_name = ob.object_name
    where ob.object_type in ('INDEX', 'TABLE', 'LOB') and (tb.tbs = :tbs_source or o
    b.owner = :schema_user);
    select 'alter user ' || ob.owner || ' quota unlimited on ' || :tbs_dest || ' def
    ault tablespace ' || :tbs_dest || ';'
    from dba_objects ob
    inner join (
    select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, 'TABLE' as t
    ipo from dba_tables ta
    union
    select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, 'INDEX' a
    s tipo from dba_indexes ind
    ) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
    where ob.object_type in ('INDEX', 'TABLE', 'LOB') and (tb.tbs = :tbs_source or o
    b.owner = :schema_user)
    group by ob.owner;
    select
    decode(ob.object_type,
    'TABLE',
    'alter table "' || ob.owner || '"."' || ob.object_name || '" move tablespa
    ce ' || :tbs_dest || ';',
    'alter index "' || ob.owner || '"."' || ob.object_name || '" rebuild table
    space ' || :tbs_dest || ';'
    )
    from dba_objects ob
    inner join (
    select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, 'TABLE' as t
    ipo from dba_tables ta
    union
    select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, 'INDEX' a

    s tipo from dba_indexes ind


    ) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
    where ob.object_type in ('TABLE') and (tb.tbs = :tbs_source or ob.owner = :schem
    a_user);
    select
    'alter table "' || lo.owner || '"."' || lo.table_name ||
    '" move lob ("' || lo.column_name || '") store as (tablespace ' || :tbs_dest |
    | ');'
    from dba_lobs lo
    inner join dba_segments se on se.segment_name = lo.segment_name
    where se.tablespace_name = :tbs_source or se.owner = :schema_user;
    select
    decode(ob.object_type,
    'TABLE',
    'alter table "' || ob.owner || '"."' || ob.object_name || '" move tablespa
    ce ' || :tbs_dest || ';',
    'alter index "' || ob.owner || '"."' || ob.object_name || '" rebuild table
    space ' || :tbs_dest || ';'
    )
    from dba_objects ob
    inner join (
    select ta.owner, ta.table_name as nom, ta.tablespace_name as tbs, 'TABLE' as t
    ipo from dba_tables ta
    union
    select ind.owner, ind.index_name as nom, ind.tablespace_name as tbs, 'INDEX' a
    s tipo from dba_indexes ind
    ) tb on tb.owner=ob.owner and tb.nom=ob.object_name and tb.tipo=ob.object_type
    where ob.object_type in ('INDEX') and (tb.tbs = :tbs_source or ob.owner = :schem
    a_user);

    You might also like