SQL & PL/SQL :: Find Out Base Table Of Synonym?

Jun 10, 2011

How to find out the base table of a synonym?

View 3 Replies


ADVERTISEMENT

Security :: Unable To See Data In Base Table Where Can Be Viewed In Synonym?

Sep 9, 2011

I have one schema argus_app where there is a table cfg_enterprise. There is a view v$cfg_enterprisewhich selects data from cfg_enterprise and there is apublic synonym cfg_enterprise for v$cfg_enterprise.

When I login to argus_app and select from cfg_enterprise there is no data where as i log in to sys and select I can see data.

SQL> conn /as sysdba
Connected.
SQL> select enterprise_name from cfg_enterprise;

ENTERPRISE_NAME

[code]...

what would be creating this problem.

View 5 Replies View Related

Server Administration :: How To Find The Base Table Of A View

Aug 15, 2011

How to find the base table of a view,such as a view:

create or replace view vw_test as select * from tb_test;

Is there dic view to get the base table of view?

View 3 Replies View Related

SQL & PL/SQL :: Script To Find Out Objects Synonym And Privs

Jun 17, 2011

There are over 100 objects like tables,procedures,packages,triggers in my database.I am looking for a script/sql which should list out synonyms and grant privs on it.

View 1 Replies View Related

PL/SQL :: Join Between Table With User Based Content And Base Table

Jul 30, 2012

I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).

i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).

Oracle version information
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE     11.2.0.2.0     Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]...

I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".

I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.

View 10 Replies View Related

SQL & PL/SQL :: XML Parsing Into Base Table

May 20, 2010

I want to load XML into base table using PL/SQL procedure.For that I have wrote procedure but that does not work well .

View 4 Replies View Related

SQL & PL/SQL :: Unable To Disable Trigger On Synonym Table

Apr 19, 2012

I have schema a and b in the same database with synonym table a (schema a) to schema b. When I try to disable trigger from table a in schema b; I got this message:

ORA-00942: table or view does not exist

I have granted all permissions to schema b and I am able to SELECT FROM table a in schema b

View 11 Replies View Related

SQL & PL/SQL :: Create View Without Base Table?

Jun 25, 2012

How to create a view without base table . some example?

View 8 Replies View Related

Forms :: Changing A Block Not Base Table

Oct 15, 2012

How do I change the status of a block to change?

View 7 Replies View Related

SQL & PL/SQL :: DBMS Scheduler Doesn't Support Synonym Table (DBLINK Used)

May 12, 2011

we are facing an issue with the dbms scheduler jobs, which is not processing the synonym which is created via dblink from anthoer schema.

Let me explain the situation.

Table in Schema :APP_COMMON DB: APPL
-------------------------------------

CREATE TABLE TEST_LOG (A VARCHAR2(10));

INSERT TEST_LOG VALUES ('TESTED');

GRANT SELECT ON TEST_LOG to APP_GEN ;

Table in Schema :APP_GEN DB: APPL
-------------------------------------

CREATE SYNONYM TEST_LOG FOR APP_COMMON.TEST_LOG;

SELECT * from TEST_LOG;

-- it returns the value

Table in Schema :APP_GEN DB: REPORT
-------------------------------------

