-------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------Control File----------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------
1 Where is the existing control file located and what is the name?
Hint:
Query the dynamic performance view
V$CONTROLFILE or
V$PARAMETER, or
execute the SHOW PARAMETER command to
display the name and the location of the control file.
SQL> COL name FORMAT a50
SQL>
SELECT * FROM v$controlfile;
STATUS NAME
------- --------------------------------------
/u01/home/db01/ORADATA/u01/ctrl01.ctl
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 Try to start the database without any control files. (Simulate this by changing the
name of the control file in the parameter file or changing the control file name.)
What happens?
SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> !cp $HOME/ORADATA/u01/ctrl01.ctl $HOME/ORADATA/u01/ctrl01.bak
SQL> !rm $HOME/ORADATA/u01/ctrl01.ctl
SQL> STARTUP
SQL> SHUTDOWN IMMEDIATE
SQL> !cp $HOME/ORADATA/u01/ctrl01.bak $HOME/ORADATA/u01/ctrl01.ctl
SQL> STARTUP
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 Multiplex the existing control file, using the directory u02, and name the new control
file ctrl02.ctl. Make sure that the Oracle Server is able to write to the new
control file. For example, on Unix use the command chmod 660. Confirm that both
control files are being used.
SQL> CONNECT / AS SYSDBA
SQL> ALTER SYSTEM SET control_files = '$HOME/ORADATA/u01/ctrl01.ctl',
'$HOME/ORADATA/u02/ctrl02.ctl' SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> !cp $HOME/ORADATA/u01/ctrl01.ctl $HOME/ORADATA/u02/ctrl02.ctl
SQL> !chmod 660 $HOME/ORADATA/u02/ctrl02.ctl
SQL> STARTUP
SQL> SELECT name FROM v$controlfile;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 What a control file consist of
SQL>Alter database backup controlfile to trace;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------Redo Log File----------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------
1 List the number and location of existing log files and display the number of redolog file groups and members your database has.
SQL>SELECT member FROM v$logfile;
SQL> SELECT group#, members FROM v$log;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 In which database mode is your database configured? Is archiving enabled?
SQL> SELECT log_mode FROM v$database;
SQL> SELECT archiver FROM v$instance;
SQL>ARCHIVE LOG LIST;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 Add a redo log member to each group in your database located on u04, using the following naming conventions:
Add member to Group 1: log01b.rdo
Add member to Group 2: log02b.rdo
Verify the result.
C:\NEWWORLD\REDO01A.LOG
C:\NEWWORLD\REDO02A.LOG
C:\NEWWORLD\REDO03A.LOG
SQL> ALTER DATABASE ADD LOGFILE MEMBER
'C:\NEWWORLD\REDO01A.LOG' to Group 1,
'C:\NEWWORLD\REDO02A.LOG' to Group 2,
'C:\NEWWORLD\REDO03A.LOG' to Group 3;
SQL> COLUMN GROUP# FORMAT 99
SQL> COLUMN MEMBER FORMAT a40
SQL> SELECT * FROM v$logfile;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 Add a redo log group in your database with two members located on u03 and u04 using the following naming conventions:
Add Group 3: log03a.rdo and log03b.rdo Verify the result.
SQL> ALTER DATABASE ADD LOGFILE GROUP 4('C:\NEWWORLD\REDO04.LOG', 'C:\NEWWORLD\REDO04A.LOG') SIZE 1024K;
SQL> COLUMN GROUP# FORMAT 99
SQL> COLUMN MEMBER FORMAT a40
SQL> SELECT * FROM v$logfile;
SQL> SELECT group#, members FROM v$log;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 Remove the redo log group created in step 4.
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT group#, members FROM v$log;
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
SQL> !rm $HOME/ORADATA/u03/log03a.rdo
SQL> !rm $HOME/ORADATA/u04/log03b.rdo
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 Resize all online redo log files to 1024 KB. (Because we cannot resize log files, we have to add new logs and drop the old.)
SQL> ALTER DATABASE ADD LOGFILE
GROUP 3( '$HOME/ORADATA/u03/log03a.rdo',
'$HOME/ORADATA/u04/log03b.rdo'
) SIZE 1024K,
GROUP 4( '$HOME/ORADATA/u03/log04a.rdo',
'$HOME/ORADATA/u04/log04b.rdo'
) SIZE 1024K;
Database altered.
SQL> SELECT group#, status FROM v$log;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1, GROUP 2;
SQL> SELECT group#, bytes FROM v$log;
------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------TableSpaces----------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------
1 Create permanent tablespaces with the following names and storage:
a DATA01 data dictionary managed.
SQL> CREATE TABLESPACE data01 DATAFILE 'F:\RAMA\data01.dbf' SIZE 2M EXTENT MANAGEMENT DICTIONARY;
SQL> COLUMN name FORMAT a50
SQL> SET LINESIZE 80
SQL> SET PAGESIZE 999
SQL> SELECT name, bytes, create_bytes FROM v$datafile;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
b DATA02 locally managed with uniform sized extents (Ensure that every used extent size in the tablespace is a multiple of 100 KB.)
SQL> CREATE TABLESPACE data02 DATAFILE 'f:\rama\data02.dbf' SIZE 1M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K;
SQL> COLUMN name FORMAT a50
SQL> SET LINESIZE 80
SQL> SET PAGESIZE 999
SQL> SELECT name, bytes, create_bytes FROM v$datafile;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c. INDX01 locally managed with uniform sized extents of 4K( Enable automatic extension of 500 KB when more extents are required with a
maximum size of 2 MB. )
SQL>CREATE TABLESPACE indx01 DATAFILE 'F:\RAMA\indx01.dbf' SIZE 1M AUTOEXTEND ON NEXT 500K MAXSIZE 2M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4K;
SQL> COLUMN name FORMAT a50
SQL> SET LINESIZE 80
SQL> SET PAGESIZE 999
SQL> SELECT name, bytes, create_bytes FROM v$datafile;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
d.RONLY for read-only tables with the default storage. DO NOT make the tablespace read only at this time.
SQL>CREATE TABLESPACE ronly DATAFILE '$HOME/ORADATA/u01/ronly01.dbf' SIZE 1M;
SQL> COLUMN name FORMAT a50
SQL> SET LINESIZE 80
SQL> SET PAGESIZE 999
SQL> SELECT name, bytes, create_bytes FROM v$datafile;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 Allocate 500K more disk space to tablespace DATA02. Verify the result.
SQL> ALTER DATABASE DATAFILE 'F:\RAMA\data02.dbf' RESIZE 1500K;
SQL> COLUMN name FORMAT a40
SQL> SELECT name, bytes, create_bytes FROM v$datafile WHERE name LIKE '%data02%‘;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 Relocate tablespace INDX01 to subdirectory u06.
SQL> ALTER TABLESPACE indx01 OFFLINE;
SQL> SELECT name, status FROM v$datafile;
SQL> !mv $HOME/ORADATA/u02/indx01.dbf $HOME/ORADATA/u06/indx01.dbf
SQL> ALTER TABLESPACE indx01 RENAME DATAFILE '$HOME/ORADATA/u02/indx01.dbf' TO '$HOME/ORADATA/u06/indx01.dbf';
SQL> ALTER TABLESPACE indx01 ONLINE;
SQL> SELECT name, status FROM v$datafile;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 Create a table in tablespace RONLY. Make tablespace RONLY read-only.Attempt to create an additional table. Drop the first created table. What happens
and why?
SQL> CREATE TABLE table1 ( x CHAR(1)) TABLESPACE ronly;
SQL> ALTER TABLESPACE ronly READ ONLY;
SQL> SELECT name, enabled, status FROM v$datafile;
SQL>CREATE TABLE table2 ( y CHAR(1)) TABLESPACE ronly;
SQL> DROP TABLE table1;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 Drop tablespace RONLY and the associated datafile. Verify it.
SQL> DROP TABLESPACE ronly INCLUDING CONTENTS AND DATAFILES;
SQL> SELECT * FROM v$tablespace;
SQL>!ls $HOME/ORADATA/u01/*
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 Set DB_CREATE_FILE_DEST to $HOME/ORADATA/u05 in memory only. Create tablespace DATA03 size 5M. Do not specify a file location. Verify the creation of the
data file.
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='$HOME/ORADATA/u05' SCOPE=MEMORY;
SQL> CREATE TABLESPACE data03 DATAFILE SIZE 5M;
SQL> SELECT * FROM v$tablespace;
SQL> !ls $HOME/ORADATA/u05
-----------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------Storage Structure and Relationships---------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
1 As user SYSTEM, run the following script to create tables and indexes.
SQL> CONNECT system/manager
SQL> CREATE TABLE emp ( empno NUMBER(4),
ename VARCHAR2(30),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2) )
TABLESPACE data01
STORAGE ( INITIAL 100K
NEXT 100K
PCTINCREASE 0
MINEXTENTS 8
MAXEXTENTS 10 );
SQL> CREATE TABLE fragment1( a NUMBER )
TABLESPACE data01
STORAGE( INITIAL 10K );
SQL> CREATE TABLE dept ( deptno NUMBER,
dname VARCHAR2(15),
loc VARCHAR2(20) )
TABLESPACE data01
STORAGE( INITIAL 50K
NEXT 50K );
SQL> CREATE TABLE fragment2( a NUMBER )
TABLESPACE data01
STORAGE ( INITIAL 8K );
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 Identify the different types of segments in the database.
SQL> SELECT DISTINCT segment_type FROM dba_segments;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 Write a query to check which segments are within five extents short of the maximum extents. Ignore the bootstrap segment. This query is useful in identifying any segments that are likely to generate errors during future data load.
SQL> COLUMN segment_name FORMAT a20
SQL> COLUMN segment_type FORMAT a15
SQL> SELECT segment_name,segment_type, max_extents, extents FROM dba_segments WHERE extents+5 > max_extents AND segment_type<>'CACHE';
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 Which files have space allocated for the EMP table?
SQL> CONNECT system/manager
Connected.
SQL> SELECT DISTINCT f.file_name FROM dba_extents e,dba_data_files f WHERE e.segment_name='EMP' AND e.file_id=f.file_id;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 List the free space available by tablespace. The query should display the number of fragments, the total free space, and the largest free extent in each tablespace.
SQL> SELECT tablespace_name,COUNT(*) AS fragments, SUM(bytes) AS total,MAX(bytes) AS largest FROM dba_free_space GROUP BY tablespace_name;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 List segments that will generate errors because of lack of space when they try to allocate an additional extent.
SQL> SELECT s.segment_name, s.segment_type, s.tablespace_name,s.next_extent FROM dba_segments s WHERE NOT EXISTS (SELECT 1 FROM dba_free_space f
WHERE s.tablespace_name=f.tablespace_name HAVING max(f.bytes) > s.next_extent) ;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------Managing Undo Data----------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Connect as user SYS, and list the undo segments in tablespace UNDOTBS1.
SQL> CONNECT / AS SYSDBA
SQL> SELECT segment_name FROM dba_rollback_segs WHERE tablespace_name = 'UNDOTBS1';
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 Create undo tablespace UNDO2, size 15M, in $HOME/oradata/u03. List the rollback segments in tablespace UNDO2.
SQL> CREATE UNDO TABLESPACE undo2 DATAFILE 'F:\RAMA\DATA02.dbf' SIZE 15M;
SQL> SELECT segment_name FROM dba_---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
rollback_segs WHERE tablespace_name = 'UNDO2';
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 In a new telnet session start SQL*Plus and connect as user HR and run script to insert a row into table DEPARTMENTS.
Do not commit, roll back, or exit the session.
SQL> CONNECT hr/hr
SQL> INSERT INTO departments (department_id, department_name) VALUES (9999,'x');
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 In the session in which you are connected as SYS, using the ALTER SYSTEM command, switch the UNDO tablespace from UNDOTBS to UNDO2 for the instance.
SQL> ALTER SYSTEM SET undo_tablespace='UNDO2' SCOPE=BOTH;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5 As SYS drop tablespace UNDOTBS1. What happened? Why?
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6 List the undo segments in tablespace UNDOTBS1 and their status. Compare this list to the list in step 1.
SQL> SELECT segment_name FROM dba_rollback_segs WHERE tablespace_name = 'UNDOTBS1';
SQL> SELECT a.usn,a.name,b.status FROM v$rollname a, v$rollstat b WHERE a.name IN ( SELECT segment_name FROM dba_segments
WHERE tablespace_name = 'UNDOTBS') AND a.usn = b.usn;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 In the session connected as HR, roll back the transaction and exit the session.
SQL> ROLLBACK;
SQL> EXIT;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8 In the session connected as SYS drop tablespace UNDOTBS. What happened? Why?
SQL> DROP TABLESPACE undotbs;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9 As SYS issue the following command:
ALTER SYSTEM SET undo_retention=0 SCOPE=memory;
Now drop tablespace UNDOTBS. What happened? Why?
Note: There still may be a delay before the tablespace is drop.
SQL> ALTER SYSTEM SET undo_retention=0 SCOPE=MEMORY;
SQL> DROP TABLESPACE undotbs INCLUDING CONTENTS AND DATAFILES;
------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------Tables---------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
1Create the following tables as user SYSTEM for an order entry system that you are implementing now.
SQL> CONNECT system/manager
SQL> CREATE TABLE customers ( cust_code VARCHAR2(3),
name VARCHAR2(50),
region VARCHAR2(5) )
TABLESPACE users;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> CREATE TABLE orders2 ( ord_id NUMBER(3),
ord_date DATE,
cust_code VARCHAR2(3),
date_of_dely DATE )
TABLESPACE users;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2. Insert some records in OrderS2 table.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 Find which files and blocks contain the rows for the orders table.
SQL> CONNECT system/manager
SQL> SELECT file_id, block_id, blocks
FROM dba_extents
WHERE owner = 'SYSTEM'
AND segment_name = 'ORDERS2'
AND segment_type = 'TABLE';
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 Check the number of extents used by the table ORDERS2.
SQL> SELECT count(*) FROM dba_extents WHERE segment_name='ORDERS2' AND owner='SYSTEM';
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> CREATE TABLE orders
TABLESPACE users
STORAGE(MINEXTENTS 10)
AS
SELECT * FROM orders2;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> SELECT count(*) FROM dba_extents WHERE segment_name='ORDERS' AND owner='SYSTEM';
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 Truncate table ORDERS without releasing space and check the number of extents to verify extents have not been deallocated.
SQL> TRUNCATE TABLE orders REUSE STORAGE;
SQL> SELECT count(*)
FROM dba_extents
WHERE segment_name='ORDERS'
AND owner='SYSTEM';
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9 Run the script to insert some rows into the ORDERS2 table.
insert into orders2 VALUES(3,'14-APR-04','D1','14-APR-04');
insert into orders2 VALUES(3,'14-APR-04','D1','14-APR-04');
insert into orders2 VALUES(3,'14-APR-04','D1','14-APR-04');
insert into orders2 VALUES(3,'14-APR-04','D1','14-APR-04');
-
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10 View the columns for the ORDERS2 table. Then mark the DATE_OF_DELY column as UNUSED. View the columns for the ORDERS2 table again. What happens?
SQL> DESCRIBE orders2;
SQL> ALTER TABLE orders2 SET UNUSED COLUMN date_of_dely CASCADE CONSTRAINTS;
SQL> DESCRIBE orders2;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11 Drop the unused column DATE_OF_DELY.
SQL> ALTER TABLE orders2 DROP UNUSED COLUMNS CHECKPOINT 1000;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12 Drop the ORDERS2 table.
SQL> DROP TABLE orders2;
--------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------Indexes----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
1 You are considering creating indexes on the NAME and REGION columns of the CUSTOMERS table. What types of index are appropriate for the two columns? Create
the indexes, naming them CUST_NAME_IDX and CUST_REGION_IDX, respectively, and placing them in the appropriate tablespaces.
SQL> CONNECT system/manager
SQL> CREATE INDEX cust_name_idx
ON customers(name)
TABLESPACE indx01;
SQL> CREATE BITMAP INDEX cust_region_idx
ON system.customers(region)
TABLESPACE indx01;
-------------------------------------------------------Indexes----------------------------------------------------------------------------------------------------
2 Move the CUST_REGION_IDX index to another tablespace.
SQL> ALTER INDEX cust_region_idx REBUILD TABLESPACE indx;
--------------------------------------------------------Indexes----------------------------------------------------------------------------------------------------
3 Note the files and blocks used by the extents by CUST_REGION_IDX index.
SQL> SELECT file_id, block_id, blocks FROM dba_extents WHERE segment_name='CUST_REGION_IDX' AND owner='SYSTEM';
---------------------------------------------------------------------------------------------Indexes----------------------------------------------------------------------------------------------------
4 Re-create the CUST_REGION_IDX index without dropping and re-creating it, and retain it in the same tablespace as before. Does the new index use the same
blocks that were used earlier?
SQL> ALTER INDEX cust_region_idx REBUILD;
SQL> SELECT file_id, block_id, blocks FROM dba_extents WHERE segment_name='CUST_REGION_IDX' AND owner='SYSTEM';
------------------------------------------------------Indexes----------------------------------------------------------------------------------------------------
2 Query the data dictionary to:
a Check for constraints, whether they are deferrable, and their status.
SQL> COLUMN constraint_name FORMAT a25
SQL> COLUMN table_name FORMAT a10
SQL> COLUMN constraint_type FORMAT a1
SQL> COLUMN deferrable FORMAT a15
SQL> COLUMN status FORMAT a10
SQL> SELECT constraint_name, table_name,constraint_type, deferrable, status FROM dba_constraints WHERE table_name IN
('PRODUCTS','ORDERS','CUSTOMERS') AND owner='SYSTEM';
-----------------------------------------------------Indexes----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------MANAGING USERS------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
1 Create a user bob with password bob and assign bob the default table space and temporary tablespace
sql> create user bob identified by bob
default tablespace users
temporary tablespace temp
quota 1m on users
quota 1m on temp;
sql> grant create session to bob;
2 Create a user Emi with a password of MARY. Make sure that any objects and sort
segments created by Emi are not created in the system tablespace.
sql> CREATE USER emi
IDENTIFIED BY mary
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
3. Display the information on Bob and Emi from the data dictionary.
Hint: This can be obtained by querying DBA_USERS.
sql> SELECT username, default_tablespace,
temporary_tablespace
FROM dba_users
WHERE username IN ('BOB', 'EMI');
4. From the data dictionary, display the information on the amount of space that Bob
can use in tablespaces.
Hint: This can be obtained by querying DBA_TS_QUOTAS.
sql> COLUMN tablespace_name FORMAT a15
sql> COLUMN user FORMAT a10
sql> SELECT *
FROM dba_ts_quotas
WHERE username = 'BOB';
5. As user BOB change his temporary tablespace. What happens? Why?
sql> connect bob/crusader;
sql> ALTER USER bob
TEMPORARY TABLESPACE users;
6. As Bob, change his password to SAM.
sql> connect bob/crusader;
sql> ALTER USER bob
IDENTIFIED BY sam;
7. As SYSTEM, remove Bob’s quota on his default tablespace.
sql> CONNECT system/manager
sql> ALTER USER bob QUOTA 0 ON users;
8. Remove Emi’s account from the database.
Hint: Because Emi owns tables, you need to use the CASCADE option.
sql> DROP USER emi;
9. Bob has forgotten his password. Assign him a password of OLINK and require
that Bob change his password the next time he logs on.
sql> ALTER USER bob
IDENTIFIED BY olink
PASSWORD EXPIRE;
-----------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------GRANTING PREVILIGES-----------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
1 As SYSTEM, create user Emi and give her the capability to log on to the database and
create objects in her schema.
SQL> CONNECT system/manager
SQL> CREATE USER emi
IDENTIFIED BY "abcd12"
DEFAULT TABLESPACE data01
TEMPORARY TABLESPACE temp
QUOTA 1M ON data01;
SQL> GRANT create session, create table TO emi;
2 a Connect as Emi, and create tables using the script lab16_02a.sql to create the
tables CUSTOMERS and ORDERS.
SQL> CONNECT emi/abcd12;
SQL> @$HOME/STUDENT/LABS/lab16_02a.sql;
2 b Connect as SYSTEM and copy the data from SYSTEM.CUSTOMERS to Emi’s
CUSOMTERS table. Verify that records have been inserted.
SQL> CONNECT system/manager;
SQL> INSERT INTO emi.customers
SELECT *
FROM system.customers;
SQL> SELECT * FROM emi.customers;
c. As SYSTEM give Bob the ability to select from Emi's CUSTOMERS table. What
happens and why?
SQL> CONNECT system/manager
SQL> GRANT select ON emi.customers TO bob;
3 Reconnect as Emi and give Bob the ability to select from Emi's CUSTOMERS table.
Also, enable Bob to give the select capability to other users. Examine the data
dictionary views that record these actions.
SQL> CONNECT emi/abcd12;
SQL> GRANT select ON customers
TO bob WITH GRANT OPTION;
SQL> CONNECT system/manager;
SQL> COLUMN grantee FORMAT a8
SQL> COLUMN owner FORMAT a8
SQL> COLUMN table_name FORMAT a10
SQL> COLUMN grantor FORMAT a8
SQL> COLUMN privilege FORMAT a10
SQL> COLUMN grantable FORMAT a3
SQL> COLUMN hiearchy FORMAT a3
SQL> SELECT *
FROM dba_tab_privs
WHERE grantee='BOB';
4 Create user Trevor with the capability to log on to the database.
SQL> CONNECT system/manager
SQL> CREATE USER trevor IDENTIFIED BY "abcd1?";
SQL> GRANT create session TO trevor;
5 a As Bob, enable Trevor to access Emi’s CUSTOMERS table. Give Bob the new
password sam.
SQL> CONNECT bob/olink
SQL> GRANT select ON emi.customers TO trevor;
b As Emi, remove Bob’s privilege to read Emi’s CUSTOMERS table.
SQL> CONNECT emi/abcd12;
SQL> REVOKE select ON customers FROM bob;
c As Trevor, query Emi’s CUSTOEMRS table. What happens and why?
SQL> CONNECT trevor/abcd1?;
SQL> SELECT *
FROM emi.customers;
6 a Enable Emi to create tables in any schema. As Emi, create the table ORDERS in
Bob’s schema as a copy of EMI.ORDERS. What happened and why?
SQL> CONNECT system/manager
SQL> GRANT create any table TO emi;
SQL> CONNECT emi/abcd12
SQL> CREATE TABLE bob.orders
AS
SELECT *
FROM orders;
b As SYSTEM, examine the data dictionary view DBA_TABLES to
check the result.
SQL> CONNECT system/manager
SQL> SELECT owner, table_name
FROM dba_tables
WHERE table_name IN ('CUSMTERS', 'ORDERS');
7 Enable Emi to start up and shut down the database without the ability to create a
new database.
SQL> CONNECT sys/oracle AS SYSDBA
SQL> GRANT sysoper TO emi;
------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------MANAGING ROLES------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
1 Examine the data dictionary view and list the system privileges of the
RESOURCE role.
SQL> CONNECT system/manager
SQL> COLUMN privilege FORMAT a20
SQL> COLUMN grantee FORMAT a10
SQL> SELECT *
FROM dba_sys_privs
WHERE grantee = 'RESOURCE';
2 Create a role called DEV, which enables a user to create a table, create a view and
select from Emi’s CUSTOMERS table.
SQL> CREATE ROLE dev;
SQL> GRANT create table, create view TO dev;
SQL> CONNECT emi/abcd12
SQL> GRANT select ON customers TO dev;
SQL> CONNECT system/manager
SQL> GRANT dev TO bob;
3 a Assign the RESOURCE and DEV roles to Bob, but make only the
RESOURCE role to be automatically enabled when he logs on.
SQL> CONNECT system/manager
SQL> GRANT dev, resource TO bob;
SQL> ALTER USER bob
DEFAULT ROLE resource;
b Give Bob the ability to read all the data dictionary information.
SQL> connect system/manager;
SQL> GRANT select_catalog_role TO bob;
4 Bob needs to check the undo segments that are currently used by the instance.
Connect as Bob and list the undo segments used.
SQL> CONNECT bob/sam
SQL> SET ROLE select_catalog_role;
SQL> SELECT segment_name
FROM dba_rollback_segs
WHERE status='ONLINE';
5 As SYSTEM, try to create a view CUST_VIEW on Emi’s CUSTOMERS table. What
happens and why?
SQL> connect system/manager
SQL> CREATE VIEW cust_view AS
SELECT *
FROM emi.customers;
6 As user Emi grant select on customers to SYSTEM. As SYSTEM try to create view
CUST_VIEW on Emi’s CUSTOMERS table. What happens and why?
SQL> CONNECT emi/abcd12
SQL> GRANT select ON customers TO system;
SQL> CONNECT system/manager
SQL> CREATE VIEW cust_view AS
SELECT *
FROM emi.customers;
-------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------MAINTAINING DATA INTEGRITY--------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
1 Examine the script lab13_01.sql. Run the script to create the constraints.
SQL> @$HOME/STUDENT/LABS/lab13_01.sql
2 Query the data dictionary to:
a Check for constraints, whether they are deferrable, and their status.
Hint: Use the DBA_CONSTRAINTS view to get this information.
SQL> COLUMN constraint_name FORMAT a25
SQL> COLUMN table_name FORMAT a10
SQL> COLUMN constraint_type FORMAT a1
SQL> COLUMN deferrable FORMAT a15
SQL> COLUMN status FORMAT a10
SQL> SELECT constraint_name, table_name,
constraint_type, deferrable, status
FROM dba_constraints
WHERE table_name IN
('PRODUCTS','ORDERS','CUSTOMERS')
AND owner='SYSTEM';
2 b Check the names and types of indexes created to validate the constraints.
Hint: The indexes are only created for primary key and unique constraints and
have the same name as the constraints.
SQL> SELECT index_name,table_name,uniqueness
FROM dba_indexes
WHERE index_name in
( SELECT constraint_name
FROM dba_constraints
WHERE table_name IN ('PRODUCTS', 'ORDERS', 'CUSTOMERS')
AND owner='SYSTEM'
AND constraint_type in ('P','U')
);
3 Insert two records with the following values into the PRODUCTS table:
SQL> INSERT INTO system.products
VALUES(4000,'UNIX Monitor',3620);
SQL> INSERT INTO system.products
VALUES(4000,'NT Monitor', 2400);
SQL> COMMIT;
4 Enable the unique constraint on the PRODUCT table. Was it successful?
Why or why not?
SQL> ALTER TABLE system.products
ENABLE CONSTRAINT products_prod_code_uk;
5 a Ensure that new rows added to the table do not violate the constraint on the
PRODUCT table.
Hint: This can be done by enabling the constraint NOVALIDATE.
SQL> ALTER TABLE system.products
ENABLE NOVALIDATE CONSTRAINT products_prod_code_uk;
b Query the data dictionary to verify the effect of the change.
SQL> SELECT constraint_name, table_name,
constraint_type, validated, status
FROM dba_constraints
WHERE table_name = 'PRODUCTS'
AND owner='SYSTEM';
5 c Test that the constraint disables inserts that violate the change by adding a
row with the following values:
3000 Monitor 4000
LIST_PRICE PRODUCT_DESCRIPTION PRODUCT_ID
SQL> INSERT INTO system.products
VALUES(4000,'Monitor',3000);
6 Take the necessary steps to identify existing constraint violations in the PRODUCTS
table, modify product codes as needed, and guarantee that all existing as well as new
data do not violate the constraint. (Assume that the table has several thousands of
rows and it is too time-consuming to verify each row manually.)
Hint: Use the following steps:
a Create the EXCEPTIONS table.
SQL> CONNECT system/manager
SQL> @?/rdbms/admin/utlexcpt
b Run the command to enable the constraint and trap the exceptions.
SQL> ALTER TABLE system.products
ENABLE CONSTRAINT products_prod_code_uk
EXCEPTIONS INTO system.exceptions;
c Use the ROWIDs in the EXCEPTIONS table to list the rows in the
PRODUCTS table that violate the constraint. (Do not list LOB columns.)
SQL> SELECT rowid, prod_code, description
FROM system.products
WHERE rowid IN ( SELECT row_id
FROM exceptions
WHERE table_name='PRODUCTS'
);
6 dRectify the errors.
SQL> UPDATE system.products
SET prod_code='4001'
WHERE rowid = ( SELECT max(row_id)
FROM exceptions
WHERE table_name='PRODUCTS'
);
e Enable the constraint.
SQL> ALTER TABLE system.products
ENABLE CONSTRAINT products_prod_code_uk
EXCEPTIONS INTO system.exceptions;
7 Run the script lab13_07.sql to insert rows into the table. Were the inserts
successful? Roll back the changes.
SQL> @$HOME/STUDENT/LABS/lab13_07.sql
SQL> INSERT INTO system.orders
VALUES (800,'01-JAN-98','J01',NULL);
SQL> INSERT INTO system.customers
VALUES ('J01','Sports Store', 'East');
SQL> ROLLBACK;
8 Now examine the script lab13_08. Notice that this script also performs the
inserts in the same sequence. Run the script and check if it executes successfully.
SQL> @$HOME/STUDENT/LABS/lab13_08.sql
SQL> ALTER SESSION SET CONSTRAINTS=deferred;
SQL> INSERT INTO system.orders
VALUES (800,'01-JAN-98','J01',NULL);
SQL> INSERT INTO system.customers
VALUES ('J01','Sports Store', 'East');
SQL> COMMIT;
9 Truncate the CUSTOMERS table. Was it successful? Why or why not?
SQL> TRUNCATE TABLE system.customers;
-----------------------------------------------------------------------------------------------------------------------------------
--------------------------------------MANAGING PASSWORD SECURITY AND RESOURCES----------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
1 Run the lab14_01.sql script to create user Jeff. Enable password management
by running script @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql.
SQL> @$HOME/STUDENT/LABS/lab14_01.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
2 Try to change the password of user Jeff to JEFF. What happens?
SQL> ALTER USER jeff IDENTIFIED BY jeff;
3 Try changing the password for Jeff to follow the password management format.
Hint: Password should contain at least one digit, one character, and one punctuation.
SQL> ALTER USER jeff
IDENTIFIED BY super1$;
4 Alter the DEFAULT profile to ensure the following applies to users assigned the
DEFAULT profile:
- After two login attempts, the account should be locked.
- The password should expire after 30 days.
- The same password should not be reused for at least one minute.
- The account should have a grace period of five days to change an expired
password.
- Ensure that the requirements given have been implemented.
Hints:
Use the ALTER PROFILE command to change the default profile limits.
Query the data dictionary view DBA_PROFILES to verify the result.
SQL> ALTER PROFILE default LIMIT
FAILED_LOGIN_ATTEMPTS 2
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 1/1440
PASSWORD_GRACE_TIME 5;
SQL> SELECT resource_name, limit
FROM dba_profiles
WHERE profile='DEFAULT'
AND resource_type='PASSWORD';
5 Log in to user Jeff supplying an invalid password. Try this twice, then log in again,
this time supplying the correct password. What happens? Why?
SQL> CONNECT jeff/superman
SQL> CONNECT jeff/super
SQL> CONNECT jeff/super1$
6 Using data dictionary view DBA_USERS verify user Jeff is locked. Unlock the account
for the user Jeff. After unlocking user Jeff connect as Jeff.
Hint: Execute the ALTER USER command to unlock the account.
SQL> CONNECT / AS SYSDBA
SQL> SELECT username, account_status
SQL> ALTER USER jeff
ACCOUNT UNLOCK;
SQL> CONNECT jeff/super1$
7 Disable password checks for the DEFAULT profile.
Hint: Execute the ALTER PROFILE command to disable the password checks.
SQL> ALTER PROFILE default LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;
8 Log in to user Jeff supplying an invalid password. Try this twice, then log in again,
this time supplying the correct password. What happens? Why?
SQL> CONNECT jeff/superman
SQL> CONNECT jeff/super
SQL> CONNECT jeff/super1$
