SQL & PL/SQL :: How Make Complete Schema Public Synonym
Apr 15, 2013how to make the complete schema public synonym, instead of creating public synonym object by object.
View 2 Replies
ADVERTISEMENT
Unable To Create Public Synonym
May 22, 2011I 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 RelatedPL/SQL :: Creating View And Public Synonym With The Same Name?
Feb 15, 2013I 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
Server Utilities :: Unable To Create Public Synonym?
May 22, 2011I 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 RelatedPL/SQL :: Reverse Update - Create Synonym In Different Schema
Aug 8, 2012I 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??
Application Express :: How To Make Use Of SSO Login Credentials Of Oracle In Authentication Schema
Jul 16, 2013I am using SSO login for Authorizations. Would like make use of SSO for creating authentication schema. My main requirement is to assign privilege based on users using SSO loging.
View 0 Replies View RelatedServer Utilities :: Import A Schema From One Database Schema To Another Schema B?
Aug 10, 2010I want to import a schema from one database schema to another schema b from db STBTST to STATST and from schema CMSSTAGINGB to CMSSTAGINGA
I first want to test this to my own schema (mvanmannekes) CMSSTAGINGA is filled at the moment.
So i've created a dump from STBTST-CMSTAGINGB For importing im using this statement:
impdp mvanmannekes/password schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
remap_tablespace=cmsliveb_index:cmslivea_index
remap_schema=cmsstagingb:mvanmannekes directory=expdp_dir dumpfile=cmstagingb.dmp
I'm getting this:
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
Master table "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "MVANMANNEKES"."SYS_IMPORT_SCHEMA_01": mvanmannekes/********
schemas=cmsstagingb remap_tablespace=cmsliveb_data:cmslivea_data
[code]....
Security :: Encrypt File With GPG Using PGP Public Key?
Oct 21, 2010I need to encrypt a file with GPG using a pgp public key I never done this, what are the commands..
View -1 Replies View RelatedApplication Express :: Deletion Of Public Reports In 4.0?
Sep 4, 2012As far as I know, in APEX 4.0 no other user other than the one who created a Public report can delete it. Can this behavior be changed somehow?
View 3 Replies View RelatedSecurity :: Grant Privileges To Public User
Jul 9, 2011grant insert,update,delete,select on staging_tb1 to public;
What is public here? i know something about public user like it is users exist in the database .
8i - Read Data From Teradata Using Public Link And Views?
Aug 12, 2011I am using oracle 8i and need to read data from teradata using public link and views.
View 4 Replies View RelatedRAC & Failsafe :: Differences Of Enabling Thread As Public Or Private?
Aug 25, 2012differences of enabling thread as public or private?
lets assume that i have two threads (1, 2) for two instances (1, 2) and each of thread consists of 2 redolog groups - minimum configuration.in case i need add third node i need third thread.
what will be difference if i set this thread as public instead private?
RAC & Failsafe :: Change Cluster Interconnection From Public To Private?
Jul 3, 2013how to change the cluster interconnection from public to private.
View 6 Replies View RelatedImportance Of Public And Virtual IPs In Etc / Hosts File When Dealing With Oracle RAC
Mar 12, 2011When working against the Oracle 10G/11G RAC database it's important to have all the relevant entries in the etc/hosts file.
Thus, when running the following code against the database sometimes it finishes with "Ok!" and sometimes getting the error below:
CODEimport java.sql.*;
public class TestDBOracle {
public static void main(String[] args)
[Code].....
The reason is missing RAC-related entries in the etc/hosts.
When dealing with Oracle RAC it should be both entries for the public and for the virtual IPs of the nodes in the etc/hosts file.
CODEA.A.A.A NODEA
A.A.A.B NODEB
...
B.B.B.X NODEA-VIP
B.B.B.Y NODEB-VIP
...
Networking And Gateways :: Testing Private Database Link With Same Name As Public
Oct 13, 2010We are currently replacing all public database links with private db links in our prod environment. The requirement is
1) private db link name should be same as public
2) Need to create and test out private db links before we can drop public db links.
How can we test out private db links while public db links are still there with the same name?
when I execute SELECT * FROM DUAL@DBLINK from user who created this private db link, which db link it will be using- public or private?
How To Use Synonym In Stored Procedure
Aug 28, 2009how 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.
Private Synonym Inside A Package?
Apr 25, 2012I'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]....
SQL & PL/SQL :: Find Out Base Table Of Synonym?
Jun 10, 2011How to find out the base table of a synonym?
View 3 Replies View RelatedHow To Collect Synonym Information And Compare
Oct 29, 2013We 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 RelatedCircular Synonym Error While Importing Dump?
Mar 5, 2011I 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.
SQL & PL/SQL :: Unable To Disable Trigger On Synonym Table
Apr 19, 2012I 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
SQL & PL/SQL :: Script To Find Out Objects Synonym And Privs
Jun 17, 2011There 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 RelatedPL/SQL :: Create A Trigger To Check If Synonym Already Exists In DB
Mar 29, 2013How 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;
/
Server Administration :: Remove Synonym STATS$SNAPSHOT-ID?
Sep 11, 2007I'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?
SQL & PL/SQL :: Is Select Privileges (grants) Needed For Created Synonym
Jun 4, 2012After 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?
SQL & PL/SQL :: Error When Create Synonym / ORA-01031 / Insufficient Privileges
Mar 13, 2013from 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
SQL & PL/SQL :: DBMS Scheduler Doesn't Support Synonym Table (DBLINK Used)
May 12, 2011we 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.
Security :: Unable To See Data In Base Table Where Can Be Viewed In Synonym?
Sep 9, 2011I 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.
Server Administration :: Accessing Single Schema From Multiple Schema Logging?
May 16, 2011A single master schema where many developers are accessing. all share same password.
now i would like to trace all the changes made by each users. so i create a individual users for all and grant permission to access that schema.do i have a possibility of auditing the changes did by each user for that particular schema
Security :: Schema Consolidation And User Schema Mapping Based On Service
Jul 24, 2011We have an application with many separate databases (one per customer). Given they share the same business requirements (service hours, change mgmt etc), we're interested in potentially consolidating the separate DBs (which are relatively small) into separate schemas within a fewer no of databases to reduce the overhead.
Our issue is that the application is hard-coded to use a specific administrator and application connection user name. Changing this is unfortunately not an option.
Given this limitation, is there any possibility to map a generic user into a customer-specific schema based on the database service that they connect to? Each customer connects to different database services but may use the same user name. We considered using private synonyms but this seems to acheive the opposite (i.e. many different users could connect and map to a single users schema). One thing to point out is that where there is a single user name, it is acceptable for a single password to be used across the different customer DBs as they will be a single admin/user.