This is the different DB (we have 2 DB's, one for report db and one for application DB). here we create the DB link (connected DB menthod) . Since we have the password sync between the databases, we create the DBLINK without user id and password.

CREATE DATABASE LINK "APPL_LINK"
USING 'APPL' ;

CREATE SYNONYM TEST_LOG FOR TEST_LOG@APPL_LINK;

SELECT * from TEST_LOG;
--it returns the value.

Now in the same DB, we have a scheduler which will run for every min.

Now Scheduler is not selecting this table. Rather not processing the synonym(TEST_LOG). Not able to capture the exception also.

View 9 Replies View Related

PL/SQL :: Update A Base Table Using Updatable Materialized View?

Sep 12, 2013

I am using sqlplus. example to update a base table using a updatable materialized view.

View 17 Replies View Related

SQL & PL/SQL :: Trigger Fired - Final Update To Base Table Not Happening

Jul 1, 2011

In my DB,We have a table rqst_list

Create table rqst_list(id Number, --(PK)
pkg_name varchar2(100),
status varchar2(100))

When a record is inserted into this table(rqst_list), a trigger is fired which calls the package which is there in pkg_name.The function does some function.If there are no exceptions while executing the package, then SUCCESS needs to be updated in the table for the id for which the trigger got fired.I have used AFTER INSERT ROW level trigger.

But,the final UPDATE (UPDATE the rqst_list table to SUCCES/FAILURE)to the base table inside the package is not happening.

View 5 Replies View Related

Application Express :: Select Some Data From Table Base On SYSDATE?

Sep 6, 2012

I'm trying to select some data from table base on SYSDATE. The below query does not return any data.

My query is:

select count(TICKET_ID) "ECEMEA" from QTMT_TICKETS where STATUS_ID=1 and TEAM_ID=3 and RECEIVED_DATE=sysdate

why this does not work? Date format in my application is: DD-MMM-YY (16-AUG-12). In the database the dates are stored in this format: MM/DD/YYYY (08/25/2012). Does it mean, that I have to play with the format?

View 6 Replies View Related

How To Use Synonym In Stored Procedure

Aug 28, 2009

how to use a synonym in a stored procedure.

I have created a public synonym for a remote table on a different schema.

Im now trying to use the synonym to load that data into a temporary table in my schema using a stored procedure and im getting an error.

how to use a synonym in a stored procedure.

View 5 Replies View Related

Private Synonym Inside A Package?

Apr 25, 2012

I'm making a function A that does many calls to procedures in an other package B. To make this function more readable, I'd like to specify synonyms for the procedures in B. I only need the synonyms inside this function, I don't want to make database synonyms.

For example:

Function get_all_employees return clob
is
v_emp clob;
begin

[Code]....

View 1 Replies View Related

Unable To Create Public Synonym

May 22, 2011

I have exported and imported a schema from one server to another. In the source schema, I have a public synonym. I do not know the name of that synonym. In the destination schema, the public synonym is missing. How to create the public synonym which is missing in the destination database? In the source, I queried dba_synonyms, all_synonyms... but it returns no rows selected.

View 2 Replies View Related

How To Collect Synonym Information And Compare

Oct 29, 2013

We are making Oracle Insight Application based on Oracle 11gR2 DB.We have two DEV environments. On Oct 17, we had trouble in one DEV environment.From dba_common_audit_trail , we found some user did drop public synonym statement and we doubt it as cause of the problem. I would like to collect synonym information of the two DEV environment.1How could I do that ?2And we are thinking of using Logminer to investigate the cause of problem.Logminer is usable to investigate the cause of problem if we have the REDO log of Oct 17 ?

View 5 Replies View Related

PL/SQL :: Creating View And Public Synonym With The Same Name?

Feb 15, 2013

I am able to create view and the public synonym with the same name but when I am quering view result is showing .

Is there any logic behind this .

CREATE OR REPLACE VIEW emp_dtls as SELECT * FROM emp;
CREATE PUBLIC synonym emp_dtls for emp;
SELECT * FROM emp_dtls;

view result is showing

View 3 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 :: How Make Complete Schema Public Synonym

Apr 15, 2013

how to make the complete schema public synonym, instead of creating public synonym object by object.

View 2 Replies View Related

PL/SQL :: Create A Trigger To Check If Synonym Already Exists In DB

Mar 29, 2013

How can I create a trigger to check if synonym already exists in db and if exists then don't create synonym.

my work: ( this is just like an outline i prepared)

select * from all_synonyms;
declare
s_exists number;
begin
-- check whether the synonym exists
select 1 into s_exists from all_synonyms;
-- an error gets raise if it doesn't
exception when no_data_found then
-- DDL has to be done inside
execute immediate ' create or replace synonym';
end;
/

View 16 Replies View Related

PL/SQL :: Reverse Update - Create Synonym In Different Schema

Aug 8, 2012

I have table a in database 1 . Now i have created the MV for table a in database 2.

then i am going to create a synonym in different schema in the database 2.

Now i want to update the synonym in database 2 the same as to refresh in table a database 1.

How to achieve this??

View 2 Replies View Related

SQL & PL/SQL :: FIFO Base Query

Sep 30, 2010

select code,inv_date,inv_co_code,inv_co_name,inv_fnd_code,inv_fnd_name,inv_amount,inv_nofu
from retreport order by inv_co_code, inv_fnd_Code,inv_date

STAT INV_DATE CODE NV_CO_CODE INV_CO_NAME INV_FND_CODE INV_FND_NAME INV_AMOUNT INV_NOFU
----- ------ -------- ----------- ------------ -------------- ---------------------- ----------------------
I 28/06/2010 117 13 CAD 1 MCB 15104708.75 148375.7308
I 01/07/2010 13 CAD 1 MCB 2804.7464
I 30/08/2010 117 13 CAD 1 MCB 700000.00 6882.2380
I 30/08/2010 117 13 CAD 1 MCB 9500000.00 93401.8018
R 31/08/2010 117 13 CAD 1 MCB 39315.8646
R 08/09/2010 117 13 CAD 1 MCB 24515.9089

6 rows selected

STAT I = IN STOCK
STAT R = OUT STOCK

I need FIFO Query First in Fist out (INV_NOFU)

STAT CODE INV_CO_CODE INV_CO_NAME INV_FND_CODE INV_FND_NAME INV_AMOUNT INV_NOFU BALACNE
----- ------ ----------- ----------- ------------ -------------- ---------------------- ---------------------- ------------
I 117 13 CAD 1 MCB 15104708.75 148375.7308 148375.7308
R 117 13 CAD 1 MCB 39315.8646 109059.8662
R 117 13 CAD 1 MCB 24515.9089 84543.8773
I 13 CAD 1 MCB 2804.7464 87348.6237
I 117 13 CAD 1 MCB 700000.00 6882.2380 94230.8617
I 117 13 CAD 1 MCB 9500000.00 93401.8018 187632.6635

View 2 Replies View Related

Server Administration :: Remove Synonym STATS$SNAPSHOT-ID?

Sep 11, 2007

I've got to collect statistics by DBMS_STAT. For the first time, the spcreate.sql is to be called, however, it returned one error following:

If this script is automatically called from spcreate (which is the supported method), all STATSPACK segments will be created in the PERFSTAT user's default tablespace.

Using perfstat tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence Sequence created.

create public synonym STATS$SNAPSHOT_ID for STATS$SNAPSHOT_ID
*
ERROR at line 1:
ORA-00955: name is already used by an existing object Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

And then, I checked it again with its owner:

SQL> select owner, synonym_name
2 from dba_synonyms
3 where synonym_name='STATS$SNAPSHOT_ID';

OWNER SYNONYM_NAME
------------------------------ ------------------------------
PUBLIC STATS$SNAPSHOT_ID

Then, I checked again after dropping schema PERFSTAT:

SQL> conn /as sysdba
Connected.
SQL>
SQL> select username, account_status

[code]...

What's the STATS@SNAPSHOT_ID synonym? Can I remove it from PUBLIC owner and recreate?

View 13 Replies View Related

SQL & PL/SQL :: Is Select Privileges (grants) Needed For Created Synonym

Jun 4, 2012

After created synonym, Is select privileges(grants) needed for created synonym? SQL> create or replace synonym gm_holding for gmblbpna. gm_ holding;

Synonym created.

SQL> select *
2 from gm_holding;
from gm_holding
*
ERROR at line 2: ORA-00942: table or view does not exist

SQL> connect gmblbpna/gmblbpna@o03gpa0
Connected.
SQL> grant select on gm_holding to solvency;

Grant succeeded.

SQL> connect solvency/solvency@o03gpa0
Connected.
SQL> select count(1)
2 from gm_holding;
COUNT(1)
----------
346742
SQL>

why grant need for synonym?

View 4 Replies View Related

Server Utilities :: Unable To Create Public Synonym?

May 22, 2011

I have exported and imported a schema from one server to another. In the source schema, I have a public synonym. I do not know the name of that synonym. In the destination schema, the public synonym is missing. How to create the public synonym which is missing in the destination database?In the source, I queried dba_synonyms, all_synonyms... but it returns no rows selected.

View 3 Replies View Related

SQL & PL/SQL :: Error When Create Synonym / ORA-01031 / Insufficient Privileges

Mar 13, 2013

from sysdba i grant to my user hospital creat any synonym but give me this error why ?

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> grant create any synonym to hospital
2 ;

Grant succeeded.

SQL> conn hospital
Enter password:
Connected.
SQL> create public synonym Sur
2 for surgeries;
create public synonym Sur
*
ERROR at line 1:
ORA-01031: insufficient privileges

View 9 Replies View Related

Upgrade RAC From Base Version To Patchset 3

Oct 4, 2010

I have one query regarding ORACLE 10g RAC.

I have installed 10gRAC [10.2.0.1] on VMware.

now I have requirement to upgarde it this existing RAC setup to 10.2.0.4.

options for me the steps or sequence I need to follow in upgrade path? like first needs to upgarde crs,then fro ASM and lastly for DB something like this.

View 5 Replies View Related

SQL & PL/SQL :: Alphanumeric Sorting Column Base

Jun 18, 2010

I am trying to sort columns base on cluster and alpha numeric field.

Column1 Columns2 column3
1 2-CA6R-234 9
1 2-CA6R-231 8
1 ARCT-0037000000ewegZ 10
2 2-QIZFF7 1
3 2-PIZFF6 6
3 ARCT-0037000000ewipk 9
3 2-QIZTF7 1

Wanted to sort in a way that column1 will be same order and the second column will order first with ARCT-XXXXX and then reset of the column2. It should look like this

Column1 Columns2 column3
1 ARCT-0037000000ewegZ 10
1 2-CA6R-231 8
1 2-CA6R-234 9
2 2-QIZFF7 1
3 ARCT-0037000000ewipk 9
3 2-PIZFF6 6
3 2-QIZTF7 1

View 9 Replies View Related

Forms :: Editing Base.htm File?

May 4, 2010

can I add a session (php code) to base.htm file?

if yes, what i have to do?

View 1 Replies View Related







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