Scribd Logo
Upload

Welcome to Scribd!

  • 6 views

    Week 15

    Uploaded by

    MahNoor Awan

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    Week 15

    Uploaded by

    MahNoor Awan
    6 views2 pages

    Original Title

    Week15

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    6 views2 pages

    Week 15

    Uploaded by

    MahNoor Awan

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 2

    1Create the following tables as user SYSTEM for an order entry system that you are

    implementing now.
    SQL> CONNECT system/manager
    SQL> CREATE TABLE customers ( cust_code VARCHAR2(3),
    name VARCHAR2(50),
    region VARCHAR2(5) )
    TABLESPACE users;
    -----------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------
    -------------------------------------

    SQL> CREATE TABLE orders2 ( ord_id NUMBER(3),


    ord_date DATE,
    cust_code VARCHAR2(3),
    date_of_dely DATE )
    TABLESPACE users;
    -----------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------
    -------------------------------------
    2 Run the script lab11_02.sql to insert rows into the tables.

    2. Insert some records in OrderS2 table.


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

    3 Find which files and blocks contain the rows for the orders table.
    Hint: Query the data dictionary view DBA_EXTENTS.

    SQL> CONNECT system/manager


    SQL> SELECT file_id, block_id, blocks
    FROM dba_extents
    WHERE owner = 'SYSTEM'
    AND segment_name = 'ORDERS2'
    AND segment_type = 'TABLE';
    -----------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------
    -------------------------------------
    4 Check the number of extents used by the table ORDERS2.
    SQL> SELECT count(*) FROM dba_extents WHERE segment_name='ORDERS2' AND
    owner='SYSTEM';
    -----------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------
    -------------------------------------
    5
    Create another table, ORDERS2 as copy of the ORDERS table in the USERS
    tablespace,
    with MINEXTENTS equal to 10. Verify that the table has been created
    with the specified number of extents.

    SQL> CREATE TABLE orders


    TABLESPACE users
    STORAGE(MINEXTENTS 10)
    AS
    SELECT * FROM orders2;
    -----------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------
    -------------------------------------

    6 Allocate an extent manually, with default size, for the table ORDERS and
    confirm
    that the extent has been added as specified.

    SQL> SELECT count(*) FROM dba_extents WHERE segment_name='ORDERS' AND


    owner='SYSTEM';
    -----------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------
    -------------------------------------
    7 Truncate table ORDERS without releasing space and check the number of extents to
    verify extents have not been deallocated.
    SQL> TRUNCATE TABLE orders REUSE STORAGE;
    SQL> SELECT count(*)
    FROM dba_extents
    WHERE segment_name='ORDERS'
    AND owner='SYSTEM';
    -----------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------
    -------------------------------------
    9 Run the script to insert some rows into the ORDERS2 table.
    insert into orders2 VALUES(3,'14-APR-04','D1','14-APR-04');
    insert into orders2 VALUES(3,'14-APR-04','D1','14-APR-04');
    insert into orders2 VALUES(3,'14-APR-04','D1','14-APR-04');
    insert into orders2 VALUES(3,'14-APR-04','D1','14-APR-04');
    -
    -----------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------
    -------------------------------------
    10 View the columns for the ORDERS2 table. Then mark the DATE_OF_DELY column as
    UNUSED. View the columns for the ORDERS2 table again. What happens?
    SQL> DESCRIBE orders2;
    SQL> ALTER TABLE orders2 SET UNUSED COLUMN date_of_dely CASCADE CONSTRAINTS;
    SQL> DESCRIBE orders2;
    -----------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------
    -------------------------------------
    11 Drop the unused column DATE_OF_DELY.
    SQL> ALTER TABLE orders2 DROP UNUSED COLUMNS CHECKPOINT 1000;
    -----------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------
    -------------------------------------
    12 Drop the ORDERS2 table.
    SQL> DROP TABLE orders2;

    You might also like