ORA-01731 Circular View Definition Encountered

Feb 22, 2013

I created a view for emp1 table.

SQL> select   view_name , text   from  dba_views  where VIEW_NAME='EMP1_VIEW';          

VIEW_NAME      TEXT

EMP1_VEW       select  empid , ename , qual,  dept  from  emp1
where  empid  between  1000 and  1010

Now  i tried to  use create or replace option

SQL>create or replace  view emp1_view as
*2 select * from emp1_view with read only;*
create or replace  view emp1_view as
*+
ERROR at line 1:
ORA-01731: circular view definition encountered

Actually i want to make read only  view (emp1_view) ;  without  doping  ;

View 16 Replies


ADVERTISEMENT

SQL & PL/SQL :: Change View Definition When Select Is Running On That View

Apr 25, 2012

We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.

This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?

I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....

View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.

View 6 Replies View Related

SQL & PL/SQL :: View Definition With Schema Name?

Nov 28, 2012

I have a view in schema 'sales', let say the view name is view_test.

This view sales.view_test uses two base tables, one from schema 'products' and other from its own schema 'sales'.

So in sales schema the view defination is,

create or replace view view_test
as
select * from t_test, products.t_products
where t_test.id=t_products.id

The view got created, I have granted select permisson with grant option on tables t_test, products.t_products and view_test to another user 'master'.But when i query the view from master schema I get an error saying that the view does not exists, upon analysis, I found one of the base table in the view defination has no schema name mentioned (select * from t_test, products.t_products --here only t_test mentioned rather sales.t_test), I feel because the table t_test belongs to the same schmea of the view, they did not mention the schema name. If not, does it mean that we need to always specify the schema names in the view defination for the base tables.

View 6 Replies View Related

Imbedded DB LINKS In View Definition

Sep 7, 2012

How can I determine what views have a dblink hard coded in them? I am talking about a large number of views so bringing up each view's DDL in a GUI data dictionary tool to look is not an option. I tried running the query below unfortunately "text" is a LONG and doesn't allow the use of the "like" statement.

select * from dba_views where text like '%@%' ;

View 4 Replies View Related

SQL & PL/SQL :: How To Retrieve A View Definition To Recreate

May 20, 2013

How to retrieve a view definition to re-create it somewhere when its size is more than 32767 bytes?Here are some constraints :

- We are in Oracle 9.2.0.8, meaning that we cannot use the DBMS_METADATA package.
- We cannot concatenate char and long : things like
set long 100000

select 'create or replace view my_view as ' || text from dba_views where view_name = 'MY_VIEW';
are not possible.

View 31 Replies View Related

PL/SQL :: Select On View Takes Long On Definition Short - No Difference In Exec-plans?

Aug 20, 2012

[URL]...

I have a quite complex view that selects from approx 10 long tables (approx 4M records each) and build one "customer sentence" pre customer id. I will be always getting just one row from this view, eg. select * from my_view where party_id = XYZ. I'll NEVER EVER select the whole view.

The problem is that running a query:
select * from my_view where party_id = XYZ takes really long time, while putting the party_id = XYZ condition directly into the view executes in 0.0 seconds.

After putting a ORDERED FIRST_ROWS(1) hint into a view the execution plans seems to be the same (or very similar) for both queries. Unfortunately, I can not transfer anything but screenshot from the environmnet - therefore I paste the exec plans as screenshots only - pls follow the link: [URL]...

View DDL:
create or replace view my_view as
select /*+ ORDERED FIRST_ROWS(1) */  pt.party_id
           pt.party_id as id_klienta_mdm,
           pt.master_reference_no as id_klienta_ref_mdm,
        
[code]...

View 6 Replies View Related

Circular Synonym Error While Importing Dump?

Mar 5, 2011

I have a db instance in which several schemas are there. I have taken a dump of a particular schema(user) using oracle exp command

exp <username>/<passwd>@<dbname> FULL=y FILE=export.dmp LOG=exportdb.log CONSISTENT=y

Now I have another db where I wish to import the above dump. This is not a empty db, however, I have dropped the particular user from this db for which I have created a dump above. Then I have created the same user using 'create user....'.And now I am trying to import the above dump into this.

imp '<username>/<passwd>@<dbname>' FILE=C:\DUMP\Dump\export.dmp LOG=C:\DUMP\logs\imp_dmp.log FULL=Y

import goes fine but I gets the circular synonym error for some of the types.

