Sunday, September 16, 2007

11g automagic partition creations - part2

its a bit different when you range partition by date column..you cannot specify a constant as your interval

SQL> Create table test_auto_partitioning_2
2 (c1 number, c2 varchar2(10) , c3 date)
3 partition by range (c3)
4 interval(30)
5 (
6 partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
7 partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
8 partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
9 )
10 /
Create table test_auto_partitioning_2
*
ERROR at line 1:
ORA-14752: Interval expression is not a constant of the correct type

Instead specify as date-interval datatype so oracle is aware of what you are requesting.. Rest is pretty much the same..



SQL> Create table test_auto_partitioning_2
2 (c1 number, c2 varchar2(10) , c3 date)
3 partition by range (c3)
4 interval(numtoyminterval(1,'MONTH'))
5 (
6 partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
7 partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
8 partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
9 )
10 /

Table created.

SQL> select partition_name,num_rows from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_2';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART1
PART2
PART3

SQL> Insert into TEST_AUTO_PARTITIONING_2(c3)
2 values (to_date('&mm/&dd/2007','mm/dd/yyyy'));
Enter value for mm: 09
Enter value for dd: 01
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/01/2007','mm/dd/yyyy'))

1 row created.


SQL> /
Enter value for mm: 09
Enter value for dd: 13
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/13/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 09
Enter value for dd: 17
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/17/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 10
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('10/16/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 11
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('11/16/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 12
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('12/16/2007','mm/dd/yyyy'))

1 row created.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_stats.gather_table_stats(ownname=>user,tabname=>'TEST_AUTO_PARTITIONING_2');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_2';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART1 2
PART2 1
PART3 1
SYS_P44 1 -- auto generated
SYS_P45 1 -- auto generated

11g rocks!!

11 comments:

Anonymous said...

Howdy,

I am regular visitor of this website[url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips].[/url]You have really contiributed very good info here oracleisfun.blogspot.com. I am sure due to busy scedules we really do not get time to care about our health. Let me show you one truth. Research shows that about 50% of all United States grownups are either chubby or weighty[url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips].[/url] Hence if you're one of these individuals, you're not alone. In fact, most of us need to lose a few pounds once in a while to get sexy and perfect six pack abs. Now next question is how you can achive quick weight loss? [url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips]Quick weight loss[/url] is not like piece of cake. You need to improve some of you daily habbits to achive weight loss in short span of time.

About me: I am webmaster of [url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips]Quick weight loss tips[/url]. I am also health trainer who can help you lose weight quickly. If you do not want to go under painful training program than you may also try [url=http://www.weightrapidloss.com/acai-berry-for-quick-weight-loss]Acai Berry[/url] or [url=http://www.weightrapidloss.com/colon-cleanse-for-weight-loss]Colon Cleansing[/url] for fast weight loss.

Anonymous said...

hello!,I really like your writing so so much! percentage we communicate more about your
article on AOL? I need a specialist in this area to solve my
problem. May be that is you! Taking a look forward to look you.
my web site - africanmango

Anonymous said...

Have you ever thought about including a little
bit more than just your articles? I mean, what you say is valuable and everything.
Nevertheless imagine if you added some great photos or videos to give your posts more, "pop"!

Your content is excellent but with images and clips, this
site could undeniably be one of the best in its field.

Terrific blog!
Also see my web site: medi weightloss clinic

Anonymous said...

When I originally commented I seem to have clicked on the
-Notify me when new comments are added- checkbox and from now on each time a comment is added I get 4 emails
with the same comment. Is there an easy method you are able
to remove me from that service? Thanks a lot!

Also visit my site; weight loss pills reviews

Anonymous said...

Also known as eczema, this skin condition may appear or worsen during pregnancy.
Once you do, you will know which type of remover to buy that
will not irritate your skin. You will find that your custom shapes are now available from the shape menu when you
have the Custom Shape Tools selected.

Feel free to surf to my blog post: wartner skin tags

Anonymous said...

Hurrah, that's what I was looking for, what a data! present here at this web site, thanks admin of this site.

Feel free to visit my blog post :: left lower back pain - lowerbackpainrightsidehq.org -

Anonymous said...

Home Made Skin Tag Removal - 5 Tips For Home Skin Tags Removal Do you have problems
with skin tags and don't know what to do. There are alternatives to having a skin tag froze, burned or cut off. Skin tags can affect you and anybody else at any age and irrespective of your skin type.

Take a look at my page; cancerous skin tags ()

Anonymous said...

Hello just wanted to give you a quick heads up. The text in your article seem
to be running off the screen in Safari. I'm not sure if this is a format issue or something to do with web browser compatibility but I figured I'd post
to let you know. The design look great though!
Hope you get the problem fixed soon. Cheers

Review my page bear river insurance

Unknown said...

من الاعمال شركة تنظيف خزانات بالمدينة المنورة التي لابد من القيام بها تحت اعين شركه كبيره تمتلك كل مقومات النجاح اعمال النظافه فالنظافه اصبحت اليوم امر هام جدا نظرا لانتشار التكنولوجيا وتعدد الاستخدامات داخل اي منزل اصبح الاعتماد علي شركه تمتلك اسم كبير امر ضروري وحتمي علي كل البيت ولتوفير ذالك الامر فعليكم شركة كشف تسربات المياه بجدة الاتصال بشركتنا كشركه كبيره في عالم النظافه حيث تقدم الشركه شركة الصفرات للتنظيف اداء عالي جدا لعملائها الكرام حيث اننا نقوم بتنظيف البيت من الداخل والخارج

3nod maka said...

توفر لكم أفضل شركة تنظيف بمكة عروض خاصة على خدمات تنظيف البيوت لنوفر لكم أسعار تنافسية و بواسطة فريق محترف على أعلى مستوى خبرة في مجال تنظيف المنازل
كما يسرنا أن نعلن عن توافر فرق تنظيف كنب بعناية شديدة للحفاظ على مظهر الكنب دون الإضرار بنسيجه و إفساد مظهره
هذا و نقدم لكم خدمة غسيل خزانات بمكة فقط من خلال عنود مكة يمكنكم الشعور بالأمان معنا لأننا نضمن لكم الأفضل

انجين محمد said...

شركة تنظيف بالدمام

تعد شركة تنظيف بالدمام الأفضل دائماً بين كافة الشركات التي توجد في المملكة العربية السعودية وليس فقط الدمام، وقد حازت على الكثير من الشهادات التي تؤهلها دائماً علي أن تصبح من أفضل الشركات المثالية في مجال التنظيف، كما أن يوجد الكثير من الأقسام التابعة للشركة مثل تنظيف السجاد وتنظيف المفروشات وتنظيف المساجد.
وتعمل الشركة على أن توفر جميع الأجهزة والأدوات والتقنيات الحديثة المستخدمة في مجال التنظيف، واستخدام أجود أنواع مواد التنظيف المصنفة عالمياً وغيرها من مواد التعقيم وأجود أنواع العطور التي يتم استيرادها من الخارج وحاملة للماركات العالمية ، من أجل إتمام مهمة التنظيف والحصول على أعلى مستوي نظافة اتصل الآن ولا تتردد.