Oracle SQL Experts: Oracle FORCE View FAQs with Examples

An Oracle FORCE view is a view that can be created even when the underlying table or other referenced objects do not currently exist.

The key syntax is:

CREATE FORCE VIEW view_name AS
SELECT ...
FROM ...;

The opposite is NOFORCE, which is the normal/default behavior.

1. What is a FORCE view?

A FORCE view tells Oracle:

"Create the view even if the referenced objects are currently missing or invalid."

Example:

CREATE FORCE VIEW employee_report AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

If EMPLOYEES does not exist, Oracle can still create the view, but the view will be INVALID.

CREATE FORCE VIEW
        |
        ↓
Referenced object exists?
        |
   +----+----+
   |         |
  YES        NO
   |         |
   ↓         ↓
 VALID     INVALID
 VIEW       VIEW

2. What is the syntax of a FORCE view?

CREATE FORCE VIEW view_name AS
SELECT ...
FROM ...;

Example:

CREATE FORCE VIEW emp_view AS
SELECT employee_id,
       first_name
FROM employees;

You can also use:

CREATE OR REPLACE FORCE VIEW emp_view AS
SELECT employee_id,
       first_name
FROM employees;

3. What happens if the underlying table does not exist?

Consider:

CREATE FORCE VIEW emp_view AS
SELECT employee_id,
       first_name
FROM employees;

If EMPLOYEES doesn't exist, Oracle creates the view but marks it invalid.

You can check:

SELECT object_name,
       object_type,
       status
FROM user_objects
WHERE object_name = 'EMP_VIEW';

Possible result:

OBJECT_NAME	OBJECT_TYPE	STATUS
EMP_VIEW	VIEW	INVALID

4. Can we query an invalid FORCE view?

No.

Suppose:

CREATE FORCE VIEW emp_view AS
SELECT employee_id,
       first_name
FROM employees;

but EMPLOYEES doesn't exist.

Trying:

SELECT *
FROM emp_view;

will fail because Oracle cannot execute the view until its dependencies are valid.

The important distinction is:

FORCE view
    ↓
Can CREATE the view
    ↓
Even if dependency is missing
    ↓
But cannot successfully SELECT
    ↓
Until dependencies are valid

5. Why would we create an invalid view?

This is useful in development and deployment scenarios where objects are created in a particular order.

For example:

Step 1
Create VIEW
    ↓
Step 2
Create TABLE
    ↓
Step 3
Fix/compile VIEW
    ↓
Step 4
Use VIEW

This can be useful when deploying a large application where dependencies are created separately.

6. What is the difference between FORCE and NOFORCE?

This is one of the most common interview questions.

FORCE

CREATE FORCE VIEW emp_view AS
SELECT employee_id
FROM employees;

Oracle attempts to create the view even if EMPLOYEES doesn't exist.

NOFORCE

CREATE NOFORCE VIEW emp_view AS
SELECT employee_id
FROM employees;

The referenced object must exist and be suitable for creating the view.

Comparison:

Feature	FORCE	NOFORCE
Referenced object exists	✅	✅
Referenced object missing	View can be created	❌
View may initially be INVALID	✅	Normally no
Default behavior	❌	✅
Useful for deployment ordering	✅	Less useful

7. What is the default: FORCE or NOFORCE?

NOFORCE is the default.

So:

CREATE VIEW emp_view AS
SELECT employee_id
FROM employees;

behaves like:

CREATE NOFORCE VIEW emp_view AS
SELECT employee_id
FROM employees;

You don't normally need to explicitly write NOFORCE.

8. Can FORCE be used with CREATE OR REPLACE?

Yes.

Example:

CREATE OR REPLACE FORCE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

This is useful when you want to replace an existing view while allowing the view to be created even if dependencies aren't currently valid.

9. Can FORCE create a view when the table does not exist?

Yes.

Example:

CREATE FORCE VIEW future_employee_view AS
SELECT employee_id,
       first_name,
       salary
FROM future_employees;

If FUTURE_EMPLOYEES doesn't exist, the view can still be created.

Check:

SELECT object_name,
       status
FROM user_objects
WHERE object_name = 'FUTURE_EMPLOYEE_VIEW';

You may see:

FUTURE_EMPLOYEE_VIEW    INVALID

10. What happens when the missing table is created later?

Suppose you first create:

CREATE FORCE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

but EMPLOYEES doesn't exist.

Then later:

CREATE TABLE employees (
    employee_id NUMBER,
    first_name  VARCHAR2(100),
    salary      NUMBER
);

The view's dependency now exists.

You can explicitly recompile the view:

ALTER VIEW emp_view COMPILE;

Then check:

SELECT object_name,
       status
FROM user_objects
WHERE object_name = 'EMP_VIEW';

Expected:

OBJECT_NAME    STATUS
------------   ------
EMP_VIEW       VALID

11. Does FORCE automatically make the view valid?

No.

This is a very important distinction.

FORCE means:

Allow CREATE

It does not mean:

Ignore dependency problems

Example:

CREATE FORCE VIEW emp_view AS
SELECT employee_id
FROM employees;

If EMPLOYEES doesn't exist:

View creation → allowed
View status   → INVALID
Query         → fails until dependency is resolved

12. Can FORCE be used when a column doesn't exist?

This is an important nuance.

Suppose EMPLOYEES exists but does not have BONUS, and you create:

CREATE FORCE VIEW emp_bonus AS
SELECT employee_id,
       bonus
FROM employees;

The view can be created in an invalid state because the referenced definition cannot be successfully resolved.

Check:

SELECT object_name,
       status
FROM user_objects
WHERE object_name = 'EMP_BONUS';

You may see:

EMP_BONUS    INVALID

The view must be corrected/recompiled after the dependency issue is fixed.

13. Can FORCE create a view based on another missing view?

Yes.

For example:

CREATE FORCE VIEW employee_report AS
SELECT employee_id,
       first_name
FROM employee_details;

If EMPLOYEE_DETAILS doesn't exist, Oracle can create EMPLOYEE_REPORT in an invalid state.

Later:

CREATE VIEW employee_details AS
SELECT employee_id,
       first_name
FROM employees;

Then recompile if necessary:

ALTER VIEW employee_report COMPILE;

14. Can FORCE be useful in deployment scripts?

Yes.

Suppose your application contains:

EMPLOYEES
DEPARTMENTS
EMP_DEPT_VIEW
EMPLOYEE_REPORT

and deployment order is temporarily:

EMP_DEPT_VIEW
      ↓
EMPLOYEES
      ↓
DEPARTMENTS
      ↓
EMPLOYEE_REPORT

A FORCE view can allow the view definition to be created before all dependencies are available.

For example:

CREATE FORCE VIEW emp_dept_view AS
SELECT e.employee_id,
       e.first_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Later, after the tables exist:

ALTER VIEW emp_dept_view COMPILE;

15. How do I check whether a FORCE-created view is valid?

Use USER_OBJECTS.

SELECT object_name,
       object_type,
       status
FROM user_objects
WHERE object_type = 'VIEW';

For one view:

SELECT object_name,
       status
FROM user_objects
WHERE object_type = 'VIEW'
AND object_name = 'EMP_VIEW';

Possible result:

OBJECT_NAME    STATUS
------------   ------
EMP_VIEW       VALID

or:

OBJECT_NAME    STATUS
------------   ------
EMP_VIEW       INVALID

16. How do I find why a FORCE view is invalid?

Use:

SELECT name,
       type,
       line,
       position,
       text
FROM user_errors
WHERE name = 'EMP_VIEW'
ORDER BY sequence;

This can show compilation errors associated with the view.

For example, you might find an error related to:

ORA-00942: table or view does not exist

17. Can I compile a FORCE view?

Yes.

Use:

ALTER VIEW emp_view COMPILE;

Then check:

SELECT object_name,
       status
FROM user_objects
WHERE object_name = 'EMP_VIEW';

If all dependencies are now correct:

EMP_VIEW    VALID

18. What is the difference between FORCE view and ALTER VIEW COMPILE?

They solve different problems.

FORCE

Used when creating the view:

CREATE FORCE VIEW emp_view AS
...

It allows creation despite dependency problems.

ALTER VIEW ... COMPILE

Used to compile/recompile an existing view:

ALTER VIEW emp_view COMPILE;

So:

CREATE FORCE
    ↓
Create the view even if dependencies aren't ready

ALTER VIEW COMPILE
    ↓
Try to compile the existing view

19. Can FORCE be used with a simple view?

Yes.

CREATE FORCE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary
FROM employees;

20. Can FORCE be used with a complex view?

Yes.

Example:

CREATE FORCE VIEW department_report AS
SELECT d.department_id,
       d.department_name,
       COUNT(e.employee_id) AS employee_count,
       AVG(e.salary) AS average_salary
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id,
         d.department_name;

If the referenced objects aren't currently available, the view can be created but may remain invalid.

21. Can FORCE be used with a materialized view?

Be careful here.

FORCE in:

CREATE FORCE VIEW ...

refers to a normal view.

A materialized view has different creation and refresh concepts, such as:

BUILD IMMEDIATE
BUILD DEFERRED
REFRESH FAST
REFRESH COMPLETE
REFRESH FORCE

Do not confuse:

FORCE VIEW

with:

REFRESH FORCE MATERIALIZED VIEW

They are different concepts.

22. FORCE View vs Materialized View REFRESH FORCE

This is a common interview trap.

Normal view:

CREATE FORCE VIEW emp_view AS
SELECT ...
FROM employees;

FORCE controls whether Oracle allows creation when dependencies cannot currently be resolved.

Materialized view:

CREATE MATERIALIZED VIEW emp_mv
REFRESH FORCE
ON DEMAND
AS
SELECT ...
FROM employees;

REFRESH FORCE tells Oracle to choose an appropriate refresh method, typically fast if possible, otherwise complete.

So:

FORCE VIEW
    ↓
View creation behavior

REFRESH FORCE
    ↓
Materialized-view refresh behavior

They are unrelated features.

23. Does FORCE mean the view will always work?

No.

This is probably the most important concept.

For example:

CREATE FORCE VIEW emp_view AS
SELECT employee_id,
       first_name
FROM employees;

If EMPLOYEES does not exist:

CREATE → succeeds
STATUS → INVALID
SELECT → fails

After creating the missing table and successfully compiling:

STATUS → VALID
SELECT → works

Therefore:

FORCE allows creation; it does not guarantee successful execution.

24. Can FORCE hide errors permanently?

No.

It doesn't fix the underlying problem.

For example:

CREATE FORCE VIEW emp_view AS
SELECT employee_id,
       unknown_column
FROM employees;

The view may exist, but it remains invalid until the definition/dependency problem is fixed.

You should always check:

SELECT object_name,
       status
FROM user_objects
WHERE object_type = 'VIEW';

and:

SELECT name,
       line,
       position,
       text
FROM user_errors
WHERE name = 'EMP_VIEW';

25. What are the advantages of FORCE views?

Common advantages include:

26. Flexible deployment

Views can be created before all dependencies are available.

27. Dependency-order flexibility

Useful when objects are deployed in different scripts.

28. Development convenience

Developers can define dependent views before completing all underlying objects.

29. Deployment automation

Large database deployments can create object definitions before all dependencies are available.

30. What are the disadvantages of FORCE views?

The main disadvantage is that you can end up with invalid objects.

FORCE VIEW
    ↓
INVALID
    ↓
Application queries it
    ↓
ERROR

Therefore, after deployment you should check:

SELECT object_name,
       object_type,
       status
FROM user_objects
WHERE status = 'INVALID';

This is especially important in production deployments.

31. FORCE vs NOFORCE — Complete Example

Step 1: Assume the table doesn't exist

DROP TABLE employees PURGE;

Step 2: Try NOFORCE

CREATE NOFORCE VIEW emp_view AS
SELECT employee_id,
       first_name
FROM employees;

This fails because the referenced table doesn't exist.

Step 3: Try FORCE

CREATE FORCE VIEW emp_view AS
SELECT employee_id,
       first_name
FROM employees;

The view can be created, but it is invalid.

Check:

SELECT object_name,
       status
FROM user_objects
WHERE object_name = 'EMP_VIEW';

Result:

EMP_VIEW    INVALID

Step 4: Create the table

CREATE TABLE employees (
    employee_id NUMBER,
    first_name  VARCHAR2(100)
);

Step 5: Compile the view

ALTER VIEW emp_view COMPILE;

Step 6: Check status

SELECT object_name,
       status