IMP-00003: ORACLE error 4055 encountered
ORA-04055: Aborted: "O_BULK_TARGET_SELECTOR" formed a non-REF mutually-dependent cycle with "T_GENERAL_IDLIST".
ORA-06550: line 5, column 25:
PLS-00421: circular synonym 'PUBLIC.T_GENERAL_IDLIST'

The db from which I created the dmp has no errors for these objects, but while impoting it gives these errors.I have even tried taking the whole database dmp(not a particular user), and importing it into full empty database. Then also I gets the same error.

View 3 Replies View Related

SQL & PL/SQL :: Determine If / When Hierarchical Circular Reference Will Occur?

May 6, 2011

I'm trying to determine if/when a possible Hierarchical circular reference will occur in my data

Sample Hierarchical structure that I have

Emp -> Supv
A
BA
CB
DC
EC

[Code]....

Finally, to my question. It seems that I can detect the problem After it happens but do I need a trigger on the update statement to detect if/when a possible circular reference will occur?? or can I run a sql statement prior to update to detect possible circular reference?

View 5 Replies View Related

ORA-07445 - Exception Encountered?

May 9, 2011

Am getting the error ORA-03113: end-of-file on communication channel when am trying to run a query and when i checked the trace file the infromation i got is given below.....

CODEksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [0x363376E] [] [] [] []
Current SQL statement for this session:
select
count(av_sal)

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Encountered The Symbol End-of-file?

May 27, 2011

i am getting error for this code

CREATE OR REPLACE FUNCTION "ABS"."PROCESS_YEAR"(processYear DATE) RETURN NUMBER DETERMINISTIC IS
year NUMBER:=NULL

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_

View 3 Replies View Related

SQL & PL/SQL :: PLS-00103 - Encountered Symbol NUMBER

Jun 7, 2011

I am trying to schedule a DBMA_JOB and getting the below mentioned error.

JOB:
====

declare
VARIABLE v_jobnum NUMBER;
begin
DBMS_JOB.SUBMIT(job => v_jobnum,
what => 'dbms_refresh.refresh(""CLARITY"."M_MV_INBASKET_ANALYSIS"");',
next_date => to_date("05-07-2011 13:45","DD-MM-YYYY HH24:MI"),
interval => '(ADD_MONTHS(TRUNC(SYSDATE,"MM")+4,1)+13.75/24)');
end;
[code]....

View 20 Replies View Related

PLS-00103 / Encountered The Symbol (end-of-file)

May 18, 2010

when creating this,

create or replace package body test as

employees emp_tab := emp_tab();
procedure test1 as
g_ref sys_REFCURSOR;
BEGIN
employees.EXTEND(2);
employees(1) := emp_type (1, 'name1');
employees(2) := emp_type (2, 'name2');
OPEN g_ref FOR
SELECT * FROM TABLE (CAST (employees AS emp_tab));
END test1;

I got a error ERROR at line 11: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin end function package pragma procedure form

1. create or replace package body test as
2. employees emp_tab := emp_tab();
3. procedure test1 as

View 1 Replies View Related

Display All Tables In DB (with Their Definition)?

Sep 19, 2007

I've tried using Select * from ALL_TABLES but this does not return me the column info for each table.

How do I get an output of all tables in a database along with column (e.g datatypes etc)?

I'm using TOAD v8.6.1.

View 3 Replies View Related

PLS-00103 / Encountered Symbol NUMBER When Expecting One Of Following

Jun 7, 2011

I am getting the below error while trying to schedule a simple dbms_job.

JOB:
=====
declare
VARIABLE v_jobnum NUMBER;
begin
DBMS_JOB.SUBMIT(job => v_jobnum,
what => 'dbms_refresh.refresh(""SCHEMA"."MyMaterializedView"");',
next_date => to_date("05-07-2011 13:45","DD-MM-YYYY HH24:MI"),
interval => '(ADD_MONTHS(TRUNC(SYSDATE,"MM")+4,1)+13.75/24)');
end;
/

