Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Data Systems Administration > Database Administration > Flashcards

Database Administration Flashcards

1
Q

What is Oracle Database?

A

Oracle Database is a multi-model database management system produced and marketed by Oracle Corporation.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

True or False: Oracle Database supports SQL and PL/SQL.

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is the purpose of the Oracle Listener?

A

The Oracle Listener is responsible for handling incoming client connection requests and directing them to the appropriate database service.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Fill in the blank: The Oracle database architecture consists of a __________ and a __________.

A

database instance, database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What command is used to create a new user in Oracle?

A

CREATE USER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Which of the following is NOT a type of Oracle database object? A) Table B) View C) Function D) Script

A

D) Script

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is a tablespace in Oracle?

A

A tablespace is a storage location in an Oracle database where data files are stored.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What command would you use to grant a user permission to access a table?

A

GRANT SELECT ON table_name TO user_name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

True or False: Oracle uses a single-threaded architecture.

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is the purpose of the Oracle Data Dictionary?

A

The Oracle Data Dictionary contains metadata about the database, including information about users, tables, and permissions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Which command is used to back up an Oracle database?

A

RMAN BACKUP DATABASE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What does the acronym RMAN stand for?

A

Recovery Manager

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Fill in the blank: The __________ command is used to modify existing data in a table.

A

UPDATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is the default port number for Oracle Listener?

A

1521

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is a primary key?

A

A primary key is a unique identifier for a record in a database table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

True or False: An Oracle database can only have one tablespace.

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is the purpose of the Oracle Archive Log?

A

The Oracle Archive Log is used to store a copy of the redo log files to support recovery and auditing.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What command is used to create a new tablespace?

A

CREATE TABLESPACE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Which of the following is NOT a valid Oracle data type? A) VARCHAR2 B) NUMBER C) FLOATING D) DATE

A

C) FLOATING

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What does the acronym DDL stand for?

A

Data Definition Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What command would you use to delete a user from Oracle?

A

DROP USER

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Fill in the blank: A __________ is a collection of related data organized in a structured format.

A

table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is the purpose of Oracle’s Flashback technology?

A

Flashback technology allows users to view and restore data from a previous state.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

