...:::SUDHIR:::...: Oracle Practicals

-------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------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> 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 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)

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');

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

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$

...:::SUDHIR:::...

Oracle Practicals

1 comment:

About Me