1) What is Block or Database Block?
Storage area for storing rows
2) What is the unit of Block?
Bytes
3) What is the default Standard Block Size in Oracle 10g?
8KB
3) What is Non Standard Block Size?
Other than 8KB
4) How many Non-Standard Block Sizes are supported by Oracle? What are they?
Four: 2KB, 4KB, 16KB, 32KB
5) What is Extent?
A Collection of Blocks
6) What is Segment?
A Collection of Extents
7) What are the types of Segments?
Table, Index, Undo & Temporary
8) What is Table Segment?
It stores actual committed data
9) What is Index Segment?
It stores Indexes information which are created on tables
10) What is Undo Segment?
It stores pre-image value
11) What are the advantages of Undo Segment?
Provides Read Consistency, to rollback uncommitted transactions during (ROLL BACKWARD) by SMON
12) What is Temporary Segment?
Performing Sorting, Joins, Having, group by
13) What is Tablespace?
It is Logical collection of Segments & Physically related with data file
14) What are the types of Tablespaces?
Permanent, Temporary
15) What is Logical Structure?
Tablespace
16) What is the hierarchy of the Logical Structures?
Tablespace _ Contains _ Segments _ Contains _ Extents _ Contains Blocks
17) What are the Mandatory Tablespaces during Database Creation?
SYSTEM & SYSAUX
18) What are the Mandatory Tablespaces during Database Usage?
TEMPORARY, UNDO & Application (User) Tablespaces
19) What is SYSTEM Tablespace?
It contains Metadata Information (Data Dictionary)
20) What is SYSAUX Tablespace?
It contains Database Workload Information
21) When SYSAUX Tablespace is introduced?
Oracle 10g
21) What is Undo Tablespace?
It stores Pre-Image value
22) What is Permanent Tablespace?
It stores the data permanently
23) What are the types of Permanent Tablespace?
SYSTEM, SYSAUX, User defined tablespaces
24) What is Temporary Tablespace?
It stores the data temporarily during Sorting Operations, Join Operations
25) What is Temporary Tablespace Group?
It is Logical which contains more than one temporary tablespace at a time
26) What is Extent Management?
Allocating & Reallocating the Blocks
27) What are the types of Extent Management?
Locally Managed, Dictionary Managed
28) What type of Extent Management is preferred?
Locally Managed Tablespace (LMT)
29) What is Locally Managed Tablespace (LMT)?
In this the Extent Information will be stored in the form of Bitmaps in Data File Header
This Bitmap indicates whether free space is available in the block or not
30) What is Dictionary Managed Tablespace (DMT)?
In this the Extent Information will be stored in the Data Dictionary Tables
31) What is Recursive SQL?
Oracle writes internal queries to get the data from the Data Dictionary Tables
32) What are the drawbacks of Dictionary Managed Tablespace?
Results in the Contention of the Data Dictionary Tables
Size of the extents are managed automatically by the system
Changes to the extent bitmaps do not generate undo information
Avoid Recursive Space Management Operations
32) What is Segment Space Management?
Managing the space in the segments
33) What are the types of Segment Space Management?
Manual Segment Space Management, Automatic Segment Space Management
34) What type of Segment Space Management is preferred? Why?
Automatic, everything will be managed by Oracle
35) What are the drawbacks of Manual Segment Space Management?
We have to specify PCTFREE, PCTUSED
36) What is PCTFREE?
How much space is available for INSERTION?
37) What is PCTUSED?
How much space is available for UPDATION?
38) What is Auto Extend on with Tablespace?
When the tablespace is filled up it will allocate some space to tablespace
39) How to make a tablespace OFFLINE?
ALTER TABLESPACE <tablespace_name> OFFLINE;
40) What are the different options available when we make a tablespace OFFLINE?
NORMAL, TEMPORARY, TRANSACTIONAL, FOR RECOVERY
41) What is Tablespace OFFLINE NORMAL?
It performs Checkpoint
It can be made OFFLINE only when there are no error conditions in data files
42) What is Tablespace OFFLINE TEMPORARY?
It performs Checkpoint
A tablespace can be taken OFFLINE even if there are error conditions on data files
43) What is Tablespace OFFLINE IMMEDIATE?
It does not perform any Checkpoint
It requires Media Recovery before making tablespace ONLINE
44) Is it possible to make a Tablespace OFFLINE when the database is running in NOARCHIVELOG mode?
No
45) What is Tablespace OFFLINE FOR RECOVER?
It is deprecated & used for backward compatibility
46) What is Physical Structure?
Data File
47) What does a Tablespace is Logically Contains?
Segments, Extents & Blocks
48) What does a Tablespace Physically Contains?
Data Files
49) What are the types of Tablespaces with respect to the data files?
Small File Tablespace & Big File Tablespace
50) What is the small file tablespace?
Default Permanent tablespace
51) How many data files can exist in small file tablespace?
65535
52) What is big file tablespace?
It is a single data file which can store data up to 128TB
53) How many data files can exist in big file tablespace?
Single
54) What are the advantages of Big File Tablespace?
One Tablespace has only one data file
No need to constantly add data files to Tablespace
Data File Management in Large Databases is simplified
Storage Capacity is Increases
55) What is LOGGING with respect to the tablespace?
Changes are tracked in Online Redo Log Files
56) What is NOLOGGING with respect to the tablespace?
Changes are not tracked in Online Redo Log Files
57) What is UNIFORM with respect to the tablespace?
Extents are allocated at a uniform rate which is specified by the user
58) What is AUTO ALLOCATE with respect to the tablespace?
Extents are allocated automatically by the oracle
59) What is the default option for allocating the extents that is UNIFORM or AUTO ALLOCATE?
Auto Allocated
60) Is it possible to Drop/Rename SYSTEM/SYSAUX Tablespace?
No
61) What is Default Permanent Tablespace?
It is allocated to a user who has been not allocated to any permanent application tablespace
62) What is Default Temporary Tablespace?
It is allocated to a user who has been not allocated to any temporary tablespace
63) How to move a table from one tablespace to another tablespace?
ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name>;
64) How to move an index from one tablespace to another tablespace?
ALTER INDEX <index_name> REBUILD TABLESPACE <tablespace_name>;
65) What are storage parameters in Tablespace (DMT)?
Free Lists, PCT FREE, PCT USED
66) What are free lists?
Space available for INSERT
67) What is PCTFREE?
Space available for UPDATE
68) What is PCTUSED?
Threshold value for how much space used
69) How to DE allocate the unused Extents?
ALTER TABLE <table_name> DEALLOCATED UNSED;
70) Create a tablespace of BLOCKSIZE 16K?
CREATE TABLESPACE <tablespace_name> DATAFILE <path> BLOCKSIZE 16K;
71) What is DROP TABLESPACE <tablespace_name>?
It will drop only the tablespace logically
72) What is DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS?
It will drop the tablespace along with the contents (Segments, Extents & Blocks)
73) What is DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES?
It will drop the tablespace along with the contents & Physical Data Files
74) How to rename a tablespace?
ALTER TABLESPACE <tablespace_name> RENAME TO <new_tablespace_name>;
75) How to make a tablespace READ ONLY?
ALTER TABLESPACE <tablespace_name> READ ONLY;
76) How to make a tablespace READ WRITE?
ALTER TABLESPACE <tablespace_name> READ WRITE;
77) How to make a tablespace ONLINE?
ALTER TABLESPACE <tablespace_name> ONLINE;
78) How to make a tablespace OFFLINE?
ALTER TABLESPACE <tablespace_name> OFFLINE;
79) What is the Data Dictionary views which gives the information about Tablespaces?
DBA_TABLESPACES, V$TABLESPACE
83) What is the Data Dictionary Views which gives the information about the Segments?
DBA_SEGMENTS
84) What is the Data Dictionary Views which gives the information about the Extents?
DBA_EXTENTS
85) What is the Data Dictionary Views which gives the information about the data files?
DBA_DATA_FILES, V$DATFILE
86) What is the Data Dictionary Views which gives the information about the Temporary files?
DBA_TEMP_FILES
87) What is the Data Dictionary Views which gives the information about the Temporary Tablespace
Groups?
DBA_TABLESPACE_GROUPS
0 comments:
Post a Comment