PRACTICALS BOOK DBA

(1)

MORE THAN 100 REAL TIME SCENARIOS WITH SOLVED ANSWERS

A HANDBOOK FOR DBAS

Published by ORACLE DBA TRAINEES TEAM

VERSION 1.0

NOV 2009

(2)

FOREWORD

This handbook contains some real time scenarios Oracle DBAs face in their

daily work. All questions in the book are fully solved. The book intends to

provide a quick reference to various queries and views which would be useful

in the daily work of a DBA.

(3)

ACKNOWLEDGEMENTS

We would like to express our sincere gratitude to all who have helped us in

bringing up this handbook.

Firstly, we would like to thank the AMEX-IM/CDM team for their valuable

support and encouragement.

We would like to thank Mr.Anup Krishnan, Portfolio Director, IM-CDM

Team, Mr.Padmanabha Reddy, Mr Ramesh.M, Mr.Dipak Laha, Mr.Vijay

Venkatesh and Mr.Suresh Karthik.

And last but not the least, a wholehearted thanks to our mentor,

Mr.Magudapathi.S, who with his constant support and guidance drove this

initiative into a success.

1.ora arch

2.storage structures

3.user admin

4.oracle utili

5.Backup rec

6.RMAN

7.v$

8query tuning

9AWR

(4)

(5)

1 ORACLE ARCHITECTURE

1. CHECK WHETHER THE INSTANCES

RMANCAT,DONSDMP,DONSRCP IS UP OR NOT.

ANS:

APDWD506:NONE:[/USR/APP/ORACLE]> ps –aef | grep pmon ORACLE 848028 1 0 JUN 11 - 0:24 ORA_PMON_DONSDMP ORACLE 954504 1 0 JUN 11 - 0:24 ORA_PMON_RMANCAT ORACLE 1085656 1 0 19:01:28 - 0:03 ORA_PMON_DONSRCP ORACLE 1200218 1249384 0 01:40:51 PTS/8 0:00 GREP PMON

ORA_PMON_DONSDMP, ORA_PMON_RMANCAT, ORA_PMON_DONSRCP shows that all these instances are up.

2. CHECK WHETHER THE DATABASE RMANCAT IS UP OR NOT.

ANS:

sql> select instance_name, status from v$instance; INSTANCE_NAME STATUS

---RMANCAT OPEN (OR)

sql> select open_mode from v$database;

OPEN_MODE

READ WRITE

Read write mode specifies RMANCAT is UP

3. IDENTIFY THE LOCATION OF PFILE,SPFILE,CONTROLFILE,LOG MEMBER FOR RMANCAT.

ANS:

(6)

$ find / -name initRMANCAT.ora. apdwd506:RMANCAT:[/usr/app/oracle/product/10.2.0/dbs]> ls afiedt.buf lkDONSRCP alert_DONSRCP.log lkRMANCAT cd ls hc_DONSDMP.dat orapwDONSDMP hc_DONSRCP.dat orapwDONSRCP hc_RMANCAT.dat orapwDONSRCP.bkp init.ora orapwRMANCAT initDONSDMP.ora orapwsid initDONSDMP2.ora snapcf_DONSDMP.f initDONSRCP.ora snapcf_DONSRCP.f initDONSRCP.ora_bkpaug8 spfileDONSDMP.ora initRMANCAT.ora spfileDONSDMP.ora.old init_DONSDMP_jul29_bkp.ora spfileDONSRCP.ora.old initdw.ora spfileRMANCAT.ora lkDONSDMP sqlnet.log

Here default location of pfile is initRMANCAT.ora

The alertlog can give the non default location of pfile and spfile. (B)

This can be displayed only when database is running on spfile. sql> sho parameter spfile

NAME TYPE VALUE

--- ---SPFILE STRING /USR/APP/ORACLE/PRODUCT/10.2.0 /DBS/SPFILERMANCAT1.ORA (C)

sql> show parameter control file

NAME TYPE VALUE

--- --- ---CONTROL_FILE_RECORD_KEEP_TIME INTEGER 7

CONTROL_FILES STRING /TEST/RMANCAT/DBFILES/ORA_CONT ROL1, /TEST/RMANCAT/DBFILES/OR A_CONTROL2

(OR)

(7)

NAME

---/TEST/RMANCAT/DBFILES/ORA_CONTROL1

/TEST/RMANCAT/DBFILES/ORA_CONTROL2 (D)

sql> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

1 1 40 20971520 2 NO INACTIVE 555884 13-JUN-09 2 1 41 20971520 2 NO INACTIVE 556452 13-JUN-09 3 1 42 20971520 2 NO CURRENT 556588 13-JUN-09

(OR)

sql> select member from v$logfile; MEMBER ---/TEST/RMANCAT/DBFILES/REDO1_1A /TEST/RMANCAT/DBFILES/REDO1_1B /TEST/RMANCAT/DBFILES/REDO1_2A /TEST/RMANCAT/DBFILES/REDO1_2B

4. IDENTIFY WHETHER THE DATABASE RMANCAT IS SETUP IN ARCHIVE LOG MODE.

ANS:

sql> select log_mode from v$database; LOG_MODE

---NOARCHIVELOG (OR)

sql> archive log list

DATABASE LOG MODE NO ARCHIVE MODE AUTOMATIC ARCHIVAL DISABLED

ARCHIVE DESTINATION /USR/APP/ORACLE/PRODUCT/10.2.0/DBS/ARCH OLDEST ONLINE LOG SEQUENCE 41

CURRENT LOG SEQUENCE 43 (OR)

(8)

Sql> select archiver from v$instance;

Archiver column has only one value i.e FAILED irrespective of the database in Archive or Noarchivelog mode. Hence this query can not be used to fetch required information.

5. IDENTIFY SIZE OF THE SGA AND FIND WHETHER THE AUTO MEMORY TUNING IS ENABLED.

ANS:

(A)

Sql> select * from v$sga; NAME VALUE --- ---FIXED SIZE 2071008 VARIABLE SIZE 113247776 DATABASE BUFFERS 8388608 REDO BUFFERS 6316032 (OR) Sql> select component, current_size from v$sga_dynamic_components; COMPONENT CURRENT_SIZE ---SHARED POOL 88080384 LARGE POOL 0 JAVA POOL 25165824 STREAMS POOL 0

DEFAULT BUFFER CACHE 0

KEEP BUFFER CACHE 0

RECYCLE BUFFER CACHE 0

DEFAULT 2K BUFFER CACHE 0

ASM BUFFER CACHE 0

13 rows selected.

(B)

Sql> sho parameter sga;

NAME TYPE VALUE

--- --- ---LOCK_SGA BOOLEAN FALSE

(9)

SGA_MAX_SIZE BIG INTEGER 124M SGA_TARGET BIG INTEGER 0

Since the SGA_TARGET value is zero, so AUTO MEMORY TUNING is not enabled.

6. IDENTIFY ALL THE BACKGROUND PROCESS FOR THE INSTANCE.

ANS:

apdwd506:RMANCAT:[/usr/app/oracle]> ps -ef |grep RMANCAT ORACLE 614566 1 0 JUN 11 - 0:03 ORA_MMON_RMANCAT ORACLE 635070 1 0 JUN 11 - 0:51 ORA_MMNL_RMANCAT

ORACLE 733420 1 0 JUN 11 - 0:00 ORACLERMANCAT (DESCRIPTION=(LOCAL=YES)(A DDRESS=(PROTOCOL=BEQ)))

ORACLE 745602 1 0 JUN 11 - 0:01 ORA_MMAN_RMANCAT ORACLE 815244 1 0 JUN 11 - 0:00 ORA_RECO_RMANCAT ORACLE 933894 1 0 JUN 11 - 0:07 ORA_CKPT_RMANCAT ORACLE 942302 1 0 JUN 11 - 0:00 ORA_PSP0_RMANCAT ORACLE 946400 1 0 JUN 11 - 0:07 ORA_SMON_RMANCAT

ORACLE 954504 1 0 JUN 11 - 0:24 ORA_PMON_RMANCAT ORACLE 958630 1 0 JUN 11 - 0:00 ORA_QMNC_RMANCAT

ORACLE 983256 1 0 JUN 11 - 0:00 ORA_Q001_RMANCAT ORACLE 1003622 1 0 21:31:41 - 0:11 ORA_CJQ0_RMANCAT

ORACLE 1007772 802994 0 01:46:58 - 0:00 ORACLERMANCAT (DESCRIPTION=(LOCAL=YES)(A DDRESS=(PROTOCOL=BEQ)))

ORACLE 1028268 1 0 JUN 11 - 0:02 ORA_DBW0_RMANCAT

ORACLE 1044694 1 0 JUN 11 - 0:00 ORA_Q000_RMANCAT ORACLE 1064984 1 0 JUN 11 - 0:02 ORA_LGWR_RMANCAT ORACLE 1089632 757786 0 07:49:45 - 0:00 ORACLERMANCAT ORACLE 1216614 1220722 0 23:11:36 - 0:00 ORACLERMANCAT (DESCRIPTION=(LOCAL=YES)(A DDRESS=(PROTOCOL=BEQ))) ORACLE 1261762 1093800 0 01:38:20 - 0:00 ORACLERMANCAT (DESCRIPTION=(LOCAL=YES)(A DDRESS=(PROTOCOL=BEQ))) ORACLE 1273984 1228906 0 01:42:24 - 0:00 ORACLERMANCAT (DESCRIPTION=(LOCAL=YES)(A DDRESS=(PROTOCOL=BEQ))) ORACLE 1282180 974868 0 01:46:56 - 0:00 ORACLERMANCAT (DESCRIPTION=(LOCAL=YES)(A DDRESS=(PROTOCOL=BEQ)))

