Saturday, December 5, 2009

Exercises with Oracle Create Table Add Columns


Step 1: Creating a basic table




First, we need a couple of tables. Create two tables, one named EMP and the other named DEPT, based on the following specifications. Add the necessary definition to your create statements to place the two tables in the USERS01 tablespace.
Be sure that you explicitly name your constraints. I would suggest that you use a format of table_column_type, for example SDEPT_DEPTNO_PK.
Table Name
Column Name
Data Type
Constraint
S_DEPT
DEPTNO
NUMBER(2)
PK
DNAME
VARCHAR2(14)


LOC
CHAR(13)








S_EMP
EMPNO
NUMBER(4)
PK
ENAME
VARCHAR2(10)


JOB
VARCHAR2(9)


MGR
NUMBER(4)
FK References EMPNO
HIREDATE
DATE


SAL
NUMBER(7,2)


COMM
NUMBER(7,2)


DEPTNO
NUMBER(2)
FK References S_DEPT
Your response will look similar to this:
Table created.
Table created.
Now query the DBA_TABLES view to verify that your tables are in the correct place.
Hint: You will need a where clause that limits the select to just your tables; otherwise, you will get some very interesting results.
Your response will look similar to this:
TABLE_NAME               TABLESPACE_NAME
------------------------------ ---------------------
S_DEPT                                 USERS01
S_EMP                                   USERS01
Step 2: Determining table storage


Using the two tables you just created, query the DBA_TABLES view to determine the current storage settings. You want to look at what the settings are for the initial extent, next extent, max extents, and percent increase. Notice that the storage settings for the two tables match those for the tablespace. When tables are created without their own storage settings, they will inherit the settings of the tablespace they reside in.
Your response will look similar to this:
TABLE_NAME              INITIAL_EXTENT NEXT_EXTENT  MAX_EXTENTS  PCT_INCREASE
------------------------------ ---------------  -------------- --------------- --------------
S_EMP                                  16384               16384             2147483645      0
S_DEPT                                 16384               16384             2147483645      0
Since the two tables were created without specifying initial, next, and max extents, the tables inherited their storage from the tablespace they were assigned to. To validate this, query the same information from the DBA_TABLESPACES dictionary view and compare.
Your response will look similar to this:
TABLESPACE_NAME                 INITIAL_EXTENT NEXT_EXTENT  MAX_EXTENTS PCT_INCREASE
------------------------------ ---------------  -------------- -------------- --------------
USERS01                               16384               16384             2147483645    0
Step 3: Specifying storage at creation


You now need a third table, but this table is going to require a slightly different storage definition that that of the tablespace you are going to put it into. Create your third table based on the specifications below and place it in the USER01 tablespace, but set the initial extent to 512K, the next to 512, and a maximum of 100 extents. Keep the percent of increase at 0.
Table Name
Column Name
Data Type
Constraint
S_ITEM
ITEMNO
NUMBER(2)
PK
DESCRIPTION
VARCHAR2(20)


PRICE
NUMBER(7,2)
Your response will look similar to this:
Table created.
Now query the DBA_TABLES view again to verify that the new table has the correct settings.
Your response will look similar to this:
TABLE_NAME                          INITIAL_EXTENT  NEXT_EXTENT  MAX_EXTENTS PCT_INCREASE
------------------------------ ----------------  -------------- -------------- --------------
S_EMP                                  16384                16384              2147483645    0
S_DEPT                                 16384                16384              2147483645    0
S_ITEM                                 524288               524288            100               0
Step 4: Temporary tables


Sometimes it is necessary to create a WORK table based on the definition of an existing table. This is not the same thing as creating an actual TEMPORARY TABLE; rather, it is a permanent table based on the definition of an existing table. Obviously, you could use the CREATE TABLE script from the existing table, but this is not always available. To help overcome this problem, you can create a new table using a SELECT statement on the existing table as the subquery to the CREATE TABLE statement. If the existing table has data in it, you can either allow the data to transfer or you can exempt the data by adding a where clause like WHERE 1 = 2. The table gets created, only without the data.
For this part of the lab, you are going to create a new table based on the definition of the S_DEPT table. Before you do this, though, we want to put some data in the table. Download the LOAD_DEPT.SQL file from Doc Sharing and run the script to load your S_DEPT table with four records.
Now, create a new table named G_DEPT based on the S_DEPT table. Make sure that it will preserve the data on commit, and make sure that the data in S_DEPT gets transferred to the new table. After the table is created, query the new G_DEPT table to verify that the data is there.
Your response will look similar to this:
Table created.
Your response will look similar to this:
DEPTNO     DNAME           LOC
---------- -------------- -------------
10            ACCOUNTING   NEW YORK
20            RESEARCH       DALLAS
30            SALES            CHICAGO
40            OPERATIONS   BOSTON
Remember that this is only a work table, good for a single session. To compare this new type of table to the three that you have already created, query the DBA_TABLES view as you did in step three, to list all four tables now. Notice the difference in the G_DEPT table.
Your response will look similar to this:
TABLE_NAME               INITIAL_EXTENT NEXT_EXTENT  MAX_EXTENTS PCT_INCREASE
------------------------------ --------------- -------------- -------------- ---------------
S_EMP                                  16384              16384             2147483645     0
S_DEPT                                 16384              16384             2147483645     0
G_DEPT
S_ITEM                                 524288             16384             2147483645     0
Step 5: Adding a column to an existing table


The S_DEPT table needs to be able to accommodate a picture of each location.  The pictures are .gif format and will require the correct type of column data type to store the photo.  Add a column to the S_DEPT table named LOCATION_PIC that will satisfy this requirement.
Your response will look similar to this:
Table altered.
Step 6: Renaming an existing column and changing data type


The column name LOC is not very descriptive of the column contents and could cause some confusion.  It has been decided to use the full description LOCATION for the column name.  Write and execute the statement that will alter the DEPT table and rename the column LOC to LOCATION.
Your response will look similar to this:
Table altered.
Additionally, some of the table’s locations are turning out to be longer names than the column will allow, and it was also decided that the data type needed to be a VARCHAR2 in place of a CHAR data type.  Write and execute the required statement to change the column data type to a VARCHAR2 with a length of 30.
Your response will look similar to this:
Table altered.
Step 7: Adding an index to a column


The department name column contains the names of the departments per location.  Most all locations have the same departments, with very few exceptions.  This column seems well suited for a Bit Map index.  Write and execute the statement that would create a new Bit map index for the LOCATION column. Name the index LOC_BM_IDX.
Your response will look similar to this:
Index created.
Looks like the table is much more functional now, but you need to make sure that all the changes are intact.  Verify the changes made to the S_DEPT table by first doing issuing the describe command on the table to look at the column names and data types.  Next, write and execute a query using the data dictionary that will list all of the indexes that are in the S_DEPT table.  This should also verify that your new index is in place.
Your response will look similar to this:
Name                                            Null?         Type
 ----------------------------------- ---------- ------------
 DEPTNO                                        NOT NULL  NUMBER(2)
DNAME                                                         VARCHAR2(14)
 LOCATION                                                     VARCHAR2(30)
 LOCATION_PIC                                               BLOB
Your response will look similar to this:  
INDEX_NAME                         TABLE_NAME                    COLUMN_NAME
 ------------------------------ ------------------------------ -----------------
 S_DEPT_PRIMARY_KEY            S_DEPT                             DEPTNO
 LOC_BM_IDX                         S_DEPT                             LOCATION
Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)