How do I partition an Oracle 11g Table?

Applications Programming
1

Hello,

I have a database called "audit_database" and I want to create two tables.

Table 1 = Table 1
Table 2 = Audit

I want to partition the Audit table into partitions grouped by month. I'm not familiar with table partitioning but doing some reading online shows that it can be done but how would I do this?

create table audit (month number(12),
  partition by range (month)
  partition 1 = january ...?
2

If you are using 11g, then you want to use interval partitioning. Oracle will create a new partition every month for that months data. However, you might find that partitioning by day will work better, since you are working with audit data you might only look at the last day or last couple of days when querying the data. Having smaller level of granularity when used with partition pruning will likely lead to fewer io operator per query. Hence more efficient queries. You might also want to partition by month and sub partition by day. This will make it easier to drop a month at a time when you truncate old data, assuming that you do. You should be able to find plenty of examples for what you want on the web.

https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#BAJHFFBE

3

Thanks for checking. I have this query and I thought i understood the interval partitioning, but it's giving me the following error. Can you help with the proper syntax please?

SQL Error: ORA-14752: Interval expression is not a constant of the correct type

Here is the statement.

CREATE TABLE "AUDIT_COLLECTION_EVENTS" 
   ("AUDIT_TS" TIMESTAMP (6) DEFAULT sysdate, 
    "AUDIT_ID" NUMBER(20,0) DEFAULT -1, 
     "EVENT_NAME" VARCHAR2(48 CHAR) DEFAULT (null), 
     "EVENT_VALUE" VARCHAR2(256 CHAR) DEFAULT (null), 
    "TABLE_TXN_ID" NUMBER(20,0)
   )
  TABLESPACE "AUDIT"
  
  PARTITION BY RANGE ("AUDIT_TS") INTERVAL (NUMTODSINTERVAL(1,'MONTH')) 
 (PARTITION "PART1"  VALUES LESS THAN (TO_DATE('2014-12-01', 'YYYY-MM-DD'));
4

You're using "NUMTODSINTERVAL"
you probably want to use: "NUMTOYMINTERVAL"
instead :wink:

Data Types

5

wow. Simple as that. It worked.

Thanks a mil.