URGENT URGENT URGENT PLEASE!!!!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: URGENT URGENT URGENT PLEASE!!!!!!

  1. #1
    Join Date
    Apr 2002
    Posts
    291
    Hi Gurus
    Here is an urgent problem to be solved. I got near about 225 tables and 85 views in a schema ABC. I want to create the same tables with out data in another Schema XYZ. I wrote a dynamic SQL like
    SELECT 'CREATE TABLE ' || TNAME ||' AS SELECT * FROM ABC. '||TNAME|| FROM TAB WHERE EMPCODE=0;

    but empcode is not there in all tables.so this statement doesn't work. I want to create all the tables similar to ABC schema. EXport and import wont work here, b'coz many of the tables has got default tablespaces in their definition itself. and this XYZ schema doesn't got quota on those tablespaces which ABC got. So give me some other solution please.
    Both the schemas are in the same database ...


    U R Earliest help is most appreciated ..
    thanks in advance

    PNRDBA

  2. #2
    Join Date
    Aug 2001
    Posts
    390
    Do an user export with row=n that way you won't export data.


  3. #3
    Join Date
    May 2002
    Posts
    2,645
    One real quick way: Fix the where clause to do something trivial such as where 1=2, then truncate the new table (means adding another line in the dynamic SQL, but is very easy to do).

  4. #4
    Join Date
    Apr 2002
    Posts
    291
    But there is a problem here, many of the tables has got their default tablespaces , on which XYX Schema doesn't got any permissions.
    PNRDBA

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Beside the fact that you can do all what you want with exp/imp (if TS quotas for importing user are set correctly), if you want to stick with your CTAS script, change your where clause from "... WHERE EMPCODE=0" withs something like "... WHERE 1 = 2". This will create table, but will not load any data in it since the where condition will never evaluate to TRUE.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    When XYZ performs the select statements, the "new" tables will be in XYZ's default tablespace....

    Make sure you use some never non-true evaluation such as where 1=2 (don't need to truncate otherwise, but you can if you're wanting to use where empcode=0 or something like that).

    Otherwise, as pointed out where 1=2 will suffice.

    [Edited by stecal on 05-20-2002 at 02:11 PM]

  7. #7
    Join Date
    Mar 2002
    Posts
    48
    SELECT 'CREATE TABLE ' || TNAME || ' tablespace AS SELECT * FROM ABC. '||TNAME|| ' WHERE 1 = 2;' FROM TAb
    /
    may work for you though not very good way


  8. #8
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    In the XYZ schema if you dont have tablespaces as in ABC, imp still works and all the objects will go to default tablespaces of XYZ.
    Thanks
    Kishore Kumar

  9. #9
    Join Date
    Apr 2002
    Posts
    291
    The Clause 'WHERE 1=2 worked out fine
    Thanks a lot gurus... Solved my problem, my work made easier
    PNRDBA

Posting Permissions

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


Click Here to Expand Forum to Full Width