180409pm spool
HR@orcl>desc user_constraints
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
HR@orcl>desc user_cons_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
POSITION NUMBER
HR@orcl>select column_name, constraint_type, search_condition
2 from user_constraints join user_cons_columns
3 on
4 /
on
*
ERROR at line 3:
ORA-00936: missing expression
HR@orcl>ed
Wrote file afiedt.buf
1 select column_name, constraint_type, search_condition
2 from user_constraints u join user_cons_columns c
3 on u.constraint_name = c.constraint_name
4* where u.table_name = upper('&tname')
HR@orcl>/
Enter value for tname: member
old 4: where u.table_name = upper('&tname')
new 4: where u.table_name = upper('member')
COLUMN_NAME
--------------------------------------------------------------------------------
C
-
SEARCH_CONDITION
--------------------------------------------------------------------------------
LAST_NAME
C
"LAST_NAME" IS NOT NULL
JOIN_DATE
C
"JOIN_DATE" IS NOT NULL
COLUMN_NAME
--------------------------------------------------------------------------------
C
-
SEARCH_CONDITION
--------------------------------------------------------------------------------
MEMBER_ID
P
HR@orcl>col column_name for a25
HR@orcl>col search_condition for a40
HR@orcl>l
1 select column_name, constraint_type, search_condition
2 from user_constraints u join user_cons_columns c
3 on u.constraint_name = c.constraint_name
4* where u.table_name = upper('&tname')
HR@orcl>/
Enter value for tname: member
old 4: where u.table_name = upper('&tname')
new 4: where u.table_name = upper('member')
COLUMN_NAME C SEARCH_CONDITION
------------------------- - ----------------------------------------
LAST_NAME C "LAST_NAME" IS NOT NULL
JOIN_DATE C "JOIN_DATE" IS NOT NULL
MEMBER_ID P
HR@orcl>/
Enter value for tname: title
old 4: where u.table_name = upper('&tname')
new 4: where u.table_name = upper('title')
COLUMN_NAME C SEARCH_CONDITION
------------------------- - ----------------------------------------
TITLE C "TITLE" IS NOT NULL
DESCRIPTION C "DESCRIPTION" IS NOT NULL
RATING C rating in ('G', 'PG','R','NC17','NR')
CATEGORY C category in ('DRAMA','COMEDY','ACTION','
CHILD','SCIFI','DOCUMENTARY')
TITLE_ID P
HR@orcl>/
Enter value for tname: title_copy
old 4: where u.table_name = upper('&tname')
new 4: where u.table_name = upper('title_copy')
COLUMN_NAME C SEARCH_CONDITION
------------------------- - ----------------------------------------
STATUS C "STATUS" IS NOT NULL
STATUS C status in('AVAILABLE','DESTROYED','RENTE
D','RESERVED')
COPY_ID P
TITLE_ID P
TITLE_ID R
HR@orcl>/
Enter value for tname: rental
old 4: where u.table_name = upper('&tname')
new 4: where u.table_name = upper('rental')
COLUMN_NAME C SEARCH_CONDITION
------------------------- - ----------------------------------------
BOOK_DATE P
MEMBER_ID P
TITLE_ID P
MEMBER_ID R
COPY_ID R
TITLE_ID R
6 rows selected.
HR@orcl>/
Enter value for tname: reservation
old 4: where u.table_name = upper('&tname')
new 4: where u.table_name = upper('reservation')
COLUMN_NAME C SEARCH_CONDITION
------------------------- - ----------------------------------------
RES_DATE P
MEMBER_ID P
TITLE_ID P
MEMBER_ID R
TITLE_ID R
HR@orcl>get reservation
1 create table reservation
2 (res_date date, member_id number(10) references member(member_id),
3* title_id number(10) references title, primary key(res_date, member_id, title_id))
HR@orcl>ed
Wrote file afiedt.buf
1 create table reservation
2 (res_date date, member_id number(10) references member(member_id),
3* title_id number(10) references title, primary key(res_date, member_id, title_id))
HR@orcl>l
1 create table reservation
2 (res_date date, member_id number(10) references member(member_id),
3* title_id number(10) references title, primary key(res_date, member_id, title_id))
HR@orcl> select column_name, constraint_type, search_condition
2 2 from user_constraints u join user_cons_columns c
3 3 on u.constraint_name = c.constraint_name
4 4* where u.table_name = upper('&tname')
5
HR@orcl>ed
Wrote file afiedt.buf
1 select column_name, constraint_type, search_condition
2 from user_constraints u join user_cons_columns c
3 on u.constraint_name = c.constraint_name
4* where u.table_name = upper('&tname')
HR@orcl>/
Enter value for tname: reservation
old 4: where u.table_name = upper('&tname')
new 4: where u.table_name = upper('reservation')
COLUMN_NAME C SEARCH_CONDITION
------------------------- - ----------------------------------------
RES_DATE P
MEMBER_ID P
TITLE_ID P
MEMBER_ID R
TITLE_ID R
HR@orcl>ed
Wrote file afiedt.buf
1 select column_name, constraint_type, c.constraint_name
2 from user_constraints u join user_cons_columns c
3 on u.constraint_name = c.constraint_name
4* where u.table_name = upper('&tname')
HR@orcl>/
Enter value for tname: reservation
old 4: where u.table_name = upper('&tname')
new 4: where u.table_name = upper('reservation')
COLUMN_NAME C CONSTRAINT_NAME
------------------------- - ------------------------------
RES_DATE P SYS_C007000
MEMBER_ID P SYS_C007000
TITLE_ID P SYS_C007000
MEMBER_ID R SYS_C007001
TITLE_ID R SYS_C007002
HR@orcl>alter table reservation drop constraint SYS_C007001
2 ;
Table altered.
HR@orcl>alter table reservation add column status varchar2;
alter table reservation add column status varchar2
*
ERROR at line 1:
ORA-00904: : invalid identifier
HR@orcl>alter table reservation set add column status varchar2;
alter table reservation set add column status varchar2
*
ERROR at line 1:
ORA-02000: missing UNUSED keyword
HR@orcl>alter table reservation add status varchar2(1);
Table altered.
HR@orcl>alter table member drop column member_id;
alter table member drop column member_id
*
ERROR at line 1:
ORA-12992: cannot drop parent key column
HR@orcl>alter table member drop column member_id cascade constraints;
Table altered.
HR@orcl>alter table title rename column title_id to title_no';
alter table title rename column title_id to title_no'
*
ERROR at line 1:
ORA-01756: quoted string not properly terminated
HR@orcl>ed
Wrote file afiedt.buf
1* alter table title rename column title_id to title_no
HR@orcl>/
Table altered.
HR@orcl>alter table reservation rename reservation to res
2 ;
alter table reservation rename reservation to res
*
ERROR at line 1:
ORA-14155: missing PARTITION or SUBPARTITION keyword
HR@orcl>alter table rename reservation to res;
alter table rename reservation to res
*
ERROR at line 1:
ORA-00903: invalid table name
HR@orcl>rename reservation to res;
Table renamed.
HR@orcl>alter table res column add constraints not null;
alter table res column add constraints not null
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
HR@orcl>alter table res modify add constraints not null;
alter table res modify add constraints not null
*
ERROR at line 1:
ORA-00904: : invalid identifier
HR@orcl>alter table res modify add constraints is not null;
alter table res modify add constraints is not null
*
ERROR at line 1:
ORA-00904: : invalid identifier
HR@orcl>alter table res modify status not null;
Table altered.
HR@orcl>spool off