Oracle Data Dictionary-Short Questions & Answers

Here as a reader you can get the valuable information of Oracle Data Dictionary in the form of short questions with answers.

01.
What's a Data Dictionary?

Ans. A data dictionary is a central
component of every database. The Oracle Data Dictionary is meta data about the
database. As for example- if you create a table in Oracle, meta data about that
table is stored in the data dictionary.

02.
What is meta data?

---

Ans. Meta data is data about data,
or data that defines other data.

03.
How is the data dictionary managed and updated?

Ans. Data dictionary is managed and
updated by the Oracle software when a database user gives a data definition
language statement- CREATE, ALTER, DROP, TRUNCATE etc. or data control language
statement - GRANT or REVOKE.

04.
Which information may a data dictionary contain?

Ans. A data dictionary may contain
the following information:

• The definitions of every schema object in the database
  having default values for columns and integrity constraint information.
• The amount of space assigned for and currently used by
  the schema objects
• The names of Oracle Database users, privileges and
  roles granted to users.

05.
What do you know about Base Table?

Ans. Base Table is an object of Data
Dictionary. The base tables contain information about Database. Only Oracle
Database should write to and read these tables. Users hardly access these base
tables directly. It is because they are normalized and most data is kept in a
puzzling format.

06.
What do you know about Views?

Views make sense of base table data
into useful information. The views hold the names and description of all
objects in the data dictionary. Some views are easy to get to all database
users, on the contrary others are intended for administrators only. 

07.
When is the data dictionary created?

Ans. The data dictionary is created
when oracle database is created.

08.
How many primary uses of the Data Dictionary? Discuss them.

Ans. The data dictionary has three
primary uses.

• Oracle database is easy to get to the data dictionary
  to find information about users, schema objects and storage
  structures. 
• Oracle Database modifies the data dictionary every
  time.
• The users of oracle database can use the data
  dictioanry as a read-only reference for information about the
  database. 

09.
 What is DBA_TABLES?

Ans. DBA_TABLES describe information
about all the tables in database.

10.
What is DBA_TABLESPACE?

Ans. DBA_TABLESPACE give information
about tablespaces.

11.
What is DBA_DATA_FILES?

Ans. DBA_DATA_FILES supplies
information about data files.

12.
What do you know about DBA_VIEWS, DBA_SYS_PRIVS, DBA_TAB_PRIVS, DBA_TRIGGERS,
DBA_USERS?

Ans. DBA_VIEWS describe information
about all views in database.

DBA_SYS_PRIVS describes the system
privileges granted to user and role.

DBA_TAB_PRIVS discuss the object
privileges granted to users and roles.

DBA_TRIGGERS describes information
about all the triggers in database.

DBA_USERS give information about
users in the database.

13.
What information do you get by view names- v$controlfile, v$archieve_dest,
v$database, v$instance, v$datafile and v$logfile?

Ans. v$controlfile gives the names
of the controlfile. Apply sql statement and you can see:

SELECT * from v$controlfile;

 C:\APP\USER\ORADATA\ORCL\CONTROL01.CTL NO
16384 594

 C:\APP\USER\ORADATA\ORCL\CONTROL02.CTL NO
16384 594

 C:\APP\USER\ORADATA\ORCL\CONTROL03.CTL NO
16384 594

v$archive_dest describes all the
archive log destinations, their current value, mode and status. 

SELECT * from v$archive_dest;

1 LOG_ARCHIVE_DEST_1 VALID MANDATORY
SYSTEM PRIMARY FOREGROUND INACTIVE C:\app\user\product\11.1.0\db_1\RDBMS 0 0 0
1 0 ARCH NO  0 0 0 0  NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC
YES ALL_LOGFILES ALL_ROLES NONE NO DISABLE

2 LOG_ARCHIVE_DEST_2 INACTIVE
OPTIONAL SYSTEM PRIMARY FOREGROUND INACTIVE
0 0 0 1 0 ARCH NO  0 0 0 0  NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC
UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE

3 LOG_ARCHIVE_DEST_3 INACTIVE
OPTIONAL SYSTEM PRIMARY FOREGROUND INACTIVE
0 0 0 1 0 ARCH NO  0 0 0 0  NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC
UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE

4 LOG_ARCHIVE_DEST_4 INACTIVE
OPTIONAL SYSTEM PRIMARY FOREGROUND INACTIVE
0 0 0 1 0 ARCH NO  0 0 0 0  NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC
UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE

5 LOG_ARCHIVE_DEST_5 INACTIVE
OPTIONAL SYSTEM PRIMARY FOREGROUND INACTIVE
0 0 0 1 0 ARCH NO  0 0 0 0  NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC
UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE

6 LOG_ARCHIVE_DEST_6 INACTIVE
OPTIONAL SYSTEM PRIMARY FOREGROUND INACTIVE
0 0 0 1 0 ARCH NO  0 0 0 0  NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC
UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE

7 LOG_ARCHIVE_DEST_7 INACTIVE
OPTIONAL SYSTEM PRIMARY FOREGROUND INACTIVE
0 0 0 1 0 ARCH NO  0 0 0 0  NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC
UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE

8 LOG_ARCHIVE_DEST_8 INACTIVE
OPTIONAL SYSTEM PRIMARY FOREGROUND INACTIVE
0 0 0 1 0 ARCH NO  0 0 0 0  NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC
UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE

9 LOG_ARCHIVE_DEST_9 INACTIVE
OPTIONAL SYSTEM PRIMARY FOREGROUND INACTIVE
0 0 0 1 0 ARCH NO  0 0 0 0  NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC
UNKNOWN ALL_LOGFILES ALL_ROLES NONE NO DISABLE

10 LOG_ARCHIVE_DEST_10 VALID
OPTIONAL SYSTEM PRIMARY FOREGROUND INACTIVE USE_DB_RECOVERY_FILE_DEST 0 300 0 1
0 ARCH YES  0 0 0 0  NONE NONE NONE 0 0 0 SYNCHRONOUS 0 NO PUBLIC
YES ALL_LOGFILES ALL_ROLES NONE NO DISABLE

v$database contains database
information from the control file. 

SELECT * from v$database;

1394372150 ORCL 2014-12-17 23:50:14
886308 2014-12-17 23:50:18 1 2007-10-15 10:08:59 NOARCHIVELOG 2519592 2433490
CURRENT 2014-12-17 23:50:15 4006 2539874 2015-01-03 20:44:53 NOT ALLOWED
2014-12-17 23:50:14 READ WRITE MAXIMUM PERFORMANCE UNPROTECTED ENABLED
1394382902 1394382902 PRIMARY 886308 DISABLED SESSIONS ACTIVE DISABLED NONE NO
NO NO NO 7 Microsoft Windows IA (32-bit) 2 2 2540045 NO NO NO orcl 0
DISABLED  0   NO  NO

v$instance displays the state of the
current instance.

SELECT * from v$instance;

1 orcl USER-PC 11.1.0.6.0 2015-01-03
17:56:36 OPEN NO 1 STOPPED  ALLOWED NO
ACTIVE PRIMARY_INSTANCE NORMAL NO

v$datafile contains information of
data files of the current instance.

SELECT * from v$datafile;

1 8 2007-10-15 10:09:07 0 1 SYSTEM
READ WRITE 2519592 2015-01-03 17:57:14 0
886307 886308 2014-12-17 23:50:18 734003200 89600 0 8192
C:\APP\USER\ORADATA\ORCL\SYSTEM01.DBF 0 8192 NONE 0  0 0  NO
0 0

2 1687 2007-10-15 10:09:13 1 2
ONLINE READ WRITE 2519592 2015-01-03 17:57:14 0    886307 886308 2014-12-17 23:50:18 686161920
83760 0 8192 C:\APP\USER\ORADATA\ORCL\SYSAUX01.DBF 0 8192 NONE 0  0 0  NO
0 0

3 883672 2007-10-15 12:01:05 2 3
ONLINE READ WRITE 2519592 2015-01-03 17:57:14 0    886307 886308 2014-12-17 23:50:18 47185920
5760 0 8192 C:\APP\USER\ORADATA\ORCL\UNDOTBS01.DBF 0 8192 NONE 0  0 0  NO
0 0

4 14893 2007-10-15 10:09:27 4 4
ONLINE READ WRITE 2519592 2015-01-03 17:57:14 0    886307 886308 2014-12-17 23:50:18 5242880
640 0 8192 C:\APP\USER\ORADATA\ORCL\USERS01.DBF 0 8192 NONE 0  0 0  NO
0 0

5 914144 2014-12-17 23:51:33 6 5
ONLINE READ WRITE 2519592 2015-01-03 17:57:14 0    914144 914149 2014-12-17 23:51:33 104857600
12800 104857600 8192 C:\APP\USER\ORADATA\ORCL\EXAMPLE01.DBF 0 8192 NONE 0  3964705275 887102 2007-10-15 12:04:13 NO
905997 886308 2014-12-17 23:50:18

v$logfile keeps information regarding
database log files.

SELECT * from v$logfile;

3
ONLINE C:\APP\USER\ORADATA\ORCL\REDO03.LOG NO

2
ONLINE C:\APP\USER\ORADATA\ORCL\REDO02.LOG NO

1
ONLINE C:\APP\USER\ORADATA\ORCL\REDO01.LOG NO