1
Q
What does it mean to use a SELECT statement to create dynamic scripts in Oracle?
A
It means using a SELECT query to generate SQL statements as text based on metadata, which are later executed separately as a script. This enables automation and scalability.
2
Q
Does a SELECT statement execute the SQL it generates?
A
No. SELECT only returns text output. The generated SQL must be copied and executed separately. This distinction is critical for Oracle exams.
3
Q
Why is this technique considered “dynamic”?
A
Because the SQL generated adapts automatically based on metadata (such as table names) rather than being hardcoded.
4
Q
Which Oracle views are typically used to generate dynamic scripts?
A
Data dictionary views such as USER_TABLES, USER_COLUMNS, USER_INDEXES, etc., because they expose metadata.
5
Q
What does USER_TABLES contain?
A
One row per table owned by the current user. Oracle stores table names in uppercase.
6
Q
Why does Oracle test dynamic script generation?
A
To verify understanding of metadata-driven SQL, automation, scalability, and separation between SQL generation and execution.
7
Q
What is the main benefit of generating DDL using SELECT?
A
It allows you to apply schema-wide changes (hundreds or thousands of objects) efficiently and consistently.
8
Q
What Oracle operator is commonly used to build dynamic SQL strings?
A
The concatenation operator `
9
Q
What does this SELECT generate: `SELECT ‘ALTER TABLE ‘
A
returns one row with one column containing the text: ALTER TABLE
10
Q
Does Oracle validate generated SQL during the SELECT phase?
A
No. Oracle treats the generated SQL as plain text; syntax errors are only caught when the script is executed.
11
Q
What is the correct way to execute generated SQL in SQL Developer?
A
Paste the generated statements into a worksheet and run them as a script using F5.
12
Q
Why should F5 be used instead of Ctrl+Enter?
A
F5 executes multiple statements as a script, while Ctrl+Enter runs only a single statement.
13
Q
What happens automatically when DDL statements run in Oracle?
A
Oracle performs an implicit commit; DDL cannot be rolled back.
14
Q
Why is manually altering tables one by one considered bad practice?
A
It is slow, error-prone, unscalable, and impractical in real enterprise environments.
15
Q
How can you dynamically generate DROP COLUMN statements?
A
By selecting concatenated strings such as ALTER TABLE <table_name> DROP COLUMN creation_date from dictionary views.</table_name>
16
Q
What is a common exam trap regarding dynamic scripts?
A
Thinking that the SELECT statement itself modifies database objects.
17
Q
Another exam trap related to execution is what?
A
Assuming generated SQL runs automatically without copying and executing it as a script.
18
Q
What is the difference between USER_TABLES and ALL_TABLES?
A
USER_TABLES lists tables owned by the user; ALL_TABLES lists tables the user can access.
19
Q
Why is dynamic script generation valuable in real-world jobs?
A
It supports migrations, schema changes, maintenance, and compliance tasks efficiently.
20
Q
How does Oracle expect you to think about this topic on the exam?
A
Conceptually: metadata → generated SQL → separate execution, not procedural automation.
21
Q
What is Oracle 11g Express Edition (XE)?
A
A free, lightweight Oracle database edition designed for learning, practice, and development, supporting most core SQL features.
22
Q
Why is Oracle 11g XE recommended if Oracle 12c fails to install?
A
It avoids container and pluggable database complexity while still supporting nearly all SQL needed for practice and exams.
23
Q
What major architectural feature exists in Oracle 12c but not in 11g XE?
A
Container Databases (CDB) and Pluggable Databases (PDB).
24
Q
Is Oracle SQL Developer included with Oracle 11g XE?
A
No. SQL Developer must be downloaded and installed separately.
25
Why should the Oracle XE installer be run as Administrator on Windows?
To ensure proper permissions for services, registry entries, network configuration, and listener setup.
26
What database and service name are created by default in Oracle 11g XE?
XE.
27
What is the default listener port used by Oracle XE?
1521
28
What hostname is typically used when connecting locally to Oracle XE?
localhost.
29
Which Oracle tool is used to verify the database installation from the command line?
SQL*Plus (SQL Command Line).
30
How do you connect as a database administrator in SQL*Plus?
CONNECT sys AS sysdba.
31
Why is connecting as SYSDBA important after installation?
It confirms the database is running, accessible, and properly configured.
32
Are sample users like HR or AIR unlocked by default in Oracle XE?
No, they are locked by default.
33
How do you unlock a locked Oracle user account?
ALTER USER username ACCOUNT UNLOCK;
34
Why must you change or set a password after unlocking a user?
A user cannot authenticate without a valid password.
35
What does successfully running SELECT * FROM user_tables indicate?
The schema is accessible and the database is working correctly.
36
What is SQL Developer’s role in the Oracle environment?
It is a client tool used to connect to and interact with the Oracle database.
37
What three parameters are required for a basic SQL Developer connection?
Hostname, port, and service name.
38
What is the correct service name when connecting to Oracle 11g XE?
XE.
39
What common mistake do beginners make regarding SQL Developer and Oracle XE?
Assuming SQL Developer is the database rather than a client.
40
What is a TNS (network alias) connection in Oracle?
A connection method using entries defined in tnsnames.ora instead of basic host/port/service fields.
41
Is a TNS connection required to use Oracle XE?
No. A basic connection is sufficient.
42
Why is Oracle XE suitable for certification exam preparation?
It mirrors standard Oracle schemas and supports nearly all exam-relevant SQL features.
43
What happens if you use the wrong service name when connecting?
The connection test fails and the database cannot be reached.
44
Why does Oracle lock sample schemas by default?
For security and to prevent unauthorized access.
45
What real-world purpose does Oracle XE commonly serve?
Training, development environments, and certification preparation.
46
What is ROWNUM in Oracle SQL?
A pseudocolumn that assigns a sequential number to rows returned by a query result, starting at 1.
47
Is ROWNUM a real column stored in the table?
No. ROWNUM is not stored in the table; it is generated at query execution time.
48
What does ROWNUM represent conceptually?
The order in which rows are returned by the query, not the physical order in the table.
49
Does ROWNUM remain the same across different queries?
No. ROWNUM can change from query to query depending on filters and execution.
50
When is ROWNUM assigned during query execution?
Before the ORDER BY clause is applied.
51
Why can ROWNUM values look unordered when ORDER BY is used?
Because ROWNUM is assigned first, then rows are sorted afterward by ORDER BY.
52
What is a common Oracle exam trap involving ROWNUM and ORDER BY?
Assuming ROWNUM reflects the sorted order produced by ORDER BY.
53
Why does ROWNUM <= 3 ORDER BY salary DESC not return the top 3 salaries?
Because ROWNUM is assigned before ORDER BY, so filtering happens on unsorted rows.
54
How do you correctly write a Top-N query using ROWNUM?
By using a subquery that applies ORDER BY first, then filtering by ROWNUM in the outer query.
55
What is the purpose of ROWNUM in practice?
Limiting result sets, pagination, and Top-N queries.
56
What is ROWID in Oracle SQL?
A pseudocolumn that uniquely identifies the physical address of a row in a table.
57
Is ROWID stored physically in the database?
Yes. ROWID represents the actual physical location of the row.
58
Does ROWID change between queries?
No. ROWID is stable unless the row is moved.
59
What does ROWID uniquely identify?
One and only one row in a table.
60
Why is ROWID considered very fast for access?
Because Oracle can directly locate the row using its physical address.
61
What are the main components of a ROWID?
Data object number, data file number, data block number, and row number within the block.
62
Is detailed ROWID structure typically required for exams?
No. Knowing that ROWID represents the physical row address is sufficient.
63
Why does SELECT ROWID, * FROM employees; cause an error?
Because ROWID must be referenced using a table alias.
64
How do you correctly select ROWID from a table?
By using a table alias, such as SELECT e.ROWID, e.* FROM employees e;.
65
What is a key use case for ROWID when no primary key exists?
Performing precise UPDATE or DELETE operations on a specific row.
66
How does Oracle internally use ROWID?
For updates, deletes, forms, and Oracle APEX row processing.
67
Which pseudocolumn is query-dependent: ROWNUM or ROWID?
ROWNUM.
68
Which pseudocolumn represents physical storage: ROWNUM or ROWID?
ROWID.
69
Can ROWNUM be used reliably to identify a specific row?
No. It is not stable and changes based on query execution.
70
Can ROWID be used to retrieve exactly one row?
Yes. ROWID uniquely identifies a row.
71
Why is ROWID important in high-performance operations?
It provides the fastest possible access path to a row.
72
What happens to ROWID if a row is deleted?
The ROWID becomes invalid because the row no longer exists.
73
Is ROWNUM assigned before or after WHERE filtering?
After WHERE filtering but before ORDER BY.
74
What common confusion exists between ROWNUM and ROW_NUMBER()?
ROWNUM is assigned before ORDER BY, while ROW_NUMBER() is assigned after ORDER BY.
75
Why does Oracle treat ROWNUM and ROWID as pseudocolumns?
Because they behave like columns but are not defined in table schemas.
76
What is PL/SQL?
An Oracle-specific procedural extension to SQL that allows programming logic to be combined with SQL statements.
77
Is PL/SQL a separate language from SQL?
No. PL/SQL extends SQL by embedding SQL statements inside procedural code.
78
Why is PL/SQL considered more important than SQL for applications?
Because database applications require logic, control flow, reuse, and error handling, which SQL alone cannot provide.
79
What type of language is SQL compared to PL/SQL?
SQL is declarative, while PL/SQL is procedural.
80
What does “procedural” mean in PL/SQL?
You can control how operations are performed using variables, conditions, loops, and structured blocks.
81
Which company owns and develops PL/SQL?
Oracle.
82
Can you build an Oracle database application using SQL only?
No. PL/SQL is required to build Oracle database applications.
83
What is the basic structure of a PL/SQL block?
DECLARE, BEGIN, EXCEPTION, END.
84
Which sections of a PL/SQL block are mandatory?
BEGIN and END.
85
Which sections of a PL/SQL block are optional?
DECLARE and EXCEPTION.
86
What is written in the DECLARE section?
Variables, constants, data types, and cursors.
87
What is written in the BEGIN section?
Executable statements including SQL and PL/SQL logic.
88
What is written in the EXCEPTION section?
Error-handling code.
89
Can SQL statements be used inside PL/SQL?
Yes. SELECT, INSERT, UPDATE, and DELETE can be used inside PL/SQL blocks.
90
How does PL/SQL improve performance compared to standalone SQL?
Entire blocks are sent to the server at once, reducing network round-trips.
91
What does PL/SQL allow that SQL alone does not?
Variables, loops, conditional statements, and exception handling.
92
What are the two engines used by Oracle when executing PL/SQL?
The SQL engine and the PL/SQL engine.
93
What happens to SQL statements inside a PL/SQL block?
They are sent to the SQL engine for execution.
94
What happens to procedural statements inside a PL/SQL block?
They are handled by the PL/SQL engine.
95
What is an anonymous PL/SQL block?
A PL/SQL block with no name, used for one-time execution.
96
What is a procedure in PL/SQL?
A named PL/SQL subprogram that performs an action but does not return a value.
97
What is a function in PL/SQL?
A named PL/SQL subprogram that returns a value.
98
What is the key difference between a procedure and a function?
A function returns a value; a procedure does not.
99
Why are procedures useful?
They allow code reuse and reduce repetition of SQL statements.
100
Why are functions useful?
They encapsulate logic and return calculated values to SQL or PL/SQL.
101
What is a package in PL/SQL?
A schema object that groups logically related procedures and functions.
102
Why are packages important?
They organize code, improve maintainability, and support reuse.
103
What control structures are supported in PL/SQL?
IF statements, CASE statements, and loops.
104
What types of loops exist in PL/SQL?
Basic LOOP, FOR LOOP, and WHILE LOOP.
105
What is a cursor in Oracle?
A memory area used by Oracle to process SQL statements row by row.
106
What is an implicit cursor?
A cursor automatically created by Oracle when a SQL statement is executed.
107
Why are cursors important in PL/SQL?
They allow row-by-row processing and full control over SQL execution.
108
What major advantage does PL/SQL provide for error handling?
It allows handling runtime errors using exceptions.
109
Where is PL/SQL commonly used in real systems?
Oracle Forms, Reports, APEX, triggers, stored procedures, and applications.
110
Why is PL/SQL considered portable?
PL/SQL code can be moved between development and production environments.
111
What does PL/SQL modularization mean?
Breaking logic into procedures, functions, and packages for reuse.
112
Why does Oracle recommend PL/SQL for enterprise applications?
It provides performance, structure, reuse, and maintainability.
113
Can PL/SQL tell Oracle “how” to perform tasks?
Yes, unlike SQL which only specifies “what” to do.
114
What problem does DBMS_ERRLOG solve in Oracle?
It allows DML operations to continue when some rows fail, logging row-level errors instead of rolling back the entire statement.
115
What happens by default when a DML statement fails on one row?
Oracle rolls back the entire statement and reports only one error.
116
What is DBMS_ERRLOG?
An Oracle-supplied package used to log row-level DML errors during INSERT, UPDATE, or DELETE operations.
117
Which types of SQL statements support DBMS_ERRLOG?
DML statements only: INSERT, UPDATE, DELETE.
118
Does DBMS_ERRLOG work with DDL statements?
No. It works only with DML.
119
What must be done before using LOG ERRORS in a DML statement?
An error logging table must be created using DBMS_ERRLOG.CREATE_ERROR_LOG.
120
What procedure creates the error log table?
DBMS_ERRLOG.CREATE_ERROR_LOG.
121
What is the default name of the error logging table?
ERR$_.
122
What information does the error log table store?
Failed row data plus Oracle-generated error metadata.
123
Name two Oracle-added columns in the error log table.
ORA_ERR_NUMBER$, ORA_ERR_MESG$ (others include ROWID$, OPTYPE$, TAG$).
124
What does ORA_ERR_NUMBER$ store?
The Oracle error number for the failed row.
125
What does ORA_ERR_MESG$ store?
The Oracle error message describing the failure.
126
What does ORA_ERR_ROWID$ store?
The ROWID of the row that caused the error.
127
What does ORA_ERR_OPTYPE$ represent?
The type of DML operation: I (INSERT), U (UPDATE), D (DELETE).
128
What clause enables error logging in a DML statement?
LOG ERRORS.
129
What does the REJECT LIMIT clause control?
The maximum number of errors Oracle will log before stopping the statement.
130
What does REJECT LIMIT UNLIMITED mean?
Log all row errors without stopping the statement.
131
Does REJECT LIMIT UNLIMITED affect how many rows are inserted?
No. It controls how many errors are logged, not how many rows are inserted.
132
Where are failed rows recorded when LOG ERRORS is used?
In the error logging table (ERR$_).
133
What happens to valid rows when LOG ERRORS is used?
They are successfully inserted, updated, or deleted.
134
Are errors raised to the client when LOG ERRORS is used?
No. Errors are logged instead of being raised.
135
Can you query the error details after execution?
Yes, by selecting from the ERR$_ table.
136
Why is DBMS_ERRLOG important for data migration?
It prevents one bad row from stopping large bulk operations and allows targeted cleanup.
137
Does DBMS_ERRLOG use implicit or explicit cursors?
It works with Oracle’s implicit cursors used during DML execution.
138
Can you limit how many errors are logged?
Yes, by specifying a numeric value in REJECT LIMIT.
139
What happens if the error log table does not exist?
The DML statement fails with an error.
140
Is DBMS_ERRLOG commonly tested conceptually or syntactically?
Conceptually—understanding behavior, purpose, and flow is key for exams.
141
What is Oracle 18c Express Edition (XE)?
The only free Oracle Database edition, intended for learning, development, and small workloads.
142
Is Oracle 18c XE free for production use?
Yes, but it has strict resource and feature limitations.
143
Why does Oracle provide Express Edition (XE)?
To allow developers and learners to use Oracle Database for free with limited resources.
144
What major architecture feature does Oracle 18c XE support?
Multitenant architecture (CDB and PDB).
145
Does Oracle 11g XE support multitenant architecture?
No, 11g XE is not multitenant.
146
What is a Container Database (CDB)?
The root database that holds system metadata and pluggable databases.
147
What is a Pluggable Database (PDB)?
A user-working database that runs inside a container database.
148
Where should users normally work: CDB root or PDB?
In the pluggable database (PDB), not the root container.
149
What default pluggable database is created in Oracle 18c XE?
XEPDB1.
150
What is the default listener port for Oracle 18c XE?
1521
151
How many pluggable databases can Oracle 18c XE support?
A maximum of three pluggable databases.
152
What is the RAM limit for Oracle 18c XE?
2 GB of RAM.
153
What is the CPU limit for Oracle 18c XE?
2 foreground CPUs.
154
What is the user data storage limit in Oracle 18c XE?
12 GB of user data.
155
Are XE resource limits configurable?
No, they are fixed and enforced by Oracle.
156
Why is Oracle 18c XE suitable for Oracle APEX?
It is free, supports multitenant architecture, and works well for small web applications.
157
Where should Oracle 18c XE be downloaded from?
The official Oracle website (oracle.com).
158
Is an Oracle account required to download XE?
Yes, you must log in and accept the license agreement.
159
Which operating system version is commonly used in the course?
Windows x64.
160
What file is used to start the Oracle 18c XE installation?
setup.exe.
161
Is Oracle 18c XE a “lite” version of Oracle Database?
Yes, it is limited but still a full Oracle database engine.
162
Which users share the same password during XE installation?
SYS, SYSTEM, and PDBADMIN.
163
What is the SYS user?
The highest-privileged Oracle database administrator account.
164
What is the SYSTEM user?
An administrative user for managing the database.
165
What is the PDBADMIN user used for?
Administering the pluggable database.
166
Does Oracle 18c XE automatically create a database during install?
Yes, it creates one CDB and one PDB.
167
Can you create unlimited pluggable databases in XE?
No, you are limited to three PDBs.
168
Why is installing Oracle on a non-C drive recommended?
For better system organization and to avoid OS disk pressure.
169
Is Oracle 18c XE considered a real Oracle database?
Yes, it is a real Oracle multitenant database with limitations.
170
What is the main purpose of Oracle 18c XE in this course?
To provide a modern Oracle environment for learning SQL and PL/SQL.
171
What database architecture does Oracle 18c XE use?
Multitenant architecture with one CDB (container database) and one or more PDBs (pluggable databases). This affects where users and objects live.
172
When you connect as SYS, which container are you placed in by default?
CDB$ROOT. SYS always connects to the root container unless the session is explicitly switched.
173
Why can’t you unlock the HR user while connected to CDB$ROOT?
Because HR is a schema user that exists inside the pluggable database (XEPDB1), not in the root container.
174
Which command shows the current container you are connected to?
SHOW CON_NAME; — it confirms whether you are in CDB$ROOT or a PDB.
175
Which data dictionary view lists pluggable databases and their status?
V$PDBS — it shows PDB names and their OPEN_MODE (READ ONLY or READ WRITE).
176
Which pluggable database is used for actual user work in Oracle 18c XE?
XEPDB1 — this is the default PDB created during installation and is used for schemas like HR.
177
Why is PDB$SEED not used for exercises?
It is a read-only template database used only to create new PDBs.
178
What command switches the current session into the PDB?
ALTER SESSION SET CONTAINER = XEPDB1; — required before managing users like HR.
179
Why is switching the container an exam-relevant concept?
Oracle tests understanding of multitenant behavior, especially the difference between CDB and PDB operations.
180
Which user has the highest privileges in Oracle?
SYS — it has full control and connects using SYS AS SYSDBA.
181
Why must SYS connect using AS SYSDBA?
Because SYS requires administrative privileges that are only granted through SYSDBA authentication.
182
Which view is used to verify that the HR user exists?
DBA_USERS — it lists all users in the current container.
183
How do you confirm that HR exists in the PDB?
SELECT COUNT(*) FROM dba_users WHERE username = 'HR'; — count = 1 means it exists.
184
What does it mean if HR exists but you cannot log in?
The account is locked and/or the password is not set.
185
Which command unlocks the HR account?
ALTER USER hr ACCOUNT UNLOCK; — executed inside XEPDB1.
186
Which command sets or resets the HR password?
ALTER USER hr IDENTIFIED BY hr; — sets the password to HR.
187
How many times do you normally unlock and set the HR password?
Only once per installation — this setup is not repeated.
188
Why does CONNECT hr/hr@XEPDB1 sometimes fail after unlocking HR?
Because XEPDB1 is not defined in the tnsnames.ora file.
189
What Oracle file controls service name resolution?
tnsnames.ora — it maps service names to host, port, and database service.
190
What error indicates a missing TNS entry?
ORA-12154: TNS: could not resolve the connect identifier specified.
191
What must the SERVICE_NAME match in tnsnames.ora?
The exact PDB name (XEPDB1), otherwise connections will fail.
192
Why is SQL*Plus sufficient for unlocking HR?
SQL*Plus is installed with XE and supports administrative commands like SYSDBA operations.
193
Why is SQL Developer not available by default in XE?
Oracle 18c XE does not bundle SQL Developer; it must be installed separately.
194
After successful HR login, what confirms everything is correct?
Running a query like SELECT * FROM employees; without errors.
195
Where should all course tables and queries be created?
Inside the XEPDB1 pluggable database, not in CDB$ROOT.
196
What is a common beginner mistake with Oracle 18c XE?
Working in the root container instead of switching to the PDB.
197
Why does Oracle enforce strict separation between CDB and PDB?
To isolate system metadata from user data and support scalable multitenant databases.
198
What is the main purpose of unlocking the HR user?
To enable access to sample schemas used for SQL practice and exams.
199
What is Oracle SQL Developer?
A free GUI client used to write, run, and debug SQL and PL/SQL against Oracle databases.
200
Is Oracle SQL Developer installed by default with Oracle 18c XE?
No. It must be downloaded and installed separately.
201
Why does Oracle 18c XE not include SQL Developer?
XE is a lightweight database-only installation; client tools are optional.
202
Which Oracle website is used to download SQL Developer?
Oracle official website under “Oracle SQL Developer Download”.
203
Which SQL Developer version should be chosen for Windows?
Windows 64-bit version with JDK included.
204
Why is the “with JDK included” version recommended?
It avoids Java configuration issues because the JDK is bundled.
205
Does SQL Developer require installation like Oracle Database?
No. It is a zip-based tool that runs directly after extraction.
206
What file starts SQL Developer?
sqldeveloper.exe.
207
What is the first configuration step after opening SQL Developer?
Configure the TNS Names directory.
208
Where is the TNS Names directory configured in SQL Developer?
Tools → Preferences → Database → Advanced → TNS Names Directory.
209
Why must the TNS Names directory be set?
So SQL Developer can detect database service names like XE and XEPDB1.
210
Which Oracle file does the TNS Names directory point to?
tnsnames.ora.
211
What happens if the TNS directory is not configured?
SQL Developer cannot display or use TNS connections.
212
What connection type is used when selecting a TNS alias?
TNS connection type.
213
Which connection should be created first in SQL Developer?
SYS connection to the root database (XE).
214
What username is used for the root database connection?
SYS (connected as SYSDBA).
215
Why connect as SYS first?
To verify database availability and container configuration.
216
What command confirms the container after connecting as SYS?
SHOW CON_NAME;.
217
Which container does SYS connect to by default?
CDB$ROOT.
218
What is the second (main) connection used for practice?
HR user connected to XEPDB1.
219
Which schema is used for most course exercises?
HR schema.
220
Which pluggable database must HR connect to?
XEPDB1.
221
Why should HR NOT connect to the root container?
User tables and data exist only inside the PDB.
222
What confirms a successful HR connection?
Running SELECT * FROM employees; successfully.
223
What is the common mistake students make when connecting HR?
Connecting HR to the root instead of XEPDB1.
224
What does XE represent in Oracle 18c XE?
The container database service name.
225
What does XEPDB1 represent?
The pluggable database used for user schemas.
226
In course instructions, what does ORCLPDB equal in XE?
XEPDB1.
227
Why is SQL Developer preferred over SQL*Plus?
It provides a GUI, better readability, history, and productivity.
228
Is SQL*Plus still required after SQL Developer is installed?
No, SQL Developer replaces it for daily SQL work.
229
How many HR connections should exist in SQL Developer?
One active connection to XEPDB1.
230
Is unlocking HR required again inside SQL Developer?
No. User unlocking is a one-time database setup step.
231
What must always be verified before running queries?
That the session is connected to XEPDB1.
232
Why is SQL Developer configuration exam-relevant?
Oracle tests understanding of tools, containers, and correct connection targets.
Oracle SQL
flashcards
Decks in class (1)
# Cards