ORACLE 1290384 1249384 0 01:57:51 PTS/8 0:00 GREP -I RMANCAT

ORACLE 1310874 1278130 0 01:56:57 PTS/15 0:00 CAT ALERT_RMANCAT.LOG

7. IDENTIFY THE SIZE OF DB BUFFER CACHE,LARGE POOL,SHARED POOL FOR RMANCAT.

(10)

ANS:

sql> sho parameter db_cache_size;

NAME TYPE VALUE --- ---

---DB_CACHE_SIZE BIG INTEGER 0

sql> sho parameter db_block_buffers; NAME TYPE VALUE --- --- ---DB_BLOCK_BUFFERS INTEGER 1024

sql> select * from v$sga; NAME VALUE --- ---FIXED SIZE 2071008 --- > BACKGROUD PROCESSES VARIABLE SIZE 113247776 --- > SHARED+LARGE+JAVA POOL DATABASE BUFFERS 8388608 --- > DATABASE BUFFER CACHE REDO BUFFERS 6316032

sql> select component, current_size from v$sga_dynamic_components; COMPONENT CURRENT_SIZE ---SHARED POOL 88080384 LARGE POOL 0 JAVA POOL 25165824 STREAMS POOL 0

DEFAULT BUFFER CACHE 0

KEEP BUFFER CACHE 0

RECYCLE BUFFER CACHE 0

ASM BUFFER CACHE 0

13 rows selected.

8. FORCE A CHECKPOINT MANUALLY

ANS:

sql> alter system switch logfile; System altered.

(11)

sql> alter system checkpoint; System altered.

apdwd506:rmancat:[/usr/app/oracle]> ps -ef |grep ckpt oracle 913416 1 0 jun 11 - 0:10 ora_ckpt_donsdmp oracle 933894 1 0 jun 11 - 0:07 ora_ckpt_rmancat oracle 966722 1 0 19:01:28 - 0:01 ora_ckpt_donsrcp oracle 1425626 1306850 0 02:49:04 pts/12 0:00 grep ckpt 9. DO A LOG SWITCH.

ANS:

sql> select group#, status from v$log; GROUP# STATUS

--- 1 INACTIVE 2 INACTIVE 3 CURRENT

sql> alter system switch logfile; System altered.

sql> select group#, status from v$log; GROUP# STATUS

--- 1 CURRENT 2 INACTIVE 3 ACTIVE

10. CHECK WHETHER THE DATABASE IS RUNNING ON SPFILE. IF YES IDENTIFY THE LOCATION OF SPFILE.

ANS:

SQL> select isspecified, count(*) from v$spparameter group by isspecified; ISSPEC COUNT(*)

--- ---FALSE 259

(12)

If NO true values, database has started from pfile and not from spfile. SQL> select name, value from v$parameter where name = 'spfile';

NAME VALUE

---spfile

SQL> show parameter spfile ;

NAME TYPE VALUE

--- --- ---spfile string

The above two queries will display the spfile location if it is being used by the database only.

11. CREATE SPFILE FROM PFILE AND STARTUP THE DATABASE USING SPFILE

ANS:

Sql> create spfile from pfile; File created.

At the next startup only, the database uses the new spfile. Sql> startup

NB: If the database is running on SPFILE already, then the above operation is not possible. 12. CREATE PFILE FROM SPFILE TO A NON DEFAULT LOCATION.

ANS:

Sql>create pfile='/ora/dogrc1p/backup01/oradata/rmancat1/pfile/initrmancat1_01.ora' from spfile; File created.

13. CHECK THE ALERT LOG FOR ANY ERRORS.

ANS:

spdwd606:RMANCAT1:[/test/rmancat1/dbfiles/admin/RMANCAT1/bdump]>vi alert_RMANCAT1.log

drop tablespace JR_LOCAL_TS including contents and datafiles Sat Jun 13 03:19:07 2009

(13)

ORA-959 signalled during: drop tablespace JR_LOCAL_TS including contents and datafiles... 14. CHECK IF THERE ARE ANY OS ERRORS RELATED TO ORACLE.

ANS:

spdwd506:RMANCAT1:[/test/rmancat1/dbfiles/admin/RMANCAT1/cdump]>ls TOTAL 0

No OS related errors are found in cdump.

15. CHECK OUT WHETHER THE PRARAMETER PROCESSES IS MODIFIABLE WITHOUT STARTUP. ALTER THE NUMBER OF PROCESSES IN THE SPFILE CHANGES FOR THE CURRENT SESSION AND MAKE IT PERSISTENT ACROSS STARTUPS.

ANS:

A)

Sql> select name, isinstance_modifiable, issys_modifiable from v$parameter where name='processes'; NAME ISINS

---PROCESSES FALSE

False indicates the parameter is static, therefore cannot be altered without startup. B)

Sql> alter system set processes=180 scope=both; system altered.

16. CHECK OUT WHETHER THE PRARAMETER SESSIONS IS MODIFIABLE WITHOUT STARTUP.ALTER THE NUMBER OF SESSIONS IN THE SPFILE AND THE CHANGES TO BE TAKEN PLACE ONLY AFTER NEXT STARTUP

ANS:

Sql> select isses_modifiable, issys_modifiable from v$parameter where name='sessions'; ISSES ISSYS_MOD

--- – ---FALSE FALSE

Sql> alter system set sessions=200 scope =spfile; System altered.

(14)

17. ALTER THE SGAMAXSIZE IMMEDIATELY FOR THE CURRENT SESSION AND PERSISTENT ACROSS STARTUPS

ANS:

SQL> select name, issys_modifiable from v$parameter where name='sga_max_size';

NAME ISSYS_MOD

---sga_max_size FALSE

The FALSE value indicates that this initialization parameter cannot be modified.

18. FIND THE CURRENT DATA_BLOCK_SIZE AND FIND OUT WHETHER THAT IS THE DEFAULT ONE.

ANS:

Sql> select name, value, isdefault from v$parameter where name='db_block_size';

NAME VALUE ISDEFAULT

---db_block_size 8192 FALSE

Size can also be found from the following query. Sql> sho parameter db_block_size

NAME TYPE VALUE --- ---DB_BLOCK_SIZE INTEGER 8192

19. VIEW THE INFORMATION IN THE CONTROL FILE.

ANS:

Sql> alter database backup controlfile to trace; Database altered.

apdwd506:RMANCAT:[/test/rmancat/dbfiles/admin/RMANCAT/udump]>vi rmancat_ora_1396826.trc Dump file /test/rmancat/dbfiles/admin/RMANCAT/udump/rmancat_ora_1396826.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options

ORACLE_HOME = /usr/app/oracle/product/10.2.0 System name: AIX

(15)

Node name: apdwd506 Release: 2

Version: 5

Machine: 0004DF5ED700 Instance name: RMANCAT

Redo thread mounted by this instance: 1 Oracle process number: 35

Unix process pid: 1396826, image: oracle@apdwd506 (TNS V1-V3) *** SERVICE NAME:(SYS$USERS) 2009-06-13 03:55:20.485 *** SESSION ID:(25.129) 2009-06-13 03:55:20.485

*** 2009-06-13 03:55:20.485

-- The following are current System-scope REDO Log Archival related -- parameters and can be included in the database initialization file. ---- LOG_ARCHIVE_DEST='' -- LOG_ARCHIVE_DUPLEX_DEST='' ---- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf ---- DB_UNIQUE_NAME="RMANCAT"

---- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' -- LOG_ARCHIVE_MAX_PROCESSES=2 -- STANDBY_FILE_MANAGEMENT=MANUAL -- STANDBY_ARCHIVE_DEST=?/dbs/arch -- FAL_CLIENT='' -- FAL_SERVER='' ---- LOG_ARCHIVE_DEST_1='LOCATION=/usr/app/oracle/product/10.2.0/dbs/arch' -- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' -- LOG_ARCHIVE_DEST_STATE_1=ENABLE

---- Below are two sets of SQL statements, each of which creates a new -- control file and uses it to open the database. The first set opens "rmancat_ora_1396826.trc" 191 lines, 7353 characters

20. FIND OUT THE NUMBER OF LOG GROUPS FOR THE DATABASE AND THE NUMBER OF MEMBERS FOR EACH GROUP.

ANS:

Sql> select group#, members from v$log; GROUP# MEMBERS

(16)

1 2 2 2 3 2

21. FIND OUT HOW MANY CONTROL FILES ARE THERE FOR THE DATABASE AND THEIR LOCATIONS.

ANS:

Sql> select name from v$controlfile; NAME

---/TEST/RMANCAT/DBFILES/ORA_CONTROL1

/TEST/RMANCAT/DBFILES/ORA_CONTROL2 (OR)

sql> show parameter control file

NAME TYPE VALUE

--- --- ---CONTROL_FILE_RECORD_KEEP_TIME INTEGER 7

CONTROL_FILES STRING /TEST/RMANCAT/DBFILES/ORA_CONT ROL1, /TEST/RMANCAT/DBFILES/OR A_CONTROL2

22. FIND OUT THE ARCHIVE LOG LOCATION IF ANY.

ANS:

Sql> select destination from v$archive_dest; DESTINATION ---/USR/APP/ORACLE/PRODUCT/10.2.0/DBS/ARCH … 10 rows selected. (OR)

