-
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
-
Do an user export with row=n that way you won't export data.
-
But there is a problem here, many of the tables has got their default tablespaces , on which XYX Schema doesn't got any permissions.
-
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).
-
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.
-
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]
-
SELECT 'CREATE TABLE ' || TNAME || ' tablespace AS SELECT * FROM ABC. '||TNAME|| ' WHERE 1 = 2;' FROM TAb
/
may work for you though not very good way
-
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.
-
The Clause 'WHERE 1=2 worked out fine
Thanks a lot gurus... Solved my problem, my work made easier
