Here's the output spool file with our notes from creation of the tables. Your spool file should look similar: SQL>SET ECHO ON; SQL>SPOOL C:\SPOOL_FILES\WAIVER.LST SQL> SQL> ALTER TABLESPACE TABLESPACE_FOR_WAIVERS 2 OFFLINE NORMAL; ALTER TABLESPACE TABLESPACE_FOR_WAIVERS * ERROR at line 1: ORA-00959: tablespace 'TABLESPACE_FOR_WAIVERS' does not exist SQL> SQL> DROP TABLESPACE TABLESPACE_FOR_WAIVERS 2 INCLUDING CONTENTS; DROP TABLESPACE TABLESPACE_FOR_WAIVERS * ERROR at line 1: ORA-00959: tablespace 'TABLESPACE_FOR_WAIVERS' does not exist SQL> SQL> SQL> CREATE TABLESPACE TABLESPACE_FOR_WAIVERS 2 DATAFILE 'TABLESPACE_FOR_WAIVERS' SIZE 500M 3 ONLINE 4 PERMANENT; Tablespace created . SQL> SQL> SQL> /* */ SQL> DROP TABLE TEST_TYPE_DESC CASCADE CONSTRAINTS; DROP TABLE TEST_TYPE_DESC CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> CREATE TABLE TEST_TYPE_DESC ( 2 TYPE_ID NUMBER(6) PRIMARY KEY, 3 TYPE_DESC VARCHAR2(30) NOT NULL 4 ) 5 TABLESPACE TABLESPACE_FOR_WAIVERS 6 ; Table created .
Now continue with the rest of the tables. SQL> /* */ SQL> SQL> DROP TABLE TEST_ID CASCADE CONSTRAINTS; DROP TABLE TEST_ID CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> CREATE TABLE TEST_ID ( 2 TEST_ID NUMBER(6) PRIMARY KEY, 3 TEST_NAME VARCHAR2(40) NOT NULL, 4 DATE_CREATED DATE NOT NULL, 5 AUTHOR VARCHAR(30) DEFAULT 'WAIVER ADMINISTRATION', 6 COMMENTS VARCHAR2(30), 7 TYPE_ID NUMBER(6) REFERENCES TEST_TYPE_DESC(TYPE_ID), 8 PASSING_GRADE NUMBER(2) NOT NULL, 9 TIME_LIMIT NUMBER(4,2) NOT NULL 10 ) 11 TABLESPACE TABLESPACE_FOR_WAIVERS 12 ; Table created . SQL> SQL> /* */ SQL> DROP TABLE QUESTIONS_TYPE_DESC CASCADE CONSTRAINTS; DROP TABLE QUESTIONS_TYPE_DESC CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> CREATE TABLE QUESTIONS QUESTIONS_TYPE_DESC ( 2 QUESTION_TYPE NUMBER(6) PRIMARY KEY, 3 QUESTION_TYPE_DESC VARCHAR2(40) NOT NULL 4 ) 5 TABLESPACE TABLESPACE_FOR_WAIVERS 6 ; CREATE TABLE QUESTIONS QUESTIONS_TYPE_DESC ( * ERROR at line 1: ORA-00922: missing or invalid option Note Here's something you may come across if you use MS Word, Notepad, or another text editor. Every so often a command simply doesn't work, and you cannot find the problem. The preceding code is an example: Even though the command is perfectly constructed , it generates an error. In these cases, all you can do is retype the command or copy the script into another text editor. Strange but true. In this case, then, we try retyping the commands and it works! The original error was probably caused by simple, poor typing that resulted in an unprintable character or escape sequence being entered. [View full width] SQL> DROP TABLE QUESTIONS_TYPE_DESC CASCADE CONSTRAINTS; DROP TABLE QUESTIONS_TYPE_DESC CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> CREATE TABLE QUESTIONS_TYPE_DESC ( 2 QUESTION_TYPE NUMBER(6) PRIMARY KEY, 3 QUESTIONS_TYPE_DESC VARCHAR2(50) NOT NULL 4 ) 5 TABLESPACE TABLESPACE_FOR_WAIVERS 6 ; Table created . SQL> /* */ SQL> DROP TABLE QUESTIONS CASCADE CONSTRAINTS; DROP TABLE QUESTIONS CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> create table QUESTIONS ( 2 QUESTION_ID NUMBER(6) PRIMARY KEY, 3 QUESTION VARCHAR2(500) NOT NULL, 4 CORRECT_ANSWER VARCHAR2(2) NOT NULL, 5 QUESTION_TYPE NUMBER(6) REFERENCES QUESTIONS_TYPE_DESC(QUESTION_TYPE), 6 DATE_CREATED DATE, 7 AUTHOR_ID NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID) 8 ) 9 TABLESPACE TABLESPACE_FOR_WAIVERS 10 ; AUTHOR_ID NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID) * ERROR at line 7: ORA-00942: table or view does not exist SQL> /* Following is a retype of the CREATE TABLE statement because what's shown above keeps producing an error */ SQL> SQL> CREATE TABLE QUESTIONS ( 2 QUESTION_ID NUMBER(6) PRIMARY KEY, 3 QUESTION VARCHAR2(500) NOT NULL, 4 CORRECT_ANSWER VARCHAR2(2) NOT NULL, 5 QUESTION_TYPE NUMBER(6) REFERENCES QUESTIONS_TYPE_DESC(QUESTION_TYPE), 6 DATE_CREATED DATE, 7 AUTHOR_ID NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID) 8 ) 9 TABLESPACE TABLESPACE_FOR_WAIVERS 10 ; AUTHOR_ID NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID) * ERROR at line 7: ORA-00942: table or view does not exist SQL> SQL> SQL> /* */ SQL> DROP TABLE AUTHORS CASCADE CONSTRAINTS; DROP TABLE AUTHORS CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> CREATE TABLE AUTHORS ( 2 AUTHOR_ID NUMBER(9) PRIMARY KEY, 3 AUTHOR VARCHAR2(75) DEFAULT 'WAIVER ADMINISTRATION' 4 ) 5 TABLESPACE TABLESPACE_FOR_WAIVERS 6 ; Table created . SQL> SQL> /* */ SQL> DROP TABLE QUESTIONS CASCADE CONSTRAINTS; DROP TABLE QUESTIONS CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> create table QUESTIONS ( 2 QUESTION_ID NUMBER(6) PRIMARY KEY, 3 QUESTION VARCHAR2(500) NOT NULL, 4 CORRECT_ANSWER VARCHAR2(2) NOT NULL, 5 QUESTION_TYPE NUMBER(6) REFERENCES QUESTIONS_TYPE_DESC(QUESTION_TYPE), 6 DATE_CREATED DATE, 7 AUTHOR_ID NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID) 8 ) 9 TABLESPACE TABLESPACE_FOR_WAIVERS 10 ; Table created . SQL> SQL> SQL> /* */ SQL> DROP TABLE TEST_QUESTIONS_LINK CASCADE CONSTRAINTS; DROP TABLE TEST_QUESTIONS_LINK CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> CREATE table TEST_QUESTIONS_LINK ( 2 LINK_TEST_ID NUMBER(6) REFERENCES TEST_ID(TEST_ID), 3 LINK_QUESTION_ID NUMBER(6) REFERENCES QUESTIONS(QUESTION_ID), 4 PRIMARY KEY (LINK_TEST_ID, LINK_QUESTION_ID) 5 ) 6 TABLESPACE TABLESPACE_FOR_WAIVERS 7 ; Table created . SQL> SQL> SQL> /* */ SQL> DROP TABLE ANSWERS CASCADE CONSTRAINTS; DROP TABLE ANSWERS CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> CREATE TABLE ANSWERS ( 2 ANSWER_QUESTION_ID NUMBER(6) PRIMARY KEY REFERENCES QUESTIONS(QUESTION_ID), 3 ANSWER_ID VARCHAR2(2) NOT NULL, 4 ANSWER VARCHAR2(30) NOT NULL 5 ) 6 TABLESPACE TABLESPACE_FOR_WAIVERS 7 ; Table created . SQL> SQL> /* */ SQL> DROP TABLE STUDENTS CASCADE CONSTRAINTS; DROP TABLE STUDENTS CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> CREATE TABLE STUDENTS ( 2 STUDENT_ID NUMBER(9) PRIMARY KEY, 3 F_NAME VARCHAR2(15) NOT NULL, 4 M_I VARCHAR2(1), 5 L_NAME VARCHAR2(20) NOT NULL, 6 SSNUM NUMBER(9) NOT NULL UNIQUE, 7 B_DATE DATE, 8 SEX VARCHAR2(1) CHECK (SEX IN ('M', 'F', 'm', 'f')), 9 STREET1 VARCHAR2(15), 10 STREET2 VARCHAR2(15), 11 TOWN VARCHAR2(20), 12 STATE VARCHAR2(2), 13 COUNTRY VARCHAR2(15) DEFAULT 'UNITED STATES', 14 ZIP NUMBER(5) NOT NULL, 15 STUDENT_LEVEL NUMBER(6) NOT NULL, 16 DATE_CREATED DATE, 17 CREATED_BY VARCHAR2(20), 18 DEPARTMENT_MAJOR VARCHAR2(10) NOT NULL, 19 EMAIL VARCHAR2(60) 20 ) 21 TABLESPACE TABLESPACE_FOR_WAIVERS 22 ; Table created . SQL> SQL> SQL> /* */ SQL> DROP TABLE TEST_HISTORY CASCADE CONSTRAINTS; DROP TABLE TEST_HISTORY CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> CREATE TABLE TEST_HISTORY ( 2 TEST_ID NUMBER(6) REFERENCES TEST_ID(TEST_ID), 3 STUDENT_ID NUMBER(9) REFERENCES STUDENTS(STUDENT_ID), 4 SCORE NUMBER(3) NOT NULL, 5 DATE_TAKEN DATE NOT NULL, 6 START_TIME VARCHAR2(8) NOT NULL, 7 END_TIME VARCHAR2(8) NOT NULL, 8 LOCATION ARCHAR2(15) NOT NULL, 9 PRIMARY KEY (TEST_ID, STUDENT_ID) 10 ) 11 TABLESPACE TABLESPACE_FOR_WAIVERS 12 ; Table created . SQL> SQL> /* */ SQL> DROP TABLE STUDENT_ANSWER_HISTORY CASCADE CONSTRAINTS; DROP TABLE STUDENT_ANSWER_HISTORY CASCADE CONSTRAINTS * ERROR at line 1: ORA-00942: table or view does not exist SQL> SQL> CREATE TABLE STUDENT_ANSWER_HISTORY ( 2 STUDENT_ID NUMBER(9) REFERENCES STUDENTS(STUDENT_ID), 3 TEST_ID NUMBER(6) REFERENCES TEST_ID(TEST_ID), 4 QUESTION_ID NUMBER(6) REFERENCES QUESTIONS(QUESTION_ID), 5 STUDENT_ANSWER VARCHAR2(2) NOT NULL 6 ) 7 TABLESPACE TABLESPACE_FOR_WAIVERS 8 ; Table created . SQL> SQL> SPOOL OFF;
|