Using DBMS_JOB to submit a Job

Syntax of Using DBMS_JOB to Submit a Job

The difinition of the DBMS_JOB.SUMIT procedure is as follows:

DBMS_JOB.SUBMIT(
job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULTSYSDATE,
interval IN VARCHAR2 DEFAULT 'NULL',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
force IN BOOLEAN DEFAULT FALSE);


We can find an example of using this procedure in the script spauto.sql


dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1/24,'HH'),
'trunc(SYSDATE+1/24,''HH'')',
TRUE,
:instno);



Automate the collection of STATPACK statistics using dbms_job
(1) check job_queue_processes paramter, it should be > 0
(2) Execute the sqlplus script ( collect every 15 min)


Viewing job information from the following views:
DBA_JOBS
DBA_JOBS_RUNNING
USR_JOBS


This procedure changes how often a job runs:

DBMS_JOB.INTERVAL (
job IN BINARY_INTEGER,
interval IN VARCHAR2);



e.g.
execute dbms_job.interval(21, 'sysdate+15/1440')

Lab: Using Statspack Under Oracle XE

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,
press  to 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

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

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

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.

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.

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.

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

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.

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 .

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.

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.
~

Fundamentals I Ch6 - Logical and Physical Database Structures

Chapter 6 - Logical and Physical Database Structures


OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla


Review Questions



1. Which two of the following statements do you execute to make the USERS tablespace read-only, if the tablespace is offline?


A. ALTER TABLESPACE USERS READ ONLY
B. ALTER DATABASE MAKE TABLESPACE USERS READ ONLY
C. ALTER TABLESPACE USERS ONLINE
D. ALTER TABLESPACE USERS TEMPORARY

---
To make a tablespace read-only, all the data files belonging to the tablespace must be online and available. So, bring the tablespace online, and then make it read-only.
Ans: C, A.


2. When is a sort segment that is allocated in a temporary tablespace released?


A. When the sort operation completes
B. When the instance is shut down
C. When you issue ALTER TABLESPACE COALESCE
D. When SMON clears up inactive sort segments

---
The sort segment or temporary segment created in a temporary tablespace is released only when the instance is shut down. Each instance can have one sort segment in the tablespace; the sort segment is created when the first sort for the instance is started.
Ans: B.


3. You created a tablespace using the following statement:


CREATE TABLESPACE MYTS
DATAFILE SIZE 200M AUTOEXTEND ON MAXSIZE 2G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M
SEGMENT SPACE MANAGEMENT AUTO;

Which three parameters does Oracle ignore when you create a table in the MYTS tablespace?

A. PCTFREE
B. PCTUSED
C. FREELISTS
D. FREELIST GROUPS
E. INITIAL
-----
When the tablespace has automatic segment space management, Oracle manages free space automatically using bitmaps. For manual segment space management, Oracle uses free lists.
Ans: B, C, D.


4. What will be the minimum size of the segment created in a tablespace if the tablespace’s default storage values are specified as (INITIAL 2M NEXT 2M MINEXTENTS 3 PCTINCREASE 50) and no storage clause is specified for the object?


A. 2MB
B. 4MB
C. 5MB
D. 7MB
E. 8MB

-----
When the segment is created, it will have three extents; the first extent is 2MB, the second is 2MB, and the third is 3MB. So the total size of the segment is 7MB.
Ans: D.

5. Which of the following would you use to add more space to a tablespace? (Choose two.)


A. ALTER TABLESPACE ADD DATAFILE SIZE
B. ALTER DATABASE DATAFILE RESIZE
C. ALTER DATAFILE RESIZE
D. ALTER TABLESPACE DATAFILE RESIZE
-----
You can add more space to a tablespace either by adding a data file or by increasing the size of an existing data file. Option A does not specify a file name and uses the OMF feature to generate file name.
Ans: A, B.


6. If the DB_BLOCK_SIZE of the database is 8KB, what will be the size of the third extent when you specify the storage parameters as (INITIAL 8K NEXT 8K PCTINCREASE 50 MINEXTENTS 3)?


A. 16KB
B. 24KB
C. 12KB
D. 40KB
----
The third extent size will be NEXT + 0.5 * NEXT, which is 12KB, but the block size is 8KB, so the third extent size will be 16KB. The initial extent allocated will be 16KB (the minimum size for INITIAL is two blocks), and the total segment size is 16 + 8 + 16 = 40KB.
Ans: A.


7. The standard block size for the database is 8KB. You need to create a tablespace with block size of 16KB. Which initialization parameters should be set? (Choose two.)


A. DB_8K_CACHE_SIZE
B. DB_16K_CACHE_SIZE
C. DB_CACHE_SIZE
D. UNDO_MANAGEMENT
E. DB_CREATE_FILE_DEST

----
Set DB_CACHE_SIZE for the standard block size, and set DB_16K_CACHE_SIZE for the non-standard block size. You must not set the DB_8K_CACHE_SIZE parameter because the standard block size is 8KB.
Ans: B, C.



8. Which data dictionary view can you query to obtain information about the files that belong to locally managed temporary tablespaces?

A. DBA_DATA_FILES
B. DBA_TABLESPACES
C. DBA_TEMP_FILES
D. DBA_LOCAL_FILES
----
You create locally managed temporary tablespaces using the CREATE TEMPORARY TABLESPACE command. The data files (temporary files) belonging to these tablespaces are in the DBA_TEMP_FILES view. The EXTENT_MANAGEMENT column of the DBA_TABLESPACES view shows the type of the tablespace. You can query the data files belonging to locally managed permanent tablespaces and dictionary-managed (permanent and temporary) tablespaces from DBA_DATA_FILES. Locally managed temporary tablespaces reduce contention on the data dictionary tables.
Ans: C.



9. When does the SMON process automatically coalesce the tablespaces?


A. When the initialization parameter COALESCE_TABLESPACES is set to TRUE
B. When the PCTINCREASE default storage of the tablespace is set to 0
C. When the PCTINCREASE default storage of the tablespace is set to 50
D. Whenever the tablespace has more than one free extent
-----
The SMON process automatically coalesces free extents in the tablespace when the tablespace’s PCTINCREASE is set to a nonzero value. You can manually coalesce a tablespace by using ALTER TABLESPACE COALESCE.
Ans: C.

Notes: For LMT, will the SMON coalesce the tablespaces?


10. Which operation is permitted on a read-only tablespace?


A. Delete data from table
B. Drop table
C. Create new table
D. None of the above
-----
A table can be dropped from a read-only tablespace. When a table is dropped, Oracle does not have to update the data file; it updates the dictionary tables. Any change to data or creation of new objects is not allowed in a read-only tablespace.
Ans: B.


11. How would you drop a tablespace if the tablespace were not empty?


A. Rename all the objects in the tablespace and then drop the tablespace
B. Remove the data files belonging to the tablespace from the disk
C. Use ALTER DATABASE DROP CASCADE
D. Use DROP TABLESPACE INCLUDING CONTENTS
-----
You use the INCLUDING CONTENTS clause to drop a tablespace that is not empty. Oracle does not remove the data files that belong to the tablespace, if the files are not Oracle managed; you need to do it manually using an operating system command. Oracle updates only the control file. To remove the files, you include the INCLUDING CONTENTS AND DATAFILES clause.
Ans: D.


12. Which command is used to enable the auto-extensible feature for a file, if the file is already part of a tablespace?


A. ALTER DATABASE.
B. ALTER TABLESPACE.
C. ALTER DATA FILE.
D. You cannot change the auto-extensible feature once the data file created.
----
You can use the ALTER TABLESPACE command to rename a file that belongs to the tablespace, but you handle all other file management operations through the ALTER DATABASE command. To enable auto-extension, use ALTER DATABASE DATAFILE AUTOEXTEND ON NEXT MAXSIZE .
Ans: A.


13. The database block size is 4KB. You created a tablespace using the following command.


CREATE TABLESPACE USER_DATA DATAFILE 'C:/DATA01.DBF'
EXTENT MANAGEMENT DICTIONARY;

If you create an object in the database without specifying any storage parameters, what will be the size of the third extent that belongs to the object?

A. 6KB
B. 20KB
C. 50KB
D. 32KB
----
When you create a tablespace with no default storage parameters, Oracle assigns (5 x DB_BLOCK_SIZE) to INITIAL and NEXT; PCTINCREASE is 50. So the third extent would be 50 percent more than the second. The first extent is 20KB, the second is 20KB, and the third is 32KB (because the block size is 4KB).
Ans: D.


14. Which of the following statements is false?


A. You can make a dictionary-managed temporary tablespace permanent.
B. You cannot change the size of the locally managed temporary tablespace file.
C. Once it is created, you cannot alter the extent management of a tablespace using ALTER TABLESPACE.
D. You cannot make a locally managed permanent tablespace temporary.
E. If you do not specify an extent management clause when creating a tablespace, Oracle creates a locally managed tablespace.
----
B. You can change the size of a temporary file using ALTER DATABASE TEMPFILE RESIZE . You cannot rename a temporary file.
Ans: B.


15. Which of the following statements is true regarding the SYSTEM tablespace?


A. Can be made read-only.
B. Can be offline.
C. Data files can be renamed.
D. Data files cannot be resized.

----
You can rename the data files that belong to the SYSTEM tablespace when the database is in the MOUNT state by using the ALTER DATABASE RENAME FILE statement.
Ans: C.


16. What are the recommended INITIAL and NEXT values for a temporary tablespace, to reduce fragmentation?


A. INITIAL = 1MB; NEXT = 2MB
B. INITIAL = multiple of SORT_AREA_SIZE + 1; NEXT = INITIAL
C. INITIAL = multiple of SORT_AREA_SIZE + DB_BLOCK_SIZE; NEXT = INITIAL
D. INITIAL = 2 \ SORT_AREA_SIZE; NEXT = SORT_AREA_SIZE
----
The recommended storage for a TEMPORARY tablespace is a multiple of SORT_AREA_SIZE + DB_BLOCK_SIZE. For example, if the sort area size is 100KB and the block size is 4KB, the sort extents should be sized 104KB, 204KB, 304KB, and so on. The disk is sorted only when there is not enough space available in memory. Memory sort size is specified by the SORT_AREA_SIZE parameter. Therefore, when the sorting is done on disk, the minimum area required is as big as the SORT_AREA_SIZE, and one block is added for the overhead. The INITIAL and NEXT storage parameters should be the same for the TEMPORARY tablespace, and PCTINCREASE should be zero. You can achieve these storage settings by creating a locally managed temporary tablespace with uniform extent sizes.
Ans: C.


17. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after you create the tablespace?


A. INITIAL
B. NEXT
C. MAXEXTENTS
D. None
----
You can change all the default storage parameters defined for the tablespace using the ALTER TABLESPACE command. Once objects are created, you cannot change their INITIAL and MINEXTENTS values.
Ans: D.


18. How would you determine how much sort space is used by a user session?


A. Query the DBA_SORT_SEGMENT view.
B. Query the V$SORT_SEGMENT view.
C. Query the V$SORT_USAGE view.
D. You can obtain only the total sort segment size; you cannot find information on individual session sort space usage.
----
The V$SORT_USAGE view provides the number of EXTENTS and number of BLOCKS used by each sort session. This view provides the username also. It can be joined with V$SESSION or V$SQL to obtain more information on the session or the SQL statement causing the sort.
Ans: C.


19. If you issue ALTER TABLESPACE USERS OFFLINE IMMEDIATE, which of the following statements is true? (Choose two.)


A. All data files belonging to the tablespace must be online.
B. Does not ensure that the data files are available.
C. Need not do media recovery when bringing the tablespace online.
D. Need to do media recovery when bringing the tablespace online.
----
When you take a tablespace offline with the IMMEDIATE clause, Oracle does not perform a checkpoint and does not make sure that all data files are available. You must perform a media recovery when the tablespace is brought online.
Ans: B, D.


20. Which format strings does Oracle use to generate OMF file names? (Choose three.)


A. %s
B. %t
C. %g
D. %a
E. %u
F. %%
---
The data file names have the format ora_%t_%u.dbf, redo log files have the format ora_%g_%u.log, and control files have the format ora_%u.ctl. %t is the tablespace name and can be a maximum of 8 characters, %u is an 8-character unique string, and %g is the redo log group number.
Ans: B, C, E.

Data Concurrency and Consistency

Data Concurrency and Consistency

Data Concurrency
- Many users can access data at the same time.

Data consistency
- Each user sees a consistent view of the data, including visible changes made
by the user's own transactions and transactions of other users.


The three preventable phenomena are:
- Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet.
- Nonrepeatable(fuzzy) reads: A transacton rereads data it has previously read and finds that another committed transaction has modified or deleted the data.
- Phantom reads: A transaction re-executes a query returing a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition


SQL92 defines four levels of isolation

---------------------------------------------------------------------
Isolation Level     Dirty Read      Nonrepeatable Read   Phantom Read
---------------------------------------------------------------------
Read uncommitted    Possible        Possible             Possible
Read committed      Not possible    Possible             Possible
Repeatable read     Not possible    Not possible         Possible
Serializable        Not possible    Not possible         Not possible
----------------------------------------------------------------------

Oracle offers
- the read committed and
- serializable isolation

Oracle Isolation Levels
------------------------
- Read Committed
-- default
-- Each query executed by a transaction sees only data that was committed before
the query (not the transaction) began. An Oracle query never reads dirty (uncommitted) data.

- Serializable
-- Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements.

- Read-only

Fundamentals I -Ch5 - Control and Redo Log Files

Chapter 5 - Control and Redo Log Files


OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla



Review Questions


1. Which method is best for renaming a control file?


A. Use the ALTER DATABASE RENAME FILE command.
B. Shut down the database, rename the control file by using an operating system command, and restart the database after changing the CONTROL_FILES parameter in the initialization parameter file.
C. Put the database in RESTRICTED mode and issue the ALTER DATABASE RENAME FILE command.
D. Shut down the database, change the CONTROL_FILES parameter, and start up the database.
E. Re-create the control file using the new name.

To rename (or multiplex, or drop) a control file, you shut down the database, rename (or copy, or delete) the control file by using operating systems commands, change the parameter CONTROL_FILES in the initialization parameter file, and start up the database.
Ans: B.



2. Which piece of information is not available in the control file?


A. Instance name
B. Database name
C. Tablespace names
D. Log sequence number
----
The instance name is not in the control file. The control file has information about the physical database structure.
Ans: A.


3. When you create a control file, the database has to be:


A. Mounted
B. Not mounted
C. Open
D. Restricted
-----
The database should be in the NOMOUNT state to create a control file. When you mount the database, Oracle tries to open the control file to read the physical database structure.
Ans: B.


4. Which data dictionary view provides the names of the control files?


A. V$DATABASE
B. V$INSTANCE
C. V$CONTROLFILESTATUS
D. None of the above
----
The V$CONTROLFILE view shows the names of the control files in the database.
Ans: D.


5. The initialization parameter FAST_START_MTTR_TARGET has been set to 500. What does this mean?


A. A checkpoint will occur every 500 seconds regardless of database activity.
B. A checkpoint will occur after writing 500 blocks to the redo log file.
C. Upon instance startup, the database will not be ready for connections for at least 500 seconds while instance recovery is performed by SMON.
D. Recovery from an instance failure should not take more than 500 seconds.
---
Oracle will automatically adjust other parameters (buffer sizes, intervals, and so on) to ensure that instance recovery will not exceed a specified number of seconds.
Ans: D.


6. Which data dictionary view shows that the database is in ARCHIVELOG mode?


A. V$INSTANCE
B. V$LOG
C. V$DATABASE
D. V$THREAD
---
The V$DATABASE view shows whether the database is in ARCHIVELOG mode or in NOARCHIVELOG mode.
Ans: C.


7. What is the biggest advantage of having the control files on different disks?


A. Database performance.
B. Guards against failure.
C. Faster archiving.
D. Writes are concurrent, so having control files on different disks speeds up control file writes.
-----
Having the control files on different disks ensures that even if you lose one disk, you lose only one control file. If you lose one of the control files, you can shut down the database, copy a control file, or change the CONTROL_FILES parameter and restart the database.
Ans: B.


8. Which file records all changes made to the database and is used only when recovering an instance?


A. Archive log file
B. Redo log file
C. Control file
D. Alert log file
----
The redo log file records all changes made to the database. The LGWR process writes the redo log buffer entries to the redo log files. These entries are used to roll forward, or to update, the data files during an instance recovery. Archive log files are used for media recovery.
Ans: B.


9. What will happen if ARCn could not write to a mandatory archive destination?


A. The database will hang.
B. The instance will shut down.
C. ARCn starts writing to LOG_ARCHIVE_DUPLEX_DEST if it is specified.
D. Oracle stops writing the archived log files.
----
Oracle will write a message to the alert file, and all database operations will be stopped. Database operation resumes automatically after successfully writing the archived log file. If the archive destination becomes full, you can make room for archives either by deleting the archive log files after copying them to a different location or by changing the parameter to point to a different archive location.
Ans: A.


10. How many ARCn processes can be associated with an instance?


A. Five
B. Four
C. Ten
D. Operating system dependent
----
You can have a maximum of ten archiver processes.
Ans: C.



11. Which of the following is an invalid status code in the V$LOGFILE view?


A. STALE
B. Blank
C. ACTIVE
D. INVALID
----

The STATUS column in V$LOGFILE can have the values INVALID (file is not accessible), STALE (file’s contents are incomplete), DELETED (file is no longer used), or blank (file is in use).

Ans: C.


12. If you have two redo log groups with four members each, how many disks does Oracle recommend to keep the redo log files?


A. Eight
B. Two
C. One
D. Four
----
Oracle recommends that you keep each member of a redo log group on a different disk. You should have a minimum of two redo log groups, and it is recommended that you have two members in each group. The maximum number of redo log groups is determined by the MAXLOGFILES database parameter. The MAXLOGMEMBERS database parameter specifies the maximum number of members per group.
Ans: D.


13. What will happen if you issue the following command?


ALTER DATABASE ADD LOGFILE
(‘/logs/file1’ REUSE, ‘/logs/file2’ REUSE);

A. Statement will fail, because the group number is missing
B. Statement will fail, because log file size is missing
C. Creates a new redo log group, with two members
D. Adds two members to the current redo log group
----
The statement creates a new redo log group with two members. When you specify the GROUP option, you must use an integer value. Oracle will automatically generate a group number if the GROUP option is not specified. Use the SIZE option if you are creating a new file. Use the REUSE option if the file already exists.

Ans: C.


14. Which two parameters cannot be used together to specify the archive destination?


A. LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST
B. LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_1
C. LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2
D. None of the above; you can specify all the archive destination parameters with valid destination names.
-----
When using a LOG_ARCHIVE_DEST_n parameter, you cannot use the LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST parameters to specify other archive locations. Using a LOG_ARCHIVE_DEST_n parameter, you can specify as many as five archiving locations.
Ans: B.


15. Which of the following statements is NOT true regarding the use of OMF for redo logs?


A. Dropping log files with OMF automatically drops the related operating system file.
B. OMF manages archived redo log files using the initialization parameter DB_CREATE_ARCHIVE_LOG_DEST_n.
C. A new log file group can be added without specifying a filename in the ALTER DATABASE statement.
D. A log file group managed with OMF can be dropped by specifying only the log group number.
----
You canot manage archived redo logs with OMF.
Ans: B.


16. Querying which view will show whether automatic archiving is enabled?


A. V$ARCHIVE_LOG
B. V$DATABASE
C. V$PARAMETER
D. V$LOG
---
You enable automatic archiving by setting the initialization parameter LOG_ARCHIVE_START = TRUE. All the parameter values can be queried using the V$PARAMETER view. The ARCHIVE LOG LIST command will also show whether automatic archiving is enabled.
Ans: C.


17. If you need to have your archive log files named with the log sequence numbers as arch_0000001, arch_0000002, and so on (zero filled, fixed width), what should be the value of the LOG_ARCHIVE_FORMAT parameter?


A. arch_%S
B. arch_%s
C. arch_000000%s
D. arch_%0%s
----
Four formatting variables are available to use with archive log file names: %s specifies the log sequence number; %S specifies the log sequence number, leading zero filled; %t specifies the thread; and %T specifies the thread, leading zero filled.
Ans: A.


18. Following are the steps needed to rename a redo log file. Order them in the proper sequence.


A. Use an operating system command to rename the redo log file.
B. Shut down the database.
C. ALTER DATABASE RENAME FILE ‘oldfile’ TO ‘newfile’
D. STARTUP MOUNT
E. ALTER DATABASE OPEN
F. Backup the control file.
----
Ans: B, A, D, C, E, and F. The correct order is:

Shut down the database.
Use an operating system command to rename the redo log file.
STARTUP MOUNT
ALTER DATABASE RENAME FILE ‘oldfile’ TO ‘newfile’
ALTER DATABASE OPEN
Back up the control file.


19. Which of the following commands is a key step in multiplexing redo log files using an SPFILE?


A.
ALTER SYSTEM SET CONTROL_FILES= ‘/u01/oradata/PRD/cntrl01.ctl’, ‘/u01/oradata/PRD/cntrl02.ctl’ SCOPE=SPFILE;

B.
ALTER SYSTEM SET CONTROL_FILES=
‘/u01/oradata/PRD/cntrl01.ctl’,
‘/u01/oradata/PRD/cntrl02.ctl’ SCOPE=MEMORY;

C.
ALTER SYSTEM SET CONTROL_FILES=
‘/u01/oradata/PRD/cntrl01.ctl’,
‘/u01/oradata/PRD/cntrl02.ctl’ SCOPE=BOTH;

D.
The number of control files is fixed when the database is created.
-----
The location of the new control files is not valid until an operating system copy is made of the current control file to the new location(s), and the instance is restarted. The SCOPE=SPFILE option specifies that the parameter change will not take place until a restart. Specifying either MEMORY or BOTH will cause an error, since the new control file does not exist yet.
Ans: A.


20. Which statement will add a member /logs/redo22.log to log file group 2?


A. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’ TO GROUP 2;
B. ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2;
C. ALTER DATABASE ADD MEMBER ‘/logs/redo22.log’ TO GROUP 2;
D. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’;
---
When adding log file members, specify the group number or specify all the existing group members. Option D would create a new group with one member.
Ans: B.

Fundamentals I Ch4 - Creating a Database and Data Dictionary

Chapter 4 - Creating a Database and Data Dictionary

OCA/OCP: Oracle9i DBA Fundamentals I Study Guide


Review Questions


1. How many control files are required to create a database?


A. One
B. Two
C. Three
D. None
---
You do not need any control files to create a database; the control files are created when you create the database, based on the filenames specified in the CONTROL_FILES parameter of the parameter file.
Ans: D.


2. Which environment variable or registry entry variable represents the instance name?


A. ORA_SID
B. INSTANCE_NAME
C. ORACLE_INSTANCE
D. ORACLE_SID
----
Ans D. The ORACLE_SID environment variable represents the instance name. When you connect to the database without specifying a connect string, Oracle connects you to this instance.


3. Complete the following sentence: The recommended configuration for control files is


A. One control file per database
B. One control file per disk
C. Two control files on two disks
D. Two control files on one disk
----
Ans: C. Oracle allows multiplexing of control files. If you have two control files on two disks, one disk failure will not damage both control files.


4. You have specified the LOGFILE clause in the CREATE DATABASE command as follows. What happens if the size of the log file redo0101.log, which already exists, is 10MB?

LOGFILE GROUP 1
    (‘/oradata02/PROD01/redo0101.log’,
     ‘/oradata03/PROD01/redo0102.log) SIZE 5M REUSE,
    GROUP 2
    (‘/oradata02/PROD01/redo0201.log’,
     ‘/oradata03/PROD01/redo0202.log) SIZE 5M REUSE

A. Oracle adjusts the size of all the redo log files to 10MB.
B. Oracle creates all the redo log files as 5MB.
C. Oracle creates all the redo log files as 5MB except redo0101.log, which is created as 10MB.
D. The command fails.
----
The CREATE DATABASE command succeeds, and sets all log files to a size of 5M, including the log file that already exists. If a particular log file does not exist, the REUSE clause is ignored and a new file with a size of 5M is created. If a file already exists, you must specify REUSE.
Ans: B.


5. Which command must you issue before you can execute the CREATE DATABASE command?


A. STARTUP INSTANCE
B. STARTUP NOMOUNT
C. STARTUP MOUNT
D. None of the above
----
You must start up the instance to create the database. Connect to the database by using the SYSDBA privilege, and start up the instance by using the command STARTUP NOMOUNT.
Ans: B.


6. Which initialization parameter cannot be changed after creating the database?


A. DB_BLOCK_SIZE
B. DB_NAME
C. CONTROL_FILES
D. None; all the initialization parameters can be changed as and when required.
----
The block size of the database cannot be changed after database creation. The database name can be changed after re-creating the control file with the new name, and the CONTROL_FILES parameter can be changed if the files are copied to a new location.
Ans: A.


7. Which of the following objects or structures can be added or removed from a DBCA template? (Choose three.)

A. Tablespaces
B. File destinations
C. Datafiles
D. Control files
E. Log file groups
----
In addition to tablespaces and data files, undo segments and initialization parameters must also remain exactly the same as defined in the template.
Ans: B, D, E.


8. When you are creating a database, where does Oracle find information about the control files that need to be created?


A. From the initialization parameter file
B. From the CREATE DATABASE command line
C. From the environment variable
D. Files created under $ORACLE_HOME and name derived from .ctl
----
The control file names and locations are obtained from the initialization parameter file. The parameter name is CONTROL_FILES. If this parameter is not specified, Oracle creates a control file; the location and name depend on the operating system platform.
Ans: A.


9. Which script creates the data dictionary views?


A. catalog.sql
B. catproc.sql
C. sql.bsq
D. dictionary.sql
----
The catalog.sql script creates the data dictionary views. The base tables for these views are created by the script sql.bsq, which is executed when you issue the CREATE DATABASE command.
Ans: A.


10. Which prefix for the data dictionary views indicates that the contents of the view belong to the current user?


A. ALL_

B. DBA_

C. USR_

D. USER_
----
DBA_ prefixed views are accessible to the DBA or anyone with the SELECT_CATALOG_ROLE privilege; these views provide information on all the objects in the database and have an OWNER column. The ALL_ views show information about the structures that the user has access to. USER_ views show information about the structures owned by the user.
Ans: D.


11. Which data dictionary view shows information about the status of a procedure?


A. DBA_SOURCE
B. DBA_OBJECTS
C. DBA_PROCEDURES
D. DBA_STATUS
----
The DBA_OBJECTS dictionary view contains information on the objects, their creation, and modification timestamp and status.
Ans: B.


12. How do you correct a procedure that has become invalid when one of the tables it is referring to was altered to drop a constraint?


A. Re-create the procedure
B. ALTER PROCEDURE RECOMPILE
C. ALTER PROCEDURE COMPILE
D. VALIDATE PROCEDURE
----
The invalid procedure, trigger, package, or view can be recompiled by using the ALTER