OraFAQ Forum: SQL & PL/SQL » Automation script related to creation of tablespace

Home » SQL & PL/SQL » SQL & PL/SQL » Automation script related to creation of tablespace

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Switch to threaded view of this topic

Create a new topic

Submit Reply

Automation script related to creation of tablespace [message #568174]

Wed, 10 October 2012 01:43

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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568180 is a reply to message #568174]

Wed, 10 October 2012 02:16

Michel Cadot
Messages: 54125
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is your problem in writing it?

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568183 is a reply to message #568180]

Wed, 10 October 2012 02:26

Michel Cadot
Messages: 54125
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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568187 is a reply to message #568183]

Wed, 10 October 2012 02:39

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.

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568190 is a reply to message #568187]

Wed, 10 October 2012 02:51

Michel Cadot
Messages: 54125
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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568202 is a reply to message #568174]

Wed, 10 October 2012 04:20

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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568205 is a reply to message #568202]

Wed, 10 October 2012 04:24

Michel Cadot
Messages: 54125
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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568207 is a reply to message #568205]

Wed, 10 October 2012 04:28

ramoradba
Messages: 2427
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

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568211 is a reply to message #568205]

Wed, 10 October 2012 04:42

hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi

Junior Member

Sorry not able to get you ... Sad

Sad

Can you please show the code made by me as what change has to be done...

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568218 is a reply to message #568211]

Wed, 10 October 2012 05:03

Michel Cadot
Messages: 54125
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

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568219 is a reply to message #568211]

Wed, 10 October 2012 05:03

ramoradba
Messages: 2427
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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568222 is a reply to message #568218]

Wed, 10 October 2012 05:10

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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568225 is a reply to message #568222]

Wed, 10 October 2012 05:15

ramoradba
Messages: 2427
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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568226 is a reply to message #568225]

Wed, 10 October 2012 05:16

hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi

Junior Member

Giving space i already did..... Razz

Razz

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568227 is a reply to message #568226]

Wed, 10 October 2012 05:18

ramoradba
Messages: 2427
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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568228 is a reply to message #568227]

Wed, 10 October 2012 05:21

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

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568230 is a reply to message #568228]

Wed, 10 October 2012 05:27

Michel Cadot
Messages: 54125
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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568231 is a reply to message #568228]

Wed, 10 October 2012 05:29

Michel Cadot
Messages: 54125
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

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568232 is a reply to message #568231]

Wed, 10 October 2012 05:30

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.

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568592 is a reply to message #568231]

Mon, 15 October 2012 00:01

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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568596 is a reply to message #568592]

Mon, 15 October 2012 00:10

Littlefoot
Messages: 16924
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

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568598 is a reply to message #568596]

Mon, 15 October 2012 00:13

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.

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568600 is a reply to message #568598]

Mon, 15 October 2012 00:51

Littlefoot
Messages: 16924
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.

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568618 is a reply to message #568600]

Mon, 15 October 2012 02:57

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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568619 is a reply to message #568618]

Mon, 15 October 2012 03:15

Littlefoot
Messages: 16924
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.

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568621 is a reply to message #568619]

Mon, 15 October 2012 04:38

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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568622 is a reply to message #568621]

Mon, 15 October 2012 04:45

Littlefoot
Messages: 16924
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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568623 is a reply to message #568622]

Mon, 15 October 2012 04:49

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?

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568624 is a reply to message #568623]

Mon, 15 October 2012 04:57

Littlefoot
Messages: 16924
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

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568625 is a reply to message #568624]

Mon, 15 October 2012 05:09

hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi

Junior Member

Hello Again..

What about Month? Will it be changed automatically to NOV?

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568626 is a reply to message #568625]

Mon, 15 October 2012 05:13

Littlefoot
Messages: 16924
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

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568627 is a reply to message #568626]

Mon, 15 October 2012 05:17

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

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568628 is a reply to message #568627]

Mon, 15 October 2012 05:25

Littlefoot
Messages: 16924
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;

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568629 is a reply to message #568628]

Mon, 15 October 2012 05:32

hitesh1907nayyar
Messages: 16
Registered: October 2012
Location: Delhi

Junior Member

Thanks for your help

Report message to a moderator

Send a private message to this user

Re: Automation script related to creation of tablespace [message #568677 is a reply to message #568629]

Mon, 15 October 2012 13:31

Bill B
Messages: 985
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.

Report message to a moderator

Send a private message to this user

Switch to threaded view of this topic

Create a new topic

Submit Reply

Previous Topic:	Test Case Builder
Next Topic:	Filtering

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 08:12:10 CDT 2013

Total time taken to generate the page: 0.08218 seconds