| Team Fly | |
As you have seen in this chapter, there is a great deal that a DBA needs to be aware of to properly manage a database. The good news is that you will have tools such as OEM to help you. Do your best to keep your environment as simple as you possibly can! You will be glad that you did as your overall database environment continues to grow.
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";
| Team Fly | |