Oracle Interview Tricks & Tips

Installation & Setup Tricks

Use Docker for Quick Setup

Docker Setup

Run Oracle XE without system changes using Docker:

                                        docker run --name oracle-xe -d -p 1521:1521 -e ORACLE_PASSWORD=yourpassword gvenzl/oracle-xe
                                    

Pro Tip: Always change the default passwords after installation.

Secure Oracle After Installation

Security Essential

Change default passwords and lock unused accounts:

                                        ALTER USER sys IDENTIFIED BY "new_strong_password";
                                        ALTER USER system IDENTIFIED BY "new_strong_password";
                                        ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 3;
                                    

Database & Table Tricks

Clone Table Structure

Schema

Create an empty table with the same structure:

                                        CREATE TABLE new_table AS 
                                        SELECT * FROM original_table WHERE 1=0;
                                    

Efficient Data Insertion

Performance Optimization

Insert data from another table with conditions:

                                        INSERT /*+ APPEND */ INTO target_table 
                                        SELECT * FROM source_table 
                                        WHERE condition;
                                    

The APPEND hint improves performance for bulk inserts.

Query Optimization Tricks

Use EXPLAIN PLAN to Debug Queries

Optimization Debugging

Analyze query execution plan:

                                        EXPLAIN PLAN FOR 
                                        SELECT * FROM employees WHERE salary > 50000;
                                        
                                        SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
                                    

Key things to check: access path (should use indexes), cost (lower = better)

Force Index Usage

Indexing Advanced

When the optimizer doesn't choose the best index:

                                        SELECT /*+ INDEX(employees emp_salary_idx) */ * 
                                        FROM employees 
                                        WHERE salary > 50000;
                                    

JOINs & Subqueries Tricks

Replace NOT IN with NOT EXISTS

Performance Best Practice

More efficient than NOT IN for large datasets:

                                        SELECT a.* FROM table_a a 
                                        WHERE NOT EXISTS (
                                            SELECT 1 FROM table_b b 
                                            WHERE b.id = a.id
                                        );
                                    

Use WITH Clause for Complex Queries

Optimization Best Practice

Improve readability and performance:

                                        WITH dept_stats AS (
                                            SELECT department_id, AVG(salary) avg_sal
                                            FROM employees
                                            GROUP BY department_id
                                        )
                                        SELECT e.*, d.avg_sal
                                        FROM employees e
                                        JOIN dept_stats d ON e.department_id = d.department_id;
                                    

Aggregation Tricks

Top N Rows Per Group

Advanced Complex Query

