1. Connect to Oracle using SQL*Plus
   
   
2. Change user to sys by using the command:
    
   connect sys/ as sysdba
   (you will be prompted for a password)
   
   
3. Set your linesize and pagesize parameters to maximum value
   
   set pagesize 132
   set linesize 132
   
   
4. Begin spooling your session
   spool “c:\cs3540W04Spool.txt”
   
   
5. List each of your tablespaces and their status with the command:
   
   SELECT tablespace_name, status  FROM dba_tablespaces;
   
   
6. The select statement from the prior step uses one of the many dba tables that are available in Oracle.  Look at the columns available in this table by issuing the command:
   
   DESC dba_tablespaces;
   
   The DESC command is used in Oracle to display the schema of a table.
   
   
7. Check out the details on the SYSTEM tablespace by using the command:
   
   select *
   FROM dba_tablespaces
   WHERE tablespace_name = 'SYSTEM';
   
   How does the extent size fit with the cluster size on your hard drive?
   
   
8. Set column sizes for the following query by typing the commands:
   (the SQL*Plus column command sets the widths of all columns with the specified name)
   
   column tablespace_name format a10
   column file_name format a50
   
   
9. Determine the name and size of each of the tablespace files with the command:
   
   SELECT tablespace_name, file_name, bytes
   FROM dba_data_files
   ORDER BY tablespace_name, file_name;
   
   Locate those same files in your OFA path using Windows Explorer.
   
   
10. Create a new tablespace with the command:
    Note that you need to replace <dbpath> with the OFA path to your data files.
    
    CREATE TABLESPACE CS3540Test
    DATAFILE '<dbpath>\CS3540Data01.dbf'
    SIZE 1M DEFAULT STORAGE
    (INITIAL 25K NEXT 10K MINEXTENTS 1 MAXEXTENTS 100
    PCTINCREASE 0 );
    
    
11. Now repeat steps 5 through 9, noting the file(s) involved in your new tablespace.
    
    
12. Create a user ‘NEWDBA”.  Assign the default table space for the user to your new tablespace.
    Use syntax similar to that found on page 89 of your Oracle text or at websites such as:
    http://www.dba-oracle.com/concepts/create_users.htm
    
    
13. Query the dba_users table to verify the default and temporary tablespaces for the NEWDBA user.
    
    
14. End spooling with the command:
    spool off
    
    
15. Upload your spool file using the file uploader.

Research Question:

1. Note there are default tablespaces that have been created with the Oracle Installation. (Step 5 above) . Answer the following question using your Oracle DBA Handbook and/or Internet resources.

What are the purposes for each of these default tablespaces?

Use Assignment Uploader to submit required files