OCP - Oracle9i Database Administrator Fundamentals 1 Exam Prep - Module 11: Managing Tables

Practice: Storing data in regular and temporary tables.

 

This practice covers the creation of regular and temporary tables.

 Instructions

1.      Create the following tables as user SYSTEM for an order entry system that you are implementing now. The tables and the columns are shown below.

 

DATE

DATE_OF_DELY

 

VARCHAR2(3)

CUST_CODE

 

DATE

ORD_DATE

 

NUMBER(3)

ORD_ID

ORDERS

VARCHAR2(5)

REGION

 

VARCHAR2(50)

NAME

 

VARCHAR2(3)

CUST_CODE

CUSTOMERS

Data Type and Size

Column

Table

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL> CONNECT system/manager

Connected.

SQL> CREATE TABLE customers

  2     ( cust_code VARCHAR2(3),

  3       name VARCHAR2(50),

  4       region VARCHAR2(5) )

  5  TABLESPACE users;

Table created.

SQL> CREATE TABLE orders

  2     ( ord_id NUMBER(3),

  2       ord_date DATE,

  3       cust_code VARCHAR2(3),

  4       date_of_dely DATE )

  5  TABLESPACE users;

Table created.

 

 

 

2.      Find which files and blocks contain the rows for the orders table.

 

Hint

 

Query data dictionary view DBA_EXTENTS.

 

 

SQL> SELECT file_id, block_id, blocks

  2  FROM   dba_extents

  3  WHERE  owner = 'SYSTEM'

  4  AND    segment_name = 'ORDERS'

  5  AND    segment_type = 'TABLE';

 

   FILE_ID   BLOCK_ID     BLOCKS

---------- ---------- ----------

         3         25          8

 

 

 

3.      Check the number of extents used by the table ORDERS.

 

 

SQL> SELECT count(*)

  2  FROM   dba_extents

  3  WHERE  segment_name='ORDERS'

  4  AND    owner='SYSTEM';

 

  COUNT(*)

----------

         1

 

 

 

Practice: Reorganize, truncate or drop a table and dropping a column within a table

 

This practice covers reorganizing, truncating and dropping tables and dropping a column within a table.

 

Instructions

 

1.      Move the ORDERS table from its current location in the USERS tablespace to the DATA01 tablespace.  Validate that the move was successful by querying  USERS_TABLE.

 

 

SQL> select table_name, tablespace_name

  2   from user_tables where table_name = 'ORDERS';

 

TABLE_NAME      TABLESPACE_NAME

--------------- ------------------------------

ORDERS          USERS

 

SQL> ALTER TABLE orders move tablespace data01;

 

Table altered.

 

SQL> select table_name, tablespace_name

  2  from user_tables where table_name = 'ORDERS';

 

TABLE_NAME      TABLESPACE_NAME

--------------- ------------------------------

 ORDERS          DATA01

 

 

  

2.      View the columns for the ORDERS table.  Then mark the DATE_OF_DELY column as UNUSED.  View the columns for the ORDERS2 table again.  What happens?

 

 

SQL> DESCRIBE orders;

 Name                       Null?    Type

 -------------------------- -------- ------------------

 ORD_ID                              NUMBER(3)

 ORD_DATE                            DATE

 CUST_CODE                           VARCHAR2(3)

 DATE_OF_DELY                        DATE

SQL> ALTER TABLE orders

  2  SET UNUSED COLUMN date_of_dely

  3  CASCADE CONSTRAINTS;

Table altered.

SQL> DESCRIBE orders;

 Name                       Null?    Type

 -------------------------- -------- ------------------

 ORD_ID                              NUMBER(3)

 ORD_DATE                            DATE

 CUST_CODE                           VARCHAR2(3)

 

 

3.      Determine the number of unused columns by querying the DBA_UNUSED_COL_TABS table.

 

 

SQL> select * from DBA_UNUSED_COL_TABS;

 

OWNER                          TABLE_NAME           COUNT

------------------------------ --------------- ----------

SCOTT                          ORDERS                   1

 

 

 

4.      Alter the table ORDERS to add a column called SHIP_DATE which is of type date and validate that the column got added to the table.

 

 

SQL> alter table orders add(ship_date date);

 

Table altered.

 

SQL> desc orders

 Name                            Null?    Type

 ------------------------------- -------- ---------------

 ORD_ID                                   NUMBER(3)

 ORD_DATE                                 DATE

 CUST_CODE                                VARCHAR2(3)

 SHIP_DATE                                DATE

 

 

 

5.      You have decided that adding the SHIP_DATE column to the orders table was not a good idea so use the drop column command to drop the SHIP_DATE column.  Validate that the column got dropped and see if the entry in the DBA_UNUSED_COL_TABS table for the unused column was removed.

 

 

SQL> alter table ORDERS drop column ship_date;

 

Table altered.

 

SQL> desc orders

 Name                           Null?    Type

 ------------------------------ -------- ----------------

 ORD_ID                                  NUMBER(3)

 ORD_DATE                                DATE

 CUST_CODE                               VARCHAR2(3)

 

SQL> select * from DBA_UNUSED_COL_TABS;

 

 no rows selected

 

 

 

 

1