Sql> archive log list

Database log mode Archive Mode Automatic archival Enabled

Archive destination /usr/app/oracle/product/10.2.0/archive1/ Oldest online log sequence 14

Next log sequence to archive 16 Current log sequence 17

(17)

23. DESCRIBE V$PRAMETER VIEW AND UNDERSTAND EACH OF THE COLUMN IN THAT.

ANS:

Sql> desc v$parameter;

NAME NULL? TYPE

--- --- NUM NUMBER NAME VARCHAR2(80) TYPE NUMBER VALUE VARCHAR2(512) DISPLAY_VALUE VARCHAR2(512) ISDEFAULT VARCHAR2(9) ISSES_MODIFIABLE VARCHAR2(5) ISSYS_MODIFIABLE VARCHAR2(9) ISINSTANCE_MODIFIABLE VARCHAR2(5) ISMODIFIED VARCHAR2(10) ISADJUSTED VARCHAR2(5) ISDEPRECATED VARCHAR2(5) DESCRIPTION VARCHAR2(255) UPDATE_COMMENT VARCHAR2(255) HASH NUMBER

DESCRIPTION

NUM NUMBER Parameter number NAME VARCHAR2(80) Name of the parameter TYPE NUMBER Parameter type:

1 - Boolean 2 - String 3 - Integer 4 - Parameter file 5 - Reserved 6 - Big integer

VALUE VARCHAR2(512) Parameter value for the session (if modified within the session); otherwise, the instance-wide parameter value

DISPLAY_VALUE VARCHAR2(512) Parameter value in a user-friendly format. For example, if the VALUE column shows the value 262144 for a big integer parameter, then the DISPLAY_VALUE column will show the value 256K.

ISDEFAULT VARCHAR2(9) Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)

ISSES_MODIFIABLE VARCHAR2(5) Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)

(18)

ISSYS_MODIFIABLE VARCHAR2(9) Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:

IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.

DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions. FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.

ISINSTANCE_MODIFIABLE VARCHAR2(5) For parameters that can be changed with ALTER SYSTEm, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If the ISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.

ISMODIFIED VARCHAR2(10) Indicates whether the parameter has been modified after instance startup:

MODIFIED - Parameter has been modified with ALTER SESSION

SYSTEM_MOD - Parameter has been modified with ALTER SYSTEM (which causes all the currently logged in sessions' values to be modified)

FALSE - Parameter has not been modified after instance startup ISADJUSTED VARCHAR2(5) Indicates whether Oracle adjusted the input value to a more

suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)

ISDEPRECATED VARCHAR2(5) Indicates whether the parameter has been deprecated (TRUE) or not (FALSE)

DESCRIPTION VARCHAR2(255) Description of the parameter

UPDATE_COMMENT VARCHAR2(255) Comments associated with the most recent update HASH NUMBER Hash value for the parameter name

(19)

2 ORACLE STORAGE STRUCTURES

1. FIND OUT ALL THE TABLESPACES PRESENT IN THE DATABASE.

ANS:

Sql> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ---SYSTEM UNDO1 SYSAUX TEMP JR_LOCAL_TS1 5 rows selected. (OR)

Sql> select name from v$tablespace; NAME ---SYSTEM UNDO1 SYSAUX TEMP JR_LOCAL_TS1 5 rows selected.

2. FIND OUT ALL THE DATAFILES FOR THE TABLESPACE AND ITS PATH

ANS:

Sql> select file_name, tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME ---/TEST/RMANCAT/DBFILES/SYSTEM_01 SYSTEM /TEST/RMANCAT/DBFILES/SYSTEM_02 SYSTEM /TEST/RMANCAT/DBFILES/UNDO1_01 UNDO1 /TEST/RMANCAT/DBFILES/SYSAUX.DBF SYSAUX … 19 rows selected.

(20)

3) FIND OUT ALL THE DIFFERENT SEGMENT TYPES IN THE DATABASE

ANS:

Sql> select distinct object_type from dba_objects; OBJECT_TYPE ---INDEX JOB CLASS CONTEXT TYPE BODY PROCEDURE RESOURCE PLAN RULE SCHEDULE TABLE PARTITION WINDOW WINDOW GROUP OBJECT_TYPE ---TABLE TYPE VIEW LIBRARY FUNCTION TRIGGER PROGRAM CLUSTER SYNONYM PACKAGE BODY QUEUE OBJECT_TYPE ---CONSUMER GROUP EVALUATION CONTEXT RULE SET DIRECTORY UNDEFINED OPERATOR SEQUENCE LOB PACKAGE JOB INDEX PARTITION OBJECT_TYPE ---LOB PARTITION

(21)

34 ROWS SELECTED.

4) CREATE A LOCALLY MANAGED TABLESPACE LOCAL_TS WITH PCT_INCREASE 20,EXTENT ALLOCATION AUTO AND SIZE 100 MB

ANS:

Sql> create tablespace local_ts_pp datafile '/test/rmancat/dbfiles/local_ts_pp.dbf' size 100m autoextend on maxsize 100m default storage (initial 20m pctincrease 20);

Tablespace created.

5) ADD A DATAFILE TO THIS TABLESPACE. DATAFILE SIZE TO BE 50 MB

ANS:

Sql> alter tablespace local_ts_pp add datafile '/test/rmancat/dbfiles/local_ts_pp_01.dbf' size 50m; Tablespace altered.

6) CREATE A BIG FILE TABLESPACE

ANS:

Sql> create bigfile tablespace tbs_pp datafile '/test/rmancat/dbfiles/tbs_pp.dbf' size 100m; Tablespace created.

7) TAKE THE TABLESPACE LOCAL_TS OFFLINE/ONLINE

ANS:

Sql> alter tablespace local_ts_pp offline; Tablespace altered.

Sql> select tablespace_name, status from dba_tablespaces where tablespace_name='LOCAL_TS_PP'; TABLESPACE_NAME STATUS

--- ---LOCAL_TS_PP OFFLINE Sql> alter tablespace local_ts_pp online; Tablespace altered.

Sql> select tablespace_name,status from dba_tablespaces where tablespace_name='LOCAL_TS_PP'; TABLESPACE_NAME STATUS

--- ---LOCAL_TS_PP ONLINE

(22)

8) PUT THE TABLESPACE IN BACKUP MODE

ANS:

Sql> alter tablespace local_ts_pp begin backup; Tablespace altered.

9) CREATE A TEMPORARY TABLESPACE

ANS:

Sql> create temporary tablespace temp_pp tempfile '/test/rmancat/dbfiles/temp_pp' size 10M; Tablespace created.

10) CHECK HOW THE EXTENT MANAGEMENT IS HAPPENING FOR THE SYSTEM TABLESPACE

ANS:

Sql> select tablespace_name, extent_management from dba_tablespaces where tablespace_name='SYSTEM';

TABLESPACE_NAME EXTENT_MAN --- ---SYSTEM LOCAL

11) REDUCE THE DATAFILE SIZE TO BE 30 MB

ANS:

Sql> alter database datafile '/test/rmancat/dbfiles/tbs_pp.dbf' resize 30M; Database altered.

Sql>select file_name, bytes/1024/1024 from dba_data_files where file_name = '/test/rmancat/dbfiles/tbs_pp.dbf';

FILE_NAME BYTES/1024/1024

---/TEST/RMANCAT/DBFILES/TBS_PP.DBF 30

12) INCREASE THE DATAFILE SIZE TO 75 MB

ANS:

Sql>alter database datafile '/test/rmancat/dbfiles/tbs_pp.dbf' resize 75M; Database altered.

Sql>select file_name, bytes/1024/1024 from dba_data_files where file_name = '/test/rmancat/dbfiles/tbs_pp.dbf';

(23)

FILE_NAME BYTES/1024/1024 ---/TEST/RMANCAT/DBFILES/TBS_PP.DBF 75

13) DROP THE DATAFILE

ANS:

Sql> alter tablespace users01 drop datafile '/ora/dogrc1p/backup01/oradata/rmancat1/dbfiles/test.dbf'; Tablespace altered.

14) CREATE A TABLE PROJECT WITH FOLLOWING COLUMNS IN THE TABLESPACE LOCAL_TS

ID NOT NULL NUMBER

PROJCLASS NOT NULL VARCHAR2(8) DESCRIPTION NOT NULL VARCHAR2(40) SHORTDESC NOT NULL VARCHAR2(15) DATABASE2 VARCHAR2(15)

BURDENCODE VARCHAR2(8) APPLYBLABOR NOT NULL NUMBER(1) APPLYBMATERIALS NOT NULL NUMBER(1) APPLYBEQUIPMENT NOT NULL NUMBER(1) APPLYBEXPENSES NOT NULL NUMBER(1) OVERHEADCODE VARCHAR2(8) APPLYOLABOR NOT NULL NUMBER(1) APPLYOMATERIALS NOT NULL NUMBER(1) APPLYOEQUIPMENT NOT NULL NUMBER(1) APPLYOEXPENSES NOT NULL NUMBER(1)

ANS:

Sql> create table project_pp (id number not null, projclass varchar2(8) not null,

description varchar2(40) not null, shortdesc varchar2(15) not null,

database2 varchar2(15), burdencode varchar2(8), applyblabor number(1) not null, applybmaterials number(1) not null, applybequipment number(1) not null, applybexpenses number(1) not null, overheadcode varchar2(8),

applyolabor number(1) not null, applyomaterials number(1) not null, applyoequipment number(1) not null,

(24)

applyoexpenses number(1) not null) tablespace local_ts_pp; Table created.

Sql> desc project_pp;

NAME NULL? TYPE

--- --- ID NOT NULL NUMBER

PROJCLASS NOT NULL VARCHAR2(8) DESCRIPTION NOT NULL VARCHAR2(40) SHORTDESC NOT NULL VARCHAR2(15) DATABASE2 VARCHAR2(15) BURDENCODE VARCHAR2(8) APPLYBLABOR NOT NULL NUMBER(1) APPLYBMATERIALS NOT NULL NUMBER(1) APPLYBEQUIPMENT NOT NULL NUMBER(1) APPLYBEXPENSES NOT NULL NUMBER(1) OVERHEADCODE VARCHAR2(8) APPLYOLABOR NOT NULL NUMBER(1) APPLYOMATERIALS NOT NULL NUMBER(1) APPLYOEQUIPMENT NOT NULL NUMBER(1) APPLYOEXPENSES NOT NULL NUMBER(1)

Sql> select table_name, tablespace_name from dba_tables where table_name ='PROJECT_PP'; TABLE_NAME TABLESPACE_NAME

--- ---PROJECT_PP LOCAL_TS_PP

15) CREATE THE FOLLOWING TABLE AUDIT WITH CLOB IN A BIG FILE TABLESPACE

ID NOT NULL NUMBER SESSION_ID NOT NULL NUMBER NAME NOT NULL VARCHAR2(80) VALUE CLOB

CREATED_DATE NOT NULL DATE CREATED_BY NOT NULL NUMBER LAST_UPDATED_DATE NOT NULL DATE LAST_UPDATED_BY NOT NULL NUMBER

ANS:

Sql> create table audit_pp (id number not null,

Session_id number not null, Name varchar2(80) not null, Value clob,

(25)

Created_by number not null, Last_updated_date date not null,

Last_updated_by number not null) tablespace tbs_pp; Table created.

Sql> desc audit_pp

NAME NULL? TYPE

SESSION_ID NOT NULL NUMBER NAME NOT NULL VARCHAR2(80) VALUE CLOB

CREATED_DATE NOT NULL DATE CREATED_BY NOT NULL NUMBER LAST_UPDATED_DATE NOT NULL DATE LAST_UPDATED_BY NOT NULL NUMBER

16) CREATE PRIMARY KEY ON TABLE PROJECT ON THE COLUMN ID. CREATE FOREIGN KEY ON ID COLUMN IN AUDIT TABLE REFERRING TO PROJECT

ANS:

(A)

Sql> alter table project_pp add constraint pro_pk_pp primary key(id); Table altered.

(B)

Sql> alter table audit_pp add constraint aud_fk_pp foreign key(id) references project_pp(id); Table altered.

17) DROP THE COLUMN DESCRIPTION IN TABLE PROJECT AND ALTER THE TABLE AUDIT TO MODIFY THE COLUMN LAST_UPDATED_DATE AS LAST_UPDATED_TIMESTAMP, WITH DEFAULT VALUE AS CURRENT TIMESTAMP

ANS:

(A)

Sql> alter table project_pp drop column description; Table altered.

(26)

Sql> alter table newaudit_pp rename column last_updated_date to last_updated_timestamp; Table altered.

Sql> alter table newaudit_pp modify (last_updated_timestamp default systimestamp); Table altered

18) RENAME THE TABLE AUDIT AS NEWAUDIT AND CHECK OUT THE FOREIGN KEY ON NEWAUDIT IS STILL IN VALID CONDITION

ANS:

(A)

Sql> alter table audit_pp rename to newaudit_pp; Table altered.

Sql> desc newaudit_pp;

NAME NULL? TYPE

CREATED_DATE NOT NULL DATE CREATED_BY NOT NULL NUMBER LAST_UPDATED_DATE NOT NULL DATE LAST_UPDATED_BY NOT NULL NUMBER (B)

Sql>select constraint_name, constraint_type, table_name, status, invalid from dba_constraints where constraint_name='aud_fk_pp';

CONSTRAINT_NAME C TABLE_NAME STATUS INVALID - --- ---AUD_FK_PP R NEWAUDIT_PP ENABLED

19) CREATE A TEMPORARY TABLE WHICH HAS GOT SAME COLUMNS AS AUDIT IN QUESTION 15

ANS:

Sql> create global temporary table temp_aud_pp as select * from audit_pp; Table created.

(27)

Sql> desc temp_aud_pp

NAME NULL? TYPE

CREATED_DATE NOT NULL DATE CREATED_BY NOT NULL NUMBER LAST_UPDATED_TIMESTAMP NOT NULL DATE LAST_UPDATED_BY NOT NULL NUMBER

20) CREATE AN UNIQUE INDEX ON COLUMN AUDIT(ID). CONCATENATED INDEX ON PROJECT (PROJCLASS, DESCRIPTION, SHORTDESC)

ANS:

(A)

Sql> create unique index idx_aud_id on audit_pp(id); Index created.

(B)

WE HAVE DELETED THE DESCRIPTIOPN COLUMN FROM THE TABLE SO CREATING ONCE AGAIN.

Sql> alter table project_pp add(description varchar2(40) not null); Table altered.

Sql> desc project_pp

NAME NULL? TYPE

PROJCLASS NOT NULL VARCHAR2(8) SHORTDESC NOT NULL VARCHAR2(15) DATABASE2 VARCHAR2(15) BURDENCODE VARCHAR2(8) APPLYBLABOR NOT NULL NUMBER(1) APPLYBMATERIALS NOT NULL NUMBER(1) APPLYBEQUIPMENT NOT NULL NUMBER(1) APPLYBEXPENSES NOT NULL NUMBER(1) OVERHEADCODE VARCHAR2(8) APPLYOLABOR NOT NULL NUMBER(1) APPLYOMATERIALS NOT NULL NUMBER(1)

(28)

APPLYOEQUIPMENT NOT NULL NUMBER(1) APPLYOEXPENSES NOT NULL NUMBER(1) DESCRIPTION NOT NULL VARCHAR2(40)

Sql> create index idx_pro_3 on project_pp (projclass, description, shortdesc); Index created.

21) CREATE A INDEX ON PROJECT(OVERHEADCODE).

ANS:

Sql> create index idx_proj_pp on project_pp(overheadcode); Index created.

22) FIND OUT ALL THE INDEXES CREATED ON THE TABLE PROJECT.

ANS:

Sql> select owner,index_name,table_name from dba_indexes where table_name ='project_pp'; OWNER INDEX_NAME TABLE_NAME

--- ---MAGU PRO_PK_PP PROJECT_PP MAGU IDX_PRO_3 PROJECT_PP MAGU IDX_PROJ_PP PROJECT_PP

23) CREATE A VIEW SAMPLE ON THE TABLES PROJECT, ADUIT MATCHED BY COLUMN ID, CONTAINING THE FOLLOWING COLUMNS

ID NOT NULL NUMBER

PROJCLASS NOT NULL VARCHAR2(8) DESCRIPTION NOT NULL VARCHAR2(40) SHORTDESC NOT NULL VARCHAR2(15) DATABASE2 VARCHAR2(15)

BURDENCODE VARCHAR2(8)

SESSION_ID NOT NULL NUMBER NAME NOT NULL VARCHAR2(80)

LAST_UPDATED_DATE NOT NULL DATE LAST_UPDATED_BY NOT NULL NUMBER

ANS:

Sql> create or replace view sample_pp As select p.id,projclass, shortdesc, Database2,burdencode,session_id,name, Last_updated_timestamp,last_updated_by

(29)

From project_pp p,audit_pp a Where p.id=a.id;

View created.

24) CREATE VIEW TEST WHICH DOES A SELECT ON THE TABLE AUDIT AND DOES A GROUP BY COLUMN CREATED_DATE.

ANS:

Sql> create view test_pp as select count(*) col1,created_date from audit_pp group by created_date; View created.

Sql> desc test_pp

NAME NULL? TYPE

--- --- COL1 NUMBER

CREATED_DATE NOT NULL DATE

25) TRY TO INSERT INTO THE VIEW SAMPLE AND TEST.

ANS:

Sql> insert into sample_pp values(001,'a','new','rmancat1','a12',57,'magu','21-may-09',3);

INSERT INTO SAMPLE_PP VALUES(001,'A','NEW','RMANCAT1','A12',57,'MAGU','21-MAY-09',3)

*

ERROR AT LINE 1:

ORA-01776: CANNOT MODIFY MORE THAN ONE BASE TABLE THROUGH A JOIN VIEW (B)

Sql> insert into test_pp values(1,sysdate); Insert into test_pp values(1,sysdate) *

ERROR AT LINE 1:

ORA-01733: VIRTUAL COLUMN NOT ALLOWED HERE

26) CREATE A SEQUENCE ID_SEQ THAT STARTS FROM 1 AND HAS GOT A MAXIMUM VALUE 1000000 WHICH INCREMENTS BY 5.

ANS:

Sql> create sequence seq_pp 2 start with 1

3 maxvalue 1000000 4 increment by 5;

(30)

Sequence created.

27) FIND OUT THE NEXT VALUE, PREVIOUS VALUE AND CURRENT VALUE OF ID_SEQ.

ANS:

Sql> select seq_pp.nextval from dual; NEXTVAL

1

Sql> select seq_pp.currval from dual; CURRVAL

1

THERE IS NO PREVIOUS VALUE OPTION IN ANY SEQUENCE. 28) CREATE A TABLE EMPLOYEE WITH COLUMNS ID NUMBER FNAME VARCHAR2(100) LNAME VARCHAR2(100) SEX CHAR(1) EMAIL VARCHAR2(50) ANS:

Sql> create table employee_pp (id number, Fname varchar2(100), Lname varchar2(100), Sex char(1), Email varchar2(50)); Table created.

29) INSERT 5 RECORDS INTO THE TABLE EMPLOYEE BY SELECTING VALUES FROM THE SEQUENCE

ANS:

Sql> insert into employee_pp values (seq_pp.nextval, '&fname', '&lname', '&sex', '&email'); Sql> select * from employee_pp;

(31)

- 11 PURBASHA SAHOO F [email protected] 16 PRIYA RAY F [email protected] 21 BIRAJA MOH M [email protected] 26 SATISH PARIDA M [email protected] 31 KUNAL KAPOOR M [email protected]

30) RESTART THE SEQUENCE FROM 1000 AND SELECT VALUES FROM THE SEQUENCE

ANS:

TO RESTART ANY SEQUENCE AT A DIFFERENT NUMBER, IT MUST BE DROPPED AND RE-CREATED.

A)

Sql> drop sequence seq; Sequence dropped. Sql> create sequence seq 2 start with 1000 3 increment by 4; Sequence created.

WE CAN NOT SELECT CURRENT VALUE OF A SEQUENCE FOR THE FIRST TIME AFTER ITS CREATION, IT HAS TO BE PRECEEDED BY SELECTING NEXTVALUE ONCE

B)

Sql> select seq.nextval from dual; NEXTVAL

1000

Sql> select seq.currval from dual; CURRVAL

1000

31) FIND OUT THE NUMBER OF TABLES, VIEWS, PROCEDURES, FUNCTIONS, AND SEQUENCES ON YOUR DATABASE.

(32)

ANS:

SQL> SELECT COUNT(*),OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW','PROCEDURE','FUNCTION','SEQUENCE') GROUP BY OBJECT_TYPE;

COUNT(*) OBJECT_TYPE --- 44 PROCEDURE 966 TABLE 3071 VIEW 59 FUNCTION 106 SEQUENCE

32) FIND OUT THE NUMBER OF TABLES, VIEWS, PROCEDURES, FUNCTIONS, AND SEQUENCES ON YOUR DATABASE FOR EACH OWNER.

ANS:

SQL> SELECT OWNER,OBJECT_TYPE,COUNT(*) FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW','PROCEDURE','FUNCTION','SEQUENCE') GROUP BY OWNER,OBJECT_TYPE;

OWNER OBJECT_TYPE COUNT(*) --- ---SYS VIEW 2949 WMSYS VIEW 102 WMSYS PROCEDURE 1 TEST260_2 TABLE 4 DBSNMP TABLE 21 DBSNMP SEQUENCE 2 DBSNMP VIEW 1 SYSTEM VIEW 12 OUTLN PROCEDURE 1 WMSYS TABLE 42 WMSYS FUNCTION 3 MAGU TABLE 54 MAGU VIEW 7 MAGU PROCEDURE 2 SYS SEQUENCE 71 SYSTEM TABLE 139 SYS PROCEDURE 39 SYS FUNCTION 56 WMSYS SEQUENCE 9 SYSTEM SEQUENCE 20 MINU TABLE 1 SYS TABLE 695 OUTLN TABLE 3 SYSTEM PROCEDURE 1 MAGU SEQUENCE 4 NTEST3 TABLE 4

(33)

NTEST1 TABLE 1 TEST260_1 TABLE 1 TSMSYS TABLE 1

33) TRUNCATE THE TABLE AUDIT AND DROP STORAGE.

ANS:

SQL> TRUNCATE TABLE AUDIT_PP DROP STORAGE; TABLE TRUNCATED.

34) FIND OUT THE SIZE OF EACH TABLESPACE AND THE FREE SPACE AVAILABLE. ANS: SQL> SELECT * FROM ( 2 SELECT C.TABLESPACE_NAME, ROUND(A.BYTES/1048576,2) MB_ALLOCATED, 3 4 ROUND(B.BYTES/1048576,2) MB_FREE, 5 ROUND((A.BYTES-B.BYTES)/1048576,2) MB_USED, 6 ROUND(B.BYTES/A.BYTES * 100,2) TOT_PCT_FREE, 7 ROUND((A.BYTES-B.BYTES)/A.BYTES,2) * 100 TOT_PCT_USED 8 FROM (SELECT TABLESPACE_NAME,

9 SUM(A.BYTES) BYTES 10 FROM SYS.DBA_DATA_FILES A GROUP BY TABLESPACE_NAME) A, 11 12 (SELECT A.TABLESPACE_NAME, 13 NVL(SUM(B.BYTES),0) BYTES 14 FROM SYS.DBA_DATA_FILES A, 15 SYS.DBA_FREE_SPACE B

16 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+) 17 AND A.FILE_ID = B.FILE_ID (+)

GROUP BY A.TABLESPACE_NAME) B, 18 19 SYS.DBA_TABLESPACES C

20 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME = C.TABLESPACE_NAME

21 22 ) WHERE TOT_PCT_USED >=0 23 ORDER BY TABLESPACE_NAME;

TABLESPACE_NAME MB_ALLOCATED MB_FREE MB_USED TOT_PCT_FREE --- --- --- ---TOT_PCT_USED ---AMRUTHA 500 499.69 .31 99.94 0 AMRU_LOCAL_TS 200 199.88 .13 99.94 0

(34)

: :

27 ROWS SELECTED.

35) FIND OUT THE SIZE OF EACH DATAFILE AND THE FREE SPACE AVAILABLE IN EACH OF THE DATAFILE.

ANS:

SQL> SELECT DF.AUTOEXTENSIBLE

"AUTOEXTENT",SUBSTR(DF.TABLESPACE_NAME,1,20) "TABLESPACE NAME", 2 SUBSTR(DF.FILE_NAME,1,50) "FILE NAME",

3 ROUND(DF.BYTES/1024/1024,2) "SIZE (M)",

4 ROUND(DF.MAXBYTES/1024/1024,2) "SIZE (MAXMBYTES)", 5 ROUND(E.USED_BYTES/1024/1024,2) "USED (M)",

6 ROUND(F.FREE_BYTES/1024/1024,2) "FREE (M)",

7 RPAD(' '|| RPAD ('X',ROUND(E.USED_BYTES*10/DF.BYTES,0), 'X'),11,'-') "% USED" FROM DBA_DATA_FILES DF,

(SELECT FILE_ID,

8 SUM(DECODE(BYTES,NULL,0,BYTES)) USED_BYTES FROM DBA_EXTENTS

9 10 11 12 GROUP BY FILE_ID) E, (SELECT MAX(BYTES) FREE_BYTES, 13 14 FILE_ID

15 FROM DBA_FREE_SPACE 16 GROUP BY FILE_ID) F

17 WHERE DF.TABLESPACE_NAME='LOCAL_TS_RS' AND E.FILE_ID (+) = DF.FILE_ID 18 AND DF.FILE_ID = F.FILE_ID (+)

19 ORDER BY DF.TABLESPACE_NAME, 20 DF.FILE_NAME;

AUT TABLESPACE NAME FILE NAME

--- ---

SIZE (M) SIZE (MAXMBYTES) USED (M) FREE (M) % USED --- --- --- ---

---NO LOCAL_TS_RS /TEST/RMANCAT/DBFILES/LOCAL_TS_RS.DBF 20 0 .06 19.88

---36) FIND OUT WHETHER A TABLESPACE IS AUTO EXTENDIBLE OR NOT.

ANS:

SQL> SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME= ‘LOCAL_TS_PP';

(35)

---/ora/DOGRC1P/backup01/oradata/RMANCAT/dbfiles/LOCAL_TS_PP.dbf

LOCAL_TS_PP NO

37) FIND THE PRIMARY KEYS AVAILABLE IN ALL THE TABLE AND CHECK IN WHAT TABLES THEY ARE REFERRED TO.

ANS:

SQL> SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE=’P’; OWNER CONSTRAINT_NAME ---TABLE_NAME ---DBSNMP BSLN_BASELINES_PK MGMT_BSLN_BASELINES DBSNMP BSLN_DATASOURCES_PK MGMT_BSLN_DATASOURCES . . . . 376 ROWS SELECTED.

38) CHECK WHETHER THERE ARE ANY INVALID INDEXES

ANS:

SQL> SELECT OWNER,INDEX_NAME,STATUS FROM DBA_INDEXES WHERE STATUS != 'VALID';

OWNER INDEX_NAME STATUS --- ---

---SYS WRH$_SERVICE_WAIT_CLASS_PK N/A SYS WRH$_SERVICE_STAT_PK N/A SYS WRH$_TABLESPACE_STAT_PK N/A SYS WRH$_ACTIVE_SESSION_HISTORY_PK N/A SYS WRH$_INST_CACHE_TRANSFER_PK N/A SYS WRH$_DLM_MISC_PK N/A

SYS WRH$_SEG_STAT_PK N/A .

. . .

(36)

39) REBUILD ALL THE INDEXES SUCH THAT IT WILL NOT CAUSE ANY OUTAGE TO THE TABLE.

ANS:

SQL>ALTER INDEX INDEX_PP REBUILD ONLINE;

INDEX ALTERED.

40) DISABLE THE FOREIGN KEY CONSTRAINT ON THE AUDIT TABLE AND INSERT A RECORD INTO AUDIT AND ENABLE THE CONSTRAINT AGAIN

ANS:

(A)

SQL> ALTER TABLE AUDIT_PP DISABLE CONSTRAINT AUD_FK_PP; TABLE ALTERED.

(B)

SQL> INSERT INTO AUDIT_PP VALUES(003,78,'DIPA','CLOB_TRIED',SYSDATE,4, SYSDATE,5);

1 ROW CREATED.

SQL> SELECT * FROM AUDIT_PP; ID SESSION_ID ---NAME ---VALUE ---CREATED_D CREATED_BY LAST_UPDA LAST_UPDATED_BY --- --- --- 3 78 DIPA CLOB_TRIED 20-JUN-09 4 20-JUN-09 5 (C)

(37)

SQL> ALTER TABLE AUDIT_PP ENABLE CONSTRAINT AUD_FK_PP; ALTER TABLE AUDIT_PP ENABLE CONSTRAINT AUD_FK_PP *

ERROR AT LINE 1:

(38)

3 USER ADMINISTRATION AND SECURITY

1) CREATE A USER TEST AND PASSWORD TEST123 WITH DEFAULT TABLESPACE AS USERS AND ACCOUNT UNLOCKED AND THE USER HAS TO CHANGE THE PASSWORD ON NEXT LOGIN

ANS:

SQL> CREATE USER TEST_PP IDENTIFIED BY TEST123 DEFAULT TABLESPACE USERS ACCOUNT UNLOCK PASSWORD EXPIRE;

USER CREATED

.

2) CREATE A USER TEST1 AND PASSWORD TEST1234 WITH DEFAULT TABLESPACE AS USERS WITH 10MB QUOTA ON THE TABLESPACE – ACCOUNT UNLOCKED

ANS:

SQL> CREATE USER TEST1_PP IDENTIFIED BY TEST1234 QUOTA 10M ON USERS ACCOUNT UNLOCK;

USER CREATED.

3) CREATE A TABLE EMPLOYEE WITH COLUMNS EMPNO, EMPNAME AND EMAIL IN THE SCHEMA TEST AND INSERT SOME SAMPLE RECORDS

ANS:

SQL> CONN TEST_PP ENTER PASSWORD: ERROR:

ORA-28001: THE PASSWORD HAS EXPIRED CHANGING PASSWORD FOR TEST_PP NEW PASSWORD:

RETYPE NEW PASSWORD: ERROR:

ORA-01045: USER TEST_PP LACKS CREATE SESSION PRIVILEGE; LOGON DENIED SQL> CREATE TABLE EMPLOYEE_PP(EMPNO NUMBER,

2 EMPNAME VARCHAR2(40), 3 EMAIL VARCHAR2(40));

(39)

TABLE CREATED.

SQL> INSERT INTO EMPLOYEE_PP VALUES(100, 'AAA','[email protected]'); 1 ROW CREATED.

SQL> INSERT INTO EMPLOYEE_PP VALUES(101,'BBB','[email protected]'); 1 ROW CREATED.

SQL> INSERT INTO EMPLOYEE_PP VALUES(102,'CCC','[email protected]'); 1 ROW CREATED.

4) OPEN AN OTHER SESSION AND TRY TO DROP THE USER TEST. DO ALL THAT IS POSSIBLE TO DROP THE USER TEST.

ANS:

SQL> DROP USER TEST_PP; DROP USER TEST_PP *

ERROR AT LINE 1:

ORA-01940: CANNOT DROP A USER THAT IS CURRENTLY CONNECTED

SQL> SELECT SID,SERIAL#,USERNAME FROM V$SESSION WHERE USERNAME ='TEST_PP'; SID SERIAL# USERNAME

--- --- 281 921 TEST_PP

SQL> ALTER SYSTEM KILL SESSION '281,921'; SYSTEM ALTERED.

THE SESSION IS KILLED

SQL> SELECT * FROM V$SESSION; SELECT * FROM V$SESSION *

ERROR AT LINE 1:

ORA-00028: YOUR SESSION HAS BEEN KILLED

5) FIND OUT WHO ARE THE USERS IN THE DATABASE AND ALL THEIR DEFAULT TABLESPACE AND DEFAULT TEMPORARY TABLESPACE

(40)

ANS:

SQL> SELECT USERNAME, DEFAULT_TABLESPACE , TEMPORARY_TABLESPACE FROM DBA_USERS; USERNAME DEFAULT_TABLESPACE ---TEMPORARY_TABLESPACE ---STUDENT USERS TEMP MAGU USERS TEMP SYSTEM SYSTEM TEMP . . . 24 ROWS SELECTED.

6) FIND OUT ALL THE USERS CREATED ON THE DATABASE BEFORE TODAY

ANS:

SQL> SELECT USERNAME,CREATED FROM DBA_USERS WHERE CREATED<SYSDATE; USERNAME CREATED --- ---STUDENT 20-JUN-09 MAGU 09-JUN-09 SYSTEM 04-JUN-09 SYS 04-JUN-09 RAMYA 20-JUN-09 HEADMASTER 20-JUN-09 NHEADMASTER 20-JUN-09 TEST_PP 20-JUN-09 DIVYA 16-JUN-09 NAYAK 16-JUN-09 NTEST1 20-JUN-09 OUTLN 04-JUN-09 MINU 16-JUN-09 PERFSTAT 16-JUN-09 TEST1_PP 20-JUN-09 NTEST3 20-JUN-09 TEST260_1 20-JUN-09 NTEACHER 20-JUN-09 TEACHER 20-JUN-09 TEST260_2 20-JUN-09

(41)

DIP 04-JUN-09 TSMSYS 04-JUN-09 WMSYS 04-JUN-09 DBSNMP 04-JUN-09 24 ROWS SELECTED.

7) FIND OUT WHICH ALL THE USERS HAVE GOT UNLIMITED QUOTA ON THE TABLESPACES

ANS:

SQL> SELECT USERNAME, TABLESPACE_NAME FROM DBA_TS_QUOTAS WHERE MAX_BYTES=-1; USERNAME TABLESPACE_NAME ---PERFSTAT JR_LOCAL_TS MINU RISHMA NAYAK JR_LOCAL_TS

8) CONNECT AS USER TEST1 AND SEE WHETHER YOU ARE ABLE TO CONNECT TO THE DATABASE. GRANT ALL THE PRIVILEGES THAT ARE NECESSARY TO CONNECT TO THE DATABASE

ANS:

SQL> GRANT CREATE SESSION TO TEST1_PP; GRANT CREATE SESSION TO TEST1_PP *

ERROR AT LINE 1:

ORA-01031: INSUFFICIENT PRIVILEGES

SQL> GRANT CREATE SESSION TO TEST_PP WITH ADMIN OPTION; GRANT SUCCEEDED.

SQL> GRANT CREATE SESSION TO TEST1_PP; GRANT SUCCEEDED.

9) TRY TO CREATE A TABLE SAMPLE IN TEST1 AND SEE WHETHER YOU ARE ABLE TO CREATE THE TABLE. IF NOT GRANT ALL THE REQUIRED PRIVILEGES TO CREATE THE TABLE

ANS:

SQL> GRANT CREATE SESSION, CREATE ANY TABLE TO TEST1_PP; GRANT SUCCEEDED.

(42)

SQL> CONN TEST1_PP ENTER PASSWORD: CONNECTED.

SQL> CREATE TABLE SAMPLE (ID NUMBER,NAME VARCHAR2(20)); TABLE CREATED.

10) CREATE AN OTHER USER TEST2 AND GRANT HIM SELECT, INSERT, UPDATE AND DELETE ON THE TABLE SAMPLE WHICH IS IN THE SCHEMA TEST1.

ANS:

SQL> CREATE USER TEST2_PP IDENTIFIED BY TEST2_PP ; USER CREATED.

SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON TEST1_PP.SAMPLE TO TEST2_PP; GRANT SUCCEEDED.

11) CREATE THE SAME TABLE EMPLOYEE IN TEST2 SCHEMA AND GRANT ALL PRIVILEGES TO TEST1

ANS:

SQL> CREATE TABLE EMPLOYEE1_PP(EMPNO NUMBER, 2 EMPNAME VARCHAR2(40),

3 EMAIL VARCHAR2(40)); TABLE CREATED.

SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON EMPLOYEE1_PP TO TEST1_PP; GRANT SUCCEEDED.

12) CREATE THE FOLLOWING TABLES IN TEST2 SCHEMA STUDENT TABLE: ROLLNO NUMBER(5) NAME VARCHAR2(20) AVERAGE NUMBER(5) SEMESTER CHAR(1) AGE NUMBER(5)

PRIMARY KEY ON ROLLNO SUBJECT TABLE:

(43)

SUBID CHAR(1)

SUB VARCHAR(10)

TUTOR VARCHAR(10)

PRIMARY KEY ON SUBID MARKS TABLE:

ROLLNO NUMBER(5)

SUBID CHAR(1)

MARKS NUMBER(5)

FOREIGN KEY ON ROLLNO AND SUBID REFERENCING STUDENT AND SUBJECT TABLE

ANS:

SQL> CREATE TABLE STUDENT_PP (ROLLNO NUMBER(5),

2 NAME VARCHAR2(20), 3 AVERAGE NUMBER(5), 4 SEMESTER CHAR(1), 5 AGE NUMBER(5),

6 CONSTRAINT STUPK1 PRIMARY KEY(ROLLNO)); TABLE CREATED.

