1 / 12

Continuation of table maintenance revisited (again)...

Continuation of table maintenance revisited (again). Please check speaker notes for additional information!. User_objects table. SQL> DESC user_objects; Name Null? Type ------------------------------- -------- ----

makya
Download Presentation

Continuation of table maintenance revisited (again)...

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Continuation of table maintenance revisited (again)... Please check speaker notes for additional information!

  2. User_objects table SQL> DESC user_objects; Name Null? Type ------------------------------- -------- ---- OBJECT_NAME VARCHAR2(128) OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(13) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(75) STATUS VARCHAR2(7) SQL> SELECT object_name, object_type FROM user_objects; OBJECT_NAME ----------------------------------------------------------- OBJECT_TYPE ------------- ADDDONATION PROCEDURE ADDMAIN PROCEDURE BONUS TABLE BOSS TABLE CHANGE_NOTES TABLE CHAR_TABLE TABLE As can be seen, the beginning of the lists shows both tables and procedures that exist in the database. The list went on for multiple hundred entries.

  3. User_catalog SQL> DESC user_catalog; Name Null? Type ------------------------------- -------- ---- TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) SQL> SELECT * FROM user_catalog; TABLE_NAME TABLE_TYPE ------------------------------ ----------- BONUS TABLE BOSS TABLE CHANGE_NOTES TABLE CHAR_TABLE TABLE CONT_INFO TABLE COURSE00 TABLE DATE1 TABLE DEPARTMENT TABLE DEPARTMENTX TABLE DEPT TABLE DEPT2X TABLE DEPTARTMENT TABLE DEPT_NO SEQUENCE DISORD1 VIEW DISORD2 VIEW DISORD3 VIEW DISORD4 VIEW DONATION TABLE DONATION_DRIVE VIEW DONATION_VIEW VIEW DONEXPAND VIEW

  4. User_tables SQL> DESC user_tables; Name Null? Type ------------------------------- -------- ---- TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME NOT NULL VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) PCT_FREE NUMBER PCT_USED NOT NULL NUMBER INI_TRANS NOT NULL NUMBER MAX_TRANS NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5) TABLE_LOCK VARCHAR2(8)

  5. Create Data Definition Language (DDL) statements are used to create, change or delete database structures. DDL statements are automatically committed when they are executed. That means that you cannot do a rollback to negate the DDL statements. SQL> CREATE TABLE my_new_test 2 (idno NUMBER(3), 3 name VARCHAR2(20), 4 dept CHAR(3) DEFAULT 'CI', 5 tuition NUMBER(5) DEFAULT 2000, 6 dateapply DATE DEFAULT sysdate); Table created. SQL> Rollback; Rollback complete. SQL> DESC my_new_test; Name Null? Type ------------------------------- -------- ---- IDNO NUMBER(3) NAME VARCHAR2(20) DEPT CHAR(3) TUITION NUMBER(5) DATEAPPLY DATE Defaults will be discussed on the next slide. In this example, I created a table called my_new_test. Then I did a rollback. Then I described my_new_test to prove that it was still there. Clearly the rollback did not change the fact that the table was created. As stated above, DDL statements are automatically committed upon execution.

  6. Defaults with create Note that idno was defined as NUMBER(3). I entered it with quotes around it. It was accepted and stored as a number SQL> CREATE TABLE my_new_test 2 (idno NUMBER(3), 3 name VARCHAR2(20), 4 dept CHAR(3) DEFAULT 'CI', 5 tuition NUMBER(5) DEFAULT 2000, 6 dateapply DATE DEFAULT sysdate); Table created. SQL> DESC my_new_test; Name Null? Type ------------------------------- -------- ---- IDNO NUMBER(3) NAME VARCHAR2(20) DEPT CHAR(3) TUITION NUMBER(5) DATEAPPLY DATE SQL> INSERT INTO my_new_test (idno, name) 2 VALUES ('123','Smith,Susan'); 1 row created. SQL> SELECT * FROM my_new_test; IDNO NAME DEP TUITION DATEAPPLY --------- -------------------- --- --------- --------- 123 Smith, Susan CI200019-JUN-00 Note that only idno and name are entered. The other information in the row/record is the result of the defaults.

  7. SQL> CREATE TABLE my_new_test 2 (idno NUMBER(3), 3 name VARCHAR2(20), 4 dept CHAR(3) DEFAULT 'CI', 5 tuition NUMBER(5) DEFAULT 2000, 6 dateapply DATE DEFAULT sysdate); Insert Idno was entered as a numeric in this example. SQL> INSERT INTO my_new_test (idno, name, tuition, dateapply) 2 VALUES(234,'Riley, James',1500, LAST_DAY(sysdate)); 1 row created. SQL> SELECT * FROM my_new_test; IDNO NAME DEP TUITION DATEAPPLY --------- -------------------- --- --------- --------- 123 Smith, Susan CI 2000 19-JUN-00 234 Riley, James CI150030-JUN-00 In this example, I am overriding the tuition and dateapply defaults with different data. The dept default is still valid since no data was entered for dept.

  8. Create new table from existing SQL> SELECT * FROM maintain00; IDN ITEMNAME PRICE PURCHASED DE COST --- ------------ --------- --------- -- --------- 123 Teddy Bear 20 TY 20 234 Dump Truck 15.95 TY 14.36 345 Baby Doll 12 19-JUN-00 456 Blocks 10 12-JUN-00 TY 8 478 Football 14.98 10-JUN-00 TY 11 488 Jump Rope 5.99 12-JUN-00 TY 5 567 Tea Set 9 09-JUN-00 TY 7.5 7 rows selected. SQL> CREATE TABLE newmain00 2 AS 3 SELECT idno, itemname, price + 1 updtprice, dept 4 FROM maintain00 5 WHERE cost > 10; Table created. SQL> SELECT * FROM newmain00; IDN ITEMNAME UPDTPRICE DE --- ------------ --------- -- 123 Teddy Bear 21 TY 234 Dump Truck 16.95 TY 478 Football 15.98 TY A new table is created with elements from the original and changes to create a new updtprice column. SQL> DESC newmain00; Name Null? Type ------------------------------- -------- ---- IDNO VARCHAR2(3) ITEMNAME VARCHAR2(12) UPDTPRICE NUMBER DEPT CHAR(2)

  9. SQL> DROP TABLE to_delete; Table dropped. SQL> SELECT * FROM to_delete; SELECT * FROM to_delete * ERROR at line 1: ORA-00942: table or view does not exist Drop table Here, I created a table to delete. SQL> CREATE TABLE to_delete 2 AS 3 SELECT * FROM maintain00; Table created. SQL> DESC to_delete; Name Null? Type ------------------------------- -------- ---- IDNO VARCHAR2(3) ITEMNAME VARCHAR2(12) PRICE NUMBER(6,2) PURCHASED DATE DEPT CHAR(2) COST NUMBER(6,2) SQL> SELECT * FROM to_delete; IDN ITEMNAME PRICE PURCHASED DE COST --- ------------ --------- --------- -- --------- 123 Teddy Bear 20 TY 20 234 Dump Truck 15.95 TY 14.36 345 Baby Doll 12 19-JUN-00 456 Blocks 10 12-JUN-00 TY 8 478 Football 14.98 10-JUN-00 TY 11 488 Jump Rope 5.99 12-JUN-00 TY 5 567 Tea Set 9 09-JUN-00 TY 7.5 7 rows selected. DROP TABLE followed by the table name removes a table from the database. It removes the definition of the table and all data in the table. It is not reversible since it is a DDL statement and DDL statements are committed upon execution.

  10. SQL> CREATE TABLE to_rename 2 AS 3 SELECT * FROM maintain00; Table created. SQL> DESC to_rename; Name Null? Type ------------------------------- -------- ---- IDNO VARCHAR2(3) ITEMNAME VARCHAR2(12) PRICE NUMBER(6,2) PURCHASED DATE DEPT CHAR(2) COST NUMBER(6,2) SQL> RENAME to_rename TO new_name; Table renamed. SQL> DESC new_name; Name Null? Type ------------------------------- -------- ---- IDNO VARCHAR2(3) ITEMNAME VARCHAR2(12) PRICE NUMBER(6,2) PURCHASED DATE DEPT CHAR(2) COST NUMBER(6,2) SQL> DESC to_rename; Object does not exist. Rename RENAME changes the name of the table. Note that I cannot access the old name after the rename has been executed. This is because the command is a DDL and therefore it commits upon execution.

  11. Drop SQL> DROP TABLE new_name; Table dropped. SQL> DESC new_name; Object does not exist. SQL> SELECT * 2 FROM new_name; FROM new_name * ERROR at line 2: ORA-00942: table or view does not exist I have now dropped the table that I renamed. Note that is is truly gone since I cannot describe it or select from it.

  12. SQL> CREATE TABLE to_truncate 2 AS 3 SELECT * FROM maintain00; Table created. SQL> SELECT * FROM to_truncate; IDN ITEMNAME PRICE PURCHASED DE COST --- ------------ --------- --------- -- --------- 123 Teddy Bear 20 TY 20 234 Dump Truck 15.95 TY 14.36 345 Baby Doll 12 19-JUN-00 456 Blocks 10 12-JUN-00 TY 8 478 Football 14.98 10-JUN-00 TY 11 488 Jump Rope 5.99 12-JUN-00 TY 5 567 Tea Set 9 09-JUN-00 TY 7.5 7 rows selected. SQL> TRUNCATE TABLE to_truncate; Table truncated. SQL> SELECT * FROM to_truncate; no rows selected SQL> DESC to_truncate; Name Null? Type ------------------------------- -------- ---- IDNO VARCHAR2(3) ITEMNAME VARCHAR2(12) PRICE NUMBER(6,2) PURCHASED DATE DEPT CHAR(2) COST NUMBER(6,2) Truncate I create a table to truncate from maintain00. After truncating the table, no data remains. All of the rows in the table are removed and the storage space is released. However the structure of the table is still there. It is a DDL statement so it is committed upon execution.

More Related