Oracle Database :: Basic Oracle Concepts and Programming Job Interview Questions and Answers

Learn Oracle database programming with hundreds of Interview Questions and Answers and examples and get a better job as an Oracle DBA with this basic and advance Oracle Database Interview Questions and Answers guide

1 What Is Oracle?
2 What Is an Oracle Database?
3 What Is an Oracle Instance?
4 What Is a Parameter File in Oracle?
5 What Is a Server Parameter File in Oracle?
6 What Is a Initialization Parameter File in Oracle?
7 What is System Global Area (SGA) in Oracle?
8 What is Program Global Area (PGA) in Oracle?
9 What Is a User Account in Oracle?
10 What Is the Relation of a User Account and a Schema in Oracle?
11 What Is a User Role in Oracle?
12 What is a Database Schema in Oracle?
13 What Is a Database Table in Oracle?
14 What Is a Table Index in Oracle?
15 What Is an Oracle Tablespace?
16 What Is an Oracle Data File?
17 What Is a Static Data Dictionary in Oracle?
18 What Is a Dynamic Performance View in Oracle?
19 What Is a Recycle Bin in Oracle?
20 What Is SQL*Plus?
21 What Is Transport Network Substrate (TNS) in Oracle?
22 What Is Open Database Communication (ODBC) in Oracle?
23 What is Oracle Database 10g Express Edition?
24 What Are the Limitations Oracle Database 10g XE?
25 What Operating Systems Are Supported by Oracle Database 10g XE?
26 How To Download Oracle Database 10g XE?
27 How To Install Oracle Database 10g XE?
28 How To Check Your Oracle Database 10g XE Installation?
29 How To Shutdown Your 10g XE Server?
30 How To Start Your 10g XE Server?
31 How Much Memory Your 10g XE Server Is Using?
32 How To Start Your 10g XE Server from Command Line?
33 How To Shutdown Your 10g XE Server from Command Line?
34 How To Unlock the Sample User Account in Oracle?
35 How To Change System Global Area (SGA) in Oracle?
36 How To Change Program Global Area (PGA) in Oracle?
37 What Happens If You Set the SGA Too Low in Oracle?
38 What To Do If the StartBD.bat Failed to Start the XE Instance?
39 How To Login to the Server without an Instance?
40 How To Use "startup" Command to Start Default Instance?
41 Where Are the Settings Stored for Each Instance in Oracle?
42 What To Do If the Binary SPFile Is Wrong for the Default Instance?
43 How To Check the Server Version in Oracle?
44 Explain What Is SQL*Plus?
45 How To Start the Command-Line SQL*Plus?
46 How To Get Help at the SQL Prompt?
47 What Information Is Needed to Connect SQL*Plus an Oracle Server?
48 What Is a Connect Identifier?
49 How To Connect a SQL*Plus Session to an Oracle Server?
50 What Happens If You Use a Wrong Connect Identifier?
51 What To Do If DBA Lost the SYSTEM Password?
52 What Types of Commands Can Be Executed in SQL*Plus?
53 How To Run SQL Commands in SQL*Plus?
54 How To Run PL/SQL Statements in SQL*Plus?
55 How To Change SQL*Plus System Settings?
56 How To Look at the Current SQL*Plus System Settings?
57 What Are SQL*Plus Environment Variables?
58 How To Generate Query Output in HTML Format?
59 What Is Output Spooling in SQL*Plus?
60 How To Save Query Output to a Local File?
61 What Is Input Buffer in SQL*Plus?
62 How To Revise and Re-Run the Last SQL Command?
63 How Run SQL*Plus Commands That Are Stored in a Local File?
64 How To Use SQL*Plus Built-in Timers?
65 What Is Oracle Server Autotrace in Oracle?
66 How To Set Up Autotrace for a User Account?
67 How To Get Execution Path Reports on Query Statements?
68 How To Get Execution Statistics Reports on Query Statements?
69 What Is SQL in Oracle?
70 How Many Categories of Data Types in Oracle?
71 What Are the Oracle Built-in Data Types?
72 What Are the Differences between CHAR and NCHAR in Oracle?
73 What Are the Differences between CHAR and VARCHAR2 in Oracle?
74 What Are the Differences between NUMBER and BINARY_FLOAT in Oracle?
75 What Are the Differences between DATE and TIMESTAMP in Oracle?
76 What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?
77 What Are the Differences between BLOB and CLOB in Oracle?
78 What Are the ANSI Data Types Supported in Oracle?
79 How To Write Text Literals in Oracle?
80 How To Write Numeric Literals in Oracle?
81 How To Write Date and Time Literals in Oracle?
82 How To Write Date and Time Interval Literals in Oracle?
83 How To Convert Numbers to Characters in Oracle?
84 How To Convert Characters to Numbers in Oracle?
85 How To Convert Dates to Characters in Oracle?
86 How To Convert Characters to Dates in Oracle?
87 How To Convert Times to Characters in Oracle?
88 How To Convert Characters to Times in Oracle?
89 What Is NULL value in Oracle?
90 How To Use NULL as Conditions in Oracle?
91 How To Concatenate Two Text Values in Oracle?
92 How To Increment Dates by 1 in Oracle?
93 How To Calculate Date and Time Differences in Oracle?
94 How To Use IN Conditions in Oracle?
95 How To Use LIKE Conditions in Oracle?
96 How To Use Regular Expression in Pattern Match Conditions in Oracle?
97 What Are DDL Statements in Oracle?
98 How To Create a New Table in Oracle?
99 How To Create a New Table by Selecting Rows from Another Table?
100 How To Add a New Column to an Existing Table in Oracle?
101 How To Delete a Column in an Existing Table in Oracle?
102 How To Drop an Existing Table in Oracle?
103 How To Create a Table Index in Oracle?
104 How To Rename an Index in Oracle?
105 How To Drop an Index in Oracle?
106 How To Create a New View in Oracle?
107 How To Drop an Existing View in Oracle?
108 What Are DML Statements in Oracle?
109 How To Create a Testing Table in Oracle?
110 How To Set Up SQL*Plus Output Format in Oracle?
111 How To Insert a New Row into a Table in Oracle?
112 How To Specify Default Values in INSERT Statement using Oracle?
113 How To Omit Columns with Default Values in INSERT Statement in Oracle?
114 How To Insert Multiple Rows with One INSERT Statement in Oracle?
115 How To Update Values in a Table in Oracle?
116 How To Update Values on Multiple Rows in Oracle?
117 How To Use Existing Values in UPDATE Statements using Oracle?
118 How To Use Values from Other Tables in UPDATE Statements using Oracle?
119 What Happens If the UPDATE Subquery Returns Multiple Rows?
120 How To Delete an Existing Row from a Table in Oracle?
121 How To Delete Multiple Rows from a Table in Oracle?
122 How To Delete All Rows a Table in Oracle?
123 What Is a SELECT Query Statement in Oracle?
124 How To Select All Columns of All Rows from a Table in Oracle?
125 How To Select Some Columns from a Table in Oracle?
126 How To Select Some Rows from a Table in Oracle?
127 How To Sort the Query Output in Oracle?
128 Can the Query Output Be Sorted by Multiple Columns in Oracle?
129 How To Sort Output in Descending Order in Oracle?
130 How To Use SELECT Statement to Count the Number of Rows in Oracle?
131 Can SELECT Statements Be Used on Views in Oracle?
132 How To Filter Out Duplications in the Returning Rows using Oracle?
133 What Are Group Functions in Oracle?
134 How To Use Group Functions in the SELECT Clause using Oracle?
135 Can Group Functions Be Mixed with Non-group Selection Fields?
136 How To Divide Query Output into Groups in Oracle?
137 How To Apply Filtering Criteria at Group Level in Oracle?
138 How To Count Duplicated Values in a Column in Oracle?
139 Can Multiple Columns Be Used in GROUP BY in Oracle?
140 Can Group Functions Be Used in the ORDER BY Clause in Oracle?
141 How To Join Two Tables in a Single Query using Oracle?
142 How To Write a Query with an Inner Join in Oracle?
143 How To Define and Use Table Alias Names in Oracle?
144 How To Write a Query with a Left Outer Join in Oracle?
145 How To Write a Query with a Right Outer Join in Oracle?
146 How To Write a Query with a Full Outer Join in Oracle?
147 How To Write an Inner Join with the WHERE Clause in Oracle?
148 How To Write a Left Outer Join with the WHERE Clause in Oracle?
149 How To Name Query Output Columns in Oracle?
150 What Is a Subquery in Oracle?
151 How To Use Subqueries with the IN Operator using Oracle?
152 How To Use Subqueries with the EXISTS Operator in Oracle?
153 How To Use Subqueries in the FROM clause in Oracle?
154 How To Count Groups Returned with the GROUP BY Clause in Oracle?
155 How To Return Top 5 Rows in Oracle?
156 What Is an Oracle Transaction?
157 How To Start a New Transaction in Oracle?
158 How To End the Current Transaction in Oracle?
159 How To Create an Oracle Testing Table?
160 How To Commit the Current Transaction in Oracle?
161 How To Rollback the Current Transaction in Oracle?
162 What Happens to the Current Transaction If a DDL Statement Is Executed?
163 What Happens to the Current Transaction If the Session Is Ended?
164 What Happens to the Current Transaction If the Session Is Killed?
165 How Does Oracle Handle Read Consistency?
166 What Is a READ WRITE Transaction in Oracle?
167 What Is a READ ONLY Transaction in Oracle?
168 How To Set a Transaction To Be READ ONLY in Oracle?
169 What Are the Restrictions in a Oracle READ ONLY Transaction?
170 What Are the General Rules on Data Consistency?
171 What Are Transaction Isolation Levels Supported by Oracle?
172 What Is a Data Lock in Oracle?
173 How Data Locks Are Respected in Oracle?
174 How To Experiment a Data Lock in Oracle?
175 How To View Existing Locks on the Database?
176 What Is a Dead Lock in Oracle?
177 How Oracle Handles Dead Locks?
178 What Is an Oracle User Account?
179 What Is the Relation of a User Account and a Schema?
180 What Is an Oracle User Role?
181 What Are the System Predefined User Roles?
182 What Are Internal User Account in Oracle?
183 How To Connect to the Server with User Account: SYS?
184 How To Use Windows User to Connect to the Server?
185 How To List All User Accounts in Oracle?
186 How To Create a New User Account in Oracle?
187 How To Change User Password in Oracle?
188 How To Delete a User Account in Oracle?
189 What Privilege Is Needed for a User to Connect to Oracle Server?
190 How To Grant CREATE SESSION Privilege to a User in Oracle?
191 How To Revoke CREATE SESSION Privilege from a User in Oracle?
192 How To Lock and Unlock a User Account in Oracle?
193 What Privilege Is Needed for a User to Create Tables in Oracle?
194 How To Assign a Tablespace to a Users in Oracle?
195 What Privilege Is Needed for a User to Create Views in Oracle?
196 What Privilege Is Needed for a User to Create Indexes in Oracle?
197 What Privilege Is Needed for a User to Query Tables in Another Schema?
198 What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
199 What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
200 How To Find Out What Privileges a User Currently Has in Oracle?
201 What Is an Oracle Database Table?
202 How Many Types of Tables Supported by Oracle?
203 How To Create a New Table in Your Schema?
204 How To Rename an Existing Table?
205 How To Drop an Existing Table?
206 How To Add a New Column to an Existing Table?
207 How To Add a New Column to an Existing Table with a Default Value?
208 How To Rename a Column in an Existing Table?
209 How To Delete a Column in an Existing Table?
210 How To View All Columns in an Existing Table?
211 How To Recover a Dropped Table in Oracle?
212 What Is an Oracle Recycle Bin?
213 How To Turn On or Off Recycle Bin for the Instance?
214 How To View the Dropped Tables in Your Recycle Bin?
215 How To Empty Your Oracle Recycle Bin?
216 How To Turn On or Off Recycle Bin for the Session?
217 How To List All Tables in Your Schema?
218 What Is a Table Index?
219 How To Run SQL Statements through the Web Interface?
220 How To Create a Table Index?
221 How To List All Indexes in Your Schema?
222 What Is an Index Associated with a Constraint?
223 How To Rename an Index?
224 How To Drop an Index?
225 Can You Drop an Index Associated with a Unique or Primary Key Constraint?
226 What Happens to Indexes If You Drop a Table?
227 How To Recover a Dropped Index?
228 What Happens to the Indexes If a Table Is Recovered?
229 How To Rebuild an Index in Oracle?
230 How To See the Table Columns Used in an Index?
231 How To Create a Single Index for Multiple Columns?
232 How a Tablespace Is Related to Data Files?
233 How a Database Is Related to Tablespaces?
234 How To View the Tablespaces in the Current Database?
235 What Are the Predefined Tablespaces in a Database?
236 How To View the Data Files in the Current Database?
237 How To Create a new Oracle Data File?
238 How To Create a New Tablespace in Oracle?
239 How To Rename a Tablespace in Oracle?
240 How To Drop a Tablespace?
241 What Happens to the Data Files If a Tablespace Is Dropped?
242 How To Create a Table in a Specific Tablespace?
243 How To See Free Space of Each Tablespace?
244 How To Bring a Tablespace Offline?
245 How To Bring a Tablespace Online?
246 How To Add Another Datafile to a Tablespace?
247 What Happens If You Lost a Data File?
248 How Remove Data Files before opening a Database?
249 How To Create an Oracle Database?
250 How To Create an Oracle Database Manually?
251 How To Select an Oracle System ID (SID)?
252 How To Establish Administrator Authentication to the Server?
253 How To Create an Initialization Parameter File?
254 How To Connect the Oracle Server as SYSDBA?
255 How To Create a Server Parameter File?
256 How To Start an Oracle Instance?
257 How To Start a Specific Oracle Instance?
258 How To Start Instance with a Minimal Initialization Parameter File?
259 How To Run CREATE DATABASE Statement?
260 How To Do Clean Up If CREATE DATABASE Failed?
261 How To Run CREATE DATABASE Statement Again?
262 How To Create Additional Tablespaces for an New Database?
263 How To Build Data Dictionary View an New Database?
264 What Is PL/SQL?
265 What Are the Types PL/SQL Code Blocks?
266 How To Define an Anonymous Block?
267 How Many Anonymous Blocks Can Be Defined?
268 How To Run the Anonymous Block Again?
269 How To Create a Stored Program Unit?
270 How To Execute a Stored Program Unit?
271 How Many Data Types Are Supported?
272 What Are the Execution Control Statements?
273 How To Use SQL Statements in PL/SQL?
274 How To Process Query Result in PL/SQL?
275 How To Create an Array in PL/SQL?
276 How To Manage Transaction Isolation Level?
277 How To Pass Parameters to Procedures?
278 How To Define a Procedure inside Another Procedure?
279 What Do You Think about PL/SQL?
280 What Is Oracle SQL Developer?
281 What Operating Systems Are Supported by Oracle SQL Developer?
282 How To Download Oracle SQL Developer?
283 How To Install Oracle SQL Developer?
284 How To Start Oracle SQL Developer?
285 Is Oracel SQL Developer written in Java?
286 How To Connect to a Local Oracle 10g XE Server?
287 How To Connect to a Remote Server?
288 How To Run SQL Statements with Oracle SQL Developer?
289 How To Export Your Connection Information to a File?
290 How To Run SQL*Plus Commands in SQL Developer?
291 How To Work with Data Objects Interactively?
292 How To Get a CREATE Statement for an Existing Table?
293 How To Create a Table Interactively?
294 How To Enter a New Row into a Table Interactively?
295 What Is the Reports View in Oracle SQL Developer?
296 How To Get a List of All Tables in the Database?
297 How To Get a List of All User Accounts in the Database?
298 How To Get a List of All Background Sessions in the Database?
299 How To Create Your Own Reports in SQL Developer?
300 How Many File Formats Are Supported to Export Data?
301 How To Export Data to a CSV File?
302 What Is Oracle SQL Developer?
303 What Operating Systems Are Supported by Oracle SQL Developer?
304 How To Download Oracle SQL Developer?
305 How To Install Oracle SQL Developer?
306 What Is PL/SQL Language Case Sensitive?
307 How To Place Comments in PL/SQL?
308 What Are the Types PL/SQL Code Blocks?
309 What Is an Anonymous Block?
310 What Is a Named Program Unit?
311 What Is a Procedure?
312 What Is a Function?
313 How To Declare a Local Variable?
314 How To Initialize Variables with Default Values?
315 How To Assign Values to Variables?
316 What Are the Arithmetic Operations?
317 What Are the Numeric Comparison Operations?
318 What Are the Logical Operations?
319 How Many Categories of Data Types?
320 How Many Scalar Data Types Are Supported in PL/SQL?
321 How To Convert Character Types to Numeric Types?
322 What Are the Execution Control Statements in Oracle?
323 How To Use "IF" Statements on Multiple Conditions?
324 How To Use "WHILE" Statements in Oracle?
325 How To Use "FOR" Statements in Oracle?
326 What Is NULL in PL/SQL?
327 How To Test NULL Values?
328 What Is a Procedure in Oracle?
329 What Is an Oracle Function?
330 How To Define an Anonymous Procedure without Variables?
331 How To Define an Anonymous Procedure with Variables?
332 How To Create a Stored Procedure in Oracle?
333 How To Execute a Stored Procedure in Oracle?
334 How To Drop a Stored Procedure in Oracle?
335 How To Pass Parameters to Procedures in Oracle?
336 How To Create a Stored Function in Oracle?
337 How To Call a Stored Function in Oracle?
338 How To Drop a Stored Function?
339 How To Call a Stored Function with Parameters?
340 How To Define an Oracle Sub Procedure?
341 How To Call a Sub Procedure?
342 How To Define a Sub Function?
343 Can Sub Procedure/Function Be Called Recursively?
344 What Happens If Recursive Calls Get Out of Control?
345 What Is the Order of Defining Local Variables and Sub Procedures/Functions?
346 What Is the Difference between Formal Parameters and Actual Parameters?
347 What Are the Parameter Modes Supported by PL/SQL?
348 How To Use "IN" Parameter Properly?
349 How To Use "OUT" Parameter Properly?
350 How To Use "IN OUT" Parameter Properly?
351 How To Define Default Values for Formal Parameters?
352 What Are Named Parameters?
353 What Is the Scope of a Local Variable?
354 Can DML Statements Be Used in PL/SQL?
355 Can DDL Statements Be Used in PL/SQL?
356 Can Variables Be Used in SQL Statements?
357 What Happens If Variable Names Collide with Table/Column Names?
358 How To Resolve Name Conflicts between Variables and Columns?
359 How To Assign Query Results to Variables?
360 Can You Assign Multiple Query Result Rows To a Variable?
361 How To Run SQL Functions in PL/SQL?
362 How To Retrieve the Count of Updated Rows?
363 What Is the Implicit Cursor in Oracle?
364 How To Assign Data of the Deleted Row to Variables?
365 What Is a RECORD in PL/SQL?
366 How To Define a Specific RECORD Type?
367 How To Define a Variable of a Specific RECORD Type?
368 How To Assign Values to Data Fields in RECORD Variables?
369 How To Retrieve Values from Data Fields in RECORD Variables?
370 How To Define a Data Field as NOT NULL?
371 How To Define a RECORD Variable to Store a Table Row?
372 How To Assign a Table Row to a RECORD Variable?
373 How To Insert a Record into a Table?
374 How To Update a Table Row with a Record?
375 How To Define a Variable to Match a Table Column Data Type?
376 What Is a Cursor in Oracle?
377 How Many Types of Cursors Supported in PL/SQL?
378 What Is the Oracle Implicit Cursor?
379 How To Use Attributes of the Implicit Cursor in Oracle?
380 How To Loop through Data Rows in the Implicit Cursor?
381 How To Define an Explicit Cursor in Oracle?
382 How To Open and Close an Explicit Cursor in Oracle?
383 How To Retrieve Data from an Explicit Cursor?
384 How To Retrieve Data from an Cursor to a RECORD?
385 How To Use FETCH Statement in a Loop?
386 How To Use an Explicit Cursor without OPEN Statements?
387 Can Multiple Cursors Being Opened at the Same Time?
388 How To Pass a Parameter to a Cursor in Oracle?
389 What Is an Oracle Cursor Variable?
390 How To Define an Oracle Cursor Variable?
391 How To Open a Cursor Variable?
392 How To Loop through a Cursor Variable?
393 How To Pass a Cursor Variable to a Procedure?
394 Why Cursor Variables Are Easier to Use than Cursors?
395 What Is the Simplest Tool to Run Commands on Oracle Servers?
396 What Is the Quickest Way to Export a Table to a Flat File?
397 How To Export Data with a Field Delimiter?
398 What Is SQL*Loader?
399 What Is a SQL*Loader Control File?
400 How To Load Data with SQL*Loader?
401 What Is an External Table?
402 How To Load Data through External Tables?
403 What Are the Restrictions on External Table Columns?
404 What Is a Directory Object?
405 How To Define an External Table with a Text File?
406 How To Run Queries on External Tables?
407 How To Load Data from External Tables to Regular Tables?
408 What Is the Data Pump Export Utility?
409 What Is the Data Pump Import Utility?
410 How To Invoke the Data Pump Export Utility?
411 How To Invoke the Data Pump Import Utility?
412 What Are Data Pump Export and Import Modes?
413 How To Estimate Disk Space Needed for an Export Job?
414 How To Do a Full Database Export?
415 Where Is the Export Dump File Located?
416 How To Export Your Own Schema?
417 How To Export Several Tables Together?
418 What Happens If the Imported Table Already Exists?
419 How To Import One Table Back from a Dump File?
420 What Are the Original Export and Import Utilities?
421 How To Invoke the Original Export Import Utilities?
422 What Is Oracle Open Database Communication (ODBC)?
423 How To Install Oracle ODBC Drivers?
424 How To Find Out What Oracle ODBC Drivers Are Installed?
425 How Can Windows Applications Connect to Oracle Servers?
426 How To Create Tables for ODBC Connection Testing?
427 How To Check the Oracle TNS Settings?
428 How To Define a Data Source Name (DSN) in ODBC Manager?
429 How To Connect MS Access to Oracle Servers?
430 How To Connect ASP Pages to Oracle Servers?