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
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?