1. Install - Run the create scripts (under Oracle 10g XE version)
SQL> connect / as sysdba
SQL> @C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN>spcreate.sql
Notes: prompted for enter password (oracle), the default tablespace SYSAUX
was chosed, the temporary tablespace was also chosen as TEMP
2. Adjusting the STATSPACK Collection Level
STATSPACK has two types of collection options: level and threshold.
level - controls the type of data collected from Oracle
threshold - as a filter for the collection of SQL statements into
the stats$sql_summary table
To show the description of different level, issue the following statement:
SQL> select * from stats$level_description order by snap_level;
To change the level of a snapshot, you can use statspack.snap function.
The i_modify_parameter=> 'true' changes the level permanent for all
snapshots in the future. e.g.
SQL> exec statspace.snap(i_snap_level => 6, i_modify_parameter => 'true');
3. Create, View and Delete Snapshots
SQL> connect perfstat/oracle
Connected.
SQL> exec statspack.snap
PL/SQL procedure successfully completed.
SQL> select name, snap_id, to_char(snap_time, 'MM-DD-YYYY:HH24:MI:SS')
2 "Date/Time" from stats$snapshot, v$database;
NAME SNAP_ID Date/Time
--------- ---------- -------------------
XE 1 09-29-2006:15:03:40
XE 2 09-29-2006:15:04:31
XE 11 09-29-2006:15:06:09
To delete
SQL> @?\rdbms\admin\sppurge;
Notes: prompted to enter low and high snapshot ID
4. Create the Report
SQL> @?\rdbms\admin\spreport.sql
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11
Begin Snapshot Id specified: 11
Enter value for end_snap: 21
End Snapshot Id specified: 21
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_11_21. To use this name,
pressto continue, otherwise enter an alternative.
Enter value for report_name:
5. Statspack at a Glance - understading the report
5.1. Statspack Report Header
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
2481462586 xe 1 29-Sep-06 14:10 10.2.0.1.0 NO
Host Name: NTBK Num CPUs: 1 Phys Memory (MB): 494
~~~~
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 11 29-Sep-06 15:06:09 16 5.4
End Snap: 21 29-Sep-06 15:14:59 16 3.8
Elapsed: 8.83 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 72M Std Block Size: 8K
Shared Pool Size: 52M Log Buffer: 2,792K
Notes:
Curs/Sess column - shows the number of open cursors per session.
the item we are most interested in is the elapsed time. We want that to be
large enough to be meaningful, but small enough to be relevant (15 to 30
minutes is OK).
5.2 Statspack Load Profile
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 5,410.54 716,896.00
Logical reads: 71.40 9,460.25
Block changes: 33.27 4,408.75
Physical reads: 0.03 4.50
Physical writes: 4.69 621.25
User calls: 0.21 28.25
Parses: 6.08 805.50
Hard parses: 0.86 114.50
Sorts: 3.84 509.25
Logons: 0.01 0.75
Executes: 13.62 1,804.00
Transactions: 0.01
% Blocks changed per Read: 46.60 Recursive Call %: 99.88
Rollback per transaction %: 0.00 Rows per Sort: 6.65
Notes:
Three items are important:
- The Hard parses (we want very few of them)
- Executes (how many statements we are executing per second/transaction
- Transactions (how many transactions per second we process).
This gives an overall view of the load on the server.
a fairly light system load (1-4 transactions per second is low).
5.3 Statspack Instance Efficiency Percentage
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.95 In-memory Sort %: 100.00
Library Hit %: 86.15 Soft Parse %: 85.79
Execute to Parse %: 55.35 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 77.17 % Non-Parse CPU: 68.03
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 77.74 79.31
% SQL with executions>1: 91.71 70.29
% Memory for SQL w/exec>1: 98.44 90.15
Notes:
Most important: Library Hit, Soft Parse and Execute to Parse. All of these
have to do with how well the shared pool is being utilized.
If the library Hit ration was low, it could be indicative of a shared pool
that is too small, or just as likely, that the system did not make correct
use of bind variables in the applicaiton
OLTP System
The Soft Parase % value is one of the most important ratio in the database.
For a typical OLTP system, it should be as near to 100% as possible. You
quite simply do not hard parse after the database has been up for a while
in your typical transactional / general-purpose database. The way you
achieve that is with bind variables.
Data Warehouse
In a data warehouse, we would like to generally see the Soft Parse ratio
lower. We don't necessarily want to use bind variables in a data warehouse.
This is because they typically use materialized views, hustograms, and
other things that are easily thwarted by bind variables. In a data warehouse,
we may have many seconds between executions, so hard parsing is not evil;
in fact, it is good in those environments
5.4 Statspack Top 5 Timed Events
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file parallel write 487 7 15 45.7
CPU time 6 37.4
log file parallel write 116 1 9 6.5
control file parallel write 177 1 4 4.8
control file sequential read 247 1 3 3.8
-------------------------------------------------------------
Notes:
CPU time - the sum of the CPU used by this session, or the amount of CPU
time used during the snapshot window. In a heavily loaded system,
if the CPU time event is the biggest event, that could point to
some CPU-intensive processing
5.5 SQL ordered by Gets
Here you will find the most CPU-Time consuming SQL statements
Friday, September 29, 2006
Lab: Using Statspack Under Oracle XE
Friday, September 22, 2006
unix tool vmstat - show how the machine is utilzed
This article showed how to interpret the vmstat on AIX
Automated Oracle Tuning Initial Procedures - Part 2
Automated Oracle Tuning Initial Procedures - Part 2
Solaris OS commands
Some command under Solaris
# prtdiag -- Show some hardware info
# swap –s 查看交换区空间大小
# df -k tmp 查看tmp空间大小
# prtconf grep Memory size 查看内存大小
# df –k 查看系统磁盘空间
# isainfo –kv 查看系统内核是否是64位
# pkginfo -i SUNWarc SUNWlibms SUNWil0f SUNWbtool SUNWsprot SUNWi1cs SUNWhea SUNWsprox
This article describes how to set swap memory:
Note: swap files != SWAP memory
Setting SWAP Memory Under Solaris
# prtdiag -- Show some hardware info
# swap –s 查看交换区空间大小
# df -k tmp 查看tmp空间大小
# prtconf grep Memory size 查看内存大小
# df –k 查看系统磁盘空间
# isainfo –kv 查看系统内核是否是64位
# pkginfo -i SUNWarc SUNWlibms SUNWil0f SUNWbtool SUNWsprot SUNWi1cs SUNWhea SUNWsprox
This article describes how to set swap memory:
Note: swap files != SWAP memory
Setting SWAP Memory Under Solaris
Tuesday, September 19, 2006
Fundamental II Ch 9 - User-Managed and RMAN-Based Backups
Chapter 9 - User-Managed and RMAN-Based Backups
OCP: Oracle9i DBA Fundamentals II Study Guide
Review Questions
1. Which type of backup most closely represents an opened backup? (Choose all that apply.)
A. Online backup
B. Offline backup
C. Hot backup
D. Cold backup
----
An opened backup is performed when the database is opened or available for access (online). A hot backup and online backup are synonymous.
Ans: A, C
2. In the event of a database failure that requires a full database restore, how would you perform a recovery for a read-only tablespace? (Choose all that apply.)
A. No recovery is necessary, if the restored copy was made when the tablespace was read-only.
B. You would recover by applying redo log entries to the read-only tablespace, regardless of the state of the tablespace copy.
C. You would recover by applying redo log entries to the read-only tablespace if the tablespace was in read-write mode at the time of the backup used for the restore.
D. You would recover by applying redo log entries to the read-only tablespace if the tablespace was in read-only mode at the time of the backup used for the restore.
---
In a read-only tablespace, the SCN doesn’t change or if it does, none of the changes get applied. So if the backup of the tablespace was taken when the tablespace was read-only, no recovery would be necessary. On the other hand, if the backup was taken when the database was read-write, then redo logs would need to be applied. The redo logs in this case would also contain the command that puts the tablespace into read-only mode.
Ans: A, C.
3. What type of backup is consistent?
A. Online backup
B. Opened backup
C. Hot backup
D. Cold backup
----
A cold backup ensures that all the SCNs in the data files are consistent for a single point in time.
Ans: D.
4. What type of backup is inconsistent? (Choose all that apply.)
A. Cold backup
B. Online backup
C. Opened backup
D. Closed backup
---
Opened and online backups both back up the data files with different SCNs in the headers, which makes recovery necessary during a restore operation.
Ans: B, C.
5. If a read-only tablespace is restored from a backup when the data file is read-only, what type of recovery is necessary?
A. Data file recovery
B. Tablespace recovery
C. Database recovery
D. No recovery is needed.
-----
No recovery is needed because the tablespace was read-only during backup and at the time of failure.
Ans: D.
6. What are valid ways to back up a control file while the database is running? (Choose all that apply.)
A. Back up to trace file
B. Back up to binary control file
C. OS copy to tape
D. Back up to restore file
----
Backing up both to a trace file and to a binary control file are valid backups of the control file. The other references are made up.
Ans: A, B.
7. Which of the following statements is true about a user-managed backup?
A. This type of backup is conducted using the RMAN utility.
B. A user-managed backup can be customized using a combination of OS and database commands.
C. A user-managed backup is a new type of backup in Oracle9i.
D. A user-managed backup is one of the backup options within RMAN.
----
A user-managed backup is a customizable backup that uses OS and database commands, and it is usually written in some sort of native scripting language.
Ans B.
8. If a tablespace was backed up shortly after it was made read-only, what would need to be done with archived logs during a recovery of that tablespace?
A. All archived logs would need to be applied.
B. Only the archived logs that were added after the backup would need to be applied.
C. Only the archived logs that were added before the backup would need to be applied.
D. No archived logs would need to be applied.
----
No archived logs would need to be applied because the tablespace was backed up after it was made read-only.
Ans: D.
9. Which of the following is a true statement about a RMAN image copy?
A. It can be backed up to tape or disk.
B. It can be backed up to disk only.
C. It can be backed up to tape only.
D. It can be copied to tape only.
---
An image copy can be backed up only to disk.
Ans B.
10. A cold backup requires the database to be in what condition? (Choose all that apply.)
A. ARCHIVELOG mode
B. NOARCHIVELOG mode
C. The database must be started.
D. The database cannot contain any read-only tablespaces.
---
Ans: A cold backup occurs when the database is shutdown. The database can be in ARCHIVELOG mode or NOARCHIVELOG mode.
11. To perform an open or hot backup, what state must the database be in?
A. ARCHIVELOG mode
B. NOARCHIVELOG mode
C. Shutdown
D. Automatic archiving must be enabled.
----
The database must be in ARCHIVELOG mode. Archiving can be set to manual or automatic.
Ans: A.
12. Hot backups are best run when what is occurring in the database?
A. Heavy DML activity
B. Heavy batch processing
C. The database is being shut down.
D. Low DML activity
----
More transactional activity gets written to redo logs when a tablespace is in backup mode. It is a good idea to do hot backups when you have the lowest transactional activity.
Ans: D.
13. What method can be used to clean up a failed online or hot backup?
A. Shutting down the database
B. Querying V$TABLESPACE
C. Querying V$BACKUP
D. Querying V$DATAFILE
----
It is a good idea to query V$BACKUP to check to see if any data files are being actively backed up. If they are, you can execute ALTER TABLESPACE END BACKUP to change the status from ACTIVE to INACTIVE.
Ans: C.
14. What utility can be used to check to see if a data file has block corruption?
A. DBVALIDATE
B. DBVERIFY
C. DBVERIFIED
D. DBVALID
----
The DBVERIFY utility is used to check whether or not a data file has any block corruption.
Ans: B.
15. Which of the following are types of RMAN backups? (Choose all that apply.)
A. Open and closed backups
B. Full and incremental backups
C. Consistent and inconsistent backups
D. Control file backups
-----
Ans: A, B, C. Open and closed, full and incremental, and consistent and inconsistent backups are the different type of RMAN backups.
OCP: Oracle9i DBA Fundamentals II Study Guide
Review Questions
1. Which type of backup most closely represents an opened backup? (Choose all that apply.)
A. Online backup
B. Offline backup
C. Hot backup
D. Cold backup
----
An opened backup is performed when the database is opened or available for access (online). A hot backup and online backup are synonymous.
Ans: A, C
2. In the event of a database failure that requires a full database restore, how would you perform a recovery for a read-only tablespace? (Choose all that apply.)
A. No recovery is necessary, if the restored copy was made when the tablespace was read-only.
B. You would recover by applying redo log entries to the read-only tablespace, regardless of the state of the tablespace copy.
C. You would recover by applying redo log entries to the read-only tablespace if the tablespace was in read-write mode at the time of the backup used for the restore.
D. You would recover by applying redo log entries to the read-only tablespace if the tablespace was in read-only mode at the time of the backup used for the restore.
---
In a read-only tablespace, the SCN doesn’t change or if it does, none of the changes get applied. So if the backup of the tablespace was taken when the tablespace was read-only, no recovery would be necessary. On the other hand, if the backup was taken when the database was read-write, then redo logs would need to be applied. The redo logs in this case would also contain the command that puts the tablespace into read-only mode.
Ans: A, C.
3. What type of backup is consistent?
A. Online backup
B. Opened backup
C. Hot backup
D. Cold backup
----
A cold backup ensures that all the SCNs in the data files are consistent for a single point in time.
Ans: D.
4. What type of backup is inconsistent? (Choose all that apply.)
A. Cold backup
B. Online backup
C. Opened backup
D. Closed backup
---
Opened and online backups both back up the data files with different SCNs in the headers, which makes recovery necessary during a restore operation.
Ans: B, C.
5. If a read-only tablespace is restored from a backup when the data file is read-only, what type of recovery is necessary?
A. Data file recovery
B. Tablespace recovery
C. Database recovery
D. No recovery is needed.
-----
No recovery is needed because the tablespace was read-only during backup and at the time of failure.
Ans: D.
6. What are valid ways to back up a control file while the database is running? (Choose all that apply.)
A. Back up to trace file
B. Back up to binary control file
C. OS copy to tape
D. Back up to restore file
----
Backing up both to a trace file and to a binary control file are valid backups of the control file. The other references are made up.
Ans: A, B.
7. Which of the following statements is true about a user-managed backup?
A. This type of backup is conducted using the RMAN utility.
B. A user-managed backup can be customized using a combination of OS and database commands.
C. A user-managed backup is a new type of backup in Oracle9i.
D. A user-managed backup is one of the backup options within RMAN.
----
A user-managed backup is a customizable backup that uses OS and database commands, and it is usually written in some sort of native scripting language.
Ans B.
8. If a tablespace was backed up shortly after it was made read-only, what would need to be done with archived logs during a recovery of that tablespace?
A. All archived logs would need to be applied.
B. Only the archived logs that were added after the backup would need to be applied.
C. Only the archived logs that were added before the backup would need to be applied.
D. No archived logs would need to be applied.
----
No archived logs would need to be applied because the tablespace was backed up after it was made read-only.
Ans: D.
9. Which of the following is a true statement about a RMAN image copy?
A. It can be backed up to tape or disk.
B. It can be backed up to disk only.
C. It can be backed up to tape only.
D. It can be copied to tape only.
---
An image copy can be backed up only to disk.
Ans B.
10. A cold backup requires the database to be in what condition? (Choose all that apply.)
A. ARCHIVELOG mode
B. NOARCHIVELOG mode
C. The database must be started.
D. The database cannot contain any read-only tablespaces.
---
Ans: A cold backup occurs when the database is shutdown. The database can be in ARCHIVELOG mode or NOARCHIVELOG mode.
11. To perform an open or hot backup, what state must the database be in?
A. ARCHIVELOG mode
B. NOARCHIVELOG mode
C. Shutdown
D. Automatic archiving must be enabled.
----
The database must be in ARCHIVELOG mode. Archiving can be set to manual or automatic.
Ans: A.
12. Hot backups are best run when what is occurring in the database?
A. Heavy DML activity
B. Heavy batch processing
C. The database is being shut down.
D. Low DML activity
----
More transactional activity gets written to redo logs when a tablespace is in backup mode. It is a good idea to do hot backups when you have the lowest transactional activity.
Ans: D.
13. What method can be used to clean up a failed online or hot backup?
A. Shutting down the database
B. Querying V$TABLESPACE
C. Querying V$BACKUP
D. Querying V$DATAFILE
----
It is a good idea to query V$BACKUP to check to see if any data files are being actively backed up. If they are, you can execute ALTER TABLESPACE
Ans: C.
14. What utility can be used to check to see if a data file has block corruption?
A. DBVALIDATE
B. DBVERIFY
C. DBVERIFIED
D. DBVALID
----
The DBVERIFY utility is used to check whether or not a data file has any block corruption.
Ans: B.
15. Which of the following are types of RMAN backups? (Choose all that apply.)
A. Open and closed backups
B. Full and incremental backups
C. Consistent and inconsistent backups
D. Control file backups
-----
Ans: A, B, C. Open and closed, full and incremental, and consistent and inconsistent backups are the different type of RMAN backups.
Monday, September 18, 2006
Fundamentals II Ch 8 - Oracle Recovery Manager Overview and Configuration
Chapter 8 - Oracle Recovery Manager Overview and Configuration
OCP: Oracle9i DBA Fundamentals II Study Guide
Review Questions
1. Does the RMAN utility require the use of the recovery catalog?
A. The recovery catalog is required.
B. The recovery catalog is not required.
C. The recovery catalog is required if it is stored in the same database as the target database.
D. The recovery catalog is not required if it is stored in the same database as the target database.
---
The recovery catalog is optional regardless of the configuration of the target database. It is used to store information about the backup and recovery process in much the same way that the data dictionary stores information about the database.
Ans: B.
2. What are some of the capabilities of the RMAN utility? (Choose all that apply.)
A. Backs up databases, tablespaces, data files, control files, and archived logs
B. Compresses backups
C. Provides scripting capabilities
D. Tests whether backups can be restored
E. All of the above
---
All answers are capabilities of the RMAN utility.
Ans: E.
3. What type of interface does the RMAN utility support? (Choose all that apply.)
A. GUI through Oracle Enterprise Manager
B. Command-line interface
C. Command line only
D. GUI through Oracle Enterprise Manager only
---
The RMAN utility can be run in GUI mode via the use of Oracle Enterprise Manager or through a command-line interface on the server.
Ans: A, B.
4. What actions can be performed within the RMAN utility? (Choose all that apply.)
A. Start up target database.
B. Shut down target database.
C. Grant roles to users.
D. Create user accounts.
---
The RMAN utility can start and stop a target database. Database objects and users’ accounts are not created with the RMAN utility.
Ans: A, B.
5. The tape media management library (MML) enables RMAN to perform which of the following? (Choose all that apply.)
A. Interface with third-party tape hardware vendors.
B. Use third-party automated tape libraries (ATLs).
C. Write to any tape.
D. Write to disk.
---
The tape media library enables RMAN to interface with other tape hardware vendors and use their automated tape library systems. Writing to disk and tape can still be performed by using special tape libraries.
Ans: A, B.
6. Which of the following commands is used in automatic channel allocation?
A. ALLOCATE CHANNEL C1 TYPE DISK
B. ALLOCATE CHANNEL C1 TYPE ‘SBT_TAPE’
C. CONFIGURE DEFAULT DEVICE TYPE
D. CONFIGURE DEFAULT TYPE DEVICE
---
The CONFIGURE DEFAULT DEVICE TYPE command is used during automatic channel allocation. The CONFIGURE DEFAULT TYPE DEVICE command is incorrect and the other examples are manual channel allocation examples.
Ans: C
7. Which of the following commands are examples of those used in the manual channel allocation process? (Choose all that apply.)
A. ALLOCATE CHANNEL T1 TYPE DISK
B. ALLOCATE CHANNEL T1 TYPE ‘SBT_TAPE’
C. CONFIGURE DEFAULT DEVICE TYPE
D. CONFIGURE DEFAULT TYPE DEVICE
---
Both examples with ALLOCATE CHANNEL as a part of the command are examples of manual channel allocation. The other examples, C and D, are not manual channel allocation.
Ans: A, B.
8. Which of the following media management libraries are provided with most RMAN installations?
A. MMLs that support Legato Storage Manager software.
B. MMLs that support Disk.
C. MMLs are not supplied.
D. MMLs are not necessary for proprietary tape hardware.
---
The MMLs that are installed with most RMAN installations support the Legato Storage Manager.
Ans: A.
9. Which of the following commands would you use to set the default parallelism for a RMAN session?
A. SET DEVICE TYPE PARALLELISM
B. CONFIGURE DEVICE TYPE PARALLELISM
C. INSTALL DEVICE TYPE PARALLELISM
D. CONFIG DEVICE TYPE PARALLELISM
----
The CONFIGURE DEVICE TYPE PARALLELISM command will set a default parallelism value.
Ans: B.
10. Which of the following commands cannot be used without a recovery catalog? (Choose all that apply.)
A. RESYNCH CATALOG
B. RESET DATABASE
C. REPLACE SCRIPT
D. LIST INCARNATION
E. All of the above
-----
None of these commands can be used unless you have implemented the recovery catalog.
Ans: E.
11. Which of the following commands best describes manual channel allocation? (Choose all that apply.)
A. CONFIGURE DEFAULT DEVICE TYPE
B. CONFIGURE CHANNEL DEVICE TYPE
C. ALLOCATE CHANNEL TYPE SBT_TAPE
D. ALLOCATE CHANNEL TYPE DISK
---
Both of these options are methods of manual channel allocation. SBT_TAPE supports tape allocation and DISK supports disk allocation.
Ans: C, D.
12. Which of the following commands automatically backs up the control file?
A. SET CONTROLFILE AUTOBACKUP ON
B. CONFIGURE CONTROLFILE AUTOBACKUP ON
C. CONFIGURE CONTROLFILE AUTO ON
D. CONFIGURE CONTROLFILE AUTOBACKUP TRUE
---
The CONFIGURE CONTROLFILE AUTOBACKUP ON command will configure the control file to be automatically backed up.
Ans: B.
13. Which of the following files best describes the default media management library for Unix?
A. libobk.sl
B. libobk.so
C. obklib.so
D. libmml.so
---
The file is the default media management library. This must be replaced or pointed to whatever vendor is being used.
Ans: B.
14. Study the following command and then choose the option that best describes what this command does.
RMAN> connect target /
A. Connects to the recovery catalog
B. Connects to the target database
C. Connects to the target database and the recovery catalog
D. Connects to neither the target database nor the recovery catalog
---
This command connects to the target database.
Ans: B.
15. What is the maximum size for the RMAN repository if the recovery catalog is not being used?
A. Dependent on OS file size limitations
B. No limits
C. 2 gigabytes
D. 4 gigabytes
----
The RMAN repository is the target database control file if the recovery catalog is not being used. The size of any file is dependent on the OS in which it resides.
Ans: A.
OCP: Oracle9i DBA Fundamentals II Study Guide
Review Questions
1. Does the RMAN utility require the use of the recovery catalog?
A. The recovery catalog is required.
B. The recovery catalog is not required.
C. The recovery catalog is required if it is stored in the same database as the target database.
D. The recovery catalog is not required if it is stored in the same database as the target database.
---
The recovery catalog is optional regardless of the configuration of the target database. It is used to store information about the backup and recovery process in much the same way that the data dictionary stores information about the database.
Ans: B.
2. What are some of the capabilities of the RMAN utility? (Choose all that apply.)
A. Backs up databases, tablespaces, data files, control files, and archived logs
B. Compresses backups
C. Provides scripting capabilities
D. Tests whether backups can be restored
E. All of the above
---
All answers are capabilities of the RMAN utility.
Ans: E.
3. What type of interface does the RMAN utility support? (Choose all that apply.)
A. GUI through Oracle Enterprise Manager
B. Command-line interface
C. Command line only
D. GUI through Oracle Enterprise Manager only
---
The RMAN utility can be run in GUI mode via the use of Oracle Enterprise Manager or through a command-line interface on the server.
Ans: A, B.
4. What actions can be performed within the RMAN utility? (Choose all that apply.)
A. Start up target database.
B. Shut down target database.
C. Grant roles to users.
D. Create user accounts.
---
The RMAN utility can start and stop a target database. Database objects and users’ accounts are not created with the RMAN utility.
Ans: A, B.
5. The tape media management library (MML) enables RMAN to perform which of the following? (Choose all that apply.)
A. Interface with third-party tape hardware vendors.
B. Use third-party automated tape libraries (ATLs).
C. Write to any tape.
D. Write to disk.
---
The tape media library enables RMAN to interface with other tape hardware vendors and use their automated tape library systems. Writing to disk and tape can still be performed by using special tape libraries.
Ans: A, B.
6. Which of the following commands is used in automatic channel allocation?
A. ALLOCATE CHANNEL C1 TYPE DISK
B. ALLOCATE CHANNEL C1 TYPE ‘SBT_TAPE’
C. CONFIGURE DEFAULT DEVICE TYPE
D. CONFIGURE DEFAULT TYPE DEVICE
---
The CONFIGURE DEFAULT DEVICE TYPE command is used during automatic channel allocation. The CONFIGURE DEFAULT TYPE DEVICE command is incorrect and the other examples are manual channel allocation examples.
Ans: C
7. Which of the following commands are examples of those used in the manual channel allocation process? (Choose all that apply.)
A. ALLOCATE CHANNEL T1 TYPE DISK
B. ALLOCATE CHANNEL T1 TYPE ‘SBT_TAPE’
C. CONFIGURE DEFAULT DEVICE TYPE
D. CONFIGURE DEFAULT TYPE DEVICE
---
Both examples with ALLOCATE CHANNEL as a part of the command are examples of manual channel allocation. The other examples, C and D, are not manual channel allocation.
Ans: A, B.
8. Which of the following media management libraries are provided with most RMAN installations?
A. MMLs that support Legato Storage Manager software.
B. MMLs that support Disk.
C. MMLs are not supplied.
D. MMLs are not necessary for proprietary tape hardware.
---
The MMLs that are installed with most RMAN installations support the Legato Storage Manager.
Ans: A.
9. Which of the following commands would you use to set the default parallelism for a RMAN session?
A. SET DEVICE TYPE PARALLELISM
B. CONFIGURE DEVICE TYPE PARALLELISM
C. INSTALL DEVICE TYPE PARALLELISM
D. CONFIG DEVICE TYPE PARALLELISM
----
The CONFIGURE DEVICE TYPE PARALLELISM command will set a default parallelism value.
Ans: B.
10. Which of the following commands cannot be used without a recovery catalog? (Choose all that apply.)
A. RESYNCH CATALOG
B. RESET DATABASE
C. REPLACE SCRIPT
D. LIST INCARNATION
E. All of the above
-----
None of these commands can be used unless you have implemented the recovery catalog.
Ans: E.
11. Which of the following commands best describes manual channel allocation? (Choose all that apply.)
A. CONFIGURE DEFAULT DEVICE TYPE
B. CONFIGURE CHANNEL DEVICE TYPE
C. ALLOCATE CHANNEL
D. ALLOCATE CHANNEL
---
Both of these options are methods of manual channel allocation. SBT_TAPE supports tape allocation and DISK supports disk allocation.
Ans: C, D.
12. Which of the following commands automatically backs up the control file?
A. SET CONTROLFILE AUTOBACKUP ON
B. CONFIGURE CONTROLFILE AUTOBACKUP ON
C. CONFIGURE CONTROLFILE AUTO ON
D. CONFIGURE CONTROLFILE AUTOBACKUP TRUE
---
The CONFIGURE CONTROLFILE AUTOBACKUP ON command will configure the control file to be automatically backed up.
Ans: B.
13. Which of the following files best describes the default media management library for Unix?
A. libobk.sl
B. libobk.so
C. obklib.so
D. libmml.so
---
The file is the default media management library. This must be replaced or pointed to whatever vendor is being used.
Ans: B.
14. Study the following command and then choose the option that best describes what this command does.
RMAN> connect target /
A. Connects to the recovery catalog
B. Connects to the target database
C. Connects to the target database and the recovery catalog
D. Connects to neither the target database nor the recovery catalog
---
This command connects to the target database.
Ans: B.
15. What is the maximum size for the RMAN repository if the recovery catalog is not being used?
A. Dependent on OS file size limitations
B. No limits
C. 2 gigabytes
D. 4 gigabytes
----
The RMAN repository is the target database control file if the recovery catalog is not being used. The size of any file is dependent on the OS in which it resides.
Ans: A.
Friday, September 15, 2006
Partitioned Table and Index
- When a table or index is partitioned, the table or index can be assigned to more than one tablespace.
- The table or index is distributed across multiple tablespaces using a partitioning key or a hashing algorithm
- An example: a histroy table
-- current data which is often queried is placed on a tablespace assigned to a faster data storage device,
-- old data placed on tablespaces assigned to slower devices
- When planning for tablespace backup with partitioned tables or indexes, consider the dependencies involved with the other tablespaces that contain the partitioned tables or indexes.
- The table or index is distributed across multiple tablespaces using a partitioning key or a hashing algorithm
- An example: a histroy table
-- current data which is often queried is placed on a tablespace assigned to a faster data storage device,
-- old data placed on tablespaces assigned to slower devices
- When planning for tablespace backup with partitioned tables or indexes, consider the dependencies involved with the other tablespaces that contain the partitioned tables or indexes.
Thursday, September 14, 2006
Fundamental I Ch9 - Managing Users, Security, and Globalization Support
Chapter 9 - Managing Users, Security, and Globalization Support
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla
This is the last chapter
Review Questions
1. Profiles cannot be used to restrict which of the following?
A. CPU time used
B. Total time connected to the database
C. Maximum time a session can be inactive
D. Time spent reading blocks
----
There is no resource parameter in the profile definition to monitor the time spent reading blocks, but you can restrict the number of blocks read per SQL statement or per session.
Ans: D.
2. Which command is used to assign a profile to an existing user?
A. ALTER PROFILE.
B. ALTER USER.
C. SET PROFILE.
D. The profile should be specified when creating the user; it cannot be changed.
----
You use the PROFILE clause in the ALTER USER command to set the profile for an existing user. You must have the ALTER USER privilege to do this.
Ans: B.
How does one enforce strict password control?
3. Which resource is not used to calculate the COMPOSITE_LIMIT?
A. PRIVATE_SGA
B. CPU_PER_SESSION
C. CONNECT_TIME
D. LOGICAL_READS_PER_CALL
----
Call-level resources are not used to calculate the COMPOSITE_LIMIT. You can set the resource cost of the four resources (the fourth is LOGICAL_READS_PER_SESSION) using the ALTER RESOURCE COST command.
Ans: D
4. Choose the option that is not true.
A. Oracle creates a profile named DEFAULT when the database is created.
B. Profiles cannot be renamed.
C. DEFAULT is a valid name for a profile resource.
D. The SESSIONS_PER_USER resource in the DEFAULT profile initially has a value of 5.
---
All resources in the default profile have a value of UNLIMITED when the database is created. You can change these values.
Ans: D.
5. What is the maximum number of profiles that can be assigned to a user?
A. 1
B. 2
C. 32
D. Unlimited
----
A user can have only one profile assigned. You can query the profile assigned to a user from the DBA_USERS view.
Ans: A.
6. What happens when you create a new user and do not specify a profile?
A. Oracle prompts you for a profile name.
B. No profile is assigned to the user.
C. The DEFAULT profile is assigned.
D. The SYSTEM profile is assigned.
---
The DEFAULT profile is created when the database is created and is assigned to users if you do not specify a profile for the new user. Before you can assign a profile, you must create the user in the database.
Ans: C.
7. Which resource specifies the value in minutes?
A. CPU_PER_SESSION
B. CONNECT_TIME
C. PASSWORD_LOCK_TIME
D. All the above
---
CONNECT_TIME is specified in minutes, CPU_PER_SESSION is specified in hundredths of a second, and PASSWORD_LOCK_TIME is specified in days.
Ans: B.
8. Which password parameter in the profile definitions can restrict the user from using the old password for 90 days?
A. PASSWORD_REUSE_TIME
B. PASSWORD_REUSE_MAX
C. PASSWORD_LIFE_TIME
D. PASSWORD_REUSE_DAYS
---
PASSWORD_REUSE_TIME specifies the number of days required before the old password can be reused; PASSWORD_REUSE_MAX specifies the number of password changes required before a password can be reused. At least one of these parameters must be set to UNLIMITED.
Ans: A.
9. Which dictionary view shows the password expiration date for a user?
A. DBA_PROFILES
B. DBA_USERS
C. DBA_PASSWORDS
D. V$SESSION
---
The DBA_USERS view shows the password expiration date, account status, and locking date along with the user’s tablespace assignments, profile, creation date, and so on.
Ans: B.
10. Which clause in the CREATE USER command can be used to specify no limits on the space allowed in tablespace APP_DATA?
A. DEFAULT TABLESPACE
B. UNLIMITED TABLESPACE
C. QUOTA
D. PROFILE
---
You use the QUOTA clause to specify the amount of space allowed on a tablespace; you can specify a size or UNLIMITED. The user will have unlimited space if the system privilege UNLIMITED TABLESPACE is granted.
Ans: C.
11. User JAMES has a table named JOBS created on the tablespace USERS. When you issue the following statement, what effect it will have on the JOBS table?
ALTER USER JAMES QUOTA 0 ON USERS;
A. No more rows can be added to the JOBS table.
B. No blocks can be allocated to the JOBS table.
C. No new extents can be allocated to the JOBS table.
D. The table JOBS cannot be accessed.
----
When the space quota is exceeded or quota is removed from a user on a tablespace, the tables remain in the tablespace, but no new extents can be allocated.
Ans: C
12. Which view would you query to see whether John has the CREATE TABLE privilege?
A. DBA_SYS_PRIVS
B. DBA_USER_PRIVS
C. DBA_ROLE_PRIVS
D. DBA_TAB_PRIVS
---
CREATE TABLE is a system privilege. You can query system privileges from DBA_SYS_PRIVS or USER_SYS_PRIVS.
Ans: A.
13. Which clause should you specify to enable the grantee to grant the system privilege to other users?
A. WITH GRANT OPTION
B. WITH ADMIN OPTION
C. CASCADE
D. WITH MANAGE OPTION
---
The WITH ADMIN OPTION specified with system privileges enables the grantee to grant the privileges to others, and the WITH GRANT OPTION specified with object privileges enables the grantee to grant the privilege to others.
Ans: B.
14. Which of the following is not a system privilege?
A. SELECT
B. UPDATE ANY
C. EXECUTE ANY
D. CREATE TABLE
----
SELECT, INSERT, UPDATE, DELETE, EXECUTE, and REFERENCES are object privileges. SELECT ANY, UPDATE ANY, and so on are system privileges.
Ans: A.
15. Which data dictionary view can you query to see whether a user has the EXECUTE privilege on a procedure?
A. DBA_SYS_PRIVS
B. DBA_TAB_PRIVS
C. DBA_PROC_PRIVS
D. SESSION_PRIVS
---
The DBA_TAB_PRIVS, USER_TAB_PRIVS, and ALL_TAB_PRIVS views show information about the object privileges.
Ans: B
16. To grant the SELECT privilege on the table CUSTOMER to all users in the database, which statement would you use?
A. GRANT SELECT ON CUSTOMER TO ALL USERS;
B. GRANT ALL ON CUSTOMER TO ALL;
C. GRANT SELECT ON CUSTOMER TO ALL;
D. GRANT SELECT ON CUSTOMER TO PUBLIC;
----
PUBLIC is the group or class of database users to which all users of the database belong.
Ans: D.
17. Which role in the following list is not a predefined role from Oracle?
A. SYSDBA
B. CONNECT
C. IMP_FULL_DATABASE
D. RESOURCE
---
SYSDBA and SYSOPER are not roles; they are system privileges.
Ans: A.
18. How do you enable a role?
A. ALTER ROLE
B. ALTER USER
C. SET ROLE
D. ALTER SESSION
----
You use the SET ROLE command to enable or disable granted roles for the user. The view SESSION_ROLES shows the roles that are enabled in the session. All default roles are enabled when the user connects to the database.
Ans: C.
19. What is accomplished when you issue the following statement?
ALTER USER JOHN DEFAULT ROLE ALL;
A. John is assigned all the roles created in the database.
B. Future roles granted to John will not be default roles.
C. All of John’s roles are enabled, except the roles with passwords.
D. All of John’s roles are enabled when he connects to the database.
---
Ans D. Default roles are enabled when a user connects to the database even if the roles are password authorized.
20. Which command defines CONNECT and RESOURCE as the default roles for user JAMES?
A. ALTER USER
B. ALTER ROLE
C. SET ROLE
D. SET PRIVILEGE
---
Ans: A. The ALTER USER command defines the default role(s) for a user.
21. Which data dictionary view shows the database character set?
A. V$DATABASE
B. NLS_DATABASE_PARAMETERS
C. NLS_INSTANCE_PARAMETERS
D. NLS_SESSION_PARAMETERS
-----
Ans: B. The NLS_DATABASE_PARAMETERS view shows the database character set and all the NLS parameter settings. The character set cannot be changed at the instance or session level, so the character set information does not show up in the NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS views.
22. Choose two NLS parameters that cannot be modified using the ALTER SESSION statement.
A. NLS_CHARACTERSET
B. NLS_SORT
C. NLS_NCHAR_CHARACTERSET
D. NLS_TERRITORY
----
You cannot change the character set after creating the database. The CHARACTER SET and NATIONAL CHARACTER SET clauses are used in the CREATE DATABASE command.
Ans: A and C.
Manage Users
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla
This is the last chapter
Review Questions
1. Profiles cannot be used to restrict which of the following?
A. CPU time used
B. Total time connected to the database
C. Maximum time a session can be inactive
D. Time spent reading blocks
----
There is no resource parameter in the profile definition to monitor the time spent reading blocks, but you can restrict the number of blocks read per SQL statement or per session.
Ans: D.
2. Which command is used to assign a profile to an existing user?
A. ALTER PROFILE.
B. ALTER USER.
C. SET PROFILE.
D. The profile should be specified when creating the user; it cannot be changed.
----
You use the PROFILE clause in the ALTER USER command to set the profile for an existing user. You must have the ALTER USER privilege to do this.
Ans: B.
How does one enforce strict password control?
3. Which resource is not used to calculate the COMPOSITE_LIMIT?
A. PRIVATE_SGA
B. CPU_PER_SESSION
C. CONNECT_TIME
D. LOGICAL_READS_PER_CALL
----
Call-level resources are not used to calculate the COMPOSITE_LIMIT. You can set the resource cost of the four resources (the fourth is LOGICAL_READS_PER_SESSION) using the ALTER RESOURCE COST command.
Ans: D
4. Choose the option that is not true.
A. Oracle creates a profile named DEFAULT when the database is created.
B. Profiles cannot be renamed.
C. DEFAULT is a valid name for a profile resource.
D. The SESSIONS_PER_USER resource in the DEFAULT profile initially has a value of 5.
---
All resources in the default profile have a value of UNLIMITED when the database is created. You can change these values.
Ans: D.
5. What is the maximum number of profiles that can be assigned to a user?
A. 1
B. 2
C. 32
D. Unlimited
----
A user can have only one profile assigned. You can query the profile assigned to a user from the DBA_USERS view.
Ans: A.
6. What happens when you create a new user and do not specify a profile?
A. Oracle prompts you for a profile name.
B. No profile is assigned to the user.
C. The DEFAULT profile is assigned.
D. The SYSTEM profile is assigned.
---
The DEFAULT profile is created when the database is created and is assigned to users if you do not specify a profile for the new user. Before you can assign a profile, you must create the user in the database.
Ans: C.
7. Which resource specifies the value in minutes?
A. CPU_PER_SESSION
B. CONNECT_TIME
C. PASSWORD_LOCK_TIME
D. All the above
---
CONNECT_TIME is specified in minutes, CPU_PER_SESSION is specified in hundredths of a second, and PASSWORD_LOCK_TIME is specified in days.
Ans: B.
8. Which password parameter in the profile definitions can restrict the user from using the old password for 90 days?
A. PASSWORD_REUSE_TIME
B. PASSWORD_REUSE_MAX
C. PASSWORD_LIFE_TIME
D. PASSWORD_REUSE_DAYS
---
PASSWORD_REUSE_TIME specifies the number of days required before the old password can be reused; PASSWORD_REUSE_MAX specifies the number of password changes required before a password can be reused. At least one of these parameters must be set to UNLIMITED.
Ans: A.
9. Which dictionary view shows the password expiration date for a user?
A. DBA_PROFILES
B. DBA_USERS
C. DBA_PASSWORDS
D. V$SESSION
---
The DBA_USERS view shows the password expiration date, account status, and locking date along with the user’s tablespace assignments, profile, creation date, and so on.
Ans: B.
10. Which clause in the CREATE USER command can be used to specify no limits on the space allowed in tablespace APP_DATA?
A. DEFAULT TABLESPACE
B. UNLIMITED TABLESPACE
C. QUOTA
D. PROFILE
---
You use the QUOTA clause to specify the amount of space allowed on a tablespace; you can specify a size or UNLIMITED. The user will have unlimited space if the system privilege UNLIMITED TABLESPACE is granted.
Ans: C.
11. User JAMES has a table named JOBS created on the tablespace USERS. When you issue the following statement, what effect it will have on the JOBS table?
ALTER USER JAMES QUOTA 0 ON USERS;
A. No more rows can be added to the JOBS table.
B. No blocks can be allocated to the JOBS table.
C. No new extents can be allocated to the JOBS table.
D. The table JOBS cannot be accessed.
----
When the space quota is exceeded or quota is removed from a user on a tablespace, the tables remain in the tablespace, but no new extents can be allocated.
Ans: C
12. Which view would you query to see whether John has the CREATE TABLE privilege?
A. DBA_SYS_PRIVS
B. DBA_USER_PRIVS
C. DBA_ROLE_PRIVS
D. DBA_TAB_PRIVS
---
CREATE TABLE is a system privilege. You can query system privileges from DBA_SYS_PRIVS or USER_SYS_PRIVS.
Ans: A.
13. Which clause should you specify to enable the grantee to grant the system privilege to other users?
A. WITH GRANT OPTION
B. WITH ADMIN OPTION
C. CASCADE
D. WITH MANAGE OPTION
---
The WITH ADMIN OPTION specified with system privileges enables the grantee to grant the privileges to others, and the WITH GRANT OPTION specified with object privileges enables the grantee to grant the privilege to others.
Ans: B.
14. Which of the following is not a system privilege?
A. SELECT
B. UPDATE ANY
C. EXECUTE ANY
D. CREATE TABLE
----
SELECT, INSERT, UPDATE, DELETE, EXECUTE, and REFERENCES are object privileges. SELECT ANY, UPDATE ANY, and so on are system privileges.
Ans: A.
15. Which data dictionary view can you query to see whether a user has the EXECUTE privilege on a procedure?
A. DBA_SYS_PRIVS
B. DBA_TAB_PRIVS
C. DBA_PROC_PRIVS
D. SESSION_PRIVS
---
The DBA_TAB_PRIVS, USER_TAB_PRIVS, and ALL_TAB_PRIVS views show information about the object privileges.
Ans: B
16. To grant the SELECT privilege on the table CUSTOMER to all users in the database, which statement would you use?
A. GRANT SELECT ON CUSTOMER TO ALL USERS;
B. GRANT ALL ON CUSTOMER TO ALL;
C. GRANT SELECT ON CUSTOMER TO ALL;
D. GRANT SELECT ON CUSTOMER TO PUBLIC;
----
PUBLIC is the group or class of database users to which all users of the database belong.
Ans: D.
17. Which role in the following list is not a predefined role from Oracle?
A. SYSDBA
B. CONNECT
C. IMP_FULL_DATABASE
D. RESOURCE
---
SYSDBA and SYSOPER are not roles; they are system privileges.
Ans: A.
18. How do you enable a role?
A. ALTER ROLE
B. ALTER USER
C. SET ROLE
D. ALTER SESSION
----
You use the SET ROLE command to enable or disable granted roles for the user. The view SESSION_ROLES shows the roles that are enabled in the session. All default roles are enabled when the user connects to the database.
Ans: C.
19. What is accomplished when you issue the following statement?
ALTER USER JOHN DEFAULT ROLE ALL;
A. John is assigned all the roles created in the database.
B. Future roles granted to John will not be default roles.
C. All of John’s roles are enabled, except the roles with passwords.
D. All of John’s roles are enabled when he connects to the database.
---
Ans D. Default roles are enabled when a user connects to the database even if the roles are password authorized.
20. Which command defines CONNECT and RESOURCE as the default roles for user JAMES?
A. ALTER USER
B. ALTER ROLE
C. SET ROLE
D. SET PRIVILEGE
---
Ans: A. The ALTER USER command defines the default role(s) for a user.
21. Which data dictionary view shows the database character set?
A. V$DATABASE
B. NLS_DATABASE_PARAMETERS
C. NLS_INSTANCE_PARAMETERS
D. NLS_SESSION_PARAMETERS
-----
Ans: B. The NLS_DATABASE_PARAMETERS view shows the database character set and all the NLS parameter settings. The character set cannot be changed at the instance or session level, so the character set information does not show up in the NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS views.
22. Choose two NLS parameters that cannot be modified using the ALTER SESSION statement.
A. NLS_CHARACTERSET
B. NLS_SORT
C. NLS_NCHAR_CHARACTERSET
D. NLS_TERRITORY
----
You cannot change the character set after creating the database. The CHARACTER SET and NATIONAL CHARACTER SET clauses are used in the CREATE DATABASE command.
Ans: A and C.
Manage Users
Monday, September 11, 2006
Fundamental I Ch8 - Managing Tables, Indexes, and Constraints
Chapter 8 - Managing Tables, Indexes, and Constraints
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
Review Questions
1. A table is created as follows:
CREATE TABLE MY_TABLE (COL1 NUMBER)
STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 6 PCTINCREASE 0);
When you issue the following statement, what will be the size of the table, if the high-water mark of the table is 200KB?
ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;
A. 1000KB
B. 200KB
C. 12000KB
D. 2MB
E. 13MB
---
Ans:
C. You use the KEEP parameter in the DEALLOCATE clause to specify the amount of space you want to keep in the table above the HWM. If you do not specify the KEEP parameter, Oracle deallocates all the space above the HWM if the HWM is above MINEXTENTS; otherwise, free space is de-allocated above MINEXTENTS.
2. Which command is used to drop a constraint?
A. ALTER TABLE MODIFY CONSTRAINT
B. DROP CONSTRAINT
C. ALTER TABLE DROP CONSTRAINT
D. ALTER CONSTRAINT DROP
----
Ans C. Constraints are defined on the table and are dropped using the ALTER TABLE command DROP clause. For dropping the primary key, you can also specify PRIMARY KEY instead of the constraint name. Similarly, to drop a unique constraint, you can also specify UNIQUE ().
3. When you define a column with datatype TIMESTAMP WITH LOCAL TIME ZONE, what is the precision of seconds stored?
A. 2
B. 6
C. 9
D. 0
---
Ans:
B. The TIMESTAMP datatypes have a default precision of 6 digits. The range of values can be from 0 to 9.
4. Which data dictionary view has the timestamp of the table creation?
A. DBA_OBJECTS
B. DBA_SEGMENTS
C. DBA_TABLES
D. All the above
---
Ans: A. The DBA_OBJECTS view has information about all the objects created in the database and has the timestamp and status of the object in the column CREATED. DBA_TABLES does not show the timestamp.
5. What happens when you issue the following statement and the CHAINED_ROWS table does not exist in the current schema?
ANALYZE TABLE EMPLOYEE LIST CHAINED ROWS;
A. Oracle creates the CHAINED_ROWS table.
B. Oracle updates the dictionary with the number of chained rows in the table.
C. Oracle creates the CHAINED_ROWS table under the SYS schema; if one already exists under SYS, Oracle uses it.
D. The statement fails.
---
Ans: D. If you do not specify a table name to insert the ROWID of chained/ migrated rows, Oracle looks for a table named CHAINED_ROWS in the user’s schema. If the table does not exist, Oracle returns an error. The dictionary (the DBA_TABLES view) is updated with the number of chained rows when you do a COMPUTE STATISTICS on the table.
6. The following statement is issued against the primary key constraint (PK_BONUS) of the BONUS table. (Choose two statements that are true.)
ALTER TABLE BONUS MODIFY CONSTRAINT PK_BONUS DISABLE VALIDATE;
A. No new rows can be added to the BONUS table.
B. Existing rows of the BONUS table are validated before disabling the constraint.
C. Rows can be modified, but the primary key columns cannot change.
D. The unique index created when defining the constraint is dropped.
---
6. A and D. DISABLE VALIDATE disables the constraint and drops the index, but keeps the constraint valid. No DML operation is allowed on the table.
7. Which clause in the ANALYZE command checks for the integrity of the rows in the table?
A. COMPUTE STATISTICS
B. VALIDATE STRUCTURE
C. LIST INVALID ROWS
D. None of the above
----
Ans: B. The VALIDATE STRUCTURE clause of the ANALYZE TABLE command checks the structure of the table and makes sure all rows are readable.
8. Which statement is not true?
A. A partition can be range-partitioned.
B. A subpartition can be range-partitioned.
C. A partition can be hash-partitioned.
D. A subpartition can be hash-partitioned.
----
Ans:
B. Subpartitions can only be hash-partitioned. A partition can be range-partitioned or hash-partitioned.
9. A table is created with an INITRANS value of 2. Which value would you choose for INITRANS of an index created on this table?
A. 4
B. 2
C. 1
----
Ans:
A. Since index blocks hold more entries per block than table data blocks hold, you should provide a higher value of INITRANS to the index than to the table.
10. When validating a constraint, why would you specify the EXCEPTIONS clause?
A. To display the ROWIDs of the rows that do not satisfy the constraint
B. To move the bad rows to the table specified in the EXCEPTIONS clause
C. To save the ROWIDs of the bad rows in the table specified in the EXCEPTIONS clause
D. To save the bad rows in the table specified in the EXCEPTIONS clause
---
Ans:
C. If you specify the EXCEPTIONS INTO clause when validating or enabling a constraint, the ROWIDs of the rows that do not satisfy the constraint are saved in the table specified in the EXCEPTIONS clause. You can remove the bad rows or fix the column values and enable the constraint.
11. Which keyword is not valid for the BUFFER_POOL parameter of the STORAGE clause?
A. DEFAULT
B. LARGE
C. KEEP
D. RECYCLE
----
The BUFFER_POOL parameter specifies a buffer pool cache for the table or index. The KEEP pool retains the blocks in the SGA. RECYCLE removes blocks from the SGA as soon as the operation is completed, and the DEFAULT pool is for objects for which KEEP or RECYCLE is not specified.
Ans: B
12. Which clause in the ALTER TABLE command do you use to reorganize a table?
A. REORGANIZE
B. REBUILD
C. RELOCATE
D. MOVE
----
You use the MOVE clause to reorganize a table. You can specify new tablespace and storage parameters. Queries are allowed on the table, but no DML operations are allowed during the move.
Ans: D.
13. Which line in the following code has an error?
1 ALTER TABLE MY_TABLE
2 STORAGE (
3 MINEXTENTS 4
4 NEXT 512K)
5 NOLOGGING;
A. 2
B. 3
C. 4
D. 5
----
When you change the storage parameters for an existing index or table, you cannot change the MINEXTENTS and INITIAL values.
Ans: B.
14. Which component is not part of the ROWID?
A. Tablespace
B. Data file number
C. Object ID
D. Block ID
----
The format of a ROWID is OOOOOOFFFBBBBBBRRR; OOOOOO is the object number, FFF is the relative data file number where the block is located, BBBBBB is the block ID where the row is located, and RRR is the row in the block.
Ans: A.
15. Which keyword do you use in the CREATE INDEX command to create a function-based index?
A. CREATE FUNCTION INDEX
B. CREATE INDEX ORGANIZATION INDEX
C. CREATE INDEX FUNCTION BASED
D. None of the above
----
You don’t need to specify a keyword to create a function-based index; you need only to specify the function itself. To enable a function- based index, you set the parameter QUERY_REWRITE_ENABLED to TRUE, and you set QUERY_REWRITE_INTEGRITY to TRUSTED.
Ans: D.
16. Which data dictionary view shows statistical information from the ANALYZE INDEX VALIDATE STRUCTURE command?
A. INDEX_STATS
B. DBA_INDEXES
C. IND
D. None; VALIDATE STRUCTURE does not generate statistics.
---
The INDEX_STATS and INDEX_HISTOGRAMS views show statistical information from the ANALYZE INDEX VALIDATE STRUCTURE statement.
Ans: A.
17. A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this mean?
A. Constraint checking is done only at commit time.
B. Constraint checking is done after each SQL statement, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.
C. Existing rows in the table are immediately checked for constraint violation.
D. The constraint is immediately checked in a DML operation, but subsequent constraint verification is done at commit time.
---
DEFERRABLE specifies that the constraint can be deferred using the SET CONSTRAINTS command. INITIALLY IMMEDIATE specifies that the constraint’s default behavior is to validate the constraint for each SQL statement.
Ans: B.
18. Which script creates the CHAINED_ROWS table?
A. catproc.sql
B. catchain.sql
C. utlchain.sql
D. No script is necessary; ANALYZE TABLE LIST CHAINED ROWS creates the table.
---
The utlchain.sql script, located in the rdbms/admin directory for the Oracle software installation, creates the table. When chained or migrated rows are found in the table after you issue the ANALYZE TABLE LIST CHAINED ROWS command, the ROWIDs of such chained/ migrated rows are inserted into the CHAINED_ROWS table.
Ans: C.
19. What is the difference between a unique constraint and a primary key constraint?
A. A unique key constraint requires a unique index to enforce the constraint, whereas a primary key constraint can enforce uniqueness using a unique or non-unique index.
B. A primary key column can be NULL, but a unique key column cannot be NULL.
C. A primary key constraint can use an existing index, but a unique constraint always creates an index.
D. A unique constraint column can be NULL, but primary key column(s) cannot be NULL.
----
Columns that are part of the primary key cannot accept NULL values.
Ans: D.
20. You can monitor an index for its usage by using the MONITORING USAGE clause of the ALTER INDEX statement. Which data dictionary view do you use to query the index usage?
A. USER_INDEX_USAGE
B. V$OBJECT_USAGE
C. V$INDEX_USAGE
D. DBA_INDEX_USAGE
----
The V$OBJECT_USAGE view has information about the indexes that are monitored. The START_MONITORING and END_ MONITORING columns give the start and end timestamp of monitoring. If an index is used, the USED column will have a value YES.
Ans: B.
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
Review Questions
1. A table is created as follows:
CREATE TABLE MY_TABLE (COL1 NUMBER)
STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 6 PCTINCREASE 0);
When you issue the following statement, what will be the size of the table, if the high-water mark of the table is 200KB?
ALTER TABLE MY_TABLE DEALLOCATE UNUSED KEEP 1000K;
A. 1000KB
B. 200KB
C. 12000KB
D. 2MB
E. 13MB
---
Ans:
C. You use the KEEP parameter in the DEALLOCATE clause to specify the amount of space you want to keep in the table above the HWM. If you do not specify the KEEP parameter, Oracle deallocates all the space above the HWM if the HWM is above MINEXTENTS; otherwise, free space is de-allocated above MINEXTENTS.
2. Which command is used to drop a constraint?
A. ALTER TABLE MODIFY CONSTRAINT
B. DROP CONSTRAINT
C. ALTER TABLE DROP CONSTRAINT
D. ALTER CONSTRAINT DROP
----
Ans C. Constraints are defined on the table and are dropped using the ALTER TABLE command DROP clause. For dropping the primary key, you can also specify PRIMARY KEY instead of the constraint name. Similarly, to drop a unique constraint, you can also specify UNIQUE (
3. When you define a column with datatype TIMESTAMP WITH LOCAL TIME ZONE, what is the precision of seconds stored?
A. 2
B. 6
C. 9
D. 0
---
Ans:
B. The TIMESTAMP datatypes have a default precision of 6 digits. The range of values can be from 0 to 9.
4. Which data dictionary view has the timestamp of the table creation?
A. DBA_OBJECTS
B. DBA_SEGMENTS
C. DBA_TABLES
D. All the above
---
Ans: A. The DBA_OBJECTS view has information about all the objects created in the database and has the timestamp and status of the object in the column CREATED. DBA_TABLES does not show the timestamp.
5. What happens when you issue the following statement and the CHAINED_ROWS table does not exist in the current schema?
ANALYZE TABLE EMPLOYEE LIST CHAINED ROWS;
A. Oracle creates the CHAINED_ROWS table.
B. Oracle updates the dictionary with the number of chained rows in the table.
C. Oracle creates the CHAINED_ROWS table under the SYS schema; if one already exists under SYS, Oracle uses it.
D. The statement fails.
---
Ans: D. If you do not specify a table name to insert the ROWID of chained/ migrated rows, Oracle looks for a table named CHAINED_ROWS in the user’s schema. If the table does not exist, Oracle returns an error. The dictionary (the DBA_TABLES view) is updated with the number of chained rows when you do a COMPUTE STATISTICS on the table.
6. The following statement is issued against the primary key constraint (PK_BONUS) of the BONUS table. (Choose two statements that are true.)
ALTER TABLE BONUS MODIFY CONSTRAINT PK_BONUS DISABLE VALIDATE;
A. No new rows can be added to the BONUS table.
B. Existing rows of the BONUS table are validated before disabling the constraint.
C. Rows can be modified, but the primary key columns cannot change.
D. The unique index created when defining the constraint is dropped.
---
6. A and D. DISABLE VALIDATE disables the constraint and drops the index, but keeps the constraint valid. No DML operation is allowed on the table.
7. Which clause in the ANALYZE command checks for the integrity of the rows in the table?
A. COMPUTE STATISTICS
B. VALIDATE STRUCTURE
C. LIST INVALID ROWS
D. None of the above
----
Ans: B. The VALIDATE STRUCTURE clause of the ANALYZE TABLE command checks the structure of the table and makes sure all rows are readable.
8. Which statement is not true?
A. A partition can be range-partitioned.
B. A subpartition can be range-partitioned.
C. A partition can be hash-partitioned.
D. A subpartition can be hash-partitioned.
----
Ans:
B. Subpartitions can only be hash-partitioned. A partition can be range-partitioned or hash-partitioned.
9. A table is created with an INITRANS value of 2. Which value would you choose for INITRANS of an index created on this table?
A. 4
B. 2
C. 1
----
Ans:
A. Since index blocks hold more entries per block than table data blocks hold, you should provide a higher value of INITRANS to the index than to the table.
10. When validating a constraint, why would you specify the EXCEPTIONS clause?
A. To display the ROWIDs of the rows that do not satisfy the constraint
B. To move the bad rows to the table specified in the EXCEPTIONS clause
C. To save the ROWIDs of the bad rows in the table specified in the EXCEPTIONS clause
D. To save the bad rows in the table specified in the EXCEPTIONS clause
---
Ans:
C. If you specify the EXCEPTIONS INTO clause when validating or enabling a constraint, the ROWIDs of the rows that do not satisfy the constraint are saved in the table specified in the EXCEPTIONS clause. You can remove the bad rows or fix the column values and enable the constraint.
11. Which keyword is not valid for the BUFFER_POOL parameter of the STORAGE clause?
A. DEFAULT
B. LARGE
C. KEEP
D. RECYCLE
----
The BUFFER_POOL parameter specifies a buffer pool cache for the table or index. The KEEP pool retains the blocks in the SGA. RECYCLE removes blocks from the SGA as soon as the operation is completed, and the DEFAULT pool is for objects for which KEEP or RECYCLE is not specified.
Ans: B
12. Which clause in the ALTER TABLE command do you use to reorganize a table?
A. REORGANIZE
B. REBUILD
C. RELOCATE
D. MOVE
----
You use the MOVE clause to reorganize a table. You can specify new tablespace and storage parameters. Queries are allowed on the table, but no DML operations are allowed during the move.
Ans: D.
13. Which line in the following code has an error?
1 ALTER TABLE MY_TABLE
2 STORAGE (
3 MINEXTENTS 4
4 NEXT 512K)
5 NOLOGGING;
A. 2
B. 3
C. 4
D. 5
----
When you change the storage parameters for an existing index or table, you cannot change the MINEXTENTS and INITIAL values.
Ans: B.
14. Which component is not part of the ROWID?
A. Tablespace
B. Data file number
C. Object ID
D. Block ID
----
The format of a ROWID is OOOOOOFFFBBBBBBRRR; OOOOOO is the object number, FFF is the relative data file number where the block is located, BBBBBB is the block ID where the row is located, and RRR is the row in the block.
Ans: A.
15. Which keyword do you use in the CREATE INDEX command to create a function-based index?
A. CREATE FUNCTION INDEX
B. CREATE INDEX ORGANIZATION INDEX
C. CREATE INDEX FUNCTION BASED
D. None of the above
----
You don’t need to specify a keyword to create a function-based index; you need only to specify the function itself. To enable a function- based index, you set the parameter QUERY_REWRITE_ENABLED to TRUE, and you set QUERY_REWRITE_INTEGRITY to TRUSTED.
Ans: D.
16. Which data dictionary view shows statistical information from the ANALYZE INDEX VALIDATE STRUCTURE command?
A. INDEX_STATS
B. DBA_INDEXES
C. IND
D. None; VALIDATE STRUCTURE does not generate statistics.
---
The INDEX_STATS and INDEX_HISTOGRAMS views show statistical information from the ANALYZE INDEX VALIDATE STRUCTURE statement.
Ans: A.
17. A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this mean?
A. Constraint checking is done only at commit time.
B. Constraint checking is done after each SQL statement, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.
C. Existing rows in the table are immediately checked for constraint violation.
D. The constraint is immediately checked in a DML operation, but subsequent constraint verification is done at commit time.
---
DEFERRABLE specifies that the constraint can be deferred using the SET CONSTRAINTS command. INITIALLY IMMEDIATE specifies that the constraint’s default behavior is to validate the constraint for each SQL statement.
Ans: B.
18. Which script creates the CHAINED_ROWS table?
A. catproc.sql
B. catchain.sql
C. utlchain.sql
D. No script is necessary; ANALYZE TABLE LIST CHAINED ROWS creates the table.
---
The utlchain.sql script, located in the rdbms/admin directory for the Oracle software installation, creates the table. When chained or migrated rows are found in the table after you issue the ANALYZE TABLE LIST CHAINED ROWS command, the ROWIDs of such chained/ migrated rows are inserted into the CHAINED_ROWS table.
Ans: C.
19. What is the difference between a unique constraint and a primary key constraint?
A. A unique key constraint requires a unique index to enforce the constraint, whereas a primary key constraint can enforce uniqueness using a unique or non-unique index.
B. A primary key column can be NULL, but a unique key column cannot be NULL.
C. A primary key constraint can use an existing index, but a unique constraint always creates an index.
D. A unique constraint column can be NULL, but primary key column(s) cannot be NULL.
----
Columns that are part of the primary key cannot accept NULL values.
Ans: D.
20. You can monitor an index for its usage by using the MONITORING USAGE clause of the ALTER INDEX statement. Which data dictionary view do you use to query the index usage?
A. USER_INDEX_USAGE
B. V$OBJECT_USAGE
C. V$INDEX_USAGE
D. DBA_INDEX_USAGE
----
The V$OBJECT_USAGE view has information about the indexes that are monitored. The START_MONITORING and END_ MONITORING columns give the start and end timestamp of monitoring. If an index is used, the USED column will have a value YES.
Ans: B.
Monday, September 04, 2006
Fundamentals I Ch7 - Segments and Storage Structures
Chapter 7 - Segments and Storage Structures
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla
Review Questions
1. Place the following logical storage structures in order—from the smallest logical storage unit to the largest.
A. Segment
B. Block
C. Tablespace
D. Extent
---
Ans:
B, D, A, and C. A data block is the smallest logical storage unit in Oracle. An extent is a group of contiguous blocks. A segment consists of one or more extents. A segment can belong to only one tablespace. A tablespace can have many segments.
2. When a table is updated, where is the before-image information (which can be used for undoing the changes) stored?
A. Temporary segment
B. Redo log buffer
C. Undo buffer
D. Rollback segment
---
Ans: D.
Before any DML operation, the undo information (before-image of data) is stored in the undo segments. This information is used to undo the changes and to provide a read-consistent view of the data.
3. Which parameter specifies the number of transaction slots in a data block?
A. MAXTRANS
B. INITRANS
C. PCTFREE
D. PCTUSED
----
Ans:
B. INITRANS specifies the number of transaction slots in a data block. Oracle uses a transaction slot when the data block is being modified. INITRANS reserves space for the transactions in the block.
MAXTRANS specifies the maximum number of concurrent transactions allowed in the block. The default of INITRANS for a block in a data segment is 1, and the default for the block in an index segment is 2.
4. Select the statement that is not true regarding undo tablespaces.
A. Undo tablespaces will not be created if they are not specified in the CREATE DATABASE statement.
B. Two undo tablespaces may be active if a new undo tablespace was specified and there are pending transactions on the old one.
C. You can switch from one undo tablespace to another.
B. UNDO_MANAGEMENT cannot be changed dynamically while the instance is running.
----
Ans:
A. If a specific undo tablespace is not defined in the CREATE DATABASE statement, Oracle automatically creates one with the name SYS_UNDOTBS .
5. Which of the following database objects consists of more than one segment?
A. Nested Table
B. Partitioned table
C. Index Partition
D. Undo segment
E. None of the above
---
Ans: B.
A partitioned table consists of multiple table partition segments in different tablespaces.
Notes: How about Nested Table?
Nested Table - If a table has columns that are tables themselves (nested tables), each column is stored in its own segment. Each segment may have its own storage parameters.
6. Which of the following segment allocation parameters is ignored when automatic segment space management is in effect for a tablespace?
A. FREELISTS
B. PCTFREE
C. INITRANS
D. MAXTRANS
----
Ans: A. Enabling automatic segment space management uses bitmaps instead of freelists to manage free space.
Notes: PCTUSED is ignored too.
7. Which data dictionary view would you query to see the free extents in a tablespace?
A. DBA_TABLESPACES
B. DBA_FREE_SPACE
C. DBA_EXTENTS
D. DBA_SEGMENTS
---
Ans:
B. DBA_FREE_SPACE shows the free extents in a tablespace. DBA_EXTENTS shows all the extents that are allocated to a segment.
8. Which two data dictionary views can account for the total amount of space in a data file?
A. DBA_FREE_SEGMENTS
B. DBA_FREE_SPACE
C. DBA_SEGMENTS
D. DBA_EXTENTS
---
Ans: B, D. The sum of the free space in DBA_FREE_SPACE plus the space allocated for extents in DBA_EXTENTS should add up to the total space specified for that tablespace. DBA_FREE_SEGMENTS is not a valid data dictionary view, and DBA_SEGMENTS only contains the number of extents and blocks allocated to each segment.
9. Which portion of the data block stores information about the table having rows in this block?
A. Common and variable header
B. Row directory
C. Table directory
D. Row data
Ans:
C. The table directory portion of the block stores information about the table having rows in the block. The row directory stores information such as row address and size of the actual rows stored in the row data area.
10. When does Oracle stop adding rows to a block?
A. When free space reaches the PCTFREE threshold
B. When row data reaches the PCTFREE threshold
C. When free space drops below the PCTUSED threshold
D. When row data drops below the PCTUSED threshold
---
Ans: A.
The PCTFREE and PCTUSED parameters are used to manage the free space in the block. Oracle inserts rows into a block until the free space falls below the PCTFREE threshold. PCTFREE is the amount of space reserved for future updates. Oracle considers adding more rows to the block only when the free space falls below the PCTUSED threshold.
11. What main restriction is placed on tablespaces defined with automatic segment space management?
A. The tablespace cannot contain nested tables.
B. The tablespace cannot be transportable.
C. The tablespace cannot contain LOBs.
D. The bootstrap segment cannot reside in a tablespace that has automatic segment space management enabled.
----
Ans:
C. Table segments that have LOBs cannot reside in a locally managed tablespace that has automatic segment space management enabled.
12. Which dynamic performance view can help you adjust the size of an undo tablespace?
A. V$UNDOSTAT
B. V$ROLLSTAT
C. V$SESSION
D. V$ROLLNAME
----
Ans:
A. The V$UNDOSTAT view, in conjunction with the value for UNDO_RETENTION and DB_BLOCK_SIZE parameters, can be used to calculate an optimal undo tablespace size when database activity is at its peak.
13. What is the default value of PCTFREE?
A. 40
B. 0
C. 100
D. 10
---
Ans:
D. The default value of PCTFREE is 10, and the default for PCTUSED is 40.
14. Which data dictionary view can you query to see the OPTIMAL value for a rollback segment?
A. DBA_ROLLBACK_SEGS
B. V$ROLLSTAT
C. DBA_SEGMENTS
D. V$ROLLNAME
----
Ans:
B. You can query the OPTIMAL value from the V$ROLLSTAT view. This view does not show the offline rollback segments.
15. What is row migration?
A. A single row spread across multiple blocks
B. Moving a table from one tablespace to another
C. Storing a row in a different block when there is not enough room in the current block for the row to expand
D. Deleting a row and adding it back to the same table
---
Ans:
C. Row migration is the movement of a row from one block to a new block. Row migration occurs when a row is updated and its new size cannot fit into the free space of the block; Oracle moves the row to a new block, leaving a pointer in the old block to the new block. You can avoid this problem by either setting a higher PCTFREE value or specifying a larger block size at database creation.
16. What can cause the Snapshot too old error?
A. Smaller rollback extents
B. Higher MAXEXTENTS value
C. Larger rollback extents
D. Higher OPTIMAL value
----
Ans:
A. Smaller rollback extents can cause the Snapshot too old error if there are long-running queries in the database.
17. The sum of the values PCTFREE and PCTUSED cannot exceed which of the following:
A. 255
B. DB_BLOCK_SIZE
C. The maximum is operating system dependent.
D. 100
---
D. These two numbers are percentages that are defined as the percentage of a given block, and since these areas cannot overlap, the sum cannot be greater than 100 percent.
18. Which of the following statements may require a temporary segment?
A. CREATE TABLE
B. CREATE INDEX
C. UPDATE
D. CREATE TABLESPACE
-----
Ans:
B. Operations that require a sort may need a temporary segment (when the sort operation cannot be completed in the memory area specified by SORT_AREA_SIZE ). Queries that use DISTINCT , GROUP BY , ORDER BY , UNION , INTERSECT , or MINUS clauses also need a sort of the result set.
19. How does Oracle determine the extent sizes for a temporary segment?
A. From the initialization parameters
B. From the tables involved in the sort operation
C. Using the default storage parameters for the tablespace
D. The database block size
---
Ans:
C. The default storage parameters for the tablespace determine the extent sizes for temporary segments.
20. Fill in the blank: The parameter MAXTRANS specifies the maximum number of concurrent transactions per __________.
A. Table
B. Segment
C. Extent
D. Block
---
Ans:
D. MAXTRANS specifies the maximum allowed concurrent transactions per block. Oracle needs transaction space for each concurrent transaction in the block s variable header. You can pre allocate space by specifying INITRANS .
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla
Review Questions
1. Place the following logical storage structures in order—from the smallest logical storage unit to the largest.
A. Segment
B. Block
C. Tablespace
D. Extent
---
Ans:
B, D, A, and C. A data block is the smallest logical storage unit in Oracle. An extent is a group of contiguous blocks. A segment consists of one or more extents. A segment can belong to only one tablespace. A tablespace can have many segments.
2. When a table is updated, where is the before-image information (which can be used for undoing the changes) stored?
A. Temporary segment
B. Redo log buffer
C. Undo buffer
D. Rollback segment
---
Ans: D.
Before any DML operation, the undo information (before-image of data) is stored in the undo segments. This information is used to undo the changes and to provide a read-consistent view of the data.
3. Which parameter specifies the number of transaction slots in a data block?
A. MAXTRANS
B. INITRANS
C. PCTFREE
D. PCTUSED
----
Ans:
B. INITRANS specifies the number of transaction slots in a data block. Oracle uses a transaction slot when the data block is being modified. INITRANS reserves space for the transactions in the block.
MAXTRANS specifies the maximum number of concurrent transactions allowed in the block. The default of INITRANS for a block in a data segment is 1, and the default for the block in an index segment is 2.
4. Select the statement that is not true regarding undo tablespaces.
A. Undo tablespaces will not be created if they are not specified in the CREATE DATABASE statement.
B. Two undo tablespaces may be active if a new undo tablespace was specified and there are pending transactions on the old one.
C. You can switch from one undo tablespace to another.
B. UNDO_MANAGEMENT cannot be changed dynamically while the instance is running.
----
Ans:
A. If a specific undo tablespace is not defined in the CREATE DATABASE statement, Oracle automatically creates one with the name SYS_UNDOTBS .
5. Which of the following database objects consists of more than one segment?
A. Nested Table
B. Partitioned table
C. Index Partition
D. Undo segment
E. None of the above
---
Ans: B.
A partitioned table consists of multiple table partition segments in different tablespaces.
Notes: How about Nested Table?
Nested Table - If a table has columns that are tables themselves (nested tables), each column is stored in its own segment. Each segment may have its own storage parameters.
6. Which of the following segment allocation parameters is ignored when automatic segment space management is in effect for a tablespace?
A. FREELISTS
B. PCTFREE
C. INITRANS
D. MAXTRANS
----
Ans: A. Enabling automatic segment space management uses bitmaps instead of freelists to manage free space.
Notes: PCTUSED is ignored too.
7. Which data dictionary view would you query to see the free extents in a tablespace?
A. DBA_TABLESPACES
B. DBA_FREE_SPACE
C. DBA_EXTENTS
D. DBA_SEGMENTS
---
Ans:
B. DBA_FREE_SPACE shows the free extents in a tablespace. DBA_EXTENTS shows all the extents that are allocated to a segment.
8. Which two data dictionary views can account for the total amount of space in a data file?
A. DBA_FREE_SEGMENTS
B. DBA_FREE_SPACE
C. DBA_SEGMENTS
D. DBA_EXTENTS
---
Ans: B, D. The sum of the free space in DBA_FREE_SPACE plus the space allocated for extents in DBA_EXTENTS should add up to the total space specified for that tablespace. DBA_FREE_SEGMENTS is not a valid data dictionary view, and DBA_SEGMENTS only contains the number of extents and blocks allocated to each segment.
9. Which portion of the data block stores information about the table having rows in this block?
A. Common and variable header
B. Row directory
C. Table directory
D. Row data
Ans:
C. The table directory portion of the block stores information about the table having rows in the block. The row directory stores information such as row address and size of the actual rows stored in the row data area.
10. When does Oracle stop adding rows to a block?
A. When free space reaches the PCTFREE threshold
B. When row data reaches the PCTFREE threshold
C. When free space drops below the PCTUSED threshold
D. When row data drops below the PCTUSED threshold
---
Ans: A.
The PCTFREE and PCTUSED parameters are used to manage the free space in the block. Oracle inserts rows into a block until the free space falls below the PCTFREE threshold. PCTFREE is the amount of space reserved for future updates. Oracle considers adding more rows to the block only when the free space falls below the PCTUSED threshold.
11. What main restriction is placed on tablespaces defined with automatic segment space management?
A. The tablespace cannot contain nested tables.
B. The tablespace cannot be transportable.
C. The tablespace cannot contain LOBs.
D. The bootstrap segment cannot reside in a tablespace that has automatic segment space management enabled.
----
Ans:
C. Table segments that have LOBs cannot reside in a locally managed tablespace that has automatic segment space management enabled.
12. Which dynamic performance view can help you adjust the size of an undo tablespace?
A. V$UNDOSTAT
B. V$ROLLSTAT
C. V$SESSION
D. V$ROLLNAME
----
Ans:
A. The V$UNDOSTAT view, in conjunction with the value for UNDO_RETENTION and DB_BLOCK_SIZE parameters, can be used to calculate an optimal undo tablespace size when database activity is at its peak.
13. What is the default value of PCTFREE?
A. 40
B. 0
C. 100
D. 10
---
Ans:
D. The default value of PCTFREE is 10, and the default for PCTUSED is 40.
14. Which data dictionary view can you query to see the OPTIMAL value for a rollback segment?
A. DBA_ROLLBACK_SEGS
B. V$ROLLSTAT
C. DBA_SEGMENTS
D. V$ROLLNAME
----
Ans:
B. You can query the OPTIMAL value from the V$ROLLSTAT view. This view does not show the offline rollback segments.
15. What is row migration?
A. A single row spread across multiple blocks
B. Moving a table from one tablespace to another
C. Storing a row in a different block when there is not enough room in the current block for the row to expand
D. Deleting a row and adding it back to the same table
---
Ans:
C. Row migration is the movement of a row from one block to a new block. Row migration occurs when a row is updated and its new size cannot fit into the free space of the block; Oracle moves the row to a new block, leaving a pointer in the old block to the new block. You can avoid this problem by either setting a higher PCTFREE value or specifying a larger block size at database creation.
16. What can cause the Snapshot too old error?
A. Smaller rollback extents
B. Higher MAXEXTENTS value
C. Larger rollback extents
D. Higher OPTIMAL value
----
Ans:
A. Smaller rollback extents can cause the Snapshot too old error if there are long-running queries in the database.
17. The sum of the values PCTFREE and PCTUSED cannot exceed which of the following:
A. 255
B. DB_BLOCK_SIZE
C. The maximum is operating system dependent.
D. 100
---
D. These two numbers are percentages that are defined as the percentage of a given block, and since these areas cannot overlap, the sum cannot be greater than 100 percent.
18. Which of the following statements may require a temporary segment?
A. CREATE TABLE
B. CREATE INDEX
C. UPDATE
D. CREATE TABLESPACE
-----
Ans:
B. Operations that require a sort may need a temporary segment (when the sort operation cannot be completed in the memory area specified by SORT_AREA_SIZE ). Queries that use DISTINCT , GROUP BY , ORDER BY , UNION , INTERSECT , or MINUS clauses also need a sort of the result set.
19. How does Oracle determine the extent sizes for a temporary segment?
A. From the initialization parameters
B. From the tables involved in the sort operation
C. Using the default storage parameters for the tablespace
D. The database block size
---
Ans:
C. The default storage parameters for the tablespace determine the extent sizes for temporary segments.
20. Fill in the blank: The parameter MAXTRANS specifies the maximum number of concurrent transactions per __________.
A. Table
B. Segment
C. Extent
D. Block
---
Ans:
D. MAXTRANS specifies the maximum allowed concurrent transactions per block. Oracle needs transaction space for each concurrent transaction in the block s variable header. You can pre allocate space by specifying INITRANS .
An example - Snapshot too old
Snapshot too old
User SCOTT has updated the EMP table and has not committed the changes. The old values of the rows updated by SCOTT are written to the undo segment. When user JAKE queries the EMP table, Oracle uses the undo segment to produce a read-consistent view of the table. If JAKE initiated a long query, Oracle fetches the blocks in multiple iterations. User SCOTT can commit his transaction, and the undo segment is marked committed. If another transaction overwrites the same undo segment, JAKE’s transaction will not be able to get the view of the EMP table when the transaction started. This produces a snapshot too old error.
User SCOTT has updated the EMP table and has not committed the changes. The old values of the rows updated by SCOTT are written to the undo segment. When user JAKE queries the EMP table, Oracle uses the undo segment to produce a read-consistent view of the table. If JAKE initiated a long query, Oracle fetches the blocks in multiple iterations. User SCOTT can commit his transaction, and the undo segment is marked committed. If another transaction overwrites the same undo segment, JAKE’s transaction will not be able to get the view of the EMP table when the transaction started. This produces a snapshot too old error.
Row chaining and Row Migration
row chaining and row migration.
If the table row length is bigger than a block, or if the table has LONG or LOB columns, it is difficult to fit one row entirely in one block. Oracle stores such rows in more than one block. This situation is unavoidable, and storing such rows in multiple blocks is known as row chaining.
In some cases, the row will fit into a block with other rows, but due to an update activity, the row length increases and no free space remains available to accommodate the modified row. Oracle then moves the entire row from its original block to a new block, leaving a pointer in the original block to refer to the new block. This process is known as row migration.
Both row migration and row chaining affect the performance of queries, because Oracle has to read more than one block to retrieve the row.
~
If the table row length is bigger than a block, or if the table has LONG or LOB columns, it is difficult to fit one row entirely in one block. Oracle stores such rows in more than one block. This situation is unavoidable, and storing such rows in multiple blocks is known as row chaining.
In some cases, the row will fit into a block with other rows, but due to an update activity, the row length increases and no free space remains available to accommodate the modified row. Oracle then moves the entire row from its original block to a new block, leaving a pointer in the original block to refer to the new block. This process is known as row migration.
Both row migration and row chaining affect the performance of queries, because Oracle has to read more than one block to retrieve the row.
~
Subscribe to:
Posts (Atom)
