Home » RDBMS Server » Performance Tuning » Oracle noforce view creates force view
Oracle noforce view creates force view [message #641833] Tue, 25 August 2015 09:54 Go to next message
ram50958
Messages: 7
Registered: September 2010
Junior Member
Whenever I am trying to create a view it is creating it with FORCE option even though I am using NOFORCE option. Is there any DB parameter/property that pushes this?

Report message to a moderator

Re: Oracle noforce view creates force view [message #641834 is a reply to message #641833] Tue, 25 August 2015 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

No.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Report message to a moderator

Re: Oracle noforce view creates force view [message #641836 is a reply to message #641833] Tue, 25 August 2015 09:59 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
Do you mean like this?
orclz> create view v1 as select * from dept;

View created.

orclz> select dbms_metadata.get_ddl('VIEW','V1','SCOTT') from dual;

DBMS_METADATA.GET_DDL('VIEW','V1','SCOTT')
-------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "SCOTT"."V1" ("DEPTNO", "DNAME", "LO

I would think it is pretty much essential, because when using export/import, you cannot be sure that all necessary objects will be available at the time of creating the view.

Report message to a moderator

Re: Oracle noforce view creates force view [message #641843 is a reply to message #641836] Tue, 25 August 2015 10:22 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
So is force actually in the data dictionary or is dbms_metadata tacking it on?

Report message to a moderator

Re: Oracle noforce view creates force view [message #641847 is a reply to message #641843] Tue, 25 August 2015 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 

SQL> create view v1 as select * from dept;

View created.

SQL> select text from user_views where view_name='V1';
TEXT
--------------------------------------------------------
select "DEPTNO","DNAME","LOC" from dept

It is DBMS_METADATA.

Report message to a moderator

Re: Oracle noforce view creates force view [message #641848 is a reply to message #641843] Tue, 25 August 2015 11:05 Go to previous messageGo to next message
ram50958
Messages: 7
Registered: September 2010
Junior Member
@John : When that is pretty much essential then what is the use of force and noforce options? I do not have configuration otpion.
@cookiemonster : I have opened the view ddl definition and it shows there.


We are using a shell script to read this view which fails saying view has errors. But that view do not have any errors and works fine. When I googled, I read that force views may not work well while reading from shell scripts.

Report message to a moderator

Re: Oracle noforce view creates force view [message #641849 is a reply to message #641847] Tue, 25 August 2015 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Also:
SQL> select object_id from user_objects where object_name='V1';
 OBJECT_ID
----------
    128336

1 row selected.

SQL> select * from sys.view$ where obj#=128336;
      OBJ# AUDIT$                                       COLS    INTCOLS   PROPERTY      FLAGS TEXTLENGTH
---------- -------------------------------------- ---------- ---------- ---------- ---------- ----------
TEXT
--------------------------------------------------------------------------------------------------------
    128336 --------------------------------------          3          3          0          0         39
select "DEPTNO","DNAME","LOC" from dept

1 row selected.

SQL> drop view v1;

View dropped.

SQL> create force view v1 as select * from dept;

View created.

SQL> select object_id from user_objects where object_name='V1';
 OBJECT_ID
----------
    128336

1 row selected.

SQL> select * from sys.view$ where obj#=128336;
      OBJ# AUDIT$                                       COLS    INTCOLS   PROPERTY      FLAGS TEXTLENGTH
---------- -------------------------------------- ---------- ---------- ---------- ---------- ----------
TEXT
---------------------------------------------------------------------------------------------------------
    128336 --------------------------------------          3          3          0          0         39
select "DEPTNO","DNAME","LOC" from dept

No difference.

Report message to a moderator

Re: Oracle noforce view creates force view [message #641850 is a reply to message #641848] Tue, 25 August 2015 11:11 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
ram50958 wrote on Tue, 25 August 2015 17:05
@John : When that is pretty much essential then what is the use of force and noforce options? I do not have configuration otpion.
@cookiemonster : I have opened the view ddl definition and it shows there.


We are using a shell script to read this view which fails saying view has errors. But that view do not have any errors and works fine. When I googled, I read that force views may not work well while reading from shell scripts.

Come on, man: how am I supposed to know what you are doing when you do not show the shell script or the errors or the view definition or the length of your... hair?

Report message to a moderator

Re: Oracle noforce view creates force view [message #641851 is a reply to message #641848] Tue, 25 August 2015 11:13 Go to previous message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ram50958 wrote on Tue, 25 August 2015 18:05
@John : When that is pretty much essential then what is the use of force and noforce options?


This has already been said just a look at the documentation will tell you:

Quote:
Specify FORCE if you want to create the view regardless of whether the base tables of the view or the referenced object types exist or the owner of the schema containing the view has privileges on them. These conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view.


Quote:
I do not have configuration otpion.


Which is completely meaningless for us.

Quote:
I have opened the view ddl definition and it shows there.


And if you'd done the simple tests I did you'd have the answer.

Quote:
We are using a shell script to read this view which fails saying view has errors.


Which has nothing to do with the FORCE option.

Quote:
But that view do not have any errors and works fine.


Oracle does not agree with you.

Quote:
I read that force views may not work well while reading from shell scripts.


Where? Reference? Stated like this it is just pointless.

Report message to a moderator

Previous Topic: Response time
Next Topic: Huge difference between CPU Time and ELAPSED TIME and GV$SQL and high times on USER_IO_WAIT_TIME
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 30 13:22:28 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.