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?
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.
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.
Ans. Meta data is data about data,
or data that defines other data.
or data that defines other data.
03.
How is the data dictionary managed and updated?
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.
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?
Which information may a data dictionary contain?
Ans. A data dictionary may contain
the following information:
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?
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.
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?
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.
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?
When is the data dictionary created?
Ans. The data dictionary is created
when oracle database is created.
when oracle database is created.
08.
How many primary uses of the Data Dictionary? Discuss them.
How many primary uses of the Data Dictionary? Discuss them.
Ans. The data dictionary has three
primary uses.
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?
What is DBA_TABLES?
Ans. DBA_TABLES describe information
about all the tables in database.
about all the tables in database.
10.
What is DBA_TABLESPACE?
What is DBA_TABLESPACE?
Ans. DBA_TABLESPACE give information
about tablespaces.
about tablespaces.
11.
What is DBA_DATA_FILES?
What is DBA_DATA_FILES?
Ans. DBA_DATA_FILES supplies
information about data files.
information about data files.
12.
What do you know about DBA_VIEWS, DBA_SYS_PRIVS, DBA_TAB_PRIVS, DBA_TRIGGERS,
DBA_USERS?
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.
about all views in database.
DBA_SYS_PRIVS describes the system
privileges granted to user and role.
privileges granted to user and role.
DBA_TAB_PRIVS discuss the object
privileges granted to users and roles.
privileges granted to users and roles.
DBA_TRIGGERS describes information
about all the triggers in database.
about all the triggers in database.
DBA_USERS give information about
users in the database.
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?
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:
of the controlfile. Apply sql statement and you can see:
SELECT * from v$controlfile;
C:\APP\USER\ORADATA\ORCL\CONTROL01.CTL NO
16384 594
16384 594
C:\APP\USER\ORADATA\ORCL\CONTROL02.CTL NO
16384 594
16384 594
C:\APP\USER\ORADATA\ORCL\CONTROL03.CTL NO
16384 594
16384 594
v$archive_dest describes all the
archive log destinations, their current value, mode and status.
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
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
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
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
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
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
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
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
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
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
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.
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
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.
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
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.
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
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
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
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
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
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.
database log files.
SELECT * from v$logfile;
3
ONLINE C:\APP\USER\ORADATA\ORCL\REDO03.LOG NO
ONLINE C:\APP\USER\ORADATA\ORCL\REDO03.LOG NO
2
ONLINE C:\APP\USER\ORADATA\ORCL\REDO02.LOG NO
ONLINE C:\APP\USER\ORADATA\ORCL\REDO02.LOG NO
1
ONLINE C:\APP\USER\ORADATA\ORCL\REDO01.LOG NO
ONLINE C:\APP\USER\ORADATA\ORCL\REDO01.LOG NO
No comments:
Post a Comment