Server Utilities :: Processing Object Type SCHEMA_EXPORT / TABLE / INDEX
Apr 28, 2011
I am importing the dump.All the tables are getting imported but when it comes to the part as shown in the error it gets struck there and nothing is showed after.
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
The dumps for which I am doing the import is huge. Its around 46 GB.
select
sl.sid,
sl.serial#,
sl.sofar,
sl.totalwork
from
v$session_longops sl,
v$session s,
dba_datapump_sessions d
where
s.saddr = d.saddr
and s.sid = sl.sid
and s.serial# = sl.serial#
order by start_time;
It showing the lock for two sid's. Should I kill the process and import the dump again? I used below command to import dums
I 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.
I 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;
I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
CREATE OR REPLACE TYPE typeObj1 AS OBJECT ( SYSTEM_IDENTIFER VARCHAR2(50), PROCESS_TYPE VARCHAR2(50), abc typeObj2
) /
[Code]...
/I have tried to access the type-object in where clause in following way
FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC, ...... Where ....... AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
AND (ADTINF.ADDTN_RQST_TYP_VAL_DT IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL ) AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL ) AND (ADTINF.ADDTN_RQST_TYP_VALUE IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.
I have existing table which needs to be copy to new table with object using the cursor. The exist table has a rank column which has some duplicate rank, which need to be remove and provide a series of numbers, like 1,2,3,4,5,...
create type UNIVERSITY as object ( U_RANK number(2), U_SCHOOL varchar2(150), U_COUNTRY varchar2(150), U_SCORE number(3) )
Can we create TABLE type object by using %ROWTYPE in SQL.
I am bale to create PL/SQL table type object .But i am unable to create SQL type
SQL> declare 2 type table_emp is table of scott.emp%rowtype index by binary_integer; 3 employees table_emp; 4 begin 5 select * bulk collect into employees from scott.emp; 6 end; 7 / PL/SQL procedure successfully completed SQL> create or replace type table_emp is table of scott.emp%rowtype index by binary_integer; 2 /
Warning: Type created with compilation errors
SQL> show errors Errors for TYPE DBO.TABLE_EMP: LINE/COL ERROR -------- ---------------------------------------------------------- 1/19 PLS-00355: use of pl/sql table not allowed in this context 0/0 PL/SQL: Compilation unit analysis terminated SQL>
How can I create global table type object with %rowtype
Ive just been trying to create an add member procedure to retrieve an object that is in another table. But before i get the ref to bring the toy across i wanted to make sure i could insert an object into the new table. I keep getting theres too many arguments, the lack of sqlplus code, the spool function isnt working.
DROP TABLE completed_toys; / CREATE OR REPLACE TYPE comp_toyobj AS OBJECT ( completed_id NUMBER(5), request_id REF toy_obj,
[Code].....
Here is the error on the procedure call
Error report: ORA-06550: line 4, column 9: PLS-00306: wrong number or types of arguments in call to 'ADD_COMPLETED' ORA-06550: line 4, column 9: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s: %s" *Cause: Usually a PL/SQL compilation error. *Action:
SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20), 2 v_ASSETTYPE varchar2(20)); 3 / Type created
--- >> create the package
SQL> create or replace package test_arr is 2 type nest_tab is table of obj1; 3 procedure insert_data(t_nest IN nest_tab); 4 end test_arr; 5 /
Package created
SQL> create or replace package body test_arr is 2 procedure insert_data(t_nest IN nest_tab) is 3 begin 4 for i in t_nest.first..t_nest.last loop 5 insert into asset(ASSETID, 6 ASSETTYPE) values (t_nest(i).v_ASSETID,
[code]....
-- >> a block to execute the above package:
SQL> declare 2 type ref_tab is table of obj1; 3 ref_tab1 ref_tab; 4 begin 5 ref_tab1 := ref_tab(1,'a'); 6 test_arr.insert_data(ref_tab1); 7 end; 8 /
But I am getting the below error when executing the package:
ORA-06550: line 5, column 13: PLS-00306: wrong number or types of arguments in call to 'REF_TAB' ORA-06550: line 5, column 13: PLS-00306: wrong number or types of arguments in call to 'REF_TAB' ORA-06550: line 5, column 1:
I 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
I'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;
I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column
like '%abc%' like 'abc%' like '&abc'
Will be the corresponding index will be for those cases?
As a part of our back up we used to export the production data every day using Original export Utility but from 11g original export Utility is de supported and also datapump doesn't support XML Objects so is there any other way to export the full database else any option to export xml Object using datapump.
Every quarterly, we export the data from DEEXTRA and import into INEXTRA user.This is not working after i made the changes 2 tables and views in DEEXTRA .
I added new columns to 2 tables and associated 2 views. After this change, my import process got failed, with message especially for those views. "IMP-00041: Warning: object created with compilation warnings"
before i change the tables, import process was working fine. My doubt is, views were created in INEXTRA before the tables in import functionality.
I had given the grants similary the other objects. I belive no problems with privilages.
Because of table changes the order of the objects in exporting got disturbed? like in the exporting functionality first views created then the table?or the order of the objects in importing got disturbed? like first views created and then tables?
D:>impdp TESTIMP/TESTIMP TABLES=ENVDB DIRECTORY=Rini DUMPFILE=envdb_11g.dmp REM AP_SCHEMA=TESTIMP:STP11G REMAP_TABLESPACE=TBS_TESTIMP:TBS_STP11G; Import: Release 11.2.0.1.0 - Production on Thu Sep 9 15:16:45 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39002: invalid operation ORA-39166: Object TESTIMP.ENVDB was not found.
I could see in teh log lot of IMP-00041: Warning: object created with compilation warnings
example: IMP-00041: Warning: object created with compilation warnings "CREATE FORCE VIEW "ARADMIN"."BMC_CORE_BMC_IMPACT" (" ""REQUESTID","SUBMITTER","CREATEDATE","ASSIGNEDTO","LASTMODIFIEDBY","MODIFIE" "DDATE","STATUS","SHORTDESCRIPTION","CMDBROWLEVELSECURITY","INSTANCEID","CMD"
(1) Does imp utility rebuild the indexes while loading data ? or it simply takes the rows from dump and load into test system without building from scratch ?
(2) I am trying to replace 'exp' and 'imp' with datapump utilities ? But, I am confused about the parameters to be used ?
(a) Can I load both data and meta data at the same time (Using CONTENT=ALL option) ? (b) I am planning to implement this in two steps :
first load only metadata using - CONTENT=METADATA_ONLY TABLE_EXISTS_ACTION=REPLACE
LOAD DATA INFILE "gateway.csv" truncate INTO TABLE GATEWAY Fields terminated by "," Optionally enclosed by '"' trailing nullcols
[code]....
and I got the following error:
zcyds891:/opt/oracle> sqlldr gwcem/gwcem@pfs control=gateway.ctl log=/tmp/ldr.log bad=/tmp/bad.log SQL*Loader: Release 9.2.0.8.0 - Production on Tue Dec 7 05:07:59 2010 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL*Loader-350: Syntax error at line 12. Expecting "," or ")", found "INTERGER". GATEWAYPROTOCOL INTERGER, ^
i load data to this table from a file using sqlldr.
what is the proper data type should i use in control-file for both the fields.? i dont mention any datatye in ctl file which is working fine with given dataset.
create or replace type testobj as object(col1 number); create or replace type tabtest as table of testobj; create or replace procedure proc(a out tabtest) is cursor c is
I have created one type as Object and I am trying to display the values available in object type for debugging purpose. To display the contents of object type.
