10-15-2012
Small help related to date change
Hi,
I am using this code in order to automate a commands in DB:
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:
Code :
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
Code :
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
Code :
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)
Moderator's Comments:
edit by bakunin: Please view this code tag video for how to use code tags when posting code and data.
Last edited by bakunin; 10-15-2012 at 03:32 AM ..
10 More Discussions You Might Find Interesting
1. UNIX for Dummies Questions & Answers
Good day folks,
This is my first post on this board and I thank you in advance for helping me with this issue.
Any idea how I can synchronize server time with another timeserver but have my server lag behind by 2 seconds?
Meaning...I need a simple unix script that I can run as crone that... (2 Replies)
Discussion started by: franklo
2 Replies
2. Shell Programming and Scripting
Hi there I currently use a line in a script
ndate=$(date +"%d/%m/%Y")
This obviously returns the date in a format i have chosen. However, I need to come up with a solution for weekends and as such need a way of returning todays date minus two days in the same format (24/11/2004)
so for... (3 Replies)
Discussion started by: hcclnoodles
3 Replies
3. Shell Programming and Scripting
hi guys, I know this might be very simple for u but not for me.
I simply want to print the active users, changeing the first letter in their names to capital. i guess sed it's useful but don't know how to find the correspondign capital letter and don't know how to change just the first... (16 Replies)
Discussion started by: atticus
16 Replies
4. Shell Programming and Scripting
Hi All,
this small script is written to recognize user input character.. it is in small case .. upeer case or is a number... but when i input first capital letter say A.. it always gives small character.... what is the problem.
#!/bin/bash
echo "Enter the character"
read a
case $a in
)... (2 Replies)
Discussion started by: johnray31
2 Replies
5. UNIX for Dummies Questions & Answers
Assume, I created one file three years back and I like to move the file to some other directory with the old date (Creation date)? Is it possible? Explain? (1 Reply)
Discussion started by: jee.ku2
1 Replies
6. Shell Programming and Scripting
Hi All,
I have a script like this which read a file and take data with file seperator ,
and it is working fine for only one line.If i am giving two line of data in this file it is taking the second line only.Can anyone help me to solve the problem.My aim is to read the file each line by line. ... (5 Replies)
Discussion started by: Renjesh
5 Replies
7. Shell Programming and Scripting
Hi,
I have
TDATE=$(date '+%b %d')
That stores "Sep 01" in the TDATE.
How I can store "Sep 1"?
Thanks in advance (3 Replies)
Discussion started by: dipeshvshah
3 Replies
8. Shell Programming and Scripting
I have 100 files in a directory , all the files have a word "error" and they are created in different date . Now I would like to change the word from "error" to "warning" , and keep the date of the files ( that means do not change the file creation date after change the word ) , can advise what can... (0 Replies)
Discussion started by: ust3
0 Replies
9. Shell Programming and Scripting
I have 100 files in a directory , all the files have a word "error" and they are created in different date . Now I would like to change the word from "error" to "warning" , and keep the date of the files ( that means do not change the file creation date after change the word ) , can advise what can... (7 Replies)
Discussion started by: ust3
7 Replies
10. UNIX for Beginners Questions & Answers
Suppose i have a list of files in a directory as mentioned below
1. Shankar_04152019_ny.txt
2. Gopi_shan_03122019_mi.txt
3. Siva_mourya_02242019_nd.txt
..
.
.
.
.
1000 . Jiva_surya_02282019_nd.txt
query : At one shot i want to modify the above all filenames present in one path with... (4 Replies)
Discussion started by: Shankar455
4 Replies