SQL> CREATE TABLE SUBJECT_PP (SUBID CHAR(1),

2 SUB VARCHAR2(10), 3 TUTOR VARCHAR2(10),

4 CONSTRAINT SUBPK1 PRIMARY KEY(SUBID)); TABLE CREATED.

SQL> CREATE TABLE MARKS_PP (ROLLNO NUMBER(5),

2 SUBID CHAR(1), 3 MARKS NUMBER(5),

4 CONSTRAINT MARKSFK1 FOREIGN KEY (ROLLNO) REFERENCES STUDENT_PP(ROLLNO),

5 CONSTRAINT MARKSFK2 FOREIGN KEY (SUBID) REFERENCES SUBJECT_PP(SUBID));

TABLE CREATED.

13) CREATE A ROLE TUTOR SUCH THAT IT HAS SELECT,INSERT,UPDATE,DELETE ON MARKS TABLE AND SELECT ON STUDENT TABLE

(44)

ANS:

SQL> CREATE ROLE TUTOR_PP; ROLE CREATED.

SQL> GRANT SELECT, INSERT,UPDATE,DELETE ON TEST2_PP.MARKS_PP TO TUTOR_PP; GRANT SUCCEEDED.

SQL> GRANT SELECT ON TEST2_PP.STUDENT_PP TO TUTOR_PP; GRANT SUCCEEDED.

14) CREATE ROLE HM SUCH THAT IT HAS SELECT,INSERT,UPDATE AND DELETE ON SUBJECT TABLE AND SELECT ON MARKS TABLE AND STUDENT TABLE

ANS:

SQL> CREATE ROLE HM_PP; ROLE CREATED.

SQL> GRANT SELECT ,INSERT,UPDATE, DELETE ON TEST2_PP.SUBJECT_PP TO HM_PP; GRANT SUCCEEDED.

SQL> GRANT SELECT ON TEST2_PP.MARKS_PP TO HM_PP; GRANT SUCCEEDED.

SQL> GRANT SELECT ON TEST2_PP.STUDENT_PP TO HM_PP; GRANT SUCCEEDED.

15) CREATE USER TEACHER AND GRANT THE ROLE TUTOR TO HIM AND CREATE USER HEADMASTER AND GRANT THE ROLE HM TO HIM

ANS:

SQL> CREATE USER TEACHER_PP IDENTIFIED BY PASSWORD; USER CREATED.

(45)

GRANT SUCCEEDED.

SQL> CREATE USER HEADMASTER_PP IDENTIFIED BY PASSWORD; USER CREATED.

SQL> GRANT HM_PP TO HEADMASTER_PP; GRANT SUCCEEDED.

16) REVOKE THE SELECT PRIVILEGE OF TUTOR FROM MARKS AND STUDENTS TABLE AND REVOKE THE DELTE PRIVILEGE FROM HM ROLE ON SUBJECT TABLE

ANS:

SQL> REVOKE SELECT ON TEST2_PP.MARKS_PP FROM TUTOR_PP; REVOKE SUCCEEDED.

SQL> REVOKE SELECT ON TEST2_PP.STUDENT_PP FROM TUTOR_PP; REVOKE SUCCEEDED.

SQL> REVOKE DELETE ON TEST2_PP.SUBJECT_PP FROM HM_PP; REVOKE SUCCEEDED.

17) FIND OUT WHAT ARE THE ROLES IN THE DATABASE

ANS:

SQL> SELECT ROLE FROM DBA_ROLES; ROLE ---CONNECT RESOURCE DBA SELECT_CATALOG_ROLE EXECUTE_CATALOG_ROLE DELETE_CATALOG_ROLE HS_ADMIN_ROLE OEM_ADVISOR OEM_MONITOR TUTOR_PP : : TUTOR_RIS HM_PP

(46)

JR_HM

25 ROWS SELECTED.

18) FIND OUT WHAT PRIVILEGES EACH ROLE HAS GOT

ANS:

WE CAN QUERY ROLE_SYS_PRIVS FOR SYSTEM PRIVILEGES AND ROLE_TAB_PRIVS FOR OBJECT PRIVILEGES.

SQL> SELECT ROLE ,PRIVILEGE FROM ROLE_SYS_PRIVS WHERE ROLE='DBA'; ROLE PRIVILEGE

--- ---DBA CREATE TABLE

DBA DELETE ANY TABLE .

.

DBA ALTER ANY RULE

DBA EXECUTE ANY PROGRAM DBA EXECUTE ANY CLASS DBA MANAGE SCHEDULER DBA MANAGE ANY FILE GROUP 160 ROWS SELECTED.

SQL> SELECT ROLE, PRIVILEGE, TABLE_NAME FROM ROLE_TAB_PRIVS WHERE ROLE='TUTOR_PP';

ROLE PRIVILEGE TABLE_NAME

---TUTOR_PP UPDATE MARKS_PP

TUTOR_PP DELETE MARKS_PP TUTOR_PP INSERT MARKS_PP

19) CHECK OUT WHAT ARE THE PRIVILEGES YOU HAVE FOR YOUR SESSION

ANS:

SQL> SELECT * FROM SESSION_PRIVS; PRIVILEGE ---ALTER SYSTEM AUDIT SYSTEM CREATE SESSION ALTER SESSION

(47)

ALTER ANY TABLE .

. .

CREATE ANY DIRECTORY DROP ANY DIRECTORY CREATE TYPE

CREATE ANY TYPE CREATE EXTERNAL JOB 166 rows selected.

20) MAKE THE ROLE HM AS PASSWORD ENABLED.

ANS:

SQL> ALTER ROLE HM_PP IDENTIFIED BY PASSWORD; ROLE ALTERED.

21) CREATE USER STUDENT AND CREATE ROLE STUD WHICH HAS GOT ONLY SELECT ON THE TABLE STUDENT.

ANS:

SQL> CREATE USER STUDENT_PP IDENTIFIED BY PASSWORD; USER CREATED.

SQL> CREATE ROLE STUD_PP; ROLE CREATED.

SQL> GRANT SELECT ON TEST2_PP.STUDENT_PP TO STUD_PP; GRANT SUCCEEDED.

22) GRANT THE ROLE STUD TO STUDENT

ANS:

SQL> GRANT STUD_PP TO STUDENT_PP; GRANT SUCCEEDED.

(48)

23) MAKE STUD AS STUDENT’S DEFAULT ROLE

ANS:

SQL> ALTER USER STUDENT_PP DEFAULT ROLE STUD_PP; USER ALTERED.

24) CHECK OUT WHAT ARE THE SYSTEM PRIVILEGES GIVEN TO USER STUDENT AND TEACHER

ANS:

SQL> SELECT USERNAME , PRIVILEGE, ADMIN_OPTION FROM USER_SYS_PRIVS WHERE USERNAME IN (STUDENT_PP,TEACHER_PP);

USERNAME PRIVILEGE ADM -

---TEACHER_PP CREATE SESSION NO STUDENT_PP CREATE SESSION NO

25) CREATE A PROFILE LIMIT THAT HAS PASSWORD EXPIRED AFTER 3 MONTHS.

ANS:

SQL> CREATE PROFILE PROFILE1_PP LIMIT PASSWORD_LIFE_TIME 90; PROFILE CREATED.

26) ALSO MAKE THE PROFILE LIMIT SUCH THAT IT TIMES OUT AFTER 30 MINUTES OF NO ACTIVITY AND IT CAN MAKE ONLY 2 SESSIONS AT A TIME

ANS:

SQL> ALTER PROFILE PROFILE1_PP LIMIT IDLE_TIME 30; PROFILE ALTERED.

SQL> ALTER PROFILE PROFILE1_PP LIMIT SESSIONS_PER_USER 2; PROFILE ALTERED.

27) ASSIGN THE PROFILE LIMIT TO STUDENT

(49)

SQL> ALTER USER STUDENT_PP PROFILE PROFILE1_PP; USER ALTERED.

28) ALTER THE PROFILE LIMIT SUCH THAT THE PASSWORD EXPIRES AFTER 2 MONTHS ALSO THE PASSWORD CASE SENSITIVE

ANS:

SQL> ALTER PROFILE PROFILE1_PP LIMIT PASSWORD_LIFE_TIME 60; PROFILE ALTERED.

29) FIND OUT ALL THE PROFILES IN THE DATABASE.

ANS:

SQL> SELECT DISTINCT PROFILE AS PROFS FROM DBA_PROFILES ; PROFS

---JR_PROFILE1

PROFILE1_PP DEFAULT

(50)

4 ORACLE UTILITIES

1) CREATE A TRIGGER ON A TABLE STUDENT SO THAT FOR EACH INSERT TO THE TABLE THE TRIGGER FIRES AND UPDATES THE DEPARTMENT TABLE DEPENDING ON THE STUDENT TABLE VALUE.

STUDENT: ROLLNO NUMBER(5) -> PRIMARY KEY NAME VARCHAR2(20)

MARKS NUMBER(5) AGE NUMBER(5)

DEPARTMENT: ROLLNO NUMBER(5) -> PRIMARY KEY DEPT_NAME VARCHAR2(10)

ANS:

sql> create table student_pp (

rollno number(5) constraint stud_roll_pk primary key, name varchar2(20),

marks number(5), age number(5)); Table created.

sql> create table department_pp( rollno number(5),

dept_name varchar2(10),

foreign key (rollno) references student_pp (rollno)); Table created.

sql> alter table department_pp add primary key (rollno); Table altered.

sql> create or replace trigger trig_pp after insert

