Thanks for the question, Bassim.
Asked: September 01, 2018 - 9:48 pm UTC
Last updated: September 03, 2018 - 1:22 am UTC
Version: oracle database 11g
Viewed 1000+ times
You Asked
Q1/IF I HAD TWO PARTITION (p1, p2). EACH PARTITION HAVE 3 tablespaces
P1Tsdata1,, p2 tsdata2 global fields data type
P1Tsidx1,, p2tsidx2 for primry key index
And P1Tsblob1,, p2tsblob2 for photo field
How can I create table( tab_test) with characteristics above ,can you write IT?
Since : tab_test ( id number pk,
Name varchar2(30),
Photo blob)
Q2/ when I know that table is needed to partitioning it? And if I don't have partitioning and create tablespace with datafile size 2G with one partition and datafile is full. How can I extend it or process this problem, where I entered data to it?
and Connor said...
1)
SQL> create table t
2 ( id int, d date, b blob, constraint pk primary key (d,id) using index tablespace users local )
3 partition by range ( d)
4 ( partition p1 values less than ( date '2018-01-01' ) tablespace largets
5 lob ( b ) store as ( tablespace demo ),
6 partition p2 values less than ( date '2018-02-01' ) tablespace largets
7 lob ( b ) store as ( tablespace demo )
8 );
Table created.
2)
You partition a table when the benefits outweigh the cost (because it is a licensed option). Those benefits could be performance, maintenance , archival, etc.
For me, I use autoextend datafiles now. But even if not, you can always add more datafiles to a single tablespace. It is not one for one.