FROM user_objects
WHERE object_name = 'EMP_VIEW';

Result:

EMP_VIEW    VALID

Now:

SELECT *
FROM emp_view;

can execute successfully.

32. What are the common FORCE-view interview traps?

Trap 1 — Does CREATE FORCE VIEW guarantee a valid view?

❌ No. It can create an INVALID view.

Trap 2 — Can you query an invalid FORCE view successfully?

❌ No. The dependencies must be resolved and the view must compile successfully.

Trap 3 — Is FORCE the default?

❌ No. NOFORCE is the default.

Trap 4 — Can FORCE be used with CREATE OR REPLACE VIEW?

✅ Yes.

CREATE OR REPLACE FORCE VIEW emp_view AS
...

Trap 5 — Does FORCE fix missing tables or columns?

❌ No. It only allows the view definition to be created despite dependency problems.

Trap 6 — How do you make an invalid view valid after fixing dependencies?

Use:

ALTER VIEW emp_view COMPILE;

Trap 7 — Is FORCE VIEW the same as REFRESH FORCE for materialized views?

❌ No. They are completely different concepts.

33. FORCE View Cheat Sheet

CREATE FORCE VIEW
        |
        ↓
Create view definition
        |
   +----+----+
   |         |
Dependencies  Dependencies
exist         missing
   |             |
   ↓             ↓
 VALID         INVALID
   |             |
   ↓             ↓
SELECT       Fix dependency
                 |
                 ↓
        ALTER VIEW COMPILE
                 |
                 ↓
               VALID

Key Commands

Create a FORCE view:

CREATE FORCE VIEW emp_view AS
SELECT employee_id,
       first_name
FROM employees;

Create or replace:

CREATE OR REPLACE FORCE VIEW emp_view AS
SELECT employee_id,
       first_name
FROM employees;

Compile:

ALTER VIEW emp_view COMPILE;

Check status:

SELECT object_name,
       status
FROM user_objects
WHERE object_type = 'VIEW';

Check errors:

SELECT name,
       line,
       position,
       text
FROM user_errors
WHERE name = 'EMP_VIEW'
ORDER BY sequence;

Most Important Interview Statement

CREATE FORCE VIEW allows Oracle to create a view even when referenced objects cannot currently be resolved. The resulting view may be INVALID and cannot be successfully queried until its dependencies are fixed and the view compiles successfully.

And remember:

NOFORCE
   ↓
Normal/default behavior
   ↓
Dependencies must be valid

FORCE
   ↓
Allow creation despite dependency problems
   ↓
May create INVALID view

ALTER VIEW ... COMPILE
   ↓
Recompile after fixing dependencies

Oracle SQL Experts

Home

Oracle FORCE View FAQs with Examples

1. What is a FORCE view?

2. What is the syntax of a FORCE view?

3. What happens if the underlying table does not exist?

4. Can we query an invalid FORCE view?

5. Why would we create an invalid view?

6. What is the difference between FORCE and NOFORCE?

7. What is the default: FORCE or NOFORCE?

8. Can FORCE be used with CREATE OR REPLACE?

9. Can FORCE create a view when the table does not exist?

10. What happens when the missing table is created later?

11. Does FORCE automatically make the view valid?

12. Can FORCE be used when a column doesn't exist?

13. Can FORCE create a view based on another missing view?

14. Can FORCE be useful in deployment scripts?

15. How do I check whether a FORCE-created view is valid?

16. How do I find why a FORCE view is invalid?

17. Can I compile a FORCE view?

18. What is the difference between FORCE view and ALTER VIEW COMPILE?

19. Can FORCE be used with a simple view?

20. Can FORCE be used with a complex view?

21. Can FORCE be used with a materialized view?

22. FORCE View vs Materialized View REFRESH FORCE

23. Does FORCE mean the view will always work?

24. Can FORCE hide errors permanently?

25. What are the advantages of FORCE views?

26. Flexible deployment

27. Dependency-order flexibility

28. Development convenience

29. Deployment automation

30. What are the disadvantages of FORCE views?

31. FORCE vs NOFORCE — Complete Example

32. What are the common FORCE-view interview traps?

33. FORCE View Cheat Sheet

Key Commands

Most Important Interview Statement

No comments:

Post a Comment