CS 3540 Week 5 Oracle Lab
Due Feb 8th before class

 

This assignment uses the Oracle DBA and V$ views to obtain information about your database.

  1. Connect to your database as SYSTEM
  2. Start spooling to the file CS3540W5Spool.txt
  3. Query the V$DATABASE view to determine the name, creation date and open mode of your database
    (hint: you can always use the DESC command to find the column names for views and tables)
  4. Query the V$DATAFILE view to list the file number, name, status and size of the database's files;
    (use the syntax "column <column name> format <format specs>" to avoid column wrapping in queries)
    (example: column name format a35 )
  5. Query the V$INSTANCE view to determine all of the information about your current instance.
  6. Query the V$SGA view to display the summary statistics for the allocation of space in the SGA.
  7. Use the DBA_TABLESPACES view to list the names of the tablespaces in your database.
  8. Use the DBA_USERS view to list the users in your database
  9. Create a user named TESTUSER with a password of testing123.
    Assign TESTUSER to default to the USERS tablespace and to the temporary tablespace TEMP.
    (hint: Chapter 10 )
  10. Query the DBA_USERS view to see that the TESTUSER was created correctly and is assigned to the specified tablespaces.
  11. Query the DBA_SYS_PRIVS view to see what privileges TESTUSER has
  12. Grant privileges to TESTUSER so that the user can create a session, create tables, create views, create indexes, create clusters and create sequences.
  13. Query DBA_SYS_PRIVS to insure that the specified privileges were correctly granted.
  14. Query DBA_TS_QUOTAS to determine TESTUSER's quota on USERS
  15. Assign TESTUSER a quota on USERS of 100M
  16. Check DBA_TS_QUOTAS to insure that the quota was placed correctly

    Use "Spool Off" to stop spooling.
  17. Run your Dreamhome Rentals database script file to create the Dreamhome Rentals database.
    (file can be downloaded from http://rbhilton.com/wsucs/cs3540/cs3540DreamHomeDBCR.sql)
  18. Research: How could you determine how much space is being used by the Dreamhome Tables?
  19. Research: How many DBA and V$ tables are available in Oracle 10g?
  20. Research: What formatting commands are available in SQL*Plus?

    Submit your spool file along with a document (CS3540W5Research.doc) containing results to the research questions using the assignment uploader.

Use Assignment Uploader to submit