SQL & PL/SQL :: View To Find DB Link Type
Feb 21, 2012Is there any view to find out the Types (Public or Private) of the DB LINK already created?
View 6 Replies
ADVERTISEMENT
PL/SQL :: Materialized View Link With All Options?
Dec 28, 2012materialized view link with all options?
View 3 Replies View RelatedError In Creating Materialized View With DB Link?
Aug 7, 2013we have the following requirement for creating materialized view.
In database "DB1", a table "abc" is there in "user1" schema.
In database "DB2", in "user2", we are accessing the "abc" through DB link "DBL1" to DB1:
select * from user1.abc@DBL1;
we have created a public synonym for "user1.abc@DBL1" as "sabc".
In database "DB3", we need to create the materialized view to access "abc" in DB1 through DB2. we are not allowed to access "DB1" directly from "DB3". so we create a db link as "DBL2" in "DB3" to "DB2".
when i try to create a materialized view as below:
create materialized view abc_mv
as
select * from sabc@DBL2;
But got the error as "synonym translation invalid". When i access the SQL statement separately as below
select * from sabc@DBL2, its working but in Materialized view, it shown that error. Even i tried with schema name as well like "user2.sabc@DBL2" , but that also thrown same error.
Is there any option available for this type of creating materialized view?
Database Link To Fetch Values From View?
Dec 8, 2008Here is the information about database I need to connect from my view, as the underlying table for my view is in this database
Database instance : orcl
Machine name : contentm (not sure, if this needs to be used)
so in my schema, I tried the following
CREATE DATABASE LINK product_lnk2 USING 'orcl'
select * from cont20.V_FB_PRODUCT_NM@product_lnk2
where cont20 is the schema on orcl
But it gives me this error ORA-12154: TNS:could not resolve the connect identifier specified
Server Administration :: Find Whether Database Link Is Being Used?
Jul 26, 2011I am having database A and database B and I am inserting into database b by selecting some records from database a using db link.Is there any way to find whether database link is being used?
View 6 Replies View RelatedNetworking And Gateways :: How To Find All The Objects Referring To DB Link
Aug 10, 2012We have two schemas which earlier used to be separate databases. There were DB links created to access the objects from one schema to the other schema when they were separate databases.
Since now they are just 2 separate schema with in the same Database, we would like to remove the DB Links and create synonyms to access those objects. These DB links were used in code in many places, it is becoming tough to find a way to implement this.
how to find all the objects that are using these objects or all the places these DB likns were being used.
Security :: View / Obtain Password Once Database Link Has Been Created?
Dec 24, 2011Is there a way to view/obtain the password once a database link has been created. Some sys dba table?
View 7 Replies View RelatedServer Utilities :: Exclude View In IMPDP Using Network Link Giving Error
Jan 14, 2011I am using below command to import a schema using network link. Command is :
impdp system directory = IMP_DIR schemas = XYZ network_link = PQR remap_schema = XYZ:XYZ exclude=view: "= 'XYZ.VW_ACCEPTDETAILS'"
This command is giving below error
LRM-00116: syntax error at 'view:' following '='
When I have tried Like in place of '=' sign i.e. EXCLUDE = VIEW:"LIKE '%VW_ACCEPTDETAILS%'" , it gives me below error:
UDI-00014: invalid value for parameter, 'exclude'
How To Find OS Storage Type
Sep 9, 2013Database Version is 11.2.0.3 & OS version is RHEL5. How can I find the OS storage type? Which command should I execute to find this?
View 3 Replies View RelatedForce View Number Data Type Precision?
Aug 18, 2011I am working on an application that pulls data from an Oracle view into Microsoft Excel (Oracle 11g, MS Excel 2003). It is an automated pivot table created through vba. The problem I'm having is that the decimal points from number columns are being truncated - they don't make it to the pivot table.
When I use the pivot table wizard and set the external data source using a SQL string (select * from view), the precision is not lost. When I create the pivot table automatically in vba, the precision is lost. The pivot table settings, regardless of how the data is brought in, shows the format of the number column as general. This tells me that Excel is trying to figure out what the data type is, and can't (not smart enough).
When looking at the description of the view, the data type is NUMBER (no precision). The table that it is pulling from has the precision set (NUMBER(11,3)). I have tried using the following, but it doesn't work:
select to_number(field_name, '99999999.999') field_name from view
XML DB :: Find Size Of Table With XML Type Column Store As Binary XML
Dec 21, 2012I have a table with structure as:
CREATE TABLE XML_TABLE_1
(
ID NUMBER NOT NULL,
SOURCE VARCHAR2(255 CHAR) NOT NULL,
XML_TEXT SYS.XMLTYPE,
CREATION_DATE TIMESTAMP(6) NOT NULL
[code].....
- So HOW do I find the total size occupied by this table. Does BINARY storage work as LOB storage. i.e. I need to consider USER_LOBS as well for this. OR foll. will work
select segment_name as tablename, sum(bytes/ (1024 * 1024 * 1024 )) as tablesize_in_GB
From dba_segments
where segment_name = 'XML_TABLE_1'
and OWNER = 'SCHEMANAME'
group by segment_name ;
- Also if I am copying it to another table of same structure as:
Insert /*+ append */ into XML_TABLE_2 Select * from XML_TABLE_1.
Then how much space in ROllbackSegment do I need. Is it equal to the size of the table XML_TABLE_1?
Networking And Gateways :: Listener Could Not Find Available Handler For Requested Type Of Server
Jun 20, 2010I have ora-12520. I run Oracle 11g on Red Hat 4 64 bit. Actually I have RAC but one node is down and I work only with up one (connection string point directly to listener of running node). I run test with not too big loading. select count(*) from v$sessions gets ~ 200 in maximum loading, but after half minutes I get this error in my logs. Each time, lsnrctl services show that all is right. It shows one service with one handler. I see nothing in alert.log. This is very strange, I know, and I'm sure that this is an actual alert.log because, I see there other events in actual time. Most important: I have SESSION parameter set to 600 and PROCESSES set to 400 so this must be enougha...
View 3 Replies View RelatedView To Find SQL Plan Changes
Jul 4, 2012The view that can be used to find the changes the plans (hash_value,plan_hash_value for a particular sql statement).
I have a particular sql statement for which the execution plan changes but, unfortunately i cannot find regarding which view can be used to find the details regarding this. V$SQL_PLAN_MONITOR is not working as well.
SQL & PL/SQL :: How To Find Number Of Rows Of View
Oct 22, 2010is there a way to find out how many rows a View has? Something similar to NUM_ROWS with regular tables perhaps? Or do I have to use Count(*)?
View 4 Replies View RelatedSQL & PL/SQL :: Find And Trace Custom View / Procedure?
May 24, 2013I know that this query had been executed again the db, but the person is no longer here. Last run was about 3~4 months ago.
SELECT Subject_ID,
TO_CHAR (completed_date, 'mm/dd/yyyy'), status
FROM ADMINDBG_USER.adbt_master
...
[ADMINDBG_USER.adbt_master] is not a part of [dba_tables]. [ADMINDBG_USER.adbt_master] must be a view/procedure/a sort.
I have no luck to find what view/procedure/syntax is populating the data onto [ADMINDBG_USER.adbt_master].
SQL & PL/SQL :: How To Find Size Of View Or Table In Database
Jul 10, 2010How can we find the size of a view or synonym or table in a database. What is the code.
View 4 Replies View RelatedServer Administration :: How To Find The Base Table Of A View
Aug 15, 2011How 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?
Security :: How To Find Out Date View Status Changed To Invalid
Mar 10, 2011We are trying to audit a view. We are currently seeing that view in production instance and trying to find out when exactly it got invalidated.
View 4 Replies View RelatedPL/SQL :: Oracle Dictionary View 2 / Find Queries Run And Its Execution Time
Feb 6, 2013Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.
View 2 Replies View RelatedSQL & PL/SQL :: Backward Accessing Super Type Attributes From Sub-type Body In Oracle Collection?
Jan 2, 2013I have 3 user defined collection types. I am trying to access the type3's attribute in type1 body (like backward accessing).
Ex. My Collection Types Structure (something like master detail structure)
create type type1 as object
(
attr1 varchar2(10),
attr2 varchar2(10),
member procedure function1
[code]...
so, in the type1 body i have to get or assign the value either to type2's attribute or type3's attribute. I have search all the in internet but i haven't find anything such. how to find the reverse method of accessing the super type's attribute(s) in sub type's body.
Server Administration :: Functions To Convert The Long Type Field Data To Varchar2 Type
Oct 17, 2011Is there some functions to convert the long type field data to varchar2 type?
View 2 Replies View RelatedSQL & PL/SQL :: Passing Values From Oracle Object Type To PLSQL Type
Mar 8, 2013I have created the below types and oracle objects.
create or replace type T_EMA_NP_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)
create or replace type T_EMA_NP_RANGE_LNPTICKET_TAB AS TABLE OF T_EMA_NP_RANGE_LNPTICKET_REC
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following types are created in the Package specification
type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);
--
subtype t_ema_lnpticket is T186_IN_REQ_PARAMETER.T186_EMA_LNPTICKET%TYPE; -- Number
type t_ema_lnpticket_tab is table of t_ema_lnpticket index by binary_integer;
I have the following two procedures
PROCEDURE getEMAReturnResponse(
p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_np_setdel_resp_rec,
p_ema_range_lnpticket_tab IN t_ema_np_range_lnpticket_tab,
p_endof_event IN varchar)
PROCEDURE Return_Response(p_in_call_request_id IN number,
p_ema_resp_rec IN t_ema_resp_rec,
p_ema_lnpticket_tab IN t_ema_lnpticket_tab,
p_endof_event IN varchar2)
getEMAReturnResponse Procedure:
Accessed by Java application to pass the values. Should call the Return_Response procedure and pass the values received from Java.
Return_Response Procedure
The p_ema_lnpticket_tab is a sort of array that can have multiple values. Please see the example of values. Has all the business rules and validation that should be adhered.
Example of Vaules
p_in_call_request_id = 1
p_ema_resp_rec = 12345, 'Operation Failed'
p_ema_lnpticket_tab = (1,2,4,5)
p_endof_event = Y
SQL & PL/SQL :: ORA-02303 - Cannot Drop Or Replace A Type With Type Or Table Dependents
Feb 1, 2012i am trying to run a script in which a command tries to create or replace a type.
i get this error:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --create a test user:
SQL>
SQL> create user tuser identified by tuser
[Code]....
Table created.
SQL>
SQL> --then change the type:
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
create or replace type t1 as object (obj_type number(15))
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL>
SQL> --if i'll do FORCE action on the type - it'll corrupt my depandant table:
SQL>
SQL> drop type t1 FORCE
2 /
Type dropped.
SQL>
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
SQL>
SQL>
SQL> --if i re-create it - my table is still corrupted:
SQL>
SQL>
SQL> create or replace type t1 as object (obj_type number(15))
2 /
Type created.
SQL>
SQL>
SQL>
SQL> desc dpntnt_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID1 NUMBER(7)
SQL>
--if i re-create it - my table is still corrupted:
create or replace type t1 as object (obj_type number(15))
/
desc dpntnt_table
[/code]
1. If i'll do drop type FORCE what will happen to the dependent object(might be a table for example) ?
2. I understand that this type is already assigned to some object, but i can't seem to find out which one.
how do i find out which object is depending on the type i want to create or replace?
PL/SQL :: Passing Values From Table Type To Oracle Object Type
Mar 8, 2013I have created the below types and oracle objects.
create or replace type T_SETDEL_RESP_REC as object
(
respCode number,
respDesc varchar2(255)
)
--
create or replace type T_EMA_NP_RANGE_LNPTICKET_REC as object
(
ticket number
)
create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following type is created in the Package specification
type t_resp_rec IS RECORD
(
resp_code number,
resp_desc varchar2(255)
);
I have the following two procedures
Procedure getResponse(p_call_request_id IN number, p_resp_rec IN t_setdel_resp_rec,
p_range_ticket_tab IN t_range_icket_tab, p_endof_event IN varchar)
PROCEDURE ProcessResponse(p_call_request_id IN number, p_resp_rec IN t_resp_rec,
p_ticket_tab IN t_ticket_tab, p_endof_event IN varchar2)
The get Response procedure is a wrapper procedure exposed to Java to pass values. The Process Response procedure is a main procedure where all logics and business rules are handled.
The Problem is:
How can I pass the values from get Response procedure to Process Response procedure. So that rules and validations are applied. Please note the p_ticket_tab may have many ticket numbers corresponding to p_call_request_id.
Values E.g. :
p_call_request_id = 1
p_resp_rec (1234, 'Error found')
p_range_ticket_tab (1,2,3,4,5)
p_endof_event = 'Y'
PL/SQL :: How To Call A Function Having OBJECT Type As Return Type
Mar 26, 2013I've the following function returning OBJECT type. how to call this function
CREATE OR REPLACE TYPE GET_EMP_OBJ is object
( emp_name varchar2(50) ,
mgr_id number,
dept_id number
);
[Code]...
The above function got created successfully. And i'm confused how to call this functions. I tried like below but didn't work
DECLARE
t_emp_info_1 GET_EMP_OBJ ;
BEGIN
t_emp_info_1 := get_emp(7566) ;
for i in 1..t_emp_info_1.COUNT
LOOP
DBMS_OUTPUT.put_line ('Values are'||i.emp_name ) ;
END LOOP;
END;
SQL & PL/SQL :: Cannot Drop Or Replace A Type With Type Or Table Dependents
May 12, 2011When i tried to drop a type using below command , i received errors
DROP TYPE JAM_ACAS_MSG_TYPE
ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents
PL/SQL :: Type Attribute With Object Type Or Nested Table?
Mar 7, 2013I have been creating lot many threads around the same problem, however i thought i knew but realized I do not know or else do not know how to. I have created object type with an attribute READINGVALUE NUMBER(21,6)...How can i use type attribute on this object while declaring variable.....can we use type attribute on NESTED TABLES, similar to the db tables?
example
CREATE TYPE READING AS OBJECT(READINGVALUE NUMBER(21,6));
CREATE TABLE INTERVALREADINGS OF TYPE READING;
SQL & PL/SQL :: Converting Record Type In Table Type
Jul 12, 2010how to convert a record type to a table type?
View 4 Replies View RelatedSQL & PL/SQL :: Using Record Type And Object Type?
Aug 17, 2013I want to return the output of this query using one OUT parameter from the procedure using RECORD type or OBJECT type.
SELECT empno,ename,sal FROM emp WHERE deptno=30;
Let us assume the query is returning 50 records.
I want to send those 50 records to OUT parameter using record type or object type.