Use Notepad Wordpad or Microsoft Word to document your solut

Use Notepad, Wordpad, or Microsoft Word to document your solution to the tasks in the assignment. Show each command you execute and the computer\'s response (example response: Database Altered, Tablespace Created, etc.).

1. UNDO tablespace management. Connect as the user SYS and list the undo segments in the UNDOTBS1 tablespace. Create a second Undo tablespace named UNDOTBS2 of size 5MB in the same location as your current UNDOTBS1 tablespace. List the undo segments and their status in this tablespace. Start a second SQLPLUS session (leave the first one open) and connect to the system as the DBA user. CREATE TABLE test_tbl and insert a new row in the table, but do not commit the transaction. Also, do not rollback the transaction or exit the session: CREATE TABLE create test_tbl (name varchar2(20)); INSERT INTO test_tbl VALUES (\'UoNA INST 523\'); Switch to the first SQLPLUS session and use the ALTER SYSTEM command to switch the active Undo tablespace from UNDOTBS1 to UNDOTBS2. Now, as the user SYS, drop the UNDOTBS1 tablespace. What happened? List the undo segments and their status. Compare this to the list you developed earlier in this task. In the second SQLPLUS session as the user DBA, rollback the transaction and exit the session. In the first SQLPLUS session as the user SYS, drop the tablespace named UNDOTBS1. What happened?

4. Alter a user. a. Alter the user TEST1 to assign him the PROFILE named test_profile. Execute the following SQL commands: COLUMN username FORMAT A8; SELECT username, profile FROM DBA_USERS WHERE username = \'TEST1\'; b. Alter the user named TEST 1to change the default tablespace to the USERS tablespace with a quota of 20K. Execute the following SQL commands: COLUMN default_tablespace FORMAT A18; COLUMN temporary_tablespace FORMAT A20; SELECT username, default_tablespace, temporary_tablespace FROM DBA_USERS WHERE username = \'TEST1\'; c. Alter the user named TEST2 to assign a quota of 10K on a non-existent tablespace named APPLICATIONS. What error code identifier and error description is generated? Do not attempt to fix the error – go on to the next part of this task. d. Alter the user named TEST3 to assign an unlimited quota on the USERS tablespace. e. In windows command line, connect to your database using SQLPlus as the user TEST1. What happens? Correct the problem, then finish connecting as TEST1. f. Execute the following SQL commands to display information about TEST1, TEST2, and TEST3. COLUMN tablespace_name FORMAT a15; COLUMN username FORMAT A8; SELECT tablespace_name, username, bytes, max_bytes, blocks FROM dba_ts_quotas WHERE username LIKE \'TEST%\';

5. Connecting as a user/granting a privilege. a. Attempt to connect to the database as user TEST2, and TEST3, in turn. Why can’t you connect? Grant TEST2 and TEST3 the privilege needed to connect to the system. b. Connect as each user (TEST1, TEST2, and TEST3) in turn and execute the SQL commands shown here: COLUMN tablespace_name FORMAT a15; SELECT tablespace_name, max_bytes, blocks FROM user_ts_quotas;

6. Create a role. a. Connect as your DBA user. Create a role named application_developer. b. Assign the system privileges: create session, create table, and select any table to the new role. c. Grant the roles application_developer and connect to the user named TEST1 with the admin option. d. Execute the SQL commands shown here: COLUMN grantee FORMAT A8; SELECT grantee, granted_role FROM dba_role_privs WHERE grantee LIKE \'TEST%\';

Solution

6.a

Syntax:
Create Role role_me;

It is created role name of the sql
Command:
SQL>-CREATE ROLE application developer,

Output
Role created
b.
For example
SQL>create user a identied by M1
User created

SQL>create user a identied by M2
User created

Syntax:
SQL> GRANT CREATE TABLE, CREATE SESSION,SELECT ANY TABLE To
application developer;

Example query:
SQL> GRANT CREATE TABLE, CREATE SESSION,SSELECT ANY TABLE

application developer to M2;

Output
Grant succeeded

c.

SQL> SELECT TESTl$.name, admin option, default role
FROM application developer$, sysauthentications, dba role privilege
WHERE privilege# =
(SELECT TEST1# from TEST1S WHERE name = \'CONNECTDBA\')
AND TEST1$.TEST1# = grantee#
AND grantee = TEST1$.name
AND granted role = \'CONNECTDBA’;

Output
NAME ADMIN_0PTION DEF
M1 NO NO
M2 YES NO

SQL>GRANT application developer to M1 WITH ADMIN OPTION;
SQL>GRANT application _developer to M2;

d.
SQL> COLUMN grantee FORMAT A8;

SELECT grantee, granted role FROM application developer WHERE grantee LIKE \'TEST%\';

Sample output

grantee granted role
M1 security admin
M2 connect

M1 DBA

M2 DBA