Home » SQL & PL/SQL » SQL & PL/SQL » A question about determining table size.
A question about determining table size. [message #224972] Fri, 16 March 2007 03:56 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member

Im trying to create a new table but would like to understand how people determine how much space to allocate for specific tables. Here is an example of a table script in our database.

CREATE TABLE SCHEDULED_JOBS
(
  JOB_ID      NUMBER                            NOT NULL,
  JOB_NAME    VARCHAR2(20 BYTE),
  STARTED_BY  VARCHAR2(10 BYTE),
  START_TIME  DATE,
  END_TIME    DATE,
  JOB_STATUS  VARCHAR2(15 BYTE)
)
TABLESPACE CLP
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;
/



Could someone please try and explain to me how i can determine whether the Initial, Minextents, maxextents specified in this table can be used to determine whether its enough for this specific table.

For example how would i know how many rows are needed in this table for the db to start mourning about tablespace size etc.

a few other question

- Does the 20 byte allocated on JOB_NAME means that JOB_NAME can have upto a maximum of 20 characters? And what will that be in kilobytes terms?
- How many bytes is JOB_ID, START_DATE and END_TIME?

As far as i know, 1 kilobyte = 1024 bytes
Does this mean that the 64k is actually 65536 bytes and if for exampmle the table had only the three columns(job_name, started_by, job_status) each row would have covered 45 bytes and that the table will only be able to take 1456 rows? (i.e. 65536/45)

What are the other attributes for? (minextents, maxextents, pctincrease etc)

And finally. What is the difference, advantages and disadvantage of creating the table using the above format or the format shown below.

CREATE TABLE SCHEDULED_JOBS
(
  JOB_ID      NUMBER                            NOT NULL,
  JOB_NAME    VARCHAR2(20 BYTE),
  STARTED_BY  VARCHAR2(10 BYTE),
  START_TIME  DATE,
  END_TIME    DATE,
  JOB_STATUS  VARCHAR2(15 BYTE)
)TABLESPACE CLP
/


Does the above mean that Oracle will deal with managing the space required? Which is the prefered method?

Any help to help me understand this will be greatly appreciated.
Thanks
Re: A question about determining table size. [message #224995 is a reply to message #224972] Fri, 16 March 2007 08:00 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You are living in the past. Since 8i, in the 1990's, Locally Managed Tablespaces have been around which take the guesswork out of any storage related to extents (min, max, pctincrease). Please, please, save yourself the headache and spend your time more wisely than giving any time at all to extents and storage. Look up Locally Managed Tablespaces if you must.
Previous Topic: how to delete blob
Next Topic: execution and compilation of a procedure and function in shell scripting
Goto Forum:
  


Current Time: Sat Dec 03 01:23:36 CST 2016

Total time taken to generate the page: 0.15619 seconds