Using analytic functions (Oracle's powerful feature):

                                        SELECT * FROM (
                                            SELECT e.*,
                                                   RANK() OVER (PARTITION BY department_id 
                                                   ORDER BY salary DESC) as rank
                                            FROM employees e
                                        ) WHERE rank <= 3;  -- Top 3 per department
                                    

Running Totals

Analytic Functions Powerful Feature

Using analytic functions for cumulative sums:

                                        SELECT order_date, amount, 
                                               SUM(amount) OVER (ORDER BY order_date) AS running_total 
                                        FROM sales;
                                    

Date & Time Tricks

Last 30 Days Records

Date Functions Common Task

Find records from the last 30 days:

                                        SELECT * FROM orders 
                                        WHERE order_date >= SYSDATE - 30;
                                    

Group By Time Periods

Aggregation Reporting

Group by week/month/year easily:

                                        SELECT 
                                            TO_CHAR(order_date, 'YYYY') AS year,
                                            TO_CHAR(order_date, 'MM') AS month,
                                            COUNT(*) AS total_orders
                                        FROM orders
                                        GROUP BY TO_CHAR(order_date, 'YYYY'), TO_CHAR(order_date, 'MM');
                                    

Indexing Tricks

Find Unused Indexes

Performance Maintenance

Identify indexes that aren't being used:

                                        SELECT index_name, table_name 
                                        FROM user_indexes 
                                        WHERE status = 'UNUSABLE';
                                    

These can be rebuilt or dropped to improve performance.

Function-Based Indexes

Optimization Best Practice

Create indexes on expressions for better performance:

                                        CREATE INDEX idx_upper_name ON employees(UPPER(last_name));
                                        -- Query uses index:
                                        SELECT * FROM employees 
                                        WHERE UPPER(last_name) = 'SMITH';
                                    

Transaction Tricks

Test Before Committing

Safety Essential

Test queries in a transaction before committing:

                                        SET TRANSACTION NAME 'test_transaction';
                                        UPDATE accounts SET balance = balance - 100 
                                        WHERE user_id = 1;
                                        -- Check for errors, then:
                                        COMMIT;  -- or ROLLBACK if issues
                                    

Set Isolation Level

Concurrency Advanced

Control how transactions interact with each other:

ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;

Other levels: READ COMMITTED, READ ONLY, SERIALIZABLE

Security Tricks

Audit User Privileges

Security Essential

Check what permissions a user has:

                                        SELECT * FROM dba_sys_privs 
                                        WHERE grantee = 'USERNAME';
                                    

Prevent SQL Injection

Best Practice Critical

Always use bind variables in applications:

                                        -- PL/SQL Example
                                        EXECUTE IMMEDIATE 
                                        'SELECT * FROM employees WHERE employee_id = :id' 
                                        USING employee_id;
                                    

Never concatenate user input directly into SQL queries!

Backup & Recovery Tricks

Export Single Table

Maintenance Common Task

Export just one table using Data Pump:

                                        expdp username/password TABLES=employees 
                                        DIRECTORY=dpump_dir DUMPFILE=employees.dmp
                                    

Schema-Only Export

Structure Migration

Export just the database structure without data:

                                        expdp username/password SCHEMAS=hr 
                                        DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp 
                                        CONTENT=METADATA_ONLY
                                    

Bonus: Common Interview Questions

                                        SELECT email, COUNT(*) FROM employees 
                                        GROUP BY email 
                                        HAVING COUNT(*) > 1;
                                    

                                        DELETE FROM employees 
                                        WHERE rowid NOT IN (
                                            SELECT MIN(rowid) 
                                            FROM employees 
                                            GROUP BY email
                                        );
                                    

DELETE: Removes rows (can rollback, logs each row)
TRUNCATE: Faster, resets storage (cannot rollback, doesn't log individual rows)
DROP: Deletes entire table structure and data

Oracle Interview Tricks & Tips

Installation & Setup Tricks

Use Docker for Quick Setup

Secure Oracle After Installation

Database & Table Tricks

Clone Table Structure

Efficient Data Insertion

Query Optimization Tricks

Use EXPLAIN PLAN to Debug Queries

Force Index Usage

JOINs & Subqueries Tricks

Replace NOT IN with NOT EXISTS

Use WITH Clause for Complex Queries

Aggregation Tricks

Top N Rows Per Group

Running Totals

Date & Time Tricks

Last 30 Days Records

Group By Time Periods

Indexing Tricks

Find Unused Indexes

Function-Based Indexes

Transaction Tricks

Test Before Committing

Set Isolation Level

Security Tricks

Audit User Privileges

Prevent SQL Injection

Backup & Recovery Tricks

Export Single Table

Schema-Only Export

Bonus: Common Interview Questions

Final Interview Tips

More Oracle Resources

Oracle Basics

Oracle PL/SQL

Oracle Certifications

Oracle Interview Tricks & Tips

Installation & Setup Tricks

Use Docker for Quick Setup

Secure Oracle After Installation

Database & Table Tricks

Clone Table Structure

Efficient Data Insertion

Query Optimization Tricks

Use EXPLAIN PLAN to Debug Queries

Force Index Usage

JOINs & Subqueries Tricks

Replace NOT IN with NOT EXISTS

Use WITH Clause for Complex Queries

Aggregation Tricks

Top N Rows Per Group

Running Totals

Date & Time Tricks

Last 30 Days Records

Group By Time Periods

Indexing Tricks

Find Unused Indexes

Function-Based Indexes

Transaction Tricks

Test Before Committing

Set Isolation Level

Security Tricks

Audit User Privileges

Prevent SQL Injection

Backup & Recovery Tricks

Export Single Table

Schema-Only Export

Bonus: Common Interview Questions

How do you find duplicate records?

How do you delete duplicates?

What's the difference between DELETE, TRUNCATE, and DROP?

Final Interview Tips

More Oracle Resources

Oracle Basics

Oracle PL/SQL

Oracle Certifications