1Z0-045 - Oracle Database 10g DBA New Features for Oracle8i OCPs
Go back to Oracle
Which four are true when implementing automatic segment-space management? (Choose four.)
You can specify automatic segment-space management only for permanent, locally managed tablespaces
Specification of automatic management is at the tablespace level
Free and used space is tracked with bitmaps rather than free lists
Automatic segment-space management simplifies space management within a segment
You work as a database administrator for . You started the instance using the init.orafile. You have two control files and three redo log groups in your database. You decided to protect the database against failures by adding one more control file. Which file is the appropriate way to perform this task?
Shut down the instance, copy the control file to the third location, modify the CONTROL_FILES parameter in the init.ora file and open the database.
You used the following command to perform backup: RMAN> BACKUP AS COPY DURATION 4:00 PARTIAL MINIMIZE LOAD DATABASE; Which recommendation regarding the above command is true?
It is recommended that you do not use the above command in the RUN block.
The Automatic Database Diagnostic Monitor (ADDM) analysis runs every 60 minutes on your database. Your database if facing a series of interrelated problems over a period of two hours. You need to ensure that the ADDM analysis is run over a time span of two hours in future. What would you do?
Modify the AWR snapshot time interval to two hours.
Consider the following configuration: /devices/D1 is a member of disk group dgroupA. /devices/D2 is a member of disk group dgroupA. /devices/D3 is a member of disk group dgroupA. You plan to add a new disk, /devices/D4, to the disk group dgroupA and execute the following command: Which task would be accomplished by the command?
The command adds the new disk, D4 to the disk group.
Which method would you use to undo the changes made by a particular transaction without affecting the changes made by other transaction?
determine all the necessary undo SQL statements from FLASHBACK_TRANSACTION_QUERY and use them for recovery.
You want the database to send an e-mail to the senior database administrator whenever the Warning threshold for the Database CPU time metric is reached. Which option would you use to accomplish this activity?
the Database Control page
You executed the following SQL statement to shrink the EMPLOYEES table segment stored in the EXAMPLE tablespace: ALTER TABLE EMPLOYEES SHRINK SPACE CASCADE ; Which statement is correct in this scenario?
The shrink behavior will be cascaded to all dependent segments of the table that support a shrink operation.
You are unable to move the Unified Job Scheduler occupant from the SYSAUX tablespace to the USERS tablespace. What could be the reason?
The united Job Scheduler occupant cannot be relocated.
Which two statements are correct regarding renaming a tablespace? (Choose two)
You can rename a tablespace provided that COMPATIBILITY initialization parameter is set to at least 10.0.0.0.0.
When you rename a tablespace, the Oracle 10g database updates all references to the tablespace name in the data dictionary as well as the control file.
What does the DBMS_WM.GETWORKSPACE function return?
the name of your current workspace
In order to speed forign key certain, Oracle 10G will cache the first_____primary key values only where there are multirow DML statements.
An AFTER SUSPEND system event trigger containing no exception handler is fired, and runs out of space after inserting some audit records. Which two events happen? (Choose two.)
The trigger is aborted
The original operation (the one that fired the trigger) is aborted
What does the Character Set Scanner command line utility do?
checks for any character definitions that will fail conversion to the new characterset
Examine this startup script called startmydb.sql: CONNECT myid/mypwd AS SYSDBA STARTUP EXIT In Oracle 8i, you could have executed this script by invoking server manager with the command: Svrmgr1 @startmydb.sql Which alternative command could you use to run the startmydb.sql script with SQL*Plus to start your Oracle 10g database?
sqlplus /NOLOG @startmydb.sql
Examine the following commands of Data Pump to import objects to new non-existing users hr1 and oe1. $ expdp system/manager schemas = hr,oe directory = EXP_DIR dumpfile = export.dat include = table $ impdp system/manager schemas = hr1,oe1 directory = EXP_DIR dumpfile = export.dat remap_schema = hr:hr1, oe:oe1 Which objective would be achieved by the above command set?
Expdp will succeed but Impdp will fail because users do no exist.
Examine the statement: SQL> CREATE TABLESPACE user_data 2> EXTENT MANAGEMENT LOCAL 3> SEGMENT SPACE MANAGEMENT AUTO; Which two assumptions must be true for this statement to execute successfully? (choose two)
Oracle Managed Files are used for this instance
The database will manage the free space of segments in the tablesapce using a bitmap.
You work as a database administrator for . You have been asked to use a centralized administrative tool to administer your database servers and application servers. In order to achieve the objective, which component would you configure on each database server?
Oracle Management Agent
You want to convert your existing non-ASM files to ASM filesfor the database PROD. Which method or command would you use to accomplish this task?
The BACKUP AS COPY DATABASE .. command of RMAN
You flashed back the jobs table at 11:00 a.m. to its state at 9:30 a.m. At 11:15 a.m., you decided to retrieve the jobs table as it was at 10:12 a.m. What would you do to retrieve the jobs table fast and with minimum impact to other objects?
use the FLASHBACK TABLE command
You modified the optimizer statistics of a table by using the DBMS_STATS.GATHER_TABLE_STATS procedure. You realized that the newly generated statistics have created suboptimal execution plans for that particular table. What would you do to revert to the previous set of statistics as soon as possible?
execute the DBMS_STATS.RESTORE_TABLE_STATS procedure
Standby redo logs are used in Data Guard environments for a number of reasons. Which three statements are true regarding standby redo logs?
They are required to implement a no-data-loss disaster recovery solution.
They are used on the primary database to allow the primary and a standby database to switch roles easily and quickly without additional DBA intervention.
They must be archived before being applied to a standby database, requiring the ARCH process to be running on the standby irrespective of the archive log mode.
The list below display four step that you need to execute in order to switch from the primary database role to the standby database role. CHOOSE the correct order execution for the steps; 1.Shut down and start up the former primary instance without mounting the database. 2.Issue the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY command. 3.Issue the ALTER DATABASE MOUNT STANDBY DATABASE comman. 4.End read or update activity on the former primary and standby database.
The system was running a normal workload during the last 24 hours. You need to ensure that with the same workload you should be able to flashbash tables by three hours. What would you do to find the system recommendations for the undo retention period and the undo tablespace size?
Specify New Undo Retention as three hours and Analysis Time period as Last One Day in the Undo Advisor page and use its recommendations.
In an online transaction processing (OLTP) environment, you find that the transaction tables get heavily fragmented during the week. You decide to defragment the transaction tables on every Friday at 9:30 p.m. to gain performance. Which two new features of the Oracle 10g database could you use to automate this task? (Choose two)
The DBMS_SCHEDULER package
Enterprise Manager job scheduling
Oracle provides a database package called dbms_redefinition to perform an online rebuild of a table. Which two steps are you recommended to do prior to issuing the dbms_redefinition.start_redef_table procedure call? (Choose two)
invoke the dbms_redefinition.can_redef_table procedure
create an empty interim table with all the desired characteristics
The ______ procedure is used to determine which statements are needed to make your materialized view eligible for fast refresh and usable for general rewrite.
Consider an Oracle instance that is using Automatic Shared Memory Management. You set some of the System Global Area (SGA) parameters as shown below: SGA_TARGET = 200 MB SHARED_POOL_SIZE= 20 MB DB_KEEP_CACHE_SIZE = 30 MB Which two statements are correct in this scenario? (Choose two.)
Shared pool size will never shrink below 20 MB.
If SGA_TARGET is reduced to 150 MB, then it will not affect the value of the DB_KEEP_CACHE_SIZE parameter.
How frequently does the Automatic Database Diagnostic Monitor (ADDM) analysis run by default?
Every 60 minutes
When WORKAREA_SIZE_POLICY is set to AUTO, the work areas are sized to accomplish which two goals? (Choose two)
To allocate memory to work areas in order to optimize both throughput and response time
To ensure that the overall size of the PGA will never exceed the value of PGA_AGGREGATE_TARGET
Which three attributes of the LOG_ARCHIVE_DEST_n initialization parameter control the data availability mode of a Data Guard standby database? (Choose three.)
SYNC or ASYNC to specify that network I/O is to be done synchronously or asynchronously
LGWR or ARCH to specify the process that will perform archival operations
AFFIRM or NOAFFIRM to control whether log archiving disk write operations are to be performed synchronously or asynchronously.
In an online transaction processing(OLTP) environment you find that the transaction tables get heavily fragmented during the week. You decide to defragment the transaction tables on every Friday at 9:30 p,m to gain performance. Which two new features of the Oracle 10G database could you use to automate this task?
the DBMS_SCHEDULER package
Enterprise Manager job scheduling
You added the following policy: EXEC DBMS_RLS.ADD_POLICY (object_schema => 'scott', object_name => 'emp', policy_name => 'hr_policy', fuction_schema => 'scott', policy_function => 'hrpol', policy_type => dbms_rls.static, sec_relevant_cols => 'sal , comm ' ) ; Which two statements regarding the above policy are correct? (Choose two.)
The policy function does not reexecute for each query.
Policy predicates are cached in the System Global Area (SGA).
Which three statements regarding the bigfile tablespace (BFT) are correct? (Choose three)
A BFT always contains a single file.
BFT is supported for locally managed tablespaces.
In BFT, the maximum file size ranges from 8 TB to 128 TB.
You have 100 segments in the USERS tablespace. You get an alert that the USERS tablespace is running low on space. You decide to shrink some segments. Which option would you use to determine which segment to shrink?
The Oracle 10G LogMiner needs a data dictionary copy to display the object names. From which three locations can LogMiner retrieve the data dictionary information?
separate supplemental log file.
Information in the control file
A Data Dictionary copy stored in the redo log file
What is true a bout version-enabled tables?
It is possible to create workspaces if there is no version-enabled table in the database
You want to execute the following statements in resumable mode: CREATE TABLE ... AS SELECT CREATE INDEX CREATE MATERIALIZED VIEW Which two methods can be used to enable a session for resumable space allocation? (Choose two.)
Execute the ALTER SESSION ENABLE RESUMABLE statement.
Set the RESUMABLE_TIMEOUT initialization parameter to a nonzero value.
You want to enforce a company's business policy on several objects by using a single policy function. Which two types of policies can be assigned to the policy_type argument in the dbms_rls.add_policy procedure to achieve the above objective? (Choose two.)
What is the result of setting the CURSOR_SHARING = SIMILAR initialization parameter?
causes the optimizer to examine the statement of ensure cursor sharing occurs for safe literals only
Which two statements regarding unusable indexes are correct? (Choose two.)
You can set SKIP_UNUSABLE_INDEXES to true to skip unusable indexes.
You can check the alert log file to get a message recorded about unusable indexes.
Tom is the DBA of DNX Bank. The users of the banking system are complaining that all debit transactions are taking too long to execute. The details of the application used for this purpose are listed below: Application - Bank_DNX to access banking accounts Module - CUSTLEDGER Action DEBIT_ENTRY Which command should Tom execute in order to gather static for the states problem?
EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE ('BANK_DNX', ' CLUSTLEDGER ', ' DEBIT_ENTRY');
In the Oracle 10g Data Guard architecture, what is the purpose of the Log Transport Services?
to control the automated transfer or redo data from the production database to one or more archival destinations
The database was started up using a text parameter file. What will be default scope of changes made by using the SET clause of the ALTER SYSTEM statement?
Only MEMORY is updated.
Which four policy rule categories are available in the Policy Framework? (Choose four.)
You set SGA parameters in your database as: DB_CACHE_SIZE=24MB JAVA_POOL_SIZE=48MB SHARED_POOL_SIZE=96MB You plan to upgrade to Oracle Database 10g. You need to provide an additional 20 MB memory space to accommodate internal overhead allocations for metadata. Which SGA parameter(s) would you modify?
change SHARED_POOL_SIZE to 116MB
While observing database statistics, you found that the Automatic Workload Repository (AWR) snapshots are being purged after each day. You want the snapshots to be purged on a weekly basis. Which two options would you use to increase the purge interval? (Choose two)
Which statements are true regarding the new Persistent CONFIGURATION FEATURE IN RMAN? (Choose two)
It allows you to set the retention period of backups.
It enables you to store the settings for channel attributes so that you do not have to specify them in each backup o restore command
You work as a database administrator for . In your production database, the size of Database Buffer Cache needs to be increased immediately for the current as well as future instances of the database. The Oracle instance has been configured to accommodate any changes in the size of the memory structures. At this is production database, you want to accomplish this task with no impact on the user's connections. Which activity must you have completed before accomplishing this task?
You must have started the database instance with server parameter file.
If the database is shown degraded performance, which three charts on the Database Control Console Performance page will help you resolve the problem? (Choose three.)
instance throughput chart
waiting and working sessions chart
host paging and run queue information chart