Home » SQL & PL/SQL » SQL & PL/SQL » Automation script related to creation of tablespace
Automation script related to creation of tablespace [message #568174] Wed, 10 October 2012 01:43 Go to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
Hi,

I am new to PL/SQL.

Can someone help me to automate creation of Tablespaces for every hour by running a script on a day.

Here are the command to automate for automation related to Tablespaces

CREATE TABLESPACE TSCGFCDR03_0001OCT2012 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR03_0001OCT2012.dbf' SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLESPACE TSCGFCDR03_0102OCT2012 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR03_0102OCT2012.dbf'
SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;


CREATE TABLESPACE TSCGFCDR03_0203OCT2012 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR03_0203OCT2012.dbf'
SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

TSCGFCDR03_0203OCT2012---Refers to 03-3rd October 0203-refers to from 02:00 hrs to 03:00 hrs rest is the date.

These needs to be created 24 files one for each hour and for every date.

/ORADATA7/CRESTELDATA/TSCGFCDR03_0203OCT2012.db---This is the path for the creation of datafile.


Can some please help with the above requirement as how to proceed.

thanks
Hitesh
Re: Automation script related to creation of tablespace [message #568180 is a reply to message #568174] Wed, 10 October 2012 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 60013
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your problem in writing it?

Regards
Michel
Re: Automation script related to creation of tablespace [message #568183 is a reply to message #568180] Wed, 10 October 2012 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 60013
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming you want today's tablespaces:
SQL> declare
  2  begin
  3    for i in 0..23 loop
  4      dbms_output.put_line (
  5        'create tablespace TSCGFCDR'||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
  6         to_char(i+1,'fm00')||to_char(sysdate,'MONYY')||' DATAFILE '||
  7         '''/ORADATA7/CRESTELDATA/TSCGFCDR'||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
  8         to_char(i+1,'fm00')||to_char(sysdate,'MONYY')||'.dbf''
  9    SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;');
 10    end loop;
 11  end;
 12  /
create tablespace TSCGFCDR10_0001OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0001OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0102OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0102OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0203OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0203OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0304OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0304OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0405OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0405OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0506OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0506OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0607OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0607OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0708OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0708OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0809OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0809OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0910OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0910OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1011OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1011OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1112OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1112OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1213OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1213OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1314OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1314OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1415OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1415OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1516OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1516OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1617OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1617OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1718OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1718OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1819OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1819OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1920OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1920OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_2021OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_2021OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_2122OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_2122OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_2223OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_2223OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_2324OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_2324OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

PL/SQL procedure successfully completed.

Change call to dbms_output.put_line by "execute immediate".

Regards
Michel
Re: Automation script related to creation of tablespace [message #568187 is a reply to message #568183] Wed, 10 October 2012 02:39 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
Hi,

Really thanks for your help....

In addition to similar above i have to execute the below command(24 times) commands after creation above tablesapce...


ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR03_0001OCT2012 VALUES LESS THAN ((TO_DATE('03OCT2012 01:00:00','DDMONYYYY HH24:MI:SS')) ) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR03_0001OCT2012 STORAGE (BUFFER_POOL DEFAULT);

ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR03_0102OCT2012 VALUES LESS THAN ((TO_DATE('03OCT2012 02:00:00','DDMONYYYY HH24:MI:SS')) ) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR03_0102OCT2012 STORAGE (BUFFER_POOL DEFAULT);


Should i use the same patten that you used above? Will i be able to get the desired output.
Re: Automation script related to creation of tablespace [message #568190 is a reply to message #568187] Wed, 10 October 2012 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 60013
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Should i use the same patten that you used above?


Yes.

Quote:
Will i be able to get the desired output.


I hope so.

If tablespace name, file name and partition name contains the same pattern and it is better to put it in a variable than to repeat the same formula in each part of the statements:
myname := TSCGFCDR'||to_char(sysdate,'DD')||'_'||
          to_char(i,'fm00')||to_char(i+1,'fm00')||to_char(sysdate,'MONYY');


Regards
Michel
Re: Automation script related to creation of tablespace [message #568202 is a reply to message #568174] Wed, 10 October 2012 04:20 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
Hi Again,

I have made query as mentioned below:

declare
begin
for i in 0..23 loop
dbms_output.put_line (
'ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION'||'PSCGFCDR'||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||to_char(i+1,'fm00')||to_char(sysdate,'MONYY')||'VALUES LESS THAN'||'(TO_DATE('||to_char(sysdate,'DD')||to_char(sysdate,'MONYY')||to_char(i,'fm00')|| '00:00' || ', DDMONYYYY HH24:MI:SS) ) )' ||' LOGGING NOCOMPRESS TABLESPACE' || 'TSCGFCDR' || to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
to_char(i+1,'fm00')||to_char(sysdate,'MONYY') || 'STORAGE (BUFFER_POOL DEFAULT)');
end loop;
end;



Output for this is coming something like this:

ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITIONPSCGFCDR10_2122OCT12VALUES LESS THAN(TO_DATE(10OCT122100:00, DDMONYYYY HH24:MI:SS) ) ) LOGGING NOCOMPRESS TABLESPACETSCGFCDR10_2122OCT12STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITIONPSCGFCDR10_2223OCT12VALUES LESS THAN(TO_DATE(10OCT122200:00, DDMONYYYY HH24:MI:SS) ) ) LOGGING NOCOMPRESS TABLESPACETSCGFCDR10_2223OCT12STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITIONPSCGFCDR10_2324OCT12VALUES LESS THAN(TO_DATE(10OCT122300:00, DDMONYYYY HH24:MI:SS) ) ) LOGGING NOCOMPRESS TABLESPACETSCGFCDR10_2324OCT12STORAGE (BUFFER_POOL DEFAULT)


Original command for this was:

ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR03_2122OCT2012 VALUES LESS THAN ((TO_DATE('03OCT2012 22:00:00','DDMONYYYY HH24:MI:SS')) ) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR03_2122OCT2012 STORAGE (BUFFER_POOL DEFAULT);



ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR03_2223OCT2012 VALUES LESS THAN ((TO_DATE('03OCT2012 23:00:00','DDMONYYYY HH24:MI:SS')) ) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR03_2223OCT2012 STORAGE (BUFFER_POOL DEFAULT);


There seems to be some problem related to space.Can you please help related to this? Like in my output VALUES LESS THAN is attached to date itself but this should not be case.

BR//
Hitesh
Re: Automation script related to creation of tablespace [message #568205 is a reply to message #568202] Wed, 10 October 2012 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 60013
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want a quote in the final string you must double it in your code.

Please How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.


Regards
Michel
Re: Automation script related to creation of tablespace [message #568207 is a reply to message #568205] Wed, 10 October 2012 04:28 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.oracle-base.com/articles/10g/plsql-enhancements-10g.php#quoting_mechanism_for_string_literals
SET SERVEROUTPUT ON
BEGIN
  -- Orginal syntax.
  DBMS_OUTPUT.put_line('This is Tim''s string!');

  -- New syntax.
  DBMS_OUTPUT.put_line(q'#This is Tim's string!#');
  DBMS_OUTPUT.put_line(q'[This is Tim's string!]');
END;
/

This is Tim's string!
This is Tim's string!
This is Tim's string!
 
PL/SQL procedure successfully completed.


SQL> select 'This is Tim''s string' data from dual;

DATA
--------------------
This is Tim's string

SQL> select q'#This is Tim's string!#' DATA from dual;

DATA
---------------------
This is Tim's string!

SQL>

[Updated on: Wed, 10 October 2012 04:30]

Report message to a moderator

Re: Automation script related to creation of tablespace [message #568211 is a reply to message #568205] Wed, 10 October 2012 04:42 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
Sorry not able to get you ... Sad

Can you please show the code made by me as what change has to be done...
Re: Automation script related to creation of tablespace [message #568218 is a reply to message #568211] Wed, 10 October 2012 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 60013
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ramoradba gave you examples.
When you want ' in your final string you have to write '' (2 quotes, not a double-quote) in the string you write in your code.

Regards
Michel

[Updated on: Wed, 10 October 2012 05:03]

Report message to a moderator

Re: Automation script related to creation of tablespace [message #568219 is a reply to message #568211] Wed, 10 October 2012 05:03 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> declare
  2  begin
  3  for i in 1..1 loop
  4  dbms_output.put_line (
  5  'ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION '||'PSCGFCDR'||
  6  to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||to_char(i+1,'fm00')||to_char(sysdate,'MONYY')||'  VALUES LESS THAN '||'('
  7  ||'(TO_DATE('||''''||to_char(sysdate,'DD')||to_char(sysdate,'MONYY')||to_char(i,'fm00')
  8  || '00:00' ||''''|| ', '||''''||'DDMONYYYY HH24:MI:SS'||''''||') ) )'
  9  ||' LOGGING NOCOMPRESS TABLESPACE ' || ' TSCGFCDR' || to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
 10  to_char(i+1,'fm00')||to_char(sysdate,'MONYY') || ' STORAGE (BUFFER_POOL DEFAULT);');
 11  end loop;
 12  end;
 13  /
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION
PSCGFCDR10_0102OCT12  VALUES LESS THAN ((TO_DATE('10OCT120100:00', 'DDMONYYYY
HH24:MI:SS') ) ) LOGGING NOCOMPRESS TABLESPACE  TSCGFCDR10_0102OCT12 STORAGE
(BUFFER_POOL DEFAULT);

PL/SQL procedure successfully completed.


This Is your original requirement.

ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR03_2122OCT2012 VALUES LESS THAN ((TO_DATE('03OCT2012 22:00:00','DDMONYYYY HH24:MI:SS')) ) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR03_2122OCT2012 STORAGE (BUFFER_POOL DEFAULT);



Try to understand the Logic with " and ' while writing custom scripts.follow the links from www.tahiti.oracle.com.

This is just like an example Only.Forum members won`t do your job. You need to Understand the logic.

Thanks
Sriram Sanka
Re: Automation script related to creation of tablespace [message #568222 is a reply to message #568218] Wed, 10 October 2012 05:10 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
kk.,..just a require a small suggestion :


ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR10_2021OCT12 VALUES LESS THAN ((TO_DATE(10OCT201220 00:00, DDMONYYYY HH24:MI:SS) ) ) LOGGING NOCOMPRESS TABLESPACETSCGFCDR10_2021OCT12STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR10_2122OCT12 VALUES LESS THAN ((TO_DATE(10OCT201221 00:00, DDMONYYYY HH24:MI:SS) ) ) LOGGING NOCOMPRESS TABLESPACETSCGFCDR10_2122OCT12STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR10_2223OCT12 VALUES LESS THAN ((TO_DATE(10OCT201222 00:00, DDMONYYYY HH24:MI:SS) ) ) LOGGING NOCOMPRESS TABLESPACETSCGFCDR10_2223OCT12STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR10_2324OCT12 VALUES LESS THAN ((TO_DATE(10OCT201223 00:00, DDMONYYYY HH24:MI:SS) ) ) LOGGING NOCOMPRESS TABLESPACETSCGFCDR10_2324OCT12STORAGE (BUFFER_POOL DEFAULT)


In the last file i want it in this format:

ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR03_2300OCT2012 VALUES LESS THAN ((TO_DATE('04OCT2012 00:00:00','DDMONYYYY HH24:MI:SS')) ) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR03_2300OCT2012 STORAGE (BUFFER_POOL DEFAULT);



my file is coming out to be TABLESPACETSCGFCDR10_2324OCT12STORAGE whereas TSCGFCDR03_2300OCT2012 is the desired file. Code already i have shared above. Can you please as how to go about it...giving just the concept will work.

Thanks
Hitesh
Re: Automation script related to creation of tablespace [message #568225 is a reply to message #568222] Wed, 10 October 2012 05:15 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please re read My post. you will get the answer.

One more I am giving you the clue Here.
SQL> select 'ThisisTim''sstring' "Data Without Space"  from dual;

Data Without Spac
-----------------
ThisisTim'sstring

SQL> select 'This is Tim''s string' "Data With Space"  from dual;

Data With Space
--------------------
This is Tim's string


Just add a space after concatanation symbol(pipe ||).

-Sriram Sanka
Re: Automation script related to creation of tablespace [message #568226 is a reply to message #568225] Wed, 10 October 2012 05:16 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
Giving space i already did..... Razz
Re: Automation script related to creation of tablespace [message #568227 is a reply to message #568226] Wed, 10 October 2012 05:18 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Show us What you did and what was the output from SQL PLUS.
Don`t say anything ..just show us. so that we will try to help you,else YOYO.

Good luck
Sriram Sanka
Re: Automation script related to creation of tablespace [message #568228 is a reply to message #568227] Wed, 10 October 2012 05:21 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
Hi,

Just help me with this .Space thing i will read and will try to resolve myself.

SQL> declare
  2  begin
  3    for i in 0..23 loop
  4      dbms_output.put_line (
  5        'create tablespace TSCGFCDR'||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
  6         to_char(i+1,'fm00')||to_char(sysdate,'MONYY')||' DATAFILE '||
  7         '''/ORADATA7/CRESTELDATA/TSCGFCDR'||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
  8         to_char(i+1,'fm00')||to_char(sysdate,'MONYY')||'.dbf''
  9    SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;');
 10    end loop;
 11  end;
 12  /
create tablespace TSCGFCDR10_0001OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0001OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0102OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0102OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0203OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0203OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0304OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0304OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0405OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0405OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0506OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0506OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0607OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0607OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0708OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0708OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0809OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0809OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_0910OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_0910OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1011OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1011OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1112OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1112OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1213OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1213OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1314OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1314OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1415OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1415OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1516OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1516OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1617OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1617OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1718OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1718OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1819OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1819OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_1920OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_1920OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_2021OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_2021OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_2122OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_2122OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_2223OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_2223OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create tablespace TSCGFCDR10_2324OCT12 DATAFILE '/ORADATA7/CRESTELDATA/TSCGFCDR10_2324OCT12.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

PL/SQL procedure successfully completed.



Last file i want /ORADATA7/CRESTELDATA/TSCGFCDR10_2324OCT12.dbf to /ORADATA7/CRESTELDATA/TSCGFCDR10_2300OCT12.dbf


How to break the loop or modify this thing.

[Updated on: Wed, 10 October 2012 05:30] by Moderator

Report message to a moderator

Re: Automation script related to creation of tablespace [message #568230 is a reply to message #568228] Wed, 10 October 2012 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 60013
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Automation script related to creation of tablespace [message #568231 is a reply to message #568228] Wed, 10 October 2012 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 60013
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How to break the loop or modify this thing.


Just think a little bit and you should be able to do it.
Clue: investigate MOD function (or CASE or...).

Regards
Michel

[Updated on: Wed, 10 October 2012 05:29]

Report message to a moderator

Re: Automation script related to creation of tablespace [message #568232 is a reply to message #568231] Wed, 10 October 2012 05:30 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
kk..thanks...will get back to you in case not resolved....Will like to thanks for all your help today.

Re: Automation script related to creation of tablespace [message #568592 is a reply to message #568231] Mon, 15 October 2012 00:01 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
Hi,

I used this code:

begin
for i in 0..23 loop
dbms_output.put_line (
'ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION'||' PSCGFCDR'||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||to_char(mod(i+1,24),'fm00')||to_char(sysdate,'MONYY')||' VALUES LESS THAN '||'((TO_DATE('''||to_char(sysdate,'DD')||to_char(sysdate,'MON')|| to_char(sysdate,'YYYY')||' ' ||to_char(mod(i+1,24),'fm00')|| ':00:00''' || ', ''DDMONYYYY HH24:MI:SS'')))' ||' LOGGING NOCOMPRESS TABLESPACE ' || 'TSCGFCDR' || to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
to_char(mod(i+1,24),'fm00')||to_char(sysdate,'MONYY') || ' ' || 'STORAGE (BUFFER_POOL DEFAULT)');
end loop;
end;
/



Output is coming out to be:

ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0001OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 01:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0001OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0102OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 02:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0102OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0203OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 03:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0203OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0304OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 04:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0304OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0405OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 05:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0405OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0506OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 06:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0506OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0607OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 07:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0607OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0708OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 08:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0708OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0809OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 09:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0809OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0910OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 10:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0910OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1011OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 11:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1011OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1112OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 12:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1112OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1213OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 13:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1213OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1314OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 14:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1314OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1415OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 15:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1415OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1516OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 16:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1516OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1617OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 17:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1617OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1718OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 18:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1718OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1819OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 19:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1819OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1920OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 20:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1920OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2021OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 21:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2021OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2122OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 22:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2122OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2223OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 23:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2223OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2300OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 00:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2300OCT12 STORAGE (BUFFER_POOL DEFAULT)



I require a small modification over here in the last time.

Last line

ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2300OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 00:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2300OCT12 STORAGE (BUFFER_POOL DEFAULT)


should be

ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2300OCT12 VALUES LESS THAN ((TO_DATE('16OCT2012 00:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2300OCT12 STORAGE (BUFFER_POOL DEFAULT)


Can somebody please help!!!
Re: Automation script related to creation of tablespace [message #568596 is a reply to message #568592] Mon, 15 October 2012 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 19892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Add 1 to SYSDATE where appropriate:
to_char(sysdate + 1, 'dd')
Be careful, though, at the end of the month such as October. On 31st of October, you'd add 1 and end up with 32nd which doesn't exist. Maybe you should check the LAST_DAY vs. SYSDATE and handle that "problem".

[EDIT: fixed 32st to 32nd]

[Updated on: Mon, 15 October 2012 04:57]

Report message to a moderator

Re: Automation script related to creation of tablespace [message #568598 is a reply to message #568596] Mon, 15 October 2012 00:13 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
If i will apply this thing to my code it will change the whole structure of the output.

sysdate + 1 will show plus one date in all the output.I only want the date change in the last line.
Re: Automation script related to creation of tablespace [message #568600 is a reply to message #568598] Mon, 15 October 2012 00:51 Go to previous messageGo to next message
Littlefoot
Messages: 19892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Wrong! I said "where appropriate", which doesn't mean all 7 occurrences of SYSDATE in your code but only one - where appropriate.
Re: Automation script related to creation of tablespace [message #568618 is a reply to message #568600] Mon, 15 October 2012 02:57 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
Hi,

I am still not able to get you.I am using a loop so if i change sysdate +1 that will affect all the changes right? Please if you can display as how it can be used in my code...that will be helpful
Re: Automation script related to creation of tablespace [message #568619 is a reply to message #568618] Mon, 15 October 2012 03:15 Go to previous messageGo to next message
Littlefoot
Messages: 19892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are 24 ALTER TABLE statements; I understood that all of them should have 16OCT at that place, but no - only the 24th should have it. It seems that I was wrong about it, sorry.

OK then, add a DECODE; if i = 23 then use SYSDATE + 1.
Re: Automation script related to creation of tablespace [message #568621 is a reply to message #568619] Mon, 15 October 2012 04:38 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
Hi,


I did it as per your saying. But you were right as there will be a problem when this script will be run on 31st October.

What to do in that case.Can you please guide
Re: Automation script related to creation of tablespace [message #568622 is a reply to message #568621] Mon, 15 October 2012 04:45 Go to previous messageGo to next message
Littlefoot
Messages: 19892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As I said, compare SYSDATE and LAST_DAY(SYSDATE) and don't add 1 if they are equal.
SQL> select trunc(sysdate) today,
  2         last_day(trunc(sysdate)) end_of_month
  3  from dual;

TODAY               END_OF_MONTH
------------------- -------------------
15.10.2012 00:00:00 31.10.2012 00:00:00
Re: Automation script related to creation of tablespace [message #568623 is a reply to message #568622] Mon, 15 October 2012 04:49 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
kk... I got it but the senario will change...


as an example consider today to be 31 and end date will also be 31st then i will require 1st Nov at the end of the line.Then how to proceed?
Re: Automation script related to creation of tablespace [message #568624 is a reply to message #568623] Mon, 15 October 2012 04:57 Go to previous messageGo to next message
Littlefoot
Messages: 19892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Gee, I must be completely dumb (read: stupid) ... I sincerely apologize for all messages I posted today as they are garbage.

"SYSDATE + 1" will always be valid; Oracle is smart (as opposed to me) and it knows that there's no 32nd in a month so - I think that you're OK with "SYSDATE + 1", always. So, you don't need to worry about LAST_DATE and stuff. All you have to do is to add 1 to SYSDATE for i = 23.

[Updated on: Mon, 15 October 2012 05:00]

Report message to a moderator

Re: Automation script related to creation of tablespace [message #568625 is a reply to message #568624] Mon, 15 October 2012 05:09 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
Hello Again..

What about Month? Will it be changed automatically to NOV?
Re: Automation script related to creation of tablespace [message #568626 is a reply to message #568625] Mon, 15 October 2012 05:13 Go to previous messageGo to next message
Littlefoot
Messages: 19892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, SYSDATE + 1 takes care about days, months and years. For example (all dates are DD.MM.YYYY)
- if today is 25.10.2012, SYSDATE + 1 = 26.10.2012
- if today is 31.10.2012, SYSDATE + 1 = 01.11.2012
- if today is 31.12.2012, SYSDATE + 1 = 01.01.2013
Re: Automation script related to creation of tablespace [message #568627 is a reply to message #568626] Mon, 15 October 2012 05:17 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
kk...one last thing...

I have made this code:

declare
begin
for i in 0..23 loop
IF i=23 THEN
dbms_output.put_line (
'ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION'||' PSCGFCDR'||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||to_char(mod(i+1,24),'fm00')||to_char(sysdate,'MONYY')||' VALUES LESS THAN '||'((TO_DATE('''||to_char(sysdate + 1,'DD')||to_char(sysdate,'MON')|| to_char(sysdate,'YYYY')||' ' ||to_char(mod(i+1,24),'fm00')|| ':00:00''' || ', ''DDMONYYYY HH24:MI:SS'')))' ||' LOGGING NOCOMPRESS TABLESPACE ' || 'TSCGFCDR' || to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
to_char(mod(i+1,24),'fm00')||to_char(sysdate,'MONYY') || ' ' || 'STORAGE (BUFFER_POOL DEFAULT)');
else
dbms_output.put_line (
'ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION'||' PSCGFCDR'||to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||to_char(mod(i+1,24),'fm00')||to_char(sysdate,'MONYY')||' VALUES LESS THAN '||'((TO_DATE('''||to_char(sysdate,'DD')||to_char(sysdate,'MON')|| to_char(sysdate,'YYYY')||' ' ||to_char(mod(i+1,24),'fm00')|| ':00:00''' || ', ''DDMONYYYY HH24:MI:SS'')))' ||' LOGGING NOCOMPRESS TABLESPACE ' || 'TSCGFCDR' || to_char(sysdate,'DD')||'_'||to_char(i,'fm00')||
to_char(mod(i+1,24),'fm00')||to_char(sysdate,'MONYY') || ' ' || 'STORAGE (BUFFER_POOL DEFAULT)');
end if;
end loop;
end;
/


I am getting the desired output as below:

ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0001OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 01:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0001OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0102OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 02:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0102OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0203OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 03:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0203OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0304OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 04:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0304OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0405OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 05:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0405OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0506OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 06:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0506OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0607OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 07:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0607OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0708OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 08:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0708OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0809OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 09:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0809OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_0910OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 10:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_0910OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1011OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 11:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1011OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1112OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 12:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1112OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1213OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 13:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1213OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1314OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 14:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1314OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1415OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 15:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1415OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1516OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 16:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1516OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1617OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 17:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1617OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1718OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 18:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1718OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1819OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 19:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1819OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_1920OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 20:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_1920OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2021OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 21:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2021OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2122OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 22:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2122OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2223OCT12 VALUES LESS THAN ((TO_DATE('15OCT2012 23:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2223OCT12 STORAGE (BUFFER_POOL DEFAULT)
ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION PSCGFCDR15_2300OCT12 VALUES LESS THAN ((TO_DATE('16OCT2012 00:00:00', 'DDMONYYYY HH24:MI:SS'))) LOGGING NOCOMPRESS TABLESPACE TSCGFCDR15_2300OCT12 STORAGE (BUFFER_POOL DEFAULT)




which is fine now.

If you are correct with the sysdate +1 thing then why there is a need to have an additional loop related to when it is 30th or 31st of everymonth. It will automatically change the date to 1st and month to be next month right?

[Updated on: Mon, 15 October 2012 05:18]

Report message to a moderator

Re: Automation script related to creation of tablespace [message #568628 is a reply to message #568627] Mon, 15 October 2012 05:25 Go to previous messageGo to next message
Littlefoot
Messages: 19892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure I understood the last sentence ... you don't have to do anything on 30th or 31st.

This is what had on mind (note the "--> here!" comment line):
begin
  for i in 0 .. 23 loop
    DBMS_OUTPUT.
          put_line (
            'ALTER TABLE CRESTELMEDIATIONPRD501.TBLMEDIATIONCDR ADD PARTITION'
            || ' PSCGFCDR'
            || TO_CHAR (SYSDATE, 'DD')
            || '_'
            || TO_CHAR (i, 'fm00')
            || TO_CHAR (MOD (i + 1, 24), 'fm00')
            || TO_CHAR (SYSDATE, 'MONYY')
            || ' VALUES LESS THAN '
            || '((TO_DATE('''
            || TO_CHAR (SYSDATE + case when i = 23 then 1 else 0 end, 'DD')     --> here!
            || TO_CHAR (SYSDATE, 'MON')
            || TO_CHAR (SYSDATE, 'YYYY')
            || ' '
            || TO_CHAR (MOD (i + 1, 24), 'fm00')
            || ':00:00'''
            || ', ''DDMONYYYY HH24:MI:SS'')))'
            || ' LOGGING NOCOMPRESS TABLESPACE '
            || 'TSCGFCDR'
            || TO_CHAR (SYSDATE, 'DD')
            || '_'
            || TO_CHAR (i, 'fm00')
            || TO_CHAR (MOD (i + 1, 24), 'fm00')
            || TO_CHAR (SYSDATE, 'MONYY')
            || ' '
            || 'STORAGE (BUFFER_POOL DEFAULT)');
  end loop;
end; 
icon14.gif  Re: Automation script related to creation of tablespace [message #568629 is a reply to message #568628] Mon, 15 October 2012 05:32 Go to previous messageGo to next message
hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi
Junior Member
Thanks for your help
Re: Automation script related to creation of tablespace [message #568677 is a reply to message #568629] Mon, 15 October 2012 13:31 Go to previous message
Bill B
Messages: 1141
Registered: December 2004
Senior Member
What database version are you using. In Oracle 11G you can automatically create partitions. based on any interval from a second up to a year.
Previous Topic: Test Case Builder
Next Topic: Filtering
Goto Forum:
  


Current Time: Mon Dec 22 00:43:08 CST 2014

Total time taken to generate the page: 0.10815 seconds