DB Management tools Flashcards
1
Q
Q: What is an “abort” shutdown in Oracle?
A
An “abort” shutdown, also known as a “fast” or “immediate” shutdown, is a method of shutting down an Oracle database that disconnects all connected users and terminates all background processes immediately, regardless of any active transactions.
2
Q
When should I use an “abort” shutdown?
A
An “abort” shutdown should be used in emergency situations when the database needs to be shut down immediately and there is no time for a “normal” or “transactional” shutdown. This can be used when there is a database corruption or when the database is stuck in a hung state and normal shutdown is not possible.
3
Q
what is SHUTDOWN NORMAL
A
A “normal” shutdown is a way to turn off an Oracle Database. It makes sure all the work is done before turning off, just like you finish your work before going to bed. It is a safe way to stop the database.
4
Q
What does Shutdown TRANSACTIONAL do
A
A “transactional” shutdown is a way to turn off an Oracle Database in a way that allows to finish any ongoing transaction before shutdown, it means that it will wait for all ongoing transactions to complete or rollback before shutting down the database. It’s a safe way to stop the database, but it may take longer than other methods
5
Q
What is a immediate shutdown
A
A “immediate” shutdown is a way to turn off an Oracle Database quickly, without waiting for any ongoing transactions to complete. It’s like turning off the lights and leaving the house quickly, without waiting for everyone to be done with what they’re doing. It’s a fast way to stop the database, but it may cause some unsaved changes to be lost.
6
Q
What is the difference between Shutdown IMMEDIATE and Shutdown ABORT
A
The difference between “immediate” and “abort” shutdown is that “immediate” shutdown will wait for all active transactions to rollback before shutting down the database, while “abort” shutdown will terminate all background processes and disconnect all connected users immediately, regardless of any active transactions. Both options will cause any unsaved changes to be lost, but “abort” shutdown may cause more data corruption if the shutdown process is not handled properly.
7
Q
What is the difference between Shutdown IMMEDIATE and Shutdown TRANSACTIONAL
A
-“Immediate” shutdown will terminate all background processes and disconnect all connected users without waiting for any active transactions to complete or rollback.
-“Transactional” shutdown will wait for all active transactions to complete or rollback before shutting down the database.
-Both options will cause any unsaved changes to be lost.
-“Transactional” shutdown may take longer than an “immediate” shutdown.
8
Q
Which command performs a clean shutdown without waiting for clients to disconnect?
A
SHUTDOWN IMMEDIATE
9
Q
Q: What is the Log Writer (LGWR) process in Oracle Database?
A
A: The Log Writer (LGWR) process is a background process in Oracle Database that is responsible for writing redo log entries to the online redo log files.
10
Q
Q: How does LGWR work?
A
A: LGWR constantly monitors the redo log buffer in the SGA (System Global Area) for new redo log entries. Once the buffer is full or there is a specified time interval, LGWR writes the contents of the buffer to the online redo log files. This process is repeated continuously to ensure that all changes made to the database are captured in the redo logs.
11
Q
Q: What is the importance of LGWR in the context of database recovery?
A
A: LGWR plays a critical role in the context of database recovery. The redo log files written by LGWR contain all the information necessary to recover the database in case of a failure. In the event of a failure, the database can be recovered by applying the redo log entries to the last known good state of the database
12
Q
Q: Can LGWR be configured?
A
A: Yes, LGWR can be configured. For example, you can configure the size of the redo log buffer, the frequency at which LGWR writes to the redo log files, and the number of redo log files that should be used. This can be done via the initialization parameters in the database configuration file or via the ALTER SYSTEM statement.
13
Q
Q: Is there any effect on performance if LGWR is slow?
A
A: If LGWR is slow, it can cause a bottleneck in the system and affect overall performance. This can happen if the redo log buffer is too small, the redo log files are not properly configured or if the storage system is slow. When this happens, LGWR will spend more time writing to the redo log files and less time processing new transactions, resulting in slow performance of the database.
14
Q
Which package should an administrator use to configure local extent management for tablespaces?
A
DBMS_SPACE_ADMIN
15
Q
Q: What is the Oracle Database Process Monitor?
A
A: The Oracle Database Process Monitor (PMON) is a background process in Oracle Database that is responsible for cleaning up after failed or terminated user processes, and for recovery of failed processes.
16
Q
Q: What are the tasks of PMON?
A
Cleaning up after failed or terminated user processes by releasing resources such as locks and memory.
Monitoring and recovering failed database connections.
Monitoring and restarting failed Dispatcher and Server processes.
Coordinating instance recovery in a Real Application Clusters (RAC) environment.
17
Q
Q: How does PMON help in the recovery process?
A
A: PMON plays an important role in the recovery process by detecting failed or terminated user processes, and releasing any resources they were using so that they can be reused by other processes. It also monitors and recovers failed database connections, and restarts failed Dispatcher and Server processes to ensure that the database is always available.
18
Q
Q: Is PMON configurable?
A
A: PMON is not configurable as it is an integral part of the Oracle Database system and its behavior is controlled by the Oracle Database software. However, some of its related functionalities can be managed via database initialization parameters, for example PMON_DUMP_TIMER which controls how frequently PMON writes diagnostic information to the alert log.
19
Q
Q: What is the impact on performance if PMON is not working properly?
A
A: If PMON is not working properly, it can cause various issues such as memory leaks, un-released locks and stuck processes, which can lead to poor performance and possibly even bring down the database. Additionally, it can cause the database to be less available and can lead to recovery failures.
20
Q
Q: What is Oracle DBMS_SPACE_ADMIN package?
A
A: The DBMS_SPACE_ADMIN package is a set of procedures and functions provided by Oracle Database for managing space allocation and usage within the database.
21
Q
Q: What are the functionalities provided by DBMS_SPACE_ADMIN package?
A
A: Some of the functionalities provided by DBMS_SPACE_ADMIN package include:
Managing space allocation for tablespaces, segments and extents.
Reclaiming unused space within a tablespace.
Compacting and defragmenting tablespaces and segments.
Managing temporary tablespaces and temporary segments.
Gathering statistics on space usage and fragmentation.
22
Q
Q: How can DBMS_SPACE_ADMIN be used to manage space allocation?
A
A: DBMS_SPACE_ADMIN provides procedures such as ALLOCATE_EXTENT and DEALLOCATE_EXTENT that can be used to manage space allocation for tablespaces, segments and extents. These procedures can be used to allocate and deallocate space as needed, allowing for more precise control over space usage within the database.
23
Q
Q: How can DBMS_SPACE_ADMIN be used to reclaim unused space?
A
DBMS_SPACE_ADMIN provides a procedure called SHRINK_Tablespace that can reclaim unused space within a tablespace. This procedure can be used to shrink a tablespace to a specified size or a percentage of its current size, effectively reclaiming any unused space.
24
Q
Q: How can DBMS_SPACE_ADMIN be used to reclaim unused space?
A
A: DBMS_SPACE_ADMIN provides a procedure called SHRINK_Tablespace that can be used to reclaim unused space within a tablespace. This procedure can be used to shrink a tablespace to a specified size or to a percentage of its current size, effectively reclaiming any unused space.
25
Q: How can DBMS_SPACE_ADMIN be used to manage temporary tablespaces?
A: DBMS_SPACE_ADMIN provides procedures such as CREATE_TEMP_TABLESPACE and DROP_TEMP_TABLESPACE that can be used to create and drop temporary tablespaces. It also provides procedures such as ADD_TEMP_FILE and DROP_TEMP_FILE for managing temp files for temporary tablespaces.
25
Q: How can DBMS_SPACE_ADMIN be used to manage temporary tablespaces?
A: DBMS_SPACE_ADMIN provides procedures such as CREATE_TEMP_TABLESPACE and DROP_TEMP_TABLESPACE that can be used to create and drop temporary tablespaces. It also provides procedures such as ADD_TEMP_FILE and DROP_TEMP_FILE for managing temp files for temporary tablespaces.
26
Is there any permission need to access DBMS_SPACE_ADMIN package?
To use the procedures and functions in the DBMS_SPACE_ADMIN package, you need the EXECUTE privilege on the package. This privilege is typically granted to database administrators and other users with similar responsibilities.
27
Q: What is Oracle DBMS_AUDIT_MGMT package?
A: The DBMS_AUDIT_MGMT package is a set of procedures and functions provided by Oracle Database for managing and purging audit data in the audit trai
28
Q: What are the functionalities provided by DBMS_AUDIT_MGMT package?
: Some of the functionalities provided by DBMS_AUDIT_MGMT package include:
Purging old audit data from the audit trail.
Archiving audit data to another location.
Managing the audit trail's storage and retention.
Compressing and encrypting the audit trail data.
Auditing and reporting on the use of the DBMS_AUDIT_MGMT packag
29
How can DBMS_AUDIT_MGMT be used to purge old audit data?
DBMS_AUDIT_MGMT provides procedures such as PURGE_AUDIT_TRAIL and PURGE_UNIFIED_AUDIT_TRAIL that can be used to purge old audit data from the audit trail. These procedures can be used to remove audit data that is no longer needed, such as data that is older than a specified date or data that is associated with a specific user or action.
30
How can DBMS_AUDIT_MGMT be used to archive audit data?
DBMS_AUDIT_MGMT provides procedures such as EXPORT_AUDIT_TRAIL and EXPORT_UNIFIED_AUDIT_TRAIL that can be used to export audit data to another location, such as a file or a remote database. These procedures can be used to create an archive of audit data for compliance or reporting purposes.
31
How can DBMS_AUDIT_MGMT be used to manage the storage and retention of the audit trail?
DBMS_AUDIT_MGMT provides procedures such as SET_AUDIT_TRAIL_LOCATION and SET_UNIFIED_AUDIT_TRAIL_LOCATION that can be used to manage the storage and retention of the audit trail. These procedures can be used to change the location where the audit trail is stored, and to set the retention period for the audit trail
32
Is there any permission need to access DBMS_AUDIT_MGMT package?
To use the procedures and functions in the DBMS_AUDIT_MGMT package, you need the EXECUTE privilege on the package. This privilege is typically granted to database administrators and other users with similar responsibilities, or to those who have been granted the AUDIT_ADMIN role.
33
What is the Oracle DBMS_TRANSFORM package?
The DBMS_TRANSFORM package is a set of procedures and functions provided by Oracle Database for performing data type conversion on table columns.
34
What are the functionalities provided by DBMS_TRANSFORM package?
Converting one data type to another.
Converting a column of one data type to another data type.
Changing the length and precision of a column
Transforming a column of one data type to another data type while preserving the data.
Generating a SQL statement that can be used to transform a table column.
35
How can DBMS_TRANSFORM be used to convert a column data type?
DBMS_TRANSFORM provides procedures such as COLUMN_TRANSFORM, COLUMN_TRANSFORM_CV, COLUMN_TRANSFORM_CT, COLUMN_TRANSFORM_CC which can be used to convert a column data type. These procedures can be used to convert a column of one data type to another data type and can also be used to change the length, precision and scale of the column
36
How can DBMS_TRANSFORM be used to generate a SQL statement for column transformation?
DBMS_TRANSFORM package provides procedures such as COLUMN_TRANSFORM_SQL which can be used to generate a SQL statement for column transformation. This procedure can be used to generate a SQL statement that can be used to transform a table column, without actually performing the transformation, in order to test or review the statement before applying it.
37
Can DBMS_TRANSFORM be used for LOB columns?
Yes, DBMS_TRANSFORM can be used to transform LOB columns. The package provides procedures such as COLUMN_TRANSFORM_BLOB, COLUMN_TRANSFORM_CLOB and COLUMN_TRANSFORM_NCLOB which can be used to transform LOB columns
38
Is there any permission need to access DBMS_TRANSFORM package?
To use the procedures and functions in the DBMS_TRANSFORM package, you need the EXECUTE privilege on the package. This privilege is typically granted to database administrators and other users with similar responsibilities
39
What are LOB columns?
LOB (Large Object) columns are a data type in Oracle Database used to store large amounts of binary or character data. LOB columns can store data up to 4 GB in size and can be used to store data such as images, audio, video and other types of unstructured data. There are three types of LOB columns in Oracle Database: BLOB (Binary Large Object), CLOB (Character Large Object) and NCLOB (National Character Large Object).
LOB columns are typically used to store large amounts of unstructured data that do not fit well in traditional relational columns. They are also used to store data that is not frequently accessed, as LOB data is stored out-of-line, separate from the rest of the row, which can affect performance when compared to storing the data in-line.
40
BLOB
columns store binary data, such as images or audio files.
41
CLOB
columns store character data, such as large text documents.
42
NCLOB
columns store national character data, which is similar to CLOB but with support for multiple character sets.
43
COLUMN_TRANSFORM
This procedure is used to convert a column data type to another data type. It can also change the length, precision and scale of the column. The data in the column is preserved during the transformation.
44
COLUMN_TRANSFORM_CV
This procedure is similar to COLUMN_TRANSFORM but it verifies the data after the transformation to ensure that the data is still valid and can be inserted into the new column. If any of the data is invalid, an exception is raised and the transformation is not completed.
45
COLUMN_TRANSFORM_CT
This procedure is similar to COLUMN_TRANSFORM but it creates a new table that contains the transformed column and copies the data from the original table to the new table. The original table is not modified.
46
COLUMN_TRANSFORM_CC
This procedure is similar to COLUMN_TRANSFORM_CV but it creates a new table and copies the data from the original table to the new table. The original table is not modified and the data is verified after the transformation to ensure that the data is still valid and can be inserted into the new column. If any of the data is invalid, an exception is raised and the transformation is not completed.
47
What is Oracle DBMS_METADATA package?
The DBMS_METADATA package is a set of procedures and functions provided by Oracle Database for extracting metadata information from the database.
48
What are the functionalities provided by DBMS_METADATA package?
Extracting the DDL (Data Definition Language) for database objects such as tables, views, indexes, and procedures.
Extracting the DDL for an entire schema or a specific object.
Extracting the DDL in a specific format such as SQL, XML or CREATE scripts.
Extracting the comments and other attributes associated with a database object.
Extracting the statistics and other information about an object.
49
How can DBMS_METADATA be used to extract the DDL for a specific object?
DBMS_METADATA provides procedures such as GET_DDL and GET_XML for extracting the DDL for a specific object. These procedures can be used to extract the DDL for a table, view, index, procedure, etc.
50
How can DBMS_METADATA be used to extract the DDL for a specific schema?
DBMS_METADATA provides procedures such as GET_SCHEMA and GET_XML_SCHEMA for extracting the DDL for a specific schema. These procedures can be used to extract the DDL for all objects in a schema such as tables, views, indexes, procedures, etc.
51
How can DBMS_METADATA be used to extract the comments and attributes of a database object?
DBMS_METADATA provides procedures such as GET_COMMENTS
52
What should an administrator use to identify tables that are fragmented?
Segment Advisor
53
What is the Oracle Segment Advisor?
The Segment Advisor is a feature in Oracle Database that provides recommendations for managing segments (such as tables and indexes) in the database. It analyzes the segments in the database and provides recommendations for improving performance, reducing space usage, and reclaiming unused space.
54
What are the functionalities provided by Segment Advisor
Identifying segments that are consuming too much space
Identifying segments that have excessive fragmentation
Recommending actions such as reorganizing, shrinking, or dropping segments
Identifying opportunities to create bitmap indexes
Analyzing segments in Real Application Clusters (RAC) environment
55
How can Segment Advisor be used to improve performance?
The Segment Advisor can be used to identify segments that are consuming too much space, or have excessive fragmentation, which can negatively impact performance. It can recommend actions such as reorganizing, shrinking, or dropping segments, which can improve performance by reducing the amount of I/O required to access the data.
56
How can Segment Advisor be used to reclaim unused space?
The Segment Advisor can be used to identify segments that are consuming too much space and recommend actions such as shrinking or dropping segments. This can help to reclaim unused space in the database and free up resources for other purposes.
57
Is there any permission need to access Segment Advisor?
To use the Segment Advisor, you need the ADVISOR privilege. This privilege is typically granted to database administrators and other users with similar responsibilities.
58
How is Segment Advisor related to other Automatic Workload Repository (AWR)?
The Segment Advisor uses statistics and other information from the AWR to make its recommendations
59
How is Segment Advisor related to other Automatic Database Diagnostics Monitor (ADDM)?
ADDM can detect performance issues related to segments and trigger the Segment Advisor to run and analyze the segments.
60
How is Segment Advisor related to other Enterprise Manager Database Express
The Segment Advisor can be accessed and run through the Enterprise Manager Database Express web-based interface.
61
How is Segment Advisor related to other DBMS_SPACE_ADMIN?
The Segment Advisor can recommend actions such as shrinking or dropping segments, which can be implemented using the procedures of the DBMS_SPACE_ADMIN package.
62
What is the Oracle Automatic Database Diagnostic Monitor (ADDM)?
The Automatic Database Diagnostic Monitor (ADDM) is a feature in Oracle Database that automatically diagnoses and resolves performance issues in the database. It analyzes performance data and provides recommendations for improving performance, identifying the root cause of performance problems, and identifying opportunities for performance optimization.
63
What are the functionalities provided by ADDM?
Identifying and diagnosing performance issues in the database
Identifying the root cause of performance problems
Providing recommendations for performance optimization
Analyzing performance data in Real Application Clusters (RAC) environment
Generating detailed performance reports
Automatically triggering other performance-related features such as the Segment Advisor to run
64
How can ADDM be used to improve performance?
ADDM can be used to automatically identify and diagnose performance issues in the database, and provide recommendations for performance optimization. It can identify the root cause of performance problems, such as poorly performing SQL statements or contention for resources, and provide recommendations for resolving these issues.
65
How can ADDM be used to generate performance reports?
ADDM can be used to generate detailed performance reports, which can be used to analyze performance over time and identify trends or patterns. These reports can be used to identify performance bottlenecks and opportunities for optimization.
66
Is there any permission need to access ADDM?
To use the ADDM, you need the ADMINISTER DIAGNOSTIC PACKAGE privilege. This privilege is typically granted to database administrators and other users with similar responsibilities.
67
How does ADDM work?
ADDM runs automatically at regular intervals and analyzes performance data collected by the Automatic Workload Repository (AWR) and other sources. It uses this data to identify and diagnose performance issues, identify the root cause of problems, and provide recommendations for resolving issues and optimizing performance.
68
Q: What is Oracle DBMS_SPACE package?
DBMS_SPACE is a package that helps manage space in the Oracle Database.
69
What can DBMS_SPACE be used for?
It can help manage tablespaces, segments, and extents in the database, and reclaim space.
70
What is Oracle DBMS_REPAIR package?
DBMS_REPAIR is a package that helps detect and repair data corruption in the Oracle Database.
71
What can DBMS_REPAIR be used for?
It can help detect and repair corrupt blocks, segments, and objects in the database, and recover lost or corrupted data.
72
What is Oracle UNDO_RETENTION?
UNDO_RETENTION is a parameter in Oracle Database that determines the minimum amount of time in seconds that undo data is kept in the undo tablespace.
73
What is the purpose of UNDO_RETENTION?
It ensures that undo data is kept for long enough for any ongoing or long-running transactions to complete and for any queries that need to access the undo data.
74
What is Oracle DBA_FLASHBACK_RETENTION_TARGET?
DBA_FLASHBACK_RETENTION_TARGET is a parameter in Oracle Database that determines the target amount of time in minutes that data should be retained in the flashback logs for the purpose of Flashback Database.
75
What is the purpose of DBA_FLASHBACK_RETENTION_TARGET?
It sets the retention time for Flashback Database, allowing you to recover the database to a specific point in time within the retention period.
76
Q: What is Oracle SORT_AREA_RETAINED_SIZE?
A: SORT_AREA_RETAINED_SIZE is a parameter in Oracle Database that determines the amount of memory in bytes that should be retained for sorting operations.
77
Q: What is the purpose of SORT_AREA_RETAINED_SIZE?
A: It controls the amount of memory allocated for sorting operations, which can help improve performance by reducing the need to write intermediate results to disk.
78
What is Oracle UNDO_MANAGEMENT?
UNDO_MANAGEMENT is a parameter in Oracle Database that determines how undo data is managed and stored
79
Q: What are the possible values of UNDO_MANAGEMENT?
It can be set to either AUTO or MANUAL. AUTO means Oracle will automatically manage undo data, while MANUAL means the DBA must manually configure undo tablespaces.
80
How is undo tablespace storage allocated?
The undo tablespace storage is allocated as part of the overall database storage allocation.
81
What are the factors that affect undo tablespace storage allocation?
The undo tablespace storage allocation is affected by the UNDO_RETENTION parameter, the size of undo blocks, the number of concurrent transactions and the amount of undo data generated by those transactions.
82
How can I check undo tablespace usage and allocation?
You can check undo tablespace usage and allocation by querying the UNDO_TABLESPACE column of the V$UNDOSTAT view, or by checking the DBA_TABLESPACES view to check the undo_tablespace column.
83
How can I increase undo tablespace storage allocation?
To increase undo tablespace storage allocation, you can either extend the undo tablespace or add an additional undo tablespace.
84
How is undo tablespace storage allocated?
Segments are assigned dynamically each time a change is made.
85
What is Flash recovery area in Oracle?
The Flash recovery area in Oracle is a file system directory or a disk drive that contains all the files needed for recovery and backup of an Oracle database. It includes files such as control files, data files, archived redo logs, and backups of data files and control files. It also includes Automatic undo management undo segment, flashback logs, and other recovery-related files. The flash recovery area is used to manage space and automate tasks related to backup and recovery, such as deleting obsolete files, and controlling the size of the area.
86
What is Segment shrinking in Oracle?
Segment shrinking in Oracle is a process of releasing space from a segment (table, index, partition) that is no longer needed by the database. This process can be performed by using the ALTER TABLE...SHRINK SPACE or ALTER INDEX...SHRINK SPACE command. When a segment is shrunk, Oracle will move the rows of the segment to fill the gaps left by deleted rows, and then return the unused space to the tablespace for use by other segments. Segment shrinking can be used to reclaim space and defragment tables and indexes, but it can also cause performance issues such as high I/O, CPU and locking contention.
87
What is Resumable Space Allocation in Oracle?
Resumable Space Allocation is a feature in Oracle that allows a user to pause and resume a space allocation operation, such as a table or index creation, if the operation encounters a space-related error. This allows the user to address the issue and resume the operation without having to start the operation from the beginning.
88
What is Oracle Managed Files in Oracle?
Oracle Managed Files (OMF) is a feature in Oracle Database that simplifies the management of database files by allowing the database to automatically create, name, and manage the files associated with the database, such as datafiles, control files, and redo log files. OMF eliminates the need for manual file management and reduces the possibility of human error.
89
What is DBA_OBJECTS view in Oracle?
DBA_OBJECTS is a view in Oracle that provides information about all objects in the database, including tables, views, indexes, procedures, and functions. The view is accessible to users with the DBA role.
90
What is V$TABLESPACE view in Oracle?
$TABLESPACE is a dynamic performance view in Oracle that provides information about the current state of all tablespaces in the database, including the name, status, and space usage. The view can be used to monitor tablespace usage, identify potential space-related issues, and plan for future space requirements.
91
What is V$SQLTEXT view in Oracle?
V$SQLTEXT is a dynamic performance view in Oracle that provides the text of SQL statements that are currently in the shared SQL area. It can be used to troubleshoot performance issues by identifying resource-intensive or inefficient SQL statements, or to monitor the activity of specific users or applications.
92
What is DBA_DATA_FILES view in Oracle?
DBA_DATA_FILES is a data dictionary view in Oracle that displays information about all data files in the database, including the file name, tablespace name, file size, and status. The view is accessible to users with the DBA role and can be used to monitor the state of the data files and plan for future space requirements.
93
What does setting the session to be resumable do in Oracle?
Setting the session to be resumable in Oracle allows the current session to handle space-related errors in a resumable manner. This means that if a space-related error occurs during an operation, the session can be paused and the error can be addressed before the operation is resumed. This can prevent the need to start the operation from the beginning and can save time and resources.
94
What does setting the session to have the retention guarantee do in Oracle?
Setting the session to have the retention guarantee in Oracle ensures that the undo data generated by the session will be retained in the undo tablespace for at least the duration specified by the retention time. This feature guarantees that the undo data will be available for the duration of the retention time in case the data needs to be rolled back or for read consistency. This ensures data consistency and integrity in the event of a failure or rollback scenario.
95
What is the impact of setting the value of the undo retention initialization parameter to 900 in an undo tablespace that uses a fixed size?
Data for committed transactions will be overwritten if an active transaction needs the space
96
Which parameter disables conventional path loading when using SQL*Loader?
DIRECT
97
What does PARALLEL option do in SQLLoader?
The PARALLEL option allows SQLLoader to divide the data load into multiple parallel streams, making the load process faster
98
What does SILENT option do in SQLLoader?
The SILENT option controls the amount of feedback SQLLoader gives during the load process. With SILENT option, SQL*Loader will not display any messages on the screen.
99
What does DIRECT option do in SQLLoader?
The DIRECT option allows SQLLoader to bypass the SQL statement generation stage and directly load the data into the database, improving performance.
100
What does RESUMABLE option do in SQLLoader?
The RESUMABLE option allows SQLLoader to pause and resume a load operation if it encounters an error, rather than halting the entire load process. This can save time and resources if the error can be resolved.
101
What is SQLLoader?
SQLLoader is a command-line utility in Oracle that allows users to load data from external files into an Oracle database. It can read data from files in a variety of formats, such as fixed-width and delimited text, and load the data into one or more tables in the database. SQL*Loader also provides options for controlling the load process, such as specifying how data should be loaded, handling errors, and providing feedback on the load process.
102
Which parameter can an administrator enable without restarting a database?
SGA_TARGET
103
What does SGA_Target do?
SGA_Target refers to the target value for the System Global Area (SGA) in an Oracle database. It is used to set the total size of the SGA and determine the amount of memory allocated to different components within the SGA, such as the buffer cache and shared pool. The SGA is a shared memory area that stores data and control information for an Oracle instance.
104
What is DB_DOMAIN in Oracle?
DB_DOMAIN is a parameter that specifies the domain name for the database in Oracle. It is used to define the fully qualified domain name for the database and is used for network communication with other databases and clients. The value of DB_DOMAIN can be set during database creation or it can be modified later using the ALTER SYSTEM command.
105
What is UNDO_MANAGEMENT in Oracle?
UNDO_MANAGEMENT is a parameter that determines how undo (rollback) data is managed in an Oracle database. It can be set to either "AUTO" or "MANUAL" mode.
106
What is UNDO_MANAGEMENT AUTO do
In "AUTO" mode, the database automatically manages undo space and undo tablespaces.
107
What does UNDO_MANAGEMENT MANUAL mode
In "MANUAL" mode, the DBA manually creates and manages undo tablespaces.
It's important to note that UNDO_MANAGEMENT should be set to AUTO in most cases, as it allows the database to automatically manage undo space and optimize performance.
108
What does the V$HS_PARAMETER view show in Oracle?
The V$HS_PARAMETER view shows the current values of parameters used by the Oracle Database GoldenGate (OGG) process. These parameters can be used to configure and control the behavior of OGG processes, such as the location of trail files, the maximum number of parallel threads, and the maximum amount of memory used by OGG. This view allows administrators to monitor and troubleshoot OGG processes and configurations.
109
What does the V$NLS_PARAMETER view show in Oracle?
The V$NLS_PARAMETER view shows the current values of National Language Support (NLS) parameters in an Oracle database. NLS parameters control the language-specific behavior of the database, such as the character set, date format, and sorting order. The V$NLS_PARAMETER view allows administrators to view the current NLS settings for the database and the session. This includes the values for parameters like NLS_DATE_FORMAT, NLS_LANGUAGE and NLS_SORT.
110
How do you associate a committed server process with each client connection in a tnsnames.ora file in Oracle?
By including the (SERVER=DEDICATED) attribute in the connect descriptor of the tnsnames.ora file. This attribute specifies that a dedicated server process should be used for each client connection, ensuring that each connection has its own process that is dedicated to handling its requests, rather than using a shared server process. This can improve performance and scalability for applications that have a large number of concurrent connections.
111
How do you associate a shared server process with each client connection in a tnsnames.ora file in Oracle?
By including the (SERVER=SHARED) attribute in the connect descriptor of the tnsnames.ora file. This attribute specifies that a shared server process should be used for each client connection, allowing multiple clients to connect to the database through a single process. This can improve scalability and reduce memory usage for applications that have a large number of concurrent connections, but it may decrease performance if the shared server process becomes a bottleneck.
112
How do you associate a pooled server process with each client connection in a tnsnames.ora file in Oracle?
By including the (SERVER=POOLED) attribute in the connect descriptor of the tnsnames.ora file. This attribute specifies that a pooled server process should be used for each client connection. This means that the database will maintain a pool of server processes and will assign one of the pooled server processes to each client connection as needed, rather than starting a new server process for each connection. This can improve scalability and reduce overhead for applications that have a large number of concurrent connections.
113
How do you associate a dispatched server process with each client connection in a tnsnames.ora file in Oracle?
By including the (DISPATCHERS='(PROTOCOL=TCP)(DISPATCHERS=2)') attribute in the connect descriptor of the tnsnames.ora file. This attribute specifies that the listener will use a dedicated dispatch process for each protocol and number of dispatchers specified in the parentheses. The dispatched server process is used for handling the client's connection request and routing the request to the appropriate shared server process. This can improve scalability and load balancing for applications that have a large number of concurrent connections and use multiple protocols.
114
What is the Local naming method in Oracle's Net Service Naming?
The Local naming method in Oracle's Net Service Naming allows a client to connect to a database using the local host's file system, rather than using a remote naming service. This method uses the "local" keyword in the connect descriptor in the tnsnames.ora file and is used to connect to a database that is running on the same machine as the client. This method is typically used for development or testing environments where the client and database are on the same machine
115
What is the External naming method in Oracle's Net Service Naming?
The External naming method in Oracle's Net Service Naming allows a client to connect to a database using a remote naming service, such as an LDAP or a DNS server. This method uses the "external" keyword in the connect descriptor in the tnsnames.ora file and is used to connect to a database that is running on a remote machine. The external naming method is useful in situations where the client and database are on different machines or in different locations, and it allows to centralize the management of the database connections.
Note that the external naming method requires the configuration of the remote naming service and the proper setup of the listener on the server side to be able to resolve the remote connection requests.
116
Q: What is the Host naming method in Oracle's Net Service Naming?
The Host naming method in Oracle's Net Service Naming allows a client to connect to a database by specifying the host name or IP address of the machine where the database is running. This method uses the "host" keyword in the connect descriptor in the tnsnames.ora file and is used to connect to a database that is running on a remote machine. The host name or IP address of the machine is specified in the connect descriptor, along with the service name of the database.
This method of naming is useful in situations where the client and database are on different machines or in different locations. It eliminates the need for a naming service like ldap or dns, and the client can connect to the database directly by specifying the host name or IP address of the machine where the database is running.
117
What is the Directory naming method in Oracle's Net Service Naming?
The Directory naming method in Oracle's Net Service Naming allows a client to connect to a database by specifying the directory location of the tnsnames.ora file. This method uses the "directory" keyword in the connect descriptor, along with the directory location of the tnsnames.ora file. The directory location is specified in the connect descriptor, along with the service name of the database.
This method of naming is useful in situations where multiple tnsnames.ora files are used, or where the location of the tnsnames.ora file is expected to change. It eliminates the need to update the tnsnames.ora file location in the client's system and allows for a more flexible and dynamic management of the database connections.
It is important to note that the directory location specified must be accessible by the client and should contain a valid tnsnames.ora file with the desired service name defined in it.
118
Which information from a remote database is included in the configuration of a database link?
User name
119
What is a database link in Oracle?
database link in Oracle is a way to connect and access a remote database from a local database. It allows a user to access data and objects located in a remote database as if they were located in the local database, allowing for distributed data processing and management.
120
How does a database link work in Oracle?
A database link in Oracle is created by specifying the connect descriptor for the remote database in a tnsnames.ora file or by using a hostname and port number in the link. Once created, the link can be used in SQL statements to access objects in the remote database by prefixing the object name with the link name, for example, SELECT * from remote_table@dblink. The link uses the credentials of the user who created the link to connect to the remote database.
121
What are the benefits of using a database link in Oracle?
Some of the benefits of using a database link in Oracle include:
-It allows for distributed data processing and management
-It allows for the consolidation of data from multiple databases into a single location
-It allows for the creation of distributed applications
-It allows for the sharing of data and objects between databases.
122
Which object can be added to an existing bigfile tablespace?
table data
123
What is a bigfile tablespace in Oracle?
A bigfile tablespace in Oracle is a type of tablespace that uses a single, large datafile, typically with a size of up to 4 terabytes, to store all of its data.
124
How is a bigfile tablespace different from a traditional tablespace in Oracle?
traditional tablespace in Oracle uses multiple small datafiles to store its data, while a bigfile tablespace uses a single large datafile. This can simplify administration and management of the tablespace as it eliminates the need to manage multiple small datafiles.
125
What are the benefits of using a bigfile tablespace in Oracle?
Some of the benefits of using a bigfile tablespace in Oracle include:
-Ease of management as it eliminates the need to manage multiple small datafiles.
-Simplified backup and recovery as only one large datafile needs to be managed.
-Allows for large-scale data storage, with a maximum size of 4 terabytes.
126
What is a smallfile tablespace in Oracle?
A smallfile tablespace in Oracle is a type of tablespace that uses multiple small datafiles to store its data. These datafiles are typically smaller in size, usually with a maximum size of around 4 GB, depending on the operating system.
127
How is a smallfile tablespace different from a bigfile tablespace in Oracle?
bigfile tablespace in Oracle uses a single large datafile to store its data, while a smallfile tablespace uses multiple small datafiles to store its data. This allows for more granular control over the storage and management of the tablespace data.
128
What are the benefits of using a smallfile tablespace in Oracle?
Some of the benefits of using a smallfile tablespace in Oracle include:
-Allows for more granular control over the storage and management of the tablespace data.
-Provides more flexibility in terms of backup and recovery, as individual datafiles can be backed up and recovered separately.
-It's more suitable for small databases as it does not require large amounts of disk space.
129
Which object can be added to an existing bigfile tablespace?
Long answer
An existing bigfile tablespace can have additional tables, partitions, indexes, and other database objects added to it. You can also create new segments, like temporary segments, undo segments, and lob segments within the bigfile tablespace.
130
What types of events are recorded in an alert log in Oracle?
Startup and shutdown of the Oracle database
-Errors and exceptions that occur during the operation of the database
-Errors and exceptions that occur during the operation of the listener
-Information about the creation and deletion of redo logs
-Information about the archiving of redo logs
-Information about changes to the database configuration
-Information about backups and recoveries
-Information about space management and segment management
131
What is a Package in Oracle?
A Package in Oracle is a collection of related procedures, functions, and variables that are grouped together and stored in the database. They are used to encapsulate and organize database logic and make it reusable.
132
How to create a Package in Oracle?
A: To create a package in Oracle, you can use the CREATE PACKAGE statement, followed by the package name and the package body. The package body includes the definitions of the procedures, functions and variables that are part of the package. Once the package is created, it can be compiled and stored in the database.
133
What are the benefits of using a Package in Oracle?
ome of the benefits of using a package in Oracle include:
-Encapsulation of related database logic, making it more organized and reusable.
-Encapsulation of variables and cursors, making them private to the package and not visible to other database objects.
-Allows for overloading of procedures and functions.
-Provides a way to group and manage related database objects.
134
What is the AUDIT_FILE_DEST parameter in Oracle?
The AUDIT_FILE_DEST parameter in Oracle is a initialization parameter that specifies the directory where the audit trail files are written.
135
What is the purpose of AUDIT_FILE_DEST parameter in Oracle?
The purpose of AUDIT_FILE_DEST parameter is to specify the directory where the audit trail files are written, it is used to store the audit trails generated by the AUDIT system.
136
How can you set the value of AUDIT_FILE_DEST parameter?
he value of AUDIT_FILE_DEST parameter can be set by editing the initialization file (PFILE or SPFILE) and adding the following line: AUDIT_FILE_DEST = 'directory path' and then restarting the database.
137
Q: What are the benefits of using AUDIT_FILE_DEST parameter in Oracle?
Some of the benefits of using AUDIT_FILE_DEST parameter in Oracle include:
-It allows the DBA to specify the location of the audit trail files.
-It can be used to store the audit trails in a more secure location.
-It can be used to store the audit trails on a separate disk or file system for performance and management purposes.
-It can be used to store the audit trails on a remote server for security purposes.
138
What is the DIAGNOSTIC_DEST parameter in Oracle?
The DIAGNOSTIC_DEST parameter in Oracle is an initialization parameter that specifies the directory where the diagnostic and trace files will be stored.
139
What is the purpose of DIAGNOSTIC_DEST parameter in Oracle?
The purpose of DIAGNOSTIC_DEST parameter is to specify the directory where the diagnostic and trace files will be stored. This allows for the collection of diagnostic and trace information for troubleshooting and performance analysis.
140
How can you set the value of DIAGNOSTIC_DEST parameter?
The value of DIAGNOSTIC_DEST parameter can be set by editing the initialization file (PFILE or SPFILE) and adding the following line: DIAGNOSTIC_DEST = 'directory path' and then restarting the database.
141
What are the benefits of using DIAGNOSTIC_DEST parameter in Oracle?
Some of the benefits of using DIAGNOSTIC_DEST parameter in Oracle include:
-It allows the DBA to specify the location of the diagnostic and trace files for troubleshooting and performance analysis.
-It can be used to store the diagnostic and trace files on a separate disk or file system for performance and management purposes.
-It can be used to store the diagnostic and trace files on a remote server for security and management purposes.
142
What is the CORE_DUMP_DEST parameter in Oracle
The CORE_DUMP_DEST parameter in Oracle is an initialization parameter that specifies the directory where the core dump files will be stored when a process abnormally terminates.
143
How can you set the value of CORE_DUMP_DEST parameter?
The value of CORE_DUMP_DEST parameter can be set by editing the initialization file (PFILE or SPFILE) and adding the following line: CORE_DUMP_DEST = 'directory path' and then restarting the database.
144
What are the benefits of using CORE_DUMP_DEST parameter in Oracle?
Some of the benefits of using CORE_DUMP_DEST parameter in Oracle include:
-It allows the DBA to specify the location of the core dump files for troubleshooting and analysis of abnormal terminations.
-It can be used to store the core dump files on a separate disk or file system for performance and management purposes.
-It can be used to store the core dump files on a remote server for security and management purposes.
145
What is a core dump file in Oracle?
A core dump file in Oracle is a file that contains a snapshot of the memory and register contents of a process at the time it abnormally terminates.
146
What information does a core dump file contain in Oracle?
A core dump file in Oracle contains a snapshot of the memory and register contents of a process at the time of abnormal termination. This includes information about the state of the process, such as memory contents, register values, and the call stack.
147
How are core dump files generated in Oracle?
Core dump files are generated in Oracle when a process abnormally terminates, such as due to a segmentation fault or other type of crash. The operating system's core dump facility is used to create the core dump file, and the location of the file is specified by the CORE_DUMP_DEST initialization parameter.
148
What are the benefits of using core dump files in Oracle?
Some of the benefits of using core dump files in Oracle include:
-They can be used to troubleshoot and diagnose the cause of abnormal terminations.
-They can provide information about the state of the process at the time of the abnormal termination.
-They can be used to identify and fix bugs in the Oracle software.
149
What is Cluster Verification Utility?
Cluster Verification Utility (CVU) is a command-line tool that checks the readiness and overall health of Oracle Clusterware and Oracle RAC environments. It verifies that the configuration and setup of the cluster are correct and that all nodes are functioning properly. CVU checks for things like network connectivity, disk space, and system resources, and can help identify and troubleshoot issues before they become a problem. It is used during the installation and maintenance of Oracle Clusterware and Oracle RAC environments.
150
What is Oracle Data Guard Broker?
Oracle Data Guard Broker is a management and automation feature for the Oracle Data Guard feature. It is used to simplify the configuration and administration of Data Guard environments. It provides a centralized management and monitoring of the Data Guard configuration, allowing for the automation of common tasks such as switchover and failover operations. Data Guard Broker also provides features like fast-start failover and automatic failover, which help to minimize the impact of a data loss or data corruption. It also provides a command-line interface, as well as a web-based graphical user interface for managing Data Guard environments.
151
What is Privilege Analysis?
Privilege Analysis is a feature in Oracle Database that helps to identify and manage database privileges. It automatically analyzes the privileges that are granted to users, roles, and other objects, and identifies any unnecessary or excessive privileges. Privilege Analysis provides detailed reports on the privileges that are used, as well as those that are not used, and allows administrators to make informed decisions about which privileges to keep, revoke, or modify. Additionally, it also provides the capability to monitor privilege usage and track the changes made to privileges over time. This feature helps to improve security and compliance, and can help to prevent privilege-related issues such as data breaches or unauthorized access to sensitive data.
152
What is DBMS_STATS?
DBMS_STATS is a package in the Oracle Database that provides a set of procedures and functions for managing and manipulating statistics about the database. It allows for the collection and maintenance of statistics for the optimizer, such as table and index statistics, and system statistics such as CPU and I/O statistics. It also allows for the customization of the statistics collection process, including the ability to specify the level of granularity and the degree of accuracy for the statistics. Additionally, it provides the capability to export and import statistics between databases. DBMS_STATS plays a crucial role in maintaining the performance of the database as it is used to gather statistics that the optimizer uses to make execution plan decisions.
153
What are Automated Maintenance Tasks?
Automated Maintenance Tasks are a set of tasks in the Oracle Database that are automatically performed by the database to maintain its overall health and performance. These tasks include activities such as optimizing the database structure, reclaiming space, and monitoring for potential issues
154
What is Automatic Segment Advisor?
Automatic Segment Advisor is an automated maintenance task in the Oracle Database that analyzes the database and recommends actions to optimize its performance and space usage.
155
What is Automatic Workload Repository?
Automatic Workload Repository is an automated maintenance task in the Oracle Database that captures and maintains performance statistics for the database.
156
What is Automatic Database Diagnostic Monitor?
Automatic Database Diagnostic Monitor is an automated maintenance task in the Oracle Database that analyzes performance data and recommends actions to improve database performance.
157
What is the goal of Automated Maintenance Tasks?
The goal of Automated Maintenance Tasks is to help maintain the overall health and performance of the database, by ensuring that the database remains efficient and healthy over time.
158
Do Automated Maintenance Tasks run automatically?
Yes, Automated Maintenance Tasks are designed to run automatically and without user intervention.
159
What is DBMS_WORKLOAD_REPOSITORY?
DBMS_WORKLOAD_REPOSITORY is a package in the Oracle Database that provides a set of procedures and functions for managing and manipulating data in the Automatic Workload Repository (AWR). The AWR is a built-in performance management and monitoring feature that captures and maintains performance statistics for the database.
160
What is the purpose of DBMS_WORKLOAD_REPOSITORY?
The DBMS_WORKLOAD_REPOSITORY package provides a set of procedures and functions for managing and manipulating data in the Automatic Workload Repository (AWR).
161
What is the Automatic Workload Repository (AWR)?
The Automatic Workload Repository (AWR) is a built-in performance management and monitoring feature in the Oracle Database that captures and maintains performance statistics for the database.
162
Can I use DBMS_WORKLOAD_REPOSITORY to create my own performance reports?
Yes, DBMS_WORKLOAD_REPOSITORY provides a set of procedures and functions to extract data from AWR and create custom performance reports.
163
Can I use DBMS_WORKLOAD_REPOSITORY to compare performance statistics between different time periods?
Yes, DBMS_WORKLOAD_REPOSITORY can be used to compare performance statistics between different time periods to identify trends and troubleshoot performance issues.
164
Does the CBO use only one method to estimate the cost of execution plan?
No, the CBO uses a variety of methods to estimate the cost of execution plans, including rule-based optimization, statistics-based optimization and machine learning based optimization
165
What is SQL Access Advisor?
SQL Access Advisor is a feature in the Oracle Database that provides recommendations for optimizing SQL statements. It analyzes SQL statements and the database structure, and recommends indexes, materialized views, and other database objects to improve performance. The Advisor also provides an estimated performance improvement for each recommendation. SQL Access Advisor is a tool that helps to improve the performance of SQL statements by recommending the creation of indexes, materialized views and other database objects.
166
What is the purpose of SQL Access Advisor?
The purpose of SQL Access Advisor is to provide recommendations for optimizing SQL statements by analyzing SQL statements and the database structure.
167
What kind of recommendations does SQL Access Advisor provide?
SQL Access Advisor provides recommendations for indexes, materialized views, and other database objects that can improve performance.
168
How does SQL Access Advisor estimate the performance improvement?
SQL Access Advisor uses simulation techniques and data statistics to estimate the performance improvement for each recommendation.
169
Can I use SQL Access Advisor to optimize a single SQL statement or a group of statements?
Yes, SQL Access Advisor can be used to optimize both a single SQL statement or a group of statements.
170
When does AWR take snapshots ?
By default, AWR takes a snapshot every hour and retains them for 8 days.
AWR also takes automatic snapshots before and after certain events such as database startup and shutdown, instance recovery, maintenance operations and execution of certain jobs or procedures.
Manual snapshots can also be taken at any time using the DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT procedure.
These snapshots are taken during a specific period of time and the data from the snapshots is used to analyze performance over that period.
Please note that the retention period and intervals can be configured as per the requirement.
