RAC & Failsafe :: Inserting Large Data Locks The Destination Table In RAC

Oct 18, 2010

Scenario:

Our application is using a two instance, one for the live active data and the other for the reports data. We have a process which moves the data from the live instance to reports instance every night. In a single db environment the process is working without any issues. However when we move to the RAC environment the reports db's (insert) in large table get locked and we are unable to insert data to the reports db.

What we are performing is:

Insert into my_table_rpt select * from may_table_live@db_link_to_livedb;

Issues:

my_table_rpt get locked

We have found the workaround by disable locking in destination and subsequent to the insert enable locking

ALTER TABLE my_table_rpt DISABLE TABLE LOCK;

Insert the data to the reports database table

Then

ALTER TABLE my_table_rpt ENABLE TABLE LOCK

Question:

Why does the large destination table (my_table_rpt) get locked in the RAC environment?

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Stop Inserting Data Into A Table Before It Inserting Using Trigger?

Jul 26, 2011

shall we stop inserting data into a table before it inserting using Trigger?

View 3 Replies View Related

SQL & PL/SQL :: Table Comparison - Loaded Data From Source To Destination

Apr 29, 2013

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

View 11 Replies View Related

Replication :: Table Data Modification From Source To Destination Database

Jan 11, 2011

I want to replicate single table data/modification from source db to destination DB.procedure or steps how to create replication between source and dest db for single table.

View 18 Replies View Related

SQL & PL/SQL :: View Sample Data From Very Table Which Is Large In Size?

Apr 26, 2010

I have a query on , how to view the sample data from a very table which is large in size ( more than 10 million ).

I just need to see some sample data from a large table ( to see what kind of data which is application related ).

My question is :

Select *
from Sample_table
where rownum < 10

is this a Good way to view the sample data ?

I have understanidng that the rownum will be assigined to the rows once all the rows are reteived.

So what is the best way to view ?..I am not sure of any condition to put in the intial time of querying.

View 5 Replies View Related

Inserting Data In New Column Where Table Has Huge Data

May 26, 2013

I am trying to add a new column in a table and insert data from another column of same table.

alter table POSITION add INT_MK_DATA_ID number(10,0) null;
update POSITION set INT_MK_DATA_ID = INST_MARKET_DATA_ID;
commit

As there are huge number of records in the POSITION table ...its taking for ever to execute this query.

View 1 Replies View Related

SQL & PL/SQL :: Inserting Data From One Table To Another?

Oct 23, 2010

I have two tables with two columns of each table in my Oracle Version :Oracle 9.2.0.1.0

TEST22:
-----------
|sno |sname |
-----------
| | |
-----------

TEST22P:
---------------
| col1 | col2 |
|---------------|
| sno | 1 |
| sname | arun|
---------------

Required outcome is

TEST22:
----------
|no | name |
|----------|
|1 |arun |
----------

Also this should be applicable for more than one value in the column col2 of table TEST22P.

ex:-
TEST22P:
--------------------
|col1 | col2 |
|--------------------|
|sno | 1,2..n |
|sname | arun,ajay..n|
--------------------

I used decode & pivot insert for this,but the result is a failure.

SQL>INSERT INTO test22 (no,name) SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;

SQL>
sno sname
--------
1 null
null arun

AND

SQL> INSERT ALL
2 INTO test22 VALUES(no)
3 INTO test22 VALUES(name)
4 SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
INTO test22 VALUES(name)
*
ERROR at line 3:
ORA-00904: "NAME": invalid identifier

View 4 Replies View Related

SQL & PL/SQL :: Inserting Data In Target Table From Source Table

Nov 22, 2012

I need to insert data in Table A from Table B where most of the fields are identical and might some of the fields will be more in Table A.

ex: Table A: a,b,c,d,e,f
Table B: a.b,c,g,h

How to insert this using user_tab_columns in cursor and if I am giving the i/P as my table names . This needs to be configurable and reusable rather i mention all the fields in my logic.

View 4 Replies View Related

Inserting Table Data Into A Variable

Sep 3, 2012

I am trying to insert a column into a variable from a trigger.

Here is the code that i have:

CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer
FOR EACH ROW
DECLARE
v_key varchar2(10);
BEGIN
select ID into v_key from buyer;
insert into message_log_table (table_name, message_comments)
values
('Buyer', 'Buyer '||v_key||' has been updated');
end;
/

When I run the above I get the following compiler error:

[Error] ORA-00904 (6: 12): PL/SQL: ORA-00904: "ID": invalid identifier

Since ID is defined in my BUYER table I do not understand what the error means.

Here is my create table statement:

CREATE TABLE BUYER
(
ID VARCHAR(50) NOT NULL PRIMARY KEY,
FNAME VARCHAR(50) NOT NULL,
LNAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP_CODE NUMBER(5) NOT NULL
);

View 1 Replies View Related

PL/SQL :: Inserting Data Into Updated Table

Jul 22, 2012

I have a table created with the following code:

CREATE TABLE CLIENT
(
CLIENT_ID NUMERIC(2),
CLIENT_NAME VARCHAR2(25),
CONTACT_LAST_NAME VARCHAR2(15),

[Code]...

I have altered the table to have the following:

ALTER TABLE CLIENT
ADD CLIENT_CITY VARCHAR2(25);

I am trying to insert new data into said table that was updated:

INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1;

Then I get the following error:

Error starting at line 1 in command:

INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1
Error at Command Line:4 Column:15
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:   
*Action:

View 3 Replies View Related

PL/SQL :: Extracting Data From XML And Inserting Into Table

Aug 8, 2013

I have an XML  of the following format:

<data>
<students>
<student>
<studentname>Raymond<studentname>
<StudentId>1</StudentId>
<StudentAge>11</StudentAge>
<StudentMark>0</StudentMark>
</student>
</students> 

Now i have to insert this xml into DB , the table consist of following columns  ( row number , property name , value )  Expected out put is   (1,student name,Raymond) ,( 1, studentid , 1) ( 1, studentAge, 11) (1,Studentmark , 0) The challenges here is

1. how to get the tag names  and populate the property name column ?
2. The number of properties for a student can be variable , How can i deal with them ?

View 8 Replies View Related

PL/SQL :: Inserting Data Into Nested Table

Jul 22, 2013

I am exploring the differences between OBJECT & RECORD. As i am still in process of learning, I found that both are structures which basically groups elements of different datatypes or columns of different datatypes, one is used in SQL and other is used in PL/SQL. Below i am trying to insert data into an table of type object but i am unsuccessful.

CREATE OR REPLACE type sam as OBJECT
(
v1 NUMBER,
v2 VARCHAR2(20 CHAR)
);

---Nested Table---
create or replace type t_sam as table of sam;
--Inserting data----
insert into table(t_sam) values(sam(10,'Dsouza')); Error Message:
[code]........

View 4 Replies View Related

Data Archive Script Is Taking Too Long To Delete A Large Table

Aug 8, 2013

We have data archive scripts, these scripts move data for a date range to a different table. so the script has two parts first copy data from original table to archive table; and second delete copied rows from the original table. The first part is executing very fast but the deletion is taking too long i.e. around 2-3 hours. The customer analysed the delete query and are saying the script is not using index and is going into full table scan. but the predicate itself is the primary key,More info below

CREATE TABLE "APP"."MON_TXNS"    (    "ID_TXN" NUMBER(12,0) NOT NULL ENABLE,     "BOL_IS_CANCELLED" VARCHAR2(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,     "ID_PAYER" NUMBER(12,0),     "ID_PAYER_PI" NUMBER(12,0),     "ID_PAYEE" NUMBER(12,0),     "ID_PAYEE_PI" NUMBER(12,0),     "ID_CURRENCY" CHAR(3 BYTE) NOT NULL ENABLE,     "STR_TEXT" VARCHAR2(60 CHAR),     "DAT_MERCHANT_TIMESTAMP" DATE,     "STR_MERCHANT_ORDER_ID" VARCHAR2(30 BYTE),     "DAT_EXPIRATION" DATE,     "DAT_CREATION" DATE,     "STR_USER_CREATION" VARCHAR2(30 CHAR),     "DAT_LAST_UPDATE"

[Code]...

 Data is first moved to table in schema3.OTW. and then we are deleting all the rows in otw from original table. below is the explain plan for delete  

SQL> explain plan for  2  delete from schema1.mon_txns where id_txn in (select id_txn from schema3.OTW); 

Explained. SQL> select * from table(dbms_xplan.display); 

PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2798378986
 -------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------|   0 | DELETE STATEMENT       |            |  2520 |   233K|    87   (2)| 00:00:02 ||   1 |  DELETE                | MON_TXNS   |       |       |            |          ||*  2 |   HASH JOIN RIGHT SEMI |            |  2520 |   233K|    87   (2)| 00:00:02 ||   3 |    INDEX FAST FULL SCAN| OTW_ID_TXN |  2520 | 15120 |     3   (0)| 00:00:01 ||   4 |    TABLE ACCESS FULL   | MON_TXNS   | 14260 |  1239K|    83   (0)| 00:00:02 |

-------------------------------------------------------------------------------------
 PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id):
--------------------------------------------------- 

View 6 Replies View Related

Application Express :: How To Show Data From A Table Having Large Number Of Columns

Oct 8, 2013

I have a report with single row having large number of columns . I have to use a scroll bar to see all the columns. Is it possible to design report in below format(half columns on one side of page, half on other side ofpage : 

Column1DataColumn11DataColumn2DataColumn12DataColumn3DataColumn13DataColumn4DataColumn14DataColumn5DataColumn15DataColumn6DataColumn16DataColumn7DataColumn17DataColumn8DataColumn18DataColumn9DataColumn19DataColumn10DataColumn20Data I am using Apex 4.2.3 version on oracle 11g xe.

View 2 Replies View Related

Performance Very Slow Inserting Data Into Table

Jun 25, 2013

I am trying to insert huge data into another huge table which is almost taking around 2-3 hrs. See my below query

INSERT /*+ APPEND *//*+ NOLOGGING */ INTO DB1.Table1 SELECT * FROM DB2.Table2 ;
COMMIT;

Both Table1 and Table2 have same structure and table1 is master table having 100 Billion records and table2 having 30 Million records. This is a direct insert where each day this operation carried.

View 4 Replies View Related

Forms :: Inserting Into A Table Using Data From A Text Box

Feb 16, 2010

I have a form that has one text box in it, and I want this value to be inserted into a table called Staff Name, which has only one column, but im unsure as to how I would get my SQL statement to pick up this value.

I tried:

begin
insert into StaffName values ('addstaffmember');
end;

Add staff member is the name of the text box. this statement compiled but when I ran the form and tried to click save it would not work.

View 5 Replies View Related

SQL & PL/SQL :: FORALL For Inserting Data (with Some Constant Value) Into Table

May 13, 2011

using FORALL for inserting data into table.

Below pl/sql works fine when we write all the cursor data together:

DECLARE
TYPE t_rec IS TABLE OF T%ROWTYPE;
l_tab t_rec;
CURSOR cur IS SELECT a,b FROM t;
BEGIN

[Code]...

but I want to insert including some constant value while inserting the data like

DECLARE
TYPE t_rec IS TABLE OF T%ROWTYPE;
l_tab t_rec;
CURSOR cur IS SELECT a,b FROM t;
BEGIN

[Code]...

How to do this by using forall, or how to do without hiting performance ( more than 5000 rows i have to write.

View 5 Replies View Related

Forms :: Reading Excel Data And Inserting Into Table Using 6i?

Feb 16, 2004

how to read the excel data and insert into tables without using SQL loader. i tried using OLE2 package,but i am getting an non-oracle exception. even i tried using CSV format. but i couldn't make it.

View 3 Replies View Related

SQL & PL/SQL :: DB Locks And Data Corruption?

Feb 25, 2012

I have a sql script where triggers, procedures and functions are written. The triggers are causing db outages and causing problems in the application as well.

I am trying my best but with my low experience and expertise, am not able to make good progress.

Scenario: Course enrollments are inserted, deleted and updated in course_main and course_users table. This is done in gui as well as in background snapshot scheduler in a cron process. Course_main table contains all course enrollments and course_users table has crsmain_pk1 as foreign key.

Its quite a big file and am not sure what should i paste here so am uploading the file in txt.

View -1 Replies View Related

Locks On Partition Table?

May 13, 2011

I have the below questions on the locking mechanism in a partition table. Example I execute the below query,

delete from table_name subpartition (subpartition_name);

In that case,

1. If we perform above query, then which level of lock is happened to the table/partition & sub-partitions?

2. If we perform above query on same sub-partition of the same table with different where clause, what will happen?

3. If we perform above query for same table but different sub partition at same time, what will happen?

View 2 Replies View Related

Table Locks In Exclusive Mode

Jan 6, 2011

Under what scenarios do we lock a table in the exclusive mode.

View 1 Replies View Related

Data Guard :: Setup/ Archive Destination Configuration

Mar 28, 2011

We have a request to configure data guard for databases on the production server. Here is my situation:

We have a backup strategy in place where the backups are being taken on a regular basis. Archive logs are deleted as soon as they are backed up.

My question now is , is there a way of configuring the Data guard in such a way that there would be no change to existing backup strategy (RMAN) and still duplicate the archive logs to another destination and not delete the archive logs by RMAN backup process on that destination while the first destination is deleted.

We will be deleting the logs in the second archive destination using a script which checks if the logs were applied.

View 2 Replies View Related

Data Guard :: Configuring Primary For A Failover (alternate) Remote Destination?

Aug 2, 2012

I have a situation where I want to configure primary database (11.2) with 2 remote destinations. dest_2 is the default and points to a standby on host_2. However, I also want the primary db to continue transporting redo to dest_3 on node_3 when node_2 is taken down (planned or unplanned).

1) Configure the ALTERNATE attribute of dest_2 to point to dest_3.

2) Configure tnsnames client-side failover on primary host to point to 2 nodes (node_2 and node_3).

View 1 Replies View Related

Server Utilities :: Data Pump For Exporting And Importing Extremely Large Data Files

Sep 24, 2010

I am considering all of the capabilities and benefits of using Data Pump for exporting and importing extremely large data files. Would like to know if importing to tape is possible? If so, would the data be accessible if needed later?

View 4 Replies View Related

Large Amount Of Data

Aug 6, 2013

I have oracle 11gr2 database on linux os. It's total sga size is 500mb only. Now, if uses wants read the 1gb of data from database, then there is no sufficient memory in buffer cache. so how it will works. the transaction will get successful or it will fail.And i have another doubt, does oracle can read the data from memory only or it can also read directly from disk. 

View 11 Replies View Related

SQL & PL/SQL :: FOR UPDATE Large Data

Jan 23, 2012

the large data FOR UPDATE in table column ?

claimClob clob:=claim; -- claim large data
v_buf varchar2(1000);
amount binary_integer:=1000;
position binary_integer:=1;

[Code]....

why the FOR UPDATE don't do nothing ?

View 4 Replies View Related

SQL & PL/SQL :: Store Large XML Data In Blob?

May 8, 2010

Since XML-files only contain character data, we could/should store it in a CLOB, rather than a BLOB.

But, One of my friend having a table where a column is defined as bloband came to know that XML data are being stored. I searched for some article with keyword 'How to insert large XML data in BLOB' But did not work.How to store the large xml content in a Blob and How to extract it?

View 2 Replies View Related

SQL & PL/SQL :: Data Of Column Is Large - How To Insert

Oct 9, 2013

I have encountered some problems in SQL I want to create a table with a bunch of prepared data. For ease of use, I choose to generate a SQL file which contains all the sql clauses used to create the table and insert the data. So all the data can only be inserted to a table using sql clause.

My questions:
1) If data of a column is large (for example, 1 M text), how to insert it using SQL, is there a piecewise method.
2) And how can I insert BLOB data using SQL clause.

What I what is to enclose all the operations in a single SQL file, and when the table is needed, just execute this SQL file.

View 2 Replies View Related

Need Clob Variable To Write Large Volume Of Data?

Oct 6, 2010

I am facing a problem with utl_http.write_text in my pl/sql application. My requirement is to write data of size>32k. So I used a clob variable in write_text. But still it is showing numeric or value error when the data size is above 8k.

I have read that chunked transfer encoding will work. But I couldn't find out how this is done.

View 5 Replies View Related

SQL & PL/SQL :: Data Types To Store Large Integer Values?

Aug 15, 2012

what could be effective data type to store large integer values like, 50,000; 10,000,000 etc.?

View 3 Replies View Related







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