Skip to Main Content
  • Questions

  • Question with 2 partition conntain 6 datafiles

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Bassim.

Asked: August 29, 2018 - 6:26 pm UTC

Last updated: September 03, 2018 - 9:51 am UTC

Version: oracle database 11g

Viewed 1000+ times

You Asked

HI Tom …
• I have a Database of more than 250 tables.
I worked 3 Tablespaces (TB1, TB2, TB3).
TB1: Used to store 5GB data tables
TB2: Used to store basic key index indexes for general tables and has a size of 2 GB
TB3: Used to store fields containing images and size 5 GB
These table spaces were used to store approximately 250 tables in them
* And the rest of the tables in the database containing many images, I worked 6 Tablespaces divided into two Partitions (p1, p2), respectively
- Split data worked into 2 table space (p1_data, p2_data) to store the data size of each 4GB
- Split the data into 2 table space (tspic1, tspic2) to store the images size of each 4 GB
- Split the data into 2 table space (p1_indx, p2_indx) to store primary key index data Size of each 2 GB
Q / Is this partitioning correct for in the example below… what is your notes about the partitions which I worked ?

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/g6r14d30x83bxwcewfa0rkju5

and Chris said...

I'm not sure what your question is here. There isn't an abstract way to say if your partitions are "correct" or not. It all depends on what you're trying to achieve.

* Why are you partitioning the table? What are you hoping to gain?
* Which queries do you run against the table? How many of these don't include the partitioning key in the where clause?
* Do you need to support mass inserts/updates/deletes of rows in the table?

Once you've defined your goals, you can then test to see if the partitioning scheme meets your needs. If you're struggling to meet your requirements, give us more details about what you're doing and we'll see how we can help.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

partitioning table ... how can i do it

Bassim Salikh, September 01, 2018 - 7:49 am UTC

Q1/IF I HAD TWO PARTITION (p1, p2). EACH PARTITION HAVE 3 tablespaces
P1Tsdata1,, p2 tsdata2 global fields data type
P1Tsidx1,, p2tsidx2 for primry key index
And P1Tsblob1,, p2tsblob2 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?

Chris Saxon
September 03, 2018 - 9:51 am UTC

1. The syntax in your LiveSQL script looks fine. What exactly is the problem you encounter? (You can't create tablespaces in LS, which is why the create table fails).

2. You never need partitioning. It's a tool which can make the maintenance of your tables easier. And might make queries faster. It does this by effectively splitting one table into smaller sub-tables.

Fully covering the in-and-outs of partitioning is too big a topic for this Q&A. Read the partitioning guide in the docs:

https://docs.oracle.com/en/database/oracle/oracle-database/18/vldbg/partition-intro.html#GUID-D01AB935-0567-42C5-B21E-FB36BA9C7BAD

If you have specific questions after reading this, ask!

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?

This is nothing to do with partitioning! Your files can fill up whether you use partitioning or not...

If you've hit the file limit, either enable autoextend and increase the maxsize of the relevant data files:

alter database datafile '...' autoextend on maxsize (unlimited | <size> );


Or add another data file to the tablespace.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database