Project 3-1: Creating Essential Objects - Oracle

This project will walk you through the creation of the essential storage and schema objects after a database has been created, which in this project will be called ora10g. You will create a new tablespace called NEW_TS and will than add a user NEW_USER who will be given the authority to this tablespace. You will then create a role called NEW_ROLE and grant privileges to it. Afterward, you’ll grant this role to the new user. A table and index will be created on this tablespace by the new user. Lastly, you will resize the undo tablespace to make it larger. You will see how to do this in OEM and the generated SQL will also be shown to you so you can do this in SQL*Plus.

1. You have been asked to create a new used named NEW_USER who will need to create objects in a new tablespace called NEW_TS that should be sized at 5MB. Your first step will be to create the tablespace. In OEM, log in as user SYSTEM, go to database ora10g, choose storage, then choose tablespace and select an existing tablespace to model. Under Objects in the toolbar, select the Create Like option to model your new tablespace after the existing one. Enter the new tablespace name, datafile name, and all properties including the size. Make this a locally managed tablespace 5MB in size with uniform extents 96KB in size. If you choose the Show Sql button, you will see the generated SQL. It should look something like the following SQL. You can either apply the change in OEM or you can copy and paste the generated SQL and run it in SQL*Plus.
   
   CREATE TABLESPACE "NEW_TS" LOGGING
   DATAFILE 'C:\ORACLE\ORA10\ORA10G\NEW_TS1.ora' SIZE 2M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 96K SEGMENT SPACE MANAGEMENT  AUTO;
   
   
2. Now you will create NEW_USER. As with the preceding tablespace creation, you can model an existing user. In OEM, go to Security and then to User, choose an existing user to model, and select Object from the toolbar. Once again, use the Create Like feature. The user should now have a password of new_password, which will be unlocked. Set the default tablespace to NEW_TS.
   
   CREATE USER "NEW_USER" PROFILE "DEFAULT" IDENTIFIED BY "new_password" PASSWORD
   EXPIRE DEFAULT TABLESPACE "NEW_TS"
   TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "TEMP”;
   
   
3. Create a role called NEW_ROLE. In OEM, go to security, and then choose Role. Under Object in the toolbar, select Create and enter the role name.
   
   CREATE ROLE "NEW_ROLE" NOT IDENTIFIED;
   
   
4. Grant the CREATE TABLE system privilege, the OLAP_USER role, and the object privilege SELECT on table SQLPLUS_PRODUCT_PROFILE to NEW_ROLE. In OEM, go to role and choose NEW_ROLE. Use the tabs System, Object, and Role to choose the objects listed here. Click the Apply button to make the changes. The generated SQL will look like the three grants listed next.
   
   GRANT CREATE TABLE TO "NEW_ROLE";
   GRANT SELECT ON "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" TO "NEW_ROLE";
   GRANT "OLAP_USER" TO "NEW_ROLE";
   
   
5. Grant NEW_ROLE and connect to NEW_USER. Also, give NEW_USER an unlimited quota on NEW_TS to allow for objects to be created in the tablespace. In OEM, navigate to Users and choose NEW_USER. Once there, choose the Role tab and select NEW_ROLE, and then select the down arrow. Click the Apply button to make the change.
   
   GRANT "NEW_ROLE" TO "NEW_USER";
   ALTER USER "NEW_USER" DEFAULT ROLE ALL;
   ALTER USER "NEW_USER" QUOTA UNLIMITED ON "NEW_TS";
   
   
6. You will now log into the database as NEW_USER and can use OEM with the NEW_USER account. Once in OEM, you will create a table called NEW_TABLE with columns col01 as number(15) and col02 as varchar2(30). In OEM, in the toolbar, select Object and under that choose Create, and then choose Table. Make sure the table is created in NEW_TS. Follow the screens to add col01 and col02. You will then create a primary key called NEW_TABLE_PK using col01. Follow the screens and choose the options you would like. We recommend you name any keys and constraints rather than relying on system defaults. Choose Finish and you have created a new table with a primary key!
   
   CREATE TABLE "NEW_USER"."NEW_TABLE"
   ("COL01" NUMBER(15) NOT NULL,
    "COL02" VARCHAR2(30) NOT NULL,
   CONSTRAINT "NEW_TABLE_PK" PRIMARY KEY("COL01"),
   CONSTRAINT "NEW_TABLE_U1" UNIQUE("COL01"))
   TABLESPACE "NEW_TS";
   
   
7. You now have one last task: resizing the undo tablespace to add 100MB to it. Log in to OEM as user System and choose the datafile under the undo tablespace. Enter the new size and click Apply. It’s as easy as that. The SQL to increase this from 50MB to 150MB is shown here:
   
   ALTER DATABASE DATAFILE'/u01/oradata/ORA10G/
   UNDOTBS01.DBF' RESIZE 150M;

Project Summary

This project has taken you through the basic steps of creating an environment for a new user, including using roles and granting privileges. You’ve seen how to manage users as well as space and have even created objects. Armed with these capabilities, you are now on your way to being a productive DBA. Congratulations!

1. What is the benefit of a role?

2. Should a table that is in tens or hundreds of extents be reorged?

3. What is the preferred method for collecting object statistics?

4. What is a segment?

5. What is an extent?

6. Name two reasons for implementing an index.

7. How can you place a database in maintenance mode without first shutting it down?

8. How can we limit the resources that a particular user can consume and how does this work?

9. When managing undo segments, what are the things that you need to think about?

10. What is likely to happen if you turn on the autoextend property for undo and temporary tablespaces with a maxsize set to unlimited?

11. What is special about the SYS user account and how does it differ from SYSTEM?

12. What are temporary tablespaces used for?

13. What are the two aspects of security that are covered in Oracle’s implementation?

14. Name and describe the types of privileges that can be granted to a user.

15. How would you implement your corporate password policy in Oracle?