Use Notepad, Wordpad, or Microsoft Word to document your solution to the tasks i
ID: 3792870 • Letter: U
Question
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?
2. Create a PROFILE. Create a PROFILE named TEST_Profile with the following limits: SESSIONS_PER_USER 1 CPU_PER_SESSION unlimited CPU_PER_CALL 5000 LOGICAL_READS_PER_SESSION unlimited LOGICAL_READS_PER_CALL 100 IDLE_TIME 45 CONNECT_TIME 240 Alter the system to enforce resource limits with this command: ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
3. Create database users. Create three new database users named TEST1, TEST2, and TEST3, Each new user is identified by the password PASSWORD. Their default tablespace is USERS. Their temporary tablespace is TEMP. The quota is 20K on USERS. The profile is the Oracle database default profile. If you encounter any problems (such as Oracle error messages) when working with these users, then you are expected to develop solutions to the problems. Execute the following SQL commands: COLUMN username FORMAT A8; COLUMN default_tablespace FORMAT A18; COLUMN temporary_tablespace FORMAT A20; SELECT username, default_tablespace, temporary_tablespace FROM DBA_USERS WHERE username LIKE 'TEST%';
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%';
7. Shut down the database.
Explanation / Answer
2)
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE; //Without Resource_limit profile cannot be created
CREATE PROFILE TEST_Profile LIMIT
SESSIONS_PER_USER 1
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 5000
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL 100
IDLE_TIME 45
CONNECT_TIME 240
3)
CREATE USER TEST1
IDENTIFIED BY PASSWORD
DEFAULT TABLESPACE USERS
QUOTA 10M ON example
TEMPORARY TABLESPACE TEMP
QUOTA 20K ON USERS
PROFILE DEFAULT;
CREATE USER TEST2
IDENTIFIED BY PASSWORD
DEFAULT TABLESPACE USERS
QUOTA 10M ON example
TEMPORARY TABLESPACE TEMP
QUOTA 20K ON USERS
PROFILE DEFAULT;
CREATE USER TEST3
IDENTIFIED BY PASSWORD
DEFAULT TABLESPACE USERS
QUOTA 10M ON example
TEMPORARY TABLESPACE TEMP
QUOTA 20K ON USERS
PROFILE DEFAULT;
COLUMN username FORMAT A8;
COLUMN default_tablespace FORMAT A18;
COLUMN temporary_tablespace FORMAT A20;
SELECT username, default_tablespace, temporary_tablespace FROM DBA_USERS WHERE username LIKE 'TEST%';
// This would return a list of tables with Test1,Test2,Test3
7)
Use the SHUTDOWN command to shut down the target database without exiting RMAN. This command is equivalent to the SQL*Plus SHUTDOWN statement.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.