ERROR:
======
LINE TEXT
2 ORA-06550: line 2, column 19:
1 PLS-00103: Encountered the symbol "NUMBER" when expecting one of the following:
1 := . ( @ % ; not null range default character
1 The symbol ":=" was substituted for "NUMBER" to continue.

View 2 Replies View Related

ORA-1403 Encountered When Generating Server Alert?

Jun 7, 2012

how to resolve the below error.. I am getting this error on the alert log .. oracle DB version is 11.2.0.1 and os is AIX6.1

ORA-1403 encountered when generating server alert SMG-4121 Thu Jun 07 13:38:27 2012
ORA-1403 encountered when generating server alert SMG-4121 Thu Jun 07 13:39:00 2012
ORA-1403 encountered when generating server alert SMG-3503 Thu Jun 07 13:43:27 2012
ORA-1403 encountered when generating server alert SMG-4121 Thu Jun 07 13:48:27 2012

View 4 Replies View Related

SQL & PL/SQL :: Handling Circular Data In Oracle / Get Disjoint Sets Of Data Connected By 2 Values

Sep 29, 2011

I have this table :

column1 column2
--------- ---------
value1 value2
value1 value3
value2 value4
value3 value7
value7 value1
value8 value9

What I was trying to retrieve is something like that:

Quote:
output_column
---------------
value1, value2, value3, value4, value7
value8, value9

I don´t care about the order of the values in the row. In other words, I want to get disjoint sets of data connected by any of both values.Every pair in the input table is unique.

I have seen in the web that it is possible to do using connect by and hierarchical retrieving but I've been trying to make a lot of combinationts and I can reproduce the output.

View 2 Replies View Related

Reports & Discoverer :: REP-1241 Circular Column Dependency Originating With Column?

Jul 13, 2010

I receive the error,

REP-1241 Circular column dependency originating with column 'cenvat_opening'

I have the following return statement for a column total_credit;

function total_creditFormula return Number is
begin
Return (return nvl(:cf_cenvat_closing ,0) - nvl(:cf_duty_on_goods_ed,0);
end;

Now i am using this total_creditFormula for the error formula column 'cenvat_opening'

function cenvat_openingFormula return Number is
f_num_opn number(14,2);
begin
SELECT NVL(Amount,0) INTO F_NUM_OPN

[code]...

here the formula column 'total_credit' has the following return statements;

RETURN Nvl(:cenvat_opening,0) + Nvl(:cenvat_credit_manu,0)

so how can i use this formula column? is there any other option to return the value in the same column.

View 6 Replies View Related

Trace File - MVs Failed (ORA-10980 Error Encountered)

Oct 19, 2010

In Trace file I am always geeting one error "Error encountered: ORA-10980" due to which MVs fails . how to overcome from this error?

View 2 Replies View Related

Server Utilities :: EXP-00008 / ORACLE Error 933 Encountered

Feb 27, 2012

every day we have full backup of oracle database (9.2.0.1.0) on windows 2003 server. since couple of days i am getting following below error. i checked the solutions on google which talk about user quotas , i checked the user quotas by which i am taking full backup it is unlimited , even i tried another user which also have umlimited quota on its default tablespace still i getting below

E:ackup>exp system/manager file=full26022012.dmp log=full26022012.log full=y statistics=none
EXP-00008: ORACLE error 933 encountered
ORA-00933: SQL command not properly ended
ORA-06512: at "SYS.DBMS_RULE_EXP_RL_INTERNAL", line 311
ORA-06512: at "SYS.DBMS_RULE_EXP_RULES", line 142
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_RULE_EXP_RULES.schema_info_exp

even i go for full export by any other user which has full export privilege & unlimited quotas. i getting the same error.

E:ackup>exp username/password file=full26022012.dmp log=full26022012.log full=y statistics=none
EXP-00008: ORACLE error 933 encountered
ORA-00933: SQL command not properly ended
ORA-06512: at "SYS.DBMS_RULE_EXP_RL_INTERNAL", line 311
ORA-06512: at "SYS.DBMS_RULE_EXP_RULES", line 142
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_RULE_EXP_RULES.schema_info_exp

View 4 Replies View Related

SQL & PL/SQL :: KUP-00554 / Error Encountered While Parsing Access Parameters

Jun 25, 2012

I am trying to create an EXT table but is constantly having the following problem, not sure why. I have done a few checks and used the scripts used as a standard but still is experiencing an error.

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "minussign": expecting one of: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 9 column 1

The Table creation script is

CREATE TABLE TABLE1_EXT
(
BUSINESS_DATE VARCHAR2(50 BYTE),
CIN_CODE VARCHAR2(50 BYTE),
CIS_CODE VARCHAR2(50 BYTE)
)

[code]....

And the data used as a sample is

BusinessDate|CIN|CISCode
19062012|1000026697|CTTGLIE
19062012|1000079416|Z29MBGB

View 12 Replies View Related

Performance Tuning :: Definition Between 2 Metrics Physical Reads

Nov 21, 2011

On 10gR2, I cannot find the exact definition of the 2 following metrics :

"Physical Read Total Bytes Per Sec"
and
"Physical Read Bytes Per Sec"

I would want to know what is the difference between them.

View 3 Replies View Related

Application Express :: Page Validation Against Table Definition?

Apr 29, 2013

I am trying to apply page validations on a tabular form. The form allows users to update data in a database table. I have created some validations such as "column x must not be null" etc and on submit, the error message appears and the relevant cells are highlighted in red. All ok so far.

However, for the primary key, I am relying on the table definitions in the Oracle database to not allow duplicate row entries. When a user tries to enter a duplicate row the error message appears but the relevant row / cells are not highlighted, just the row number is given. In a table with many rows this is a bit annoying.

Is there anyway to get the cells to highlight in red for such circumstances or do I need to create the primary keys within APEX itself?

APEX 4.2.1.00.08

View 0 Replies View Related

Networking And Gateways :: ORACLE Error 12154 Encountered - Could Not Resolve Service Name

May 13, 2013

I have overwritten tnsnames.ora file with newone. In new tnsnames.ora file I have added new host string, remaining all unchanged.

after that, when trying to connect using sqlplus(from windows machine, sqlplusw.exe), working fine. But when trying to connect through putty getting error.

ORA-12154: TNS:could not resolve service name

for the same reason my scheduled cron tab can not run export backup. cron tab error is

EXP-00056: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve service name
EXP-00000: Export terminated unsuccessfully

I am suspecting problem may be due to file accessing permission related issue. But cant solve it. Current tnsnames.ora files permission is

# ls -l admin
total 112
-rw-r--r-- 1 oracle oinstall 14661 Apr 26 2002 libnk59.exp
-rw-r--r-- 1 oracle oinstall 643 Apr 26 2002 libnk59.imp
-rw-r--r-- 1 oracle oinstall 5728 Apr 26 2002 libnrad9.exp

[code]....

View 5 Replies View Related

Server Utilities :: KUP-00554 / Error Encountered While Parsing Access Parameters

Dec 2, 2010

I receive the following error message

ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "binary_double,

[code].....

when I select count(*) on the external table created below.

SQL> CREATE TABLE cac_500_load
2 (
3 EMAILADDRESS VARCHAR2(80),
4 FIRSTNAME VARCHAR2(60),
5 LASTNAME VARCHAR2(60),
6 STREETADDRESS VARCHAR2(100),

[code].....

Here is the db version info:

SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT

View 12 Replies View Related

Server Utilities :: External Table Definition And Content Of CSV File

May 26, 2010

Below is the external table definition and the content of csv file, why the first record fails ?

0546-0*LB-CRP*16*"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 --FAILED
0546-0*LB-CRP*16*ID"Tech", ZAO*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS
0546-0*LB-CRP*16*"Tech, ZAO"*29-DEC-2009*29-DEC-2010***A051453*RU*29-DEC-2009*0***21-MAY-2010*21-MAY-2010 -- SUCCESS
[code]....

View 8 Replies View Related

Server Utilities :: ORA-01466 - Unable To Read Data - Table Definition Has Changed

Apr 2, 2013

My job is running at 2 am and that time no application user is connected. Even though, my exp utility shows error on 3 tables (2 are temp tables), everyday. But when expdp is running without error, which was scheduled at 4 am.

Below are the error -

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

. . exporting table DW_TEST_MOTOR
EXP-00056: ORACLE error 1466 encountered
ORA-01466: unable to read data - table definition has changed

[Code].....

View 9 Replies View Related

Application Express :: 4.2.1 Dynamic Action Definition True Action Settings Not Visible

May 16, 2013

im working with apex 4.2.1 and when i try to define a Dynamic action on any item, if the action type is "Set Value" or execute SQL/Javascript code it should appear a text field that let me define the action taking place. However this wont do..

this is what happens:

And this is what should happen:

I know that patch 4.2.2 solves some issues regarding dynamic actions but in the bug report there's no reference to such a problem.

View 1 Replies View Related

Server Utilities :: User Creation Definition In User Mode Export

Mar 31, 2010

I would like to know if 'user creation definition' is exported in user mode export if export is done with DBA role..If it is Not, does it mean we always need to precreate the user before we import the dump created using 'user mode export'?

View 2 Replies View Related

Backup & Recovery :: Adding DB ID To RMAN Backup Definition

Oct 13, 2011

is there any way I can add the DBID to my rman backup name? I've already tried %F/%f as seen in the man page. but this option remains ignored.

It works for the rman backup of the control file. but no change to get this running proper for the datafiles.

View 4 Replies View Related

Server Administration :: How To Change Existing Materialized View To Normal View

Jan 17, 2013

can we change the existing materialized view to normal view? if yes how?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved