|
|
Re: can we create a table [message #305263 is a reply to message #305260] |
Mon, 10 March 2008 04:47   |
rman_yv
Messages: 9 Registered: February 2008
|
Junior Member |
|
|
Actually we are planning to archive the subpartitions data and our subpartitions are list partitions on day field... to exchange the subpartition we need to create new table...so just want to try and if it goes well we will plan to continue ...
can you please forward me the sample procedure in getting this?
|
|
|
Re: can we create a table [message #305264 is a reply to message #305262] |
Mon, 10 March 2008 04:50   |
Frank Naude
Messages: 4590 Registered: April 1998
|
Senior Member |
|
|
DECLARE
cols VARCHAR2(80) := '(c1 NUMBER, c2 VARCHAR2(20))';
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE t1 '||cols;
EXECUTE IMMEDIATE 'CREATE TABLE t2 '||cols;
EXECUTE IMMEDIATE 'CREATE TABLE t3 '||cols;
EXECUTE IMMEDIATE 'CREATE TABLE t4 '||cols;
EXECUTE IMMEDIATE 'CREATE TABLE t5 '||cols;
EXECUTE IMMEDIATE 'CREATE TABLE t6 '||cols;
EXECUTE IMMEDIATE 'CREATE TABLE t7 '||cols;
EXECUTE IMMEDIATE 'CREATE TABLE t8 '||cols;
EXECUTE IMMEDIATE 'CREATE TABLE t8 '||cols;
EXECUTE IMMEDIATE 'CREATE TABLE t10 '||cols;
END;
/
|
|
|
|
Re: can we create a table [message #305266 is a reply to message #305260] |
Mon, 10 March 2008 05:03   |
rman_yv
Messages: 9 Registered: February 2008
|
Junior Member |
|
|
and i do have one more doubt..
If user wants to archive data from some 17th to 29th of month 08.
Is it possible to create the tables as per user given dates and month through procedure?
Even for this can you support me with a sample procedure.
|
|
|
|
|
Re: can we create a table [message #306220 is a reply to message #305260] |
Thu, 13 March 2008 06:07  |
rman_yv
Messages: 9 Registered: February 2008
|
Junior Member |
|
|
BEGIN
for X in fromdate..todate
loop
dbms_output.put_line('CREATE TABLE Archiving_month_'||X|| ' (day1 number,month1 number) tablespace ARCHIVING_TS');
--EXECUTE IMMEDIATE 'CREATE TABLE Archiving_month_'||X|| ' '||cols;
end loop;
END;
I created list of tables using this loop
I hope i'm not wrong in creation loop .... even i tested and i got the output
CREATE TABLE Archiving_mnth_6 (day1 number,mnth1 number) tablespace ARCHIVING_TS
CREATE TABLE Archiving_mnth_7 (day1 number,mnth1 number) tablespace ARCHIVING_TS
CREATE TABLE Archiving_mnth_8 (day1 number,mnth1 number) tablespace ARCHIVING_TS
CREATE TABLE Archiving_mnth_9 (day1 number,mnth1 number) tablespace ARCHIVING_TS
if any more suggestions guide me to correct
|
|
|