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 CS3540Lab6p1.txt
3. Create a new Tablespace named TBTest using only the required parameters: datafile and size.
   Set the size to 1M.
   Name the datafile tbtest01.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)
4. Check to see what default values Oracle supplied in the table space by querying DBA_TABLESPACES for the new tablespace.
5. Check the OFA tree to see that the datafile was indeed created.
6. Remove the tablespace by:
   ALTER TABLESPACE TBTest offline;
   DROP TABLESPACE TBTest INCLUDING CONTENTS AND DATAFILES;
7. Query DBA_TABLESPACES again to insure that the tablespace has been dropped.
8. Check the OFA tree to insure that the datafile has been deleted.
9. Recreate the tablespace with a size of 500M.
10. Query DBA_TABLESPACES to insure that the tablespace was created with the correct size.
11. Create a new user named lab6user with a password of testing123
    Set the user's default tablespace to be the new tablespace that you created.
    Set the temporary tablespace to be temp
12. Give the new user privileges to create a session, connect, create a cluster, create a table
    create a sequence and to create a procedure.
13. Connect as the new user.
14. Attempt to create a new table named TESTTABLE with a single VARCHAR2(50) column named col1.
    (hint: CREATE TABLE syntax on page 182)
15. Connect as system
16. Give the user some space to create objects with the syntax:
    alter user lab6user quota unlimited on tbtest;
17. Attempt to create a new table named TESTTABLE with a single VARCHAR2(50) column named col1.
18. Query the USER_TABLES user view to insure that the table was created.
19. Query all rows of TESTTABLE;
20. DROP TESTTABLE;
21. Stop Spooling
22. Research: Why did the create table fail in step 14?
23. Research: What is the proper amount of object space to grant to users?
24. Research: What is the risk of granting an unlimited quota to a user?
25. Research: What will happen if a user exceeds their quota? What if the quota is unlimited?
    
    

Use Assignment Uploader to submit