on student_pp for each row declare

(51)

begin

dep:='&dep';

insert into department_pp values (:new.rollno,dep); end;

/ Trigger created.

2) CREATE THE EMP TABLE IN TEXT/TEXT123 SCHEMA USING THE FOLLOWING DEFINITION EMPID NUMBER(5) EMPNAME VARCHAR2(20) DESIGNATION VARCHAR2(10) SALARY NUMBER(10) COMPANYNAME VARCHAR2(15)); ANS: sql> conn text/text123 Connected.

sql> create table emp_pp( empid number(5), empname varchar2(20), designation varchar2(10), salary number(10), companyname varchar2(15)); Table created.

TRANSFERING DATAFILES TO REQUIRED LOCATION

$ ftp <ip_address> ftp> get data1.sql

(similarly for data2.sql to data6.sql)

POPULATE THE EMP TABLE USING SQLLOADER

(52)

ANS:

$ sqlldr parfile=par1.par

load completed - logical record count 25.

PARAMETER FILE (PAR1.PAR)

userid = text/text123 control = '/ora/dogrc1p/backup01/oradata/rmancat1/ctl1.ctl' log = '/ora/dogrc1p/backup01/oradata/rmancat1/lg1.log' bad = '/ora/dogrc1p/backup01/oradata/rmancat1/bd1.txt' discard = '/ora/dogrc1p/backup01/oradata/rmancat1/dis1.txt' direct = true CONTROL FILE (CTL1.CTL) load data infile '/ora/dogrc1p/backup01/oradata/rmancat1/data1.sql' insert into table emp_pp

fields terminated by '|' optionally enclosed by " " (

empid integer external, empname char,

designation char, salary integer external, companyname char )

sql> select count(*) from emp_pp1; COUNT(*)

25

(B) AFTER LOADING, APPEND SOME DATA TO EMP -> DATA2.SQL

(53)

$ sqlldr parfile=par2.par

userid = text/text123 control = '/ora/dogrc1p/backup01/oradata/rmancat1/ctl2.ctl' log = '/ora/dogrc1p/backup01/oradata/rmancat1/lg2.log' bad = '/ora/dogrc1p/backup01/oradata/rmancat1/bd2.txt' discard = '/ora/dogrc1p/backup01/oradata/rmancat1/dis2.txt' direct = true CONTROL FILE (CTL2.CTL) load data infile '/ora/dogrc1p/backup01/oradata/rmancat1/data2.sql' append into table emp_pp

55

(C) DELETE THE DATA FROM TABLE AND ADD NEW DATA -> DATA3.SQL

ANS:

(54)

userid = text/text123 control = '/ora/dogrc1p/backup01/oradata/rmancat1/ctl3.ctl' log = '/ora/dogrc1p/backup01/oradata/rmancat1/lg3.log' bad = '/ora/dogrc1p/backup01/oradata/rmancat1/bd3.txt' discard = '/ora/dogrc1p/backup01/oradata/rmancat1/dis3.txt' direct = true CONTROL FILE (CTL3.CTL) load data infile '/ora/dogrc1p/backup01/oradata/rmancat1/data3.sql' insert into table emp_pp truncate

30

3) CREATE AND POPULATE ITEMS TABLE FROM 3 FLAT FILES USING SQLLOADER.

-> DATA4.SQL, DATA5.SQL, DATA6.SQL ITEMS:

ITEMNO NUMBER(5)

PRODUCT_NAME VARCHAR2(15)

PRICE NUMBER(5)

(55)

ANS:

sql> create table item_pp(

itemno varchar2(5), product_name varchar2(15), price number(5), shopname varchar2(15)); Table created. $ sqlldr parfile=par4.par

userid = text/text123 control = '/ora/dogrc1p/backup01/oradata/rmancat1/ctl4.ctl' log = '/ora/dogrc1p/backup01/oradata/rmancat1/lg4.log' bad = '/ora/dogrc1p/backup01/oradata/rmancat1/bd4.txt' discard = '/ora/dogrc1p/backup01/oradata/rmancat1/dis4.txt' direct = true CONTROL FILE (CTL4.CTL) load data infile '/ora/dogrc1p/backup01/oradata/rmancat1/data4.sql' infile '/ora/dogrc1p/backup01/oradata/rmancat1/data5.sql' infile '/ora/dogrc1p/backup01/oradata/rmancat1/data6.sql' append into table item_pp

fields terminated by '|' optionally enclosed by " " ( itemno char,

product_name char, price integer external, shopname char )

(56)

4) EXPORT THE TABLE EMP FROM SCHEMA TEXT IN RMANCAT1 DATABASE USING EXPDP/EXP.

ANS:

USING EXPDP

sql> create directory dir_priya as

'/usr/app/oracle/product/10.2.0/rdbms/log/dir_text’; Directory created.

sql> grant read, write on directory dir_priya to text; Grant succeeded.

$ expdp text/text123 tables=emp_pp1 directory= dir_priya dumpfile= priya.dmp logfile=priya.log

connected to: oracle database 10g enterprise edition release 10.2.0.3.0 - 64bit production with the partitioning, olap and data mining options

starting "text"."sys_export_table_01": text/******** tables=emp_pp1 directory= dir_priya dumpfile= priya.dmp logfile=priya.log

estimate in progress using blocks method...

processing object type table_export/table/table_data total estimation using blocks method: 64 kb

processing object type table_export/table/table

processing object type table_export/table/statistics/table_statistics . . exported "text"."emp_pp1" 6.914 kb 30 rows master table "text"."sys_export_table_01" successfully loaded/unloaded

****************************************************************************** dump file set for text.sys_export_table_01 is:

/usr/app/oracle/product/10.2.0/rdbms/log/dir_priya/priya.dmp job "text"."sys_export_table_01" successfully completed at 23:55:59 USING EXP

$ exp userid=text/text123 tables=emp_pp1 file= priya1.dmp

(57)

export done in us7ascii character set and utf8 nchar character set about to export specified tables via conventional path ...

. . exporting table emp_pp1 30 rows exported export terminated successfully without warnings.

5. EXPORT/IMPORT ONLY THE DATA FROM TEXT USER USING EXPDP/ EXP .

ANS:

USING EXPDP

sql> create directory dir_priya1 as '/usr/app/oracle/product/10.2.0/rdbms/log/dir_priya1'; Directory created.

sql> grant read,write on directory dir_priya1 to text; Grant succeeded.

sql> !

$ expdp text/text123 tables=emp_pp1 directory= dir_priya1 content=data_only

starting "text"."sys_export_table_01": text/******** tables=emp_pp1 directory= dir_priya1 content=data_only

estimate in progress using blocks method...

processing object type table_export/table/table_data total estimation using blocks method: 64 kb

. . exported "text"."emp_pp1" 6.914 kb 30 rows master table "text"."sys_export_table_01" successfully loaded/unloaded

****************************************************************************** Dump file set for TEXT.SYS_EXPORT_TABLE_01 is:

/usr/app/oracle/product/10.2.0/rdbms/log/dir_priya1/expdat.dmp

(58)

spdwd606:/usr/app/oracle/product/10.2.0/rdbms/log[RMANCAT1]$ expdp text/text123 tables=emp_pp1 directory= dir_priya1 dumpfile= priya1.dmp logfile=priya1.log content=data_only

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options

Starting "TEXT"."SYS_EXPORT_TABLE_01": text/******** tables=emp_pp1 directory= dir_priya1 dumpfile= priya1.dmp logfile=priya1.log content=data_only

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB

. . exported "TEXT"."EMP_PP1" 6.914 KB 30 rows

Master table "TEXT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

****************************************************************************** Dump file set for TEXT.SYS_EXPORT_TABLE_01 is:

/usr/app/oracle/product/10.2.0/rdbms/log/dir_priya1/priya1.dmp

Job "TEXT"."SYS_EXPORT_TABLE_01" successfully completed at 01:48:37

USING EXP

$ exp userid=text/text123 tables=emp_pp1 file= priya1_exp.dmp full=n rows=y

Export done in US7ASCII character set and UTF8 NCHAR character set About to export specified tables via Conventional Path ...

. . exporting table EMP_PP1 30 rows exported Export terminated successfully without warnings.

USING IMPDP

$ impdp userid=text/text123 tables=emp_pp1 directory=dir_priya1 dumpfile= priya1.dmp logfile=priya1.log content=data_only

(59)

Master table "TEXT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "TEXT"."SYS_IMPORT_TABLE_01": userid=text/******** tables=emp_pp1 directory=dir_priya1 dumpfile= priya1.dmp logfile=priya1.log content=data_only

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "TEXT"."EMP_PP1" 6.914 KB 30 rows Job "TEXT"."SYS_IMPORT_TABLE_01" successfully completed at 02:01:18 USING IMP

$ imp userid=text/text123 tables=emp_pp2 file= priya2_exp.dmp full=n rows=y

Export file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and UTF8 NCHAR character set . importing TEXT's objects into TEXT

. importing TEXT's objects into TEXT

. . importing table "EMP_PP2" 90 rows imported Import terminated successfully without warnings.

N:B OTHER VALUES FOR CONTENT ARE ‘METADATA ONLY’ AND ‘ALL’.

6) DO EXPORT ONLY THE STRUCTURE OF THE TABLE EMP1.

ANS:

EXPORT ( USING EXPDP)

$ expdp text/text123 tables=emp_pp1 directory= dir_priya_meta dumpfile= priya_meta.dmp logfile=priya_meta.log content=metadata_only