Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2001
    Location
    The Netherlands
    Posts
    38

    Unanswered:

    If you want to 'automate' things could do the following.

    Run the following from sqlplus

    set pages 0
    spool alt.sql
    select 'rename table '||table_name||' to tmp_'||table_name||';'||'
    create table '||table_name||' as select * from tmp_'||table_name||';'
    from user_segments
    where segment_type = 'TABLE'
    and table_space_name = 'SYSTEM'
    spool off

    @alt.sql

    Sqlplus is often a handy tool if you know how to use it...

  2. #2
    Join Date
    Nov 2001
    Posts
    7
    Dear sir

    It's really confusing me a little bit cause it's complicated for me.
    Can you make it easier?

    "select 'rename table '||table_name||' to tmp_'||table_name||';'||'
    create table '||table_name||' as select * from tmp_'||table_name||';'
    from user_segments
    where segment_type = 'TABLE'
    and table_space_name = 'SYSTEM' "

    By the way, I wonder what it will look like in alt.sql after I run the statement?


    Thanks for reply.


    Newpaul

  3. #3
    Join Date
    Jun 2001
    Location
    The Netherlands
    Posts
    38
    Hi,

    First of all I made some errors in the script I gave. Here's the correct version with some more explanation.

    Oracle stores information on the tables in the data dictionary tables. This information also includes the tablespace where the table is created.
    For a specific schema (or user) this info is stored in USER_TABLES.
    Just startup sqlplus with the user that has tables stored in tablespace system and have a look at which tables these are.

    sqlplus <username>/<password>@<dbname>
    SQL> select table_name
    2> from user_tables
    3> where tablespace_name = 'SYSTEM';

    The statement above will show these tables.

    Now, what you want to do is create a sequence of statements that will do the following:

    - rename the table
    - create the table in the correct tablespace
    - drop the renamed table

    For this you can use the user_tables dictionary table and some clever sql. Just run the statement shown below and have a look at the output:

    SQL>select 'rename '||table_name||' to tmp_'||table_name||';
    2> create table '||table_name||' as select * from tmp_'||table_name||';
    3> drop table '||table_name||';'
    4> from user_tables
    5> where tablespace_name = 'SYSTEM';

    It will show the rename, create and drop statements for tables within tablespace SYSTEM.

    If you preced the select-statement with a spool statement (like spool alt.sql) it will write the output to the file alt.sql. After the execution stops you have to issue a 'spool off' to stop spooling to the file.

    Within sqlplus you can use the @ to execute the contents of a file. So:

    SQL> @alt.sql

    will execute the statements from the file alt.sql.

    Good luck,

    Ben


    PS: The SQL> and 2>, 3>, etc. is the sqlplus prompt.
    PS2: all statements have to be entered as mentioned to run properly

  4. #4
    Join Date
    Nov 2001
    Posts
    7
    Dear Ben

    Thanks for your help.

    It sort of works.
    It seems that sqlplus treats semicolon as the end of a statement.
    So I put the whole statement in just one line and it looked like,

    SQL>select 'rename '||table_name||' to tmp_'||table_name||'; create table '||table_name||' as select * from tmp_'||table_name||'; drop table tmp_'||table_name||';' from user_tables where tablespace_name = 'SYSTEM';

  5. #5
    Join Date
    Jun 2001
    Location
    The Netherlands
    Posts
    38
    If you enter the statement the way I showed you get an outputfile with statements formatted in such a way that you can run the file, otherwise you may have to do some editing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •