CS 3540 Week 6 Lab Part 2
Complete in class

 

This assignment is designed to help you to setup an environment to facilitate performance testing.

  1. Use SQL*Plus to connect to the database as SYSTEM.
  2. Begin spooling to file CS3540Lab6p2.txt
  3. Drop and/or Delete any tablespaces, users and/or objects left over from prior labs.
  4. Create a new Tablespace named TBSizeTest.
    Set the size to 100M.
    Name the datafile     TBSizeTest01.dbf and place it with the other datafiles in your OFA tree.
    (hint: Find CREATE TABLESPACE syntax in your text page 79 and/or Internet resources)
  5. Check DBA_TABLESPACES and/or OFA tree to insure that the tablespace was created correctly.
  6. Create a new user named SmallUser with a password of testing123
    Set the user's default tablespace to be TBSizeTest.
    Set the temporary tablespace to be temp
  7. Give the user a 1M quota on tablespace TBSizeTest.
  8. Give the new user privileges to create a session, connect, create a cluster, create a table
    create a sequence and to create a procedure.
  9. Connect as SmallUser.
  10. Ensure that you are connected as SmallUser by querying the user_users table.
    (note that you can also use the SQL*Plus command: show user)
  11. Download and execute the script: CS3540PLSQL1.TXT
    (note that the quota is exceeded)
  12. Query the table TESTTABLE to determine the number of rows that were created by the procedure.
  13. Create a new user named BigUser with a password of testing123
    Set the user's default tablespace to be TBSizeTest.
    Set the temporary tablespace to be temp
  14. Give the user a 50M quota on tablespace TBSizeTest.
  15. Give the new user privileges to create a session, connect, create a cluster, create a table
    create a sequence and to create a procedure.
  16. Connect as BigUser.
    (always ensure that you are connected as the correct user)
  17. Execute the script CS3540PLSQL1.TXT
    (this time it should run to completion)
  18. Query the table TESTTABLE to determine the number of rows that were created by the procedure.
  19. Connect as system
  20. Query DBA_SEGMENTS to determine the size of TESTTABLE
  21. End Spooling
  22. Research: Why did the script fail the first time?
  23. Research: What is the difference between a segment and an extent?
  24. Research: When an object such as a table is dropped, what happens to the space it was using?

Use Assignment Uploader to submit