Forum Stats

  • 3,768,293 Users
  • 2,252,772 Discussions
  • 7,874,519 Comments

Discussions

How do I calculate my Storage Clause values?

Hey, can someone please tell me how do i calculate the values for my storage the one i have here doesn't work and throws a error. Thank you very much.

Create table MOVIE

(

MOVIE_ID number(10) PRIMARY KEY NOT NULL,

MOVIE_NAME Varchar2(55),

MOVIE_RELEASE_DATE date,

MOVIE_RATING Varchar2(5) DEFAULT 'NR',

CHECK (MOVIE_RATING = 'G'OR MOVIE_RATING = 'PG' OR MOVIE_RATING = 'PG-13' OR MOVIE_RATING = 'R' OR MOVIE_RATING = 'NC'),

  TOTAL_SALES number(10),

  STORAGE (INITIAL 1G NEXT 50K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 50 )

);


CREATE TABLE Producer(

  Pro_ID number(10) PRIMARY KEY NOT NULL,

  MOVIE_ID number(10) NOT NULL UNIQUE,

  Pro_fname Varchar2(30),

  Pro_lname Varchar2(30),

  FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE(MOVIE_ID),

  STORAGE (INITIAL 1G NEXT 50K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 50 )

);


CREATE TABLE Theater(

  Tht_ID number(10) PRIMARY KEY NOT NULL,

  MOVIE_ID number(10) NOT NULL,

  Tht_name Varchar2(55),

  Tht_address Varchar2(350),

  Tickets_sold NUMBER(19),

  CHECK (Tickets_sold >=0),

  FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE(MOVIE_ID),

  STORAGE (INITIAL 1G NEXT 50K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 50 )

);


CREATE TABLE Ticket_Price(

  Ticket_ID number(10) PRIMARY KEY NOT NULL,

  MOVIE_ID number(10) NOT NULL,

  Tht_ID number(10) NOT NULL UNIQUE,

  Ticket_Price number(10) ,

  CHECK (Ticket_Price >=0),

  FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE(MOVIE_ID),

  FOREIGN KEY (Tht_ID) REFERENCES Theater(Tht_ID),

  STORAGE (INITIAL 1G NEXT 50K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 50 )

);



CREATE TABLE Main_Actor(

  Actor_ID number(10) PRIMARY KEY NOT NULL,

  MOVIE_ID number(10) NOT NULL,

  First_Name Varchar2(20),

  Last_Name Varchar2(20),

  Payment_amount number(10),

  CHECK (Payment_amount >0),

  FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE(MOVIE_ID),

  STORAGE (INITIAL 1G NEXT 50K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 50 )

);


CREATE TABLE Supporting_Actor(

  sActor_ID number(10) PRIMARY KEY NOT NULL,

  MOVIE_ID number(10) NOT NULL,

  First_Name Varchar2(20),

  Last_Name Varchar2(20),

  Payment_amount number(10),

  CHECK (Payment_amount >0),

  FOREIGN KEY (MOVIE_ID) REFERENCES MOVIE(MOVIE_ID),

  STORAGE (INITIAL 1G NEXT 50K MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 50 )

);

Tagged:

Answers

  • EdStevens
    EdStevens Member Posts: 28,519 Gold Crown
    edited Aug 5, 2021 2:33PM

    Welcome to the forum.

    Please note that your posted code will be MUCH easier to read (and so, more likely to get a good response) if you will format it. Please read https://community.oracle.com/tech/apps-infra/kb/articles/12-format-text

    Now, to your question. "doesn't work and throws a error." is not found in any error reference I have access to. Oracle emits actual error codes for a reason. Further, you posted DDL for six different tables. Not only do we not know the error, we don't even know which table.

    I will say that your storage spec (since that is what you seem to think is the source of the unknown error) make no sense at all. Your initial extent is 1 gig, but then additional extents at only 50k? And you allow only four of these 50k extents? so you are only allowing, what, 0.0191% growth? Does that make any sense? And you are setting pct_increase at 50? This means each data block will only be filled to 50% of its capacity, to allow for growth of individual rows during updates. Do you really expect your varchar2 columns to grow that much due to update operations?