SQL & PL/SQL :: Very Slow Access And Updation Of Table Data Of Another Schema
Jun 3, 2010
I am trying to access and modify data of a table of another schema which contains 80,000-90,000 records. My procedure is taking near about 30 mins to complete the operation. faster access and updation of table data.
Details:
I have two schema: TEST and PROD
I am running the below code from TEST Schema.
/* CODE START HERE*/
DECLARE
exc_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (exc_bulk_errors, -24381);
v_block_count NUMBER := 1000;
[Code]....
The above code is taking near about 30mins to process.
I have also tried another approch: Creating a procedure in PROD schema to update COMPONENT_MASTER table and by calling the procedure from above code by passing component code.
/* PROCEDURE CALL FROM ABOCE CODE INTEST SCHEMA*/
PROD.PROCEDURE_TO_UPDATE(v_comp_code);
But still it is taking same time.
View 12 Replies
ADVERTISEMENT
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
Jul 9, 2013
How to get the data access for a temp table ?
View 5 Replies
View Related
Aug 2, 2013
I have created one procedure in SCOTT schema. just i need to see that same procedure information to TEST schema. how its possible.if im using
SELECT DBMS_METADATA.GET_DDL('PROCEDURE','PROC_NAME','SCOTT')
FROM DUAL in scott schema mean i can see that full procedure information,like wise i need to see in TEST schema mean what command i need to use.
View 20 Replies
View Related
Oct 12, 2012
Below is the request i have received from a user -
" Can you setup user <usernmae> in <database>S to have read only access to <applicationname> (CHADBA, CHAS, EMI and SVCM schemas)"
Steps I have followed -
create user <usernmae> identified by password default tablespace USERS temporary tablespace TEMPL;
Grant PRODUCEBILL to <username>;
Grant REPORTING to <username>;
Grant PAYMENT to <username>;
Grant CONNECT to <username>;
My question is as user has request for read only access to specific schemas, so how do I validate them?
View 11 Replies
View Related
Oct 5, 2013
Let's consider such table that all rows fit into single block:
SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin
[code].....
why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2
View 8 Replies
View Related
Apr 15, 2012
I want to get clear with one thing yesterday i installed oracle9i and dev 2000 to my client.
when they run one report they got stuck with pl/sql compilation errorrep-1247
when i checked that report in the report builder, in the query they are using some other table which is not belongs to that schema,then I give that schema.tablename and compiled, but this is coming for other reports also, then only i came to know that they are acceessin other schema also, how can i sort this out.
can i fix this by givin full access privilige or what privilige can i give to get full access of other schema table.
how can i check in the old database what are all the roles and privileges given to this user,
View 5 Replies
View Related
Aug 9, 2011
I need to insert data from one schema table to other schema's table in same database.The thing is columns are not equal.so when I am trying to use insert statement it is throwing error as not enough values. The situation is explained clearly below.The insert stmt is implemented in second schema whose table name is b.
SQL> create table a(ID_A NUMBER,Name varchar2(10),rollno NUMBER,address varchar2(10));
Table created.
SQL> insert into a(ID_A,Name,rollno,address)values(1,'ravi', 101,'hyd');
1 row created.
SQL> insert into a(ID_A,Name,rollno,address)values(2,'rav', 102,'delhi');
1 row created.
SQL> insert into a(ID_A,Name,rollno,address)values(3,'ra', 103,'chn');
1 row created.
SQL> create table b(ID NUMBER,Name varchar2(10),rollno NUMBER,address varchar2(1
0),route varchar2(10),direction varchar2(10));
Table created.
SQL> ALTER TABLE B ADD
2 CONSTRAINT B_PK1
3 PRIMARY KEY
4 (ID);
Table altered.
SQL> create sequence b_seg start with 1;
Sequence created.
SQL> insert into b select b_seg.nextval,lexcom.a.* from lexcom.a,dual;
insert into b select b_seg.nextval,lexcom.a.* from lexcom.a,dual
*
ERROR at line 1:
ORA-00947: not enough values
So,for table b in ID column sequence needed to be inserted and other columns need to be taken from table a.I can understand the error is because two tables are not having equal columns.So,the insert stmt is throwing error.
I can manually write by taking columns from a and b and write insert stmt as follows,but this is tedious process.
SQL> insert into b(ID,Name,rollno,address)select b_seg.nextval,lexcom.a.Name,lex
com.a.rollno,lexcom.a.address from lexcom.a,dual;
3 rows created.
But this is time taking and I had tables which has many columns to be inserted.So is there any other way to solve it and implement insert stmt.
View 6 Replies
View Related
Oct 31, 2011
I would like to create a trigger that will fire whenever any user will access to a Schema of Oracle DB (for each and every login). regardless the access will be through an application or SQLPLUS and this trigger must insert the below information into a table.
1) IP address
2) Machine Name
3) login time
4) logout time
5) name of accessed schema
writing this trigger and creating the table that will hold the required data.
View 6 Replies
View Related
Nov 2, 2012
I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.
Below is the example.
I have created a table employee in abc schema which has two values.
EMPLOYEE
ABC
XYZ
In the above scenario the abc user can only fire select query on the EMPLOYEE table.
SELECT * FROM EMPLOYEE;
He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.
View 10 Replies
View Related
Apr 22, 2013
has copied one table data from Production to any other schema , know which user has done this ?
View 1 Replies
View Related
Nov 2, 2012
I have scenario here.
I want to know how to restrict a user(Schema) from deleting the values from a table created in the same schema.
Below is the example.
I have created a table employee in abc schema which has two values.
EMPLOYEE
ABC
XYZ
In the above scenario the abc user can only fire select query on the EMPLOYEE table.
SELECT * FROM EMPLOYEE;
He should not be able to use any other DML commands on that table. If he uses then Insufficient privileges error should be thrown.
View 6 Replies
View Related
Apr 26, 2010
I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.
How could I do this without using export import. I am using TOAD 9.0.1.
View 10 Replies
View Related
Nov 3, 2012
I had done following steps,
schemas(toy,toys)
1) i open the session of toy schema
First i taken backup of table
create table bck20121103_himan as select * from himan;
Backup table is created.
After taking the Backup table
delete himan;(deleting the records)
2) i log in to another session(toys)
exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/
3) i log in to toy schema
imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y
i tried the above statement it taken so much of time..
Later i tried
I log in to toy session
i rename the table with other name.
later i imported
imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y
it's successfully imported.
View 3 Replies
View Related
Apr 17, 2012
The issue is slow insertion in particular table(i.e A Table) it means insertion in all other tables(i.e B, C, D tables) in same schema is going properly but only when i am trying to insert in one particular table(i.e A table) in same schema it takes long time to complete insertion. Daily insertion is 6000 rows.
I have check all the details like Tablespace size, Analyzing of table, Analyzing of indexes and all. There is no any error alertlog file.
View 1 Replies
View Related
Mar 14, 2012
I have got an issue while uploading the raster data which are in .img format, the upload process is very slow.Previously it was done in 6-8 mins for 10 images but now it is of 30min and even 1 hour too..
View 20 Replies
View Related
Jan 12, 2011
I'm extracting/retrieving the data from the oracle database using Java application it's bit slow. However, when I retrieve from the SQL server it's faster than oracle.
View 6 Replies
View Related
Jul 13, 2012
I have a table called Order_Mstr
order_id order_Desc
-------- ----------
1 Order1
2 Order2
3 Order3
4 Order4
Child tables referring Order_id are:
Order_child1
Order_child2
Order_child3
Order_child4
Order_child5
Order_child6
.
.
.
Order_child50
I have to update Order_id with some 8 digit number generated by some algorithm. And i have to update that value in child tables as well. In how many ways i can do this task. for example..
1) Disabling the constraint and updating Master value and child value.
View 5 Replies
View Related
Dec 11, 2012
I have a issue in updation of data from CLOB to BLOB.
create or replace function CLOB_TO_BLOB (p_clob CLOB) return BLOB
as
l_blob blob;
l_dest_offset integer := 1;
l_source_offset integer := 1;
l_lang_context integer := DBMS_LOB.DEFAULT_LANG_CTX;
[code]........
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 696
ORA-06512: at "SYSADM.CLOB_TO_BLOB", line 11
ORA-06512: at line 6
View 1 Replies
View Related
Sep 4, 2011
If a table(have a primary key) is empty(after truncate),the sql of dml(insert,update) is very quickly,but if the table have many rows about 10,000,000 rows, the dml is very slowly,why?
View 6 Replies
View Related
Feb 13, 2012
I simply want to insert data in CLOB column but the size of the data is almost 1 MB. (Copying from a MS word document)
View 2 Replies
View Related
Apr 2, 2013
I want to execute a db trigger once for updation of more than one record on a single primary key. The updation would be made by a 10g form by using the common tabular data block.
View 1 Replies
View Related
Oct 9, 2013
I have table with 129 million records.
If I just to select count(*) on the table its taking more than a minute in Sql Developer.
The table structure is as below, Primary key is a sequence and then 3 foriegn keys and one non-unique index on the date column.
<Table_Name>
column1 NOT NULL NUMBER ( Primary Key)
column2 NOT NULL NUMBER ( FK1)
[Code].....
View 1 Replies
View Related
Sep 14, 2013
In when-Validate-Item trigger,I am checking a condition like
if %>=90 and days<=7 then status='Y'
else status='N'
It is working fine in 95% cases But in Some cases it is wrongly updating like even if both conditions are true status ='N'
This is happening in user side on rare occasions so what we do currently is ask the user to delete that line and insert it again and it is working
What I need is how to recreate that scenario which is wrongly updating?
View 37 Replies
View Related
Jun 24, 2013
if we update partition key, internally row movement is done as if you had in fact deleted the row and reinserted it. It will update every single index on this table, and delete the old entry and insert a new one. It will do the physical work of a DELETE plus an INSERT. However, it is considered an update by Oracle even though it physically deletes and inserts the row¿therefore, it won't cause INSERT and DELETE triggers to fire, just the UPDATE triggers. Additionally, child tables that might prevent a DELETE due to a foreign key constraint won¿t. You do have to be prepared, however, for the extra work that will be performed; it is much more expensive than a normal UPDATE.
CREATE TABLE MYTEST1
(
MYID NUMBER(10) PRIMARY KEY,
MYFLAG VARCHAR2(1)
) TABLESPACE SIMANG_D
PARTITION BY LIST (MYFLAG)
(
PARTITION P1 VALUES ('A') tablespace DJB,
PARTITION P2 VALUES ('B') tablespace users
);
[code]....
In above I inserted few rows into table and those belong to partition P1 and Tablespace DJB and then I updated partition key from A to B which resulted row movement from partition P1 to P2 which belong to tablespace users. Before updation data has consumed some space in tablespace DJB and after update those records consumed some space in tablespace USERS. But in this scenario I checked that space consumed in DJB is not released. Is there any way to get this space released ?
View 7 Replies
View Related
May 13, 2013
that are my biggest tables
Users use front end (called ESS Console) and when they try to open one of those tables they wait very long (really bad performance). Sometimes the GUI even hanging without displaying results.
Does Partitioned Tables feature works for better performance?
View 3 Replies
View Related
May 30, 2011
I created a data warehouse in oracle 10g n with three Dimension and one cube after that it crates 4 tables . How to use an insert sql statement to insert data in those tables n how to access them.
View 7 Replies
View Related
Aug 10, 2010
I 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]....
View 4 Replies
View Related
Oct 2, 2013
I have the following XML structure:
<TZR Action="Insert" OrgaT="400"> <Division id="4"> <label language="de">ok</label> <label language="en">Live engl.</label> <Productstructure id="300" Sparte="5"> <label language="en">Live engl.</label> </Productstructure> </Division></TZR>
I'd like to get 4, ok, Live engl, 300, 1, Live engl.
using XMLTYPE but how to get
id="4" or Sparte="1" SELECT *FROM XMLTable( '/TZR' passing XMLType(bfilename('XML_DATA_DIR', 'xxx.xml'), nls_charset_id('AL32UTF8')) columns Division_id varchar2(30) path 'Division/id' ); Oracle 11.2.02 on Windows 2008.
View 2 Replies
View Related
Oct 25, 2010
I would like my application to have access to 2 different data sources [reached by two different database links]:
- DS_ONLINE that would be the main data source dedicated for an ONLINE access
- DS_OFFLINE, consisting of some materialized views which would refer to the objects of the DS_ONLINE data source, maintained in case of the DS_ONLINE's temporary inaccessibility A flag DS_ONLINE_ACCESS_FLAG indicating the DS_ONLINE's accessibility would be maintained.
I would like to make the choice of the current data source TRANSPARENT to my PL/SQL application [I wouldn't like the logic of determining the current data source to be embedded in my application code]. How can I do it?
I thought I could write the code as follows:
OPEN CURSOR c FOR
SELECT A, B, C FROM TABLE_SYNONYM;
Where the definition of the TABLE_SYNONYM would change in function of the DS_ONLINE_ACCESS_FLAG flag.
It could be done as DDL statement of "create or replace synonym ..." placed in a procedure dedicated to set the DS_ONLINE_ACCESS_FLAG flag...
...but I'm not sure if it is going to work and even so
View 2 Replies
View Related