True or False: A view is a virtual table in Oracle.

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
What command is used to create an index in Oracle?
CREATE INDEX
26
What is the difference between a clustered and a non-clustered index?
A clustered index determines the physical order of data in a table, while a non-clustered index creates a separate structure for quick data access.
27
What is a foreign key?
A foreign key is a field in one table that uniquely identifies a row of another table.
28
Fill in the blank: The __________ command is used to retrieve data from a database.
SELECT
29
What is Oracle's Automatic Storage Management (ASM)?
ASM is a file system and volume manager for Oracle databases that simplifies storage management.
30
Which Oracle tool is used for database performance tuning?
Oracle Enterprise Manager
31
What is a rollback segment?
A rollback segment is used to store old versions of data for transaction rollback and recovery.
32
True or False: Oracle supports both single-instance and clustered database configurations.
True
33
What does the acronym SQL stand for?
Structured Query Language
34
What command is used to modify a table structure?
ALTER TABLE
35
Fill in the blank: A __________ is a predefined SQL statement that is stored in the database.
stored procedure
36
What is Oracle's Data Pump?
Data Pump is a utility for fast data and metadata movement between Oracle databases.
37
Which command is used to import data into an Oracle database?
impdp
38
What is the purpose of the Oracle Scheduler?
The Oracle Scheduler is used to manage and automate the execution of jobs in the database.
39
True or False: A synonym in Oracle is an alternative name for a database object.
True
40
What command is used to drop a table in Oracle?
DROP TABLE
41
Fill in the blank: The __________ function returns the current date and time in Oracle.
SYSDATE
42
What is a session in Oracle?
A session is a connection between a user and an Oracle database instance.
43
What does the acronym PL/SQL stand for?
Procedural Language/Structured Query Language
44
Which command is used to commit a transaction in Oracle?
COMMIT
45
What is the purpose of Oracle's Data Guard?
Data Guard provides data protection and disaster recovery for Oracle databases.
46
True or False: Oracle allows for partitioning of tables.
True
47
What is a materialized view?
A materialized view is a database object that contains the results of a query and can be refreshed periodically.
48
What command is used to create a materialized view?
CREATE MATERIALIZED VIEW
49
Fill in the blank: The __________ command is used to create a sequence in Oracle.
CREATE SEQUENCE
50
What is the purpose of Oracle's Flash Recovery Area?
The Flash Recovery Area is a designated storage location for backup and recovery files.
51
What does the acronym AWR stand for?
Automatic Workload Repository
52
What is the purpose of Oracle's SQL Tuning Advisor?
SQL Tuning Advisor provides recommendations to improve the performance of SQL statements.
53
True or False: An Oracle index can be created on multiple columns.
True
54
What command is used to alter a user's password in Oracle?
ALTER USER
55
What is a trigger in Oracle?
A trigger is a stored procedure that automatically executes in response to certain events on a table or view.
56
What command is used to create a trigger?
CREATE TRIGGER
57
Fill in the blank: A __________ is a set of instructions that are executed when a specific event occurs in a database.
trigger
58
What is the purpose of Oracle's Resource Manager?
Resource Manager is used to manage database resource allocation among users and sessions.
59
Which command is used to enable a database in Oracle?
ALTER DATABASE OPEN
60
True or False: Oracle supports both row-level and column-level security.
True
61
What is the function of the Oracle Query Optimizer?
The Query Optimizer determines the most efficient way to execute a SQL query.
62
What command is used to create a database link in Oracle?
CREATE DATABASE LINK
63
Fill in the blank: The __________ command is used to revoke privileges from a user.
REVOKE
64
What is Oracle's Transportable Tablespace feature?
Transportable Tablespace allows the movement of tablespaces between different Oracle databases.
65
What command is used to start up an Oracle database?
STARTUP
66
True or False: Oracle databases can be configured for high availability.
True
67
What is a schema in Oracle?
A schema is a collection of database objects that are owned by a specific user.
68
What command is used to create a new role in Oracle?
CREATE ROLE
69
Fill in the blank: The __________ command is used to check the status of an Oracle database.
SELECT status FROM v$instance
70
What is Oracle's SQL Developer?
SQL Developer is a free graphical tool for database development and management.
71
What is the purpose of Oracle's Data Guard Broker?
Data Guard Broker automates and simplifies the management of Data Guard configurations.
72
True or False: Oracle allows for the use of JSON data types.
True
73
What command is used to create a snapshot in Oracle?
CREATE SNAPSHOT
74
What is the purpose of Oracle's Audit Trail?
Audit Trail records database activities to ensure compliance and security.
75
Fill in the blank: The __________ command is used to refresh a materialized view.
REFRESH
76
What is a database cluster in Oracle?
A database cluster is a set of database instances that access a single database.
77
What command is used to drop a materialized view?
DROP MATERIALIZED VIEW
78
True or False: Oracle allows for the use of spatial data types.
True
79
What is Oracle's GoldenGate?
GoldenGate is a software product for real-time data integration and replication.
80
What command is used to create a partition in Oracle?
CREATE TABLE ... PARTITION BY
81
Fill in the blank: The __________ command is used to change the storage parameters of a database object.
ALTER
82
What is the purpose of Oracle's SQL*Plus?
SQL*Plus is an interactive and batch query tool for Oracle databases.
83
What command is used to create a user-defined function in Oracle?
CREATE FUNCTION
84
What is a relational database management system (RDBMS)?
A system where data consists of a set of relational objects
85
What is the basic storage of data in a database?
A table
86
How is data stored in a relational database?
In rows and columns
87
Can a department have multiple employees?
Yes
88
Can an employee work for multiple departments?
No
89
What is a primary key in a relational database?
A column or set of columns that uniquely identifies a row
90
According to relational theory, what must each table in a relational database have?
A primary key
91
What is a foreign key in RDBMS terminology?
A foreign key states that the data value in the column exists in another table and should continue to exist in the other table to keep the relationship between tables.
92
What is the primary key of a table?
The primary key is a column that uniquely identifies each row in a table.
93
What is the parent table in a parent-child relationship?
The table where the column is a primary key.
94
What is the child table in a parent-child relationship?
The table where the foreign key column exists.
95
How does Oracle enforce the parent-child relationship between tables?
Oracle enforces the parent-child relationship using constraints.
96
What types of database objects does Oracle Database 12c support?
Oracle Database 12c supports a variety of database objects, including: * tables * views * constraints * packages * sequences * materialized views.
97
Fill in the blank: The primary key of one table is placed in another table as a _______.
[foreign key]
98
True or False: A foreign key can exist without a corresponding primary key in another table.
False
99
What is a table in a database?
A table is the basic form of data storage with columns and rows of data. ## Footnote Tables are fundamental structures in relational databases.
100
What is a view?
A view is a stored query that does not occupy data-storage space. ## Footnote Views are used to present data in a specific format without storing it physically.
101
What is the purpose of an index?
An index is an optional structure that is useful for fetching data faster. ## Footnote Indexes improve the speed of data retrieval operations.
102
What are materialized views?
Materialized views are used to summarize and store data, taking up storage space. ## Footnote They differ from regular views as they store actual data.
103
What is an index-organized table?
An index-organized table stores the table data along with the index, instead of separately. ## Footnote This structure improves performance by combining data and index.
104
Define a cluster in a database context.
A cluster is a group of tables sharing a common column, storing rows together with the common columns stored once. ## Footnote Clusters enhance performance by reducing storage space.
105
What is a constraint?
A constraint is a stored rule to enforce data integrity. ## Footnote Constraints ensure that the data meets specific criteria.
106
What does a sequence do?
A sequence provides a mechanism for the continuous generation of numbers. ## Footnote Sequences are often used for primary key generation.
107
What is a synonym in a database?
A synonym is an alias for a database schema object. ## Footnote Synonyms simplify access to database objects.
108
What is a trigger?
A trigger is a PL/SQL program unit that is executed when an event occurs. ## Footnote Triggers can automate actions in response to certain changes in the database.
109
What are stored functions?
Stored functions are PL/SQL programs that can be used to create user-defined functions to return a value. ## Footnote They encapsulate complex calculations or operations.
110
What is the purpose of stored procedures?
Stored procedures are PL/SQL programs to define a business process. ## Footnote They allow for code reuse and encapsulation of business logic.
111
What is a package in PL/SQL?
A package is a collection of procedures, functions, and other program constructs. ## Footnote Packages help organize related code and improve modularity.
112
What are stored Java procedures?
Stored Java procedures can be created in Oracle to define business processes. ## Footnote They allow for the integration of Java within the database.
113
What are database links used for?
Database links are used to communicate between databases to share data. ## Footnote They facilitate remote database access.
114
What language is used to interact with Oracle Database 12c?
SQL ## Footnote SQL is the standard language for managing and manipulating databases.
115
What is SQL*Plus?
A SQL command-line interface utility for database administration ## Footnote SQL*Plus allows DBAs to run SQL commands directly.
116
What is SQL Developer?
A GUI tool to explore and manage the database using predefined menu actions and SQL statements ## Footnote SQL Developer provides a graphical interface for easier database management.
117
What is Oracle Enterprise Manager Database Express 12c?
A GUI tool for database administration and performance management ## Footnote This tool offers a more user-friendly way to manage Oracle databases.
118
How do you start SQL*Plus on Windows?
Using the SQLPLUS.EXE executable ## Footnote This can be done from the command prompt.
119
How do you start SQL*Plus on Unix/Linux?
Using the $ORACLE_HOME/bin/sqlplus executable ## Footnote This requires the proper Oracle environment to be set up.
120
What must you do before running SQL statements in SQL*Plus?
Connect to Oracle Database 12c ## Footnote Connection is essential to execute any SQL commands.
121
Fill in the blank: You interact directly with Oracle Database 12c using SQL statements and a superset of commands such as _______.
STARTUP, SHUTDOWN ## Footnote These commands are fundamental for managing the database lifecycle.
122
True or False: Using Enterprise Manager, you interact directly with Oracle Database 12c.
False ## Footnote Interaction through Enterprise Manager is indirect.
123
What command would you use to show the current user in SQL*Plus?
SHOW USER ## Footnote This command reveals the current logged-in user.
124
What is the significance of 'CDB' in the context of Oracle Database 12c?
It indicates that the database is a Container Database ## Footnote CDBs are part of Oracle's multitenant architecture.
125
Fill in the blank: The command to select the unique name and CDB status from the database is _______.
SELECT db_unique_name, cdb FROM vsdatabase ## Footnote This command retrieves specific information from the database.
126
What does the command 'SELECT db_unique_name, cdb FROM vsdatabase' return?
The unique name of the database and whether it is a CDB ## Footnote This helps in identifying database characteristics.
127
How is EM Database Express configured?
Using a check box in Database Configuration Assistant when creating a new database.
128
What is required for EM Database Express to function?
XMLDB must be installed in the database.
129
What is the default port configured for EM Database Express?
5,500.
130
What is the URL format for accessing EM Database Express?
https://:5500/em.
131
How can the port for EM Database Express be changed?
Using the DBMS_XDB_CONFIG.setHTTPsPort(‹port>) procedure.
132
When is EM Database Express available for use?
When the database is open.
133
Can EM Database Express be used to start or stop a database?
No.
134
What does the term 'database' commonly refer to in Oracle?
The physical files that store data ## Footnote The term 'database' can represent different concepts on various platforms, but in Oracle, it specifically refers to data storage files.
135
What is an instance in the context of a database?
A composition of memory structures and background processes ## Footnote An instance is essential for the operation of a database.
136
How many instances should be associated with each database?
At least one instance ## Footnote Multiple instances can access a single database in certain configurations.
137
What is the configuration called when multiple instances access a single database?
Real Application Clusters (RAC) ## Footnote This configuration allows for improved availability and scalability.
138
What type of databases will be the focus of this book?
Single-instance databases ## Footnote Real Application Clusters (RAC) are not included in the OCA certification exam.
139
True or False: A database can only have one instance associated with it.
False ## Footnote A database can have multiple instances, especially in RAC configurations.
140
Fill in the blank: A database is something related to _______.
storing data ## Footnote The term 'database' can be confusing as it represents different things on different platforms.
141
What are the two main components that make up an Oracle instance?
Memory structures and background processes ## Footnote These components work together to manage the database operations.
142
What are the key database components?
Memory structures, process structures, storage structures ## Footnote These components are essential for the functioning of a database system.
143
What are process and memory structures collectively called?
An instance ## Footnote An instance refers to the combination of process and memory structures in a database environment.
144
What is the storage structure in a database referred to as?
A database ## Footnote The storage structure is specifically designed to hold data within a database system.
145
What is the term used to describe the combination of an instance and a database?
An Oracle server ## Footnote An Oracle server encompasses both the instance (process and memory structures) and the database (storage structure).
146
What is created for each server process in addition to user and server processes?
Program Global Area (PGA) ## Footnote The PGA is a memory structure associated with each server process.
147
What type of information does the Program Global Area (PGA) store?
User-specific session information such as bind variables and session variables ## Footnote The PGA is crucial for maintaining session-specific data.
148
True or False: Every server process on the server has a PGA memory area.
True ## Footnote Each server process requires a PGA to store session-specific information.
149
Fill in the blank: The _____ is a memory structure that stores user-specific session information for each server process.
Program Global Area (PGA) ## Footnote The PGA plays a vital role in managing session data.
150
What is PGA memory?
PGA memory is not shared and is exclusive to each server process.
151
What is the role of a DBA regarding PGA memory?
The DBA sets the total memory that can be allocated to all the PGA memory for server and background processes.
152
What is the SQL Work Area in the PGA?
Area used for memory-intensive operations such as sorting or building a hash table during join operations.
153
What does the Private SQL Area in the PGA hold?
Holds information about SQL statements and bind variable values.
154
How can the PGA be managed automatically?
By setting the database parameter PGA_AGGREGATE_TARGET.
155
What is the Oracle Instance composed of?
Consists of the system global area (SGA) and several Oracle background processes.
156
What is the SGA?
A shared memory area that all users of the database share.
157
When is memory allocated for the SGA?
When the instance is started.
158
What happens to the SGA memory when the instance is shut down?
It is de-allocated.
159
What are the mandatory components of the SGA?
Three mandatory components.
160
What is the function of the Shared Pool in the SGA?
Caches the most recently used SQL statements issued by database users.
161
What does the Database Buffer Cache do?
Caches the data that has been most recently accessed by database users.
162
What is the purpose of the Redo Log Buffer?
Stores transaction information for recovery purposes.
163
What is the Java pool in Oracle Database 12c?
Caches the most recently used Java objects and application code ## Footnote This component is utilized when Oracle's JVM option is used.
164
What does the Large pool in Oracle Database 12c cache?
Caches data for large operations such as Recovery Manager (RMAN) backup and restore activities and Shared Server components ## Footnote The Large pool is beneficial for memory-intensive operations.
165
What is the purpose of the Streams pool in Oracle Database 12c?
Caches the data associated with queued message requests when Oracle's Advanced Queuing option is used ## Footnote It helps in managing message queuing efficiently.
166
What is the Result cache in Oracle Database 12c?
Stores results of SQL queries and PL/SQL functions for better performance ## Footnote This area is a new feature introduced in Oracle Database 12c.
167
What is Automatic Memory Management (AMM) in Oracle Database 12c?
Feature that allows Oracle Database 12c to manage the components of SGA and PGA automatically
168
What are granules in Oracle Database 12c?
Units of contiguous memory in which memory in the SGA is allocated ## Footnote The size of a granule depends on the MEMORY_MAX_TARGET parameter.
169
How does MEMORY_MAX_TARGET affect granule size?
If MEMORY_MAX_TARGET is larger than 1,024MB, granule size is either 16MB or 4MB
170
How many granules must be allocated to SGA?
A minimum of three granules must be allocated to SGA
171
What are the two ways to manage SGA component sizes?
* Manually * Automatically
172
What must you do if managing SGA components manually?
Specify the size of each SGA component and adjust their sizes according to application needs
173
What happens when SGA components are managed automatically?
The instance monitors the utilization of each SGA component and adjusts their sizes accordingly
174
What is the purpose of the dynamic performance views V$SGA and V$SGAINFO?
To see the components and sizes of SGA
175
What SQL command can be used to display SGA information in SQL*Plus?
SHOW SGA
176
What does the V$SGA query return?
Displays the current sizes of SGA components
177
Fill in the blank: Memory in the SGA is allocated in units called _______.
granules
178
What is the purpose of the database buffer cache?
Caches the database data, holding blocks from the data files that have been accessed recently.
179
What are the three types of buffers in the database buffer cache?
* Dirty buffers * Free buffers * Pinned buffers
180
What are dirty buffers?
Buffer blocks that need to be written to the data files; the data has changed and not yet written to disk.
181
What are free buffers?
Buffers that do not contain any data or are free to be overwritten.
182
What are pinned buffers?
Buffers that are currently being accessed or explicitly retained for future use.
183
What algorithm does Oracle use to manage the contents of the shared pool and database buffer cache?
Least Recently Used (LRU) algorithm.
184
How does Oracle improve overall server performance with the LRU algorithm?
By keeping frequently accessed SQL statements and database buffers in memory longer, minimizing parsing and physical disk I/O.
185
What is the role of the background process DBWn?
Writes the database blocks from the database buffer cache to the data files.
186
True or False: Pinned buffers can be overwritten by new data.
False.
187
Fill in the blank: The database buffer cache is shared among all the ______ connected to the database.
users
188
What happens when Oracle reads data from the disk?
Free buffers hold this data.
189
What is the DEFAULT cache used for?
Used for general data. ## Footnote The DEFAULT cache stores data that is not specifically categorized as important or temporary.
190
What is the purpose of the KEEP cache?
Used for important data you want to 'keep' in memory as long as possible. ## Footnote The KEEP cache is designed to retain critical information that needs to be accessed frequently.
191
What does the RECYCLE cache store?
Used for temporary or less important data you don't want taking up space. ## Footnote The RECYCLE cache is intended for data that is only needed for a short duration.
192
What is the purpose of the Buffer Cache?
Stores data blocks in memory for quick access ## Footnote The Buffer Cache is crucial for improving the performance of database operations by keeping frequently accessed data in memory.
193
What does the KEEP / RECYCLE Cache do?
Keep important/unimportant data longer/shorter in memory ## Footnote This allows for better management of memory resources by prioritizing important data.
194
What is the function of Flash Cache?
Extra buffer on SSD when memory is full ## Footnote Flash Cache enhances performance by utilizing SSD storage to supplement memory when needed.
195
What is the purpose of the Redo Log Buffer?
Temporarily holds changes for recovery ## Footnote The Redo Log Buffer is essential for ensuring data integrity and recovery in case of failures.
196
What does the Shared Pool store?
Stores SQL/PLSQL, metadata, execution plans ## Footnote The Shared Pool is important for reusing SQL execution plans and reducing parsing time.
197
What is the important parameter for Buffer Cache?
DB_CACHE_SIZE ## Footnote This parameter determines the size of the Buffer Cache and impacts database performance.
198
What are the important parameters for KEEP / RECYCLE Cache?
* DB_KEEP_CACHE_SIZE * DB_RECYCLE_CACHE_SIZE ## Footnote These parameters help manage the sizes of the KEEP and RECYCLE caches effectively.
199
What parameters are associated with Flash Cache?
* DB_FLASH_CACHE_FILE * DB_FLASH_CACHE_SIZE ## Footnote These parameters define the configuration and size of the Flash Cache.
200
What is the parameter for Redo Log Buffer?
LOG_BUFFER ## Footnote This parameter specifies the size of the Redo Log Buffer, which is crucial for transaction logging.
201
Is there a single parameter for Shared Pool?
No single parameter, part of SGA ## Footnote The Shared Pool is managed as part of the System Global Area (SGA) and does not have a single dedicated parameter.

Data Systems Administration (5 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions