I have a table emp_up, daily this table is uploaded by a SQL *LOADER(with REPLACE option) script run by a UNIX JOB.There is no particular timestamp column in this table. Is it possible to know when/AT what time the table is uploaded.
View 2 RepliesI have a staging table and a target table. How do I pull in last loaded data from staging table to target table?
View 4 Replies View RelatedI got Scenario that, i need to insert the number of records loaded in target table into the log table.
But the resultset is handled in the in the cursor. how to get the number of records the cursor handles.
/* Formatted on 08/03/2013 15:00:44 (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PROCEDURE DASHBOARD75.SP_STG_MLY_GL_HKP_V1_00
AS
CURSOR GL_HKP
IS
SELECT CAL.MTH_NM,
CAL.YEAR,
GLM.BU_CD,
[code].......
i have 2 tables in two different sources.
I have loaded data from source to destination.
some rows are missed while loading.
i want to know the missing rows
problem is: Our HFM application is running very slow, but we don't know where the bottleneck is. I would like to prove that the DB is not the one causing the issue.
solution: Show AWR metrics that indicate that utilization of the DB
I've been reading thru a lot of articles in the net (Oracle sites and others), but I cannot find a clear AWR baseline that will tell me if my database is heavily loaded. how much of my DB capacity am I using in a said time period. The different OEM graphs show: During non-peak: DB is relatively Idle Peak time: the graph suggest we are just using 30-40% of max capacity. This is when a dataload into the DB happens
So, is my DB loaded if my AWR Load Profile stats are:
CODELoad Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 1.0 0.3 0.00 0.00
DB CPU(s): 0.8 0.3 0.00 0.00
Redo size: 408,893.4 125,411.1
Logical reads: 5,606.3 1,719.5
Block changes: 2,119.2 650.0
Physical reads: 455.9 139.8
Physical writes: 83.0 25.5
User calls: 469.4 144.0
Parses: 82.4 25.3
Hard parses: 44.5 13.7
W/A MB processed: 2,383,203.7 730,949.0
Logons: 0.2 0.1
Executes: 345.9 106.1
Rollbacks: 0.0 0.0
Transactions: 3.3
CODETop 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 8,687 79.5
db file sequential read 1,699,159 1,539 1 14.1 User I/O
log file sync 35,518 170 5 1.6 Commit
direct path read 418,577 165 0 1.5 User I/O
enq: TX - index contention 8,136 121 15 1.1 Concurrenc
Host CPU (CPUs: 64 Cores: 8 Sockets: 1)
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
1.03 1.29 1.7 0.5 0.0 97.8
Server Stats show CPU is 975 idle
Snap TimeLoad%busy%user%sys%idle%iowait
04-Aug 12:00:591.45
04-Aug 13:00:051.003.082.550.530.0096.92
04-Aug 14:00:111.291.951.460.490.0098.05
I select the data from sql server column type nvarchar2 and load into varchar2 but data once loaded looks very different and can't be read.
let say name column in sqlser values is 'Rajesh' then in oracle it looks like series of square shape.
How can i get the partition name which was recently loaded. When my load start it truncate the partition and load the data.once the data is loaded there is another load which will read from earlier loaded partition table. i want to know on the fly which partition was loaded recently.
View 10 Replies View RelatedMy requirement is as below,
I have one main form and there is one button when i click on it, another form should be opened which should display multi records with check box for each record.
I have implemented so far. When the new form is opened by default all the records should be checked. if user does not want some of them then it can be unchecked(This can done manually)..
I have put below code in when-new-block instance trigger
set_item_property('LOCKBOX_DETAILS.CB_SUBMIT', ENABLED, PROPERTY_TRUE);
Unable to load the block with all the records checkboxes checked.
when i run my form im immediately im geting geting this error message
oracle.forms.webutil.file.FileFunctions bean not found.CLIENT_TEXT_IO.fopen will not work
when i searched in meta link i found a solution but not know ing how to implement it..
Symptoms When running a customized Webutil Form an error similar with the following is displayed:
oracle.forms.webutil.file.FileFunctions bean not found. CLIENT_TEXT_IO.fopen will not work.
The exact function that "will not work" may change in the error message depending on the WebUtil code used. There are no errors displayed in the Java Console. The original WebUtil Demo Form runs successfully. Cause The WebUtil code that is failing is placed in triggers that are firing before Forms instantiates the WebUtil PJCs.
This is not allowed when using WebUtil and it is explained in the WebUtil User Guide release 1.0.6, section 5.3: "Once the WebUtil library has been attached to your form you can start to add calls to the various PL/SQL APIs defined by the utility. However, there is an important restriction in the use of WebUtil functions: WebUtil can only start to communicate with the client once the Form has instantiated the WebUtil PJCs. This means that you cannot call WebUtil functions before the Forms user interface is rendered.
This would include triggers such as PRE-FORM, WHEN-NEW-FORM-INSTANCE and WHEN-NEW-BLOCK-INSTANCE for the first block in the Form." SolutionDo not use Webutil code in triggers like PRE-FORM, WHEN-NEW-FORM-INSTANCE and WHEN-NEW-BLOCK-INSTANCE because these triggers are firing before Forms instantiates the WebUtil PJCs Instead, you can test the WebUtil functionality with a WHEN-BUTTON-PRESSED trigger and you can use it in your application in other allowed locations.
I am loading content of an XML file into a table using SQL loader.Below is my Control file script -
LOAD DATA
INFILE *
INTO TABLE xx_cc_response_xml_stg TRUNCATE
xmltype(XML_DATA)
FIELDS
( COLUMN_ID constant 1,
file_name filler char(4000),
XML_DATA LOBFILE(file_name) TERMINATED BY EOF)
BEGINDATA
B2B_MasterDataUpdate_20120906152137.xml
------------------------------------------------------------------------------------
The file B2B_MasterDataUpdate_20120906152137.xml is correct and XML is well formed.When i try to query for XML_DATA (datatype XMLType) column in the table, i cannot see any content in the record, and it appears as (XMLTYPE)When I parse this XML using the below,
select value(d)
from xxnbn_cc_response_xml_stg a,
table(xmlsequence(extract(a.xml_data,'/InventorySearch'))) d;
------------------------------------------------------------------------------------
I get this error:
------------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [qmcxdsSelf4], [], [], [], [], [], [], [], [], [], [], []
00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause: This is the generic internal error number for Oracle program
exceptions. This indicates that a process has encountered an
exceptional condition.
*Action: Report as a bug - the first argument is the internal error number
------------------------------------------------------------------------------------
I have an Oracle IMP that seems to complete, but there are two things that I don't understand:
1) The tables seem to load successfully in about 4 hours. After the tables are loaded, the IMP procedure appears to hang for10+ hours. I can query the tables at this time, so I'm not sure what it's doing. Is it maybe applying constraints?Is it normal for applying constraints to take 3-4X as long as importing the data?
2) I see a few of these messages in the alert log during the import.Thread 1 cannot allocate new log, sequence 2754 I don't see errors in the IMP log so are these messages informational or warnings, or will there be something wrong with my imported data?
I have few tables in Oracle 9i/10g , and they already have data in them. I am trying to migrate the data coming from various source systems into these Oracle tables. There is a chance that after loading I might get some unwanted data into these tables.
How do I remove just that data which I have loaded recently, and do not disturb the original data it already has.
Need to backup those tables and reload the data back if there is any problem, but I am looking at a different approach. I just don't want to change the existing system, as lot of users use the system.
I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.
So far the only option I have is to export and then import back the data.
know if there is any way to move a partitioned table between table spaces of different block size?
I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.
CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)
-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.
ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
[code]...
Oracle 10g, Windows XP
There is an interface table and there is an normal transcational table..interface table is being compared with normal table and if match found the result is dumped into another normal table.
I am using two cursors one is to query the interface table and in a for loop pass the results to the second cursor..The interface table is having 5000 + rows and the transcation table is having more than 3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes..
create table x_interface /* INterface table */ -- 5000 + rows
( name varchar2(80), addr_line1 varchar2(35), addr_line2 varchar2(35), addr_line3 varchar2(35),
addr_line4 varchar2(35), addr_line5 varchar2(35), addr_line6 varchar2(35), suffix varchar2(35),
city varchar2(15), state varchar2(10), zcode varchar2(10))
[code]....
creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
View 3 Replies View RelatedI am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......
We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
View 11 Replies View RelatedWe have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table
Parent Child
Report1Table A
Table ATable M
Table ATable N
Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3
-Report1Table ATable M
-Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20));
insert into hierarchy values ('Report1','Table A');
insert into hierarchy values ('Report1','Table B');
insert into hierarchy values ('Table A','Table M');
insert into hierarchy values ('Table B','Table N');
insert into hierarchy values ('Report2','Table P');
insert into hierarchy values ('Table M','Table X');
insert into hierarchy values ('Table N','Table Y');
insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function :
select *
from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path
from hierarchy start with parent='Report1'
connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies View RelatedI've 2 table with below colums
Create table rent (customer_id number(10),
doc_num varchar2(20)
);
Create table doc_id (doc_num varchar2(20));
Insert into rent(customer_id) values (1);
Insert into rent(customer_id) values (2);
Insert into rent(customer_id) values (3);
Insert into rent(customer_id) values (4);
[code]...
Now my requirement is i need to assign doc_num from doc_id table to 4 customers in rent table randomly. I mean update doc_num in rent table from doc_id table randomly. how to write update statement.
There is a requirement to make a table data in a database (eg: HR database) available in another database (eg: EMP database), instead of accessing it using database link. In EMP database(where data needs to be cloned), data will only be queried and no write operation will be done. Data in remote database (eg: HR DATABASE) will be occassionally fully truncated and reinserted. The plan is to do a similar truncate and reinsert of data (from HR database) into EMP database monthly once using dbms scheduler job. So basically data in just one table needs to be cloned in another database.
Question: For this situation, is a regular table or Materialized view the right choice to clone the table in EMP database and why? The table in HR database (remote database) is not very big.
We deleted millions of records from a table.
1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.
2.Will re org table and index improve the database performance ?
Oracle 11gI have a large table of 125 million records - t3_universe. This table never gets updated or altered once loaded, but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads - that will be updated with regard to when the lead is mailed and for other relevant information. select records from this 125 million record table to insert into the smaller table.
I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below. Then use a second query that inserts into T3_Leads from this View V_Market. This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause? My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.
CREATE VIEW V_Market asWITH got_pairs AS ( SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */ l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no , ROW_NUMBER () OVER ( PARTITION BY l.address_key ORDER BY l.hh_verification_date DESC ) AS r_num FROM t3_universe e JOIN t3_universe l ON l.address_key = e.address_key AND l.zip_code = e.zip_code AND l.p1_gender != e.p1_gender
[code]....
I want to do an import of a table from my old dump file.The same table is already there in the development box but few more columns are added to that table while testing so in the dump those columns are not available.
TABLE_EXISTS_ACTION=TRUNCATE
The new table
SQL> desc "TESTINVENTORY"."TTRANSACTION"
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
TRANSACTIONIDNOT NULL CHAR(26)
BRANCHCODE NOT NULL CHAR(3)
EXTERNALSYSTEM NOT NULL CHAR(3)
EXTRACTSYSTEM NOT NULL CHAR(3)
OWNERBRANCHCODE NOT NULL CHAR(3)
TRADEREFERENCE NOT NULL CHAR(20)
[code]...
It giving error while doing an import.
I have got two tables emp_dtl and iou_tab. i have already made entries i.e booking no, emp_cd, emp_name etc in emp_dtl snc its my master table. I want to retrieve the booking nos through lov in iou_tab which are generated in emp_dtl and corresponding info of emp_cd and emp_name should come in the respected fields in iou_tab.
View 1 Replies View RelatedI 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.
I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?
Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?
I have a table with a BLOB column ;
I want read data from table and insert to another table with a cursor
My code is :
procedure read_data is
cursor get_data is
select id,image from picture1;
id1 number;
pic blob;
begin
open get_data;
[code]....
when I run form , error FRM-40734 occurred
error in line " fetch .... "