SQL & PL/SQL :: Script Hangs When Deleting From Table

May 24, 2010

I'm testing a procedure which loads data into my database, and after each test I want to empty some of the tables and reset the sequences. I have this script to do that...

DELETE FROM COM_MERGE;
COMMIT;
DELETE FROM COM_TITLE;
COMMIT;
DELETE FROM COM_ISSUE;
COMMIT;
DELETE FROM COM_PAGE_ELEMENT;
COMMIT;
DELETE FROM COM_ELEMENT;
COMMIT;
DELETE FROM COM_STORY_TITLE;
COMMIT;
BEGIN
COM_RESET_SEQUENCES;
END;

Today I added the call to the sequences procedure to my script, but I have been using the script to delete from tables for a number of days without problem.However today I am finding that when I run the script it works ok the first couple of times, but when I try running it for a third time, it hangs after the second delete (in other words it stops when it gets to the delete from COM_ISSUE). After this happened the first couple of times I stopped the db and restarted it, then the script was ok twice, but again I'm finding that the script hangs. There is no error message, but the script fails to complete.

I didn't know if it was because originally I had one commit at the end of the script, so I added commits after each delete but that didn't solve it.I am using SQL Developer but I have found the same problem when running the script from SQL Plus.This is the definition of the COM_ISSUE table (just in case the table is the source of the problem).There is only one row in COM_ISSUE.

CREATE TABLE "BILL"."COM_ISSUE"
(
"CI_ID" NUMBER NOT NULL ENABLE,
"CI_TITLE" NUMBER NOT NULL ENABLE,
"CI_DATE" NUMBER NOT NULL ENABLE,
"CI_PRICE" NUMBER NOT NULL ENABLE,
"CI_PUBLISHER" NUMBER NOT NULL ENABLE,
CONSTRAINT "COM_ISSUE_PK" PRIMARY KEY ("CI_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536
[code]....

View 9 Replies


ADVERTISEMENT

Server Utilities :: IMPDP Hangs On Table Data?

May 26, 2010

I'm trying to do a network datapump between oracle databases, and it seems to continually hang when it gets to the point where it should be processing table data.

C:>impdp DP_USER/DP_USER parfile=sde_webmap_2.par

Import: Release 11.1.0.7.0 - 64bit Production on Wednesday, 26 May, 2010 17:42:03

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DP_USER"."SYS_IMPORT_FULL_01": DP_USER/******** parfile=sde_webmap_2.par
Estimate in progress using BLOCKS method...

[code]...

It just sits at this point indefinitely.The parfile for those interested:

directory=datapumps
logfile=sde_webmap_2.log
network_link=backup
full=y
INCLUDE=SCHEMA:"IN ('SDE_WEBMAP_BUSINESS','SDE_WEBMAP_BUSINESS_A','SDE_WEBMAP_BUSINESS_B')"

And the results from V$SESSION_LONGOPS

69 SYS_IMPORT_FULL_01 IMPORT 0 1031 MB 5/26/2010 5:50:37 PM 5/26/2010 6:03:29 PM

View 3 Replies View Related

Server Utilities :: Oracle Table Export Hangs

Nov 29, 2010

I got a problem while exporting schema. Export hangs on a particular table table. i checked the table structure and integrity

(ANALYZE TABLE user.table_name VALIDATE STRUCTURE CASCADE; )

and it seems all okay.

View 5 Replies View Related

SQL & PL/SQL :: Deleting Partition Of A Table

Nov 19, 2012

create or replace
Procedure ReadingsPurge
As
v_sql varchar2(500);
v_date date;
p_count NUMBER;

[Code]...

-- Code below drops partitions that are older than the NoOfDays Parameter
OPEN c1;
LOOP
FETCH c1 INTO v_partition_name, v_high_value;
EXIT WHEN c1%NOTFOUND;

[Code]....

Above code is compiling successfully.

After I added the lines makred in the red font, when I tried to execute the stored procedure, I got an error

Error starting at line 1 in command:
execute ReadingsPurge
Error report:
ORA-00933: SQL command not properly ended
ORA-06512: at "CDC_USER.READINGSPURGE", line 30
ORA-06512: at line 1
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:

View 2 Replies View Related

Deleting Huge Records From Table?

Apr 29, 2013

Consider tables A,B,C,D,E,F. all are having 100000++ records Tables B,C,D are dependent on table A (with foreign key constraint). When I am deleting records from all tables, table B,C,D are taking max 30-40 seconds while table A is taking 30-40 mins. All tables are having indexes.

Method I have used:

1. Created Temp table

2. then deleted all records from B,C,D,E,F for all records in temp table for limit of 500.

delete from B where exists (select 1 from temp where b.col1=temp.col1);

3. why it is taking too much time for deleting records in table A.

View 5 Replies View Related

SQL & PL/SQL :: Deleting Duplicate Combination Of Records From Table?

Sep 29, 2011

How can I delete the duplicate combination of records from the below table.

CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);

[code]....

The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.

Expected result after deletion

GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26

View 5 Replies View Related

PL/SQL :: Deleting Large Number Of Rows From Table

Apr 30, 2013

Consider tables A,B,C,D,E,F. all are having 100000++ records Tables B,C,D are dependent on table A (with foreign key constraint). When I am deleting records from all tables, table B,C,D are taking max 30-40 seconds while table A is taking 30-40 mins. All tables are having indexes.

Method I have used:

1. Created Temp table

2. then deleted all records from B,C,D,E,F for all records in temp table for limit of 500.
delete from B where exists (select 1 from temp where b.col1=temp.col1);

3. Why it is taking too much time for deleting records in table A.

Is there any thing that during deleting data from such master table, it is referring to all dependent tables even if dependent data is not present ?

View 12 Replies View Related

SQL & PL/SQL :: How To Restrict User (Schema) From Deleting Data From Table

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

PL/SQL :: How To Restrict User (Schema) From Deleting Data From A Table

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

SQL & PL/SQL :: Stored Procedure Just Hangs?

Nov 20, 2011

I entered the following procedure code into SQLPLUS for compilation, but it just hangs. I suspect the cause is an infinite loop, but I can't locate it.

CREATE OR REPLACE PROCEDURE populate_sales_fact
AS
BEGIN
INSERT INTO sales_fact
(orderid,
prod_key,
order_day_key,
shipping_day_key,
sales_dollar_amount,
quantity,
cust_key,
emp_key)
[code]....

View 11 Replies View Related

RAC & Failsafe :: Database Creation Hangs - RAC On SP2 On AIX-4.3.3?

Jan 17, 2003

Creating RAX 9.2.0 on SP2-AIX-4.3.3 64 bit hangs (2 node configuration) tablespacess are on VSD/raw partitions.

View 3 Replies View Related

Database Hangs On Performing Logswitch

Oct 16, 2012

Iam usin 11.2.0.3 database on linux 64 bit machine

I have got the report from the users that the dataload operation which they perform in the database is taking normal than the expected time

I rebooted the server and started the database i can see that the database startup is even taking more time

The memory allocated is sufficient and when i tried to perform the logswitch the database simply hangs

alertlog contents after restart
---------------------------------------

minact-scn: useg scan erroring out with error e:12751
Tue Oct 16 11:02:03 2012
minact-scn: useg scan erroring out with error e:12751
Suspending MMON action 'Block Cleanout Optim, Undo Segment Scan' for 82800 seconds
Tue Oct 16 11:12:47 2012

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of ASHSIZE to a sufficiently large value. Currently, ASH size is 50331648 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:

select total_size,awr_flush_emergency_count from v$ash_info;
Tue Oct 16 11:17:53 2012
Suspending MMON action 'undo usage' for 82800 seconds
Tue Oct 16 11:28:05 2012
Suspending MMON action 'metrics monitoring' for 82800 seconds

View 3 Replies View Related

Server Administration :: Drop Index Hangs On SAP?

Apr 14, 2010

On a SAP system, am trying to drop six indexes, largest is 300MB and smallest is 50MB.

I tried running drop index sapusername.index_name on the 50MB index via SQL*Plus and it seems to be taking forever. anything I can check on the database on why it is taking such a long time?

I can leave it to run overnight but worried that when I come back the next day, it will still be hanged. Is there any quick way of dropping the index, .i.e. drop immediate ...

Am not using SAP's BRTOOLs as it is also hanging from there and the SAP-ADMIN had approved for the DBA to drop it from our end instead.

View 4 Replies View Related

Server Utilities :: Expdp Hangs In Oracle 11g?

May 27, 2011

I am trying to export Schema using expdp command. but its going hang after few minutes. it seems that it stucks any where. Even I am trying with normal scott schema it is also hanging.

View 16 Replies View Related

Networking And Gateways :: Listener Hangs After Starting Up

Apr 28, 2011

I just installed oracle 11g on a fresh RHEL 5.2 installation on virtual box.

The database is up, but the listener (LISTENER) hangs after a minute from starting it, and then any lsnrctl command just hangs without showing any error messages, when I restart the virtual machine and then start the DB and listener, it works for a minute and it hangs again!

View 2 Replies View Related

Forms :: Hangs With Large Text Items

Oct 4, 2010

I'm facing a strange problem with one of my client.

Sometimes the application hangs when they are typing in text item defined with 2000 or 4000 char long.

The application needs to be close manually.

The first time we faced the problem, we tought it was due to database locks but the DBA confirmed that everything was OK.

The OAS admin also tried to log the application but nothing special. So we tried to run the application using JRE instead of JINITIATOR and the problem disappeared.

So, my question is, is there known bug about using large text item in Forms 10G? and especially with JInitiator? I tried to find informations on metalink but found nothing.

View 1 Replies View Related

Forms :: Hangs While Doing A Shift Tab On Radio Button?

Sep 12, 2012

When i open the form,select a radio button and immediately do a SHIFT+TAB form hangs.

After selecting the radio button if i do a TAB and then do shift+tab it works fine for me.

There is no KEY-PREV-ITEM trigger in radio group.For my testing i have also written go_item in this trigger,but still it fails.

View 3 Replies View Related

Database Hangs In Shutdown Immediate For Cold Backup

Jan 28, 2013

we have scheduled cold backup for our database. is there any solution to resolve below mentioned errors other than RMAN hot backup or shutdown abort ?

Sat Jan 26 00:20:55 2013
Active call for process 3939 user 'oracle' program 'oracle@proddb.fgho.com'
SHUTDOWN: waiting for active calls to complete.

Sat Jan 26 01:15:59 2013
SHUTDOWN: Active sessions prevent database close operation
Instance shutdown aborted

View 9 Replies View Related

PL/SQL :: Bulk Collect Update Data Just Hangs?

Aug 3, 2012

optimize this code. Scenario have to update about 40 million rows to static value, I'm committing 1Million rows in one loop. The first 1 millions rows are getting updated very fast probably in a 2 minute, after that the code just hungs and i don't see increase in committed rows.

Declare
cursor c1 is
select rowid from t1
where c1 is not null;

[Code]....

View 2 Replies View Related

DBCA Wizard  Hangs After Clicking Next On Welcome Screen?

Oct 4, 2012

I am facing problems with the data base configuration wizard.The welcome screen opens on clicking the launch iconbut the moment i click on the next button it hangs

View 3 Replies View Related

Data Guard :: Archiver Hangs When Standby - Database Is Not Available

Oct 10, 2013

we have two primary databases (Oracle release 9.2.0.8, HP-UX 11.31) installed on the same server. The corresponding standby-databases (Oracle release 9.2.0.8, HP-UX 11.31)are configured on one server, too.It happens once in a while that the server of the standby-databases struggles and performs a shutdown. The primary- and standby-databases are configured with Data Guard. If the server of the standy-databases is not reachable anymore that should not really cause any problems,meaning that both primary databases can be used as usual. One of our primary database behaves like this: the applications can connect to the database and work like as expected. The other primary database behaves different: the applications can't connect anymore. But the database is still up and running. When looking at the alert.log file the following messages can be found:

Fri Oct  4 03:19:51 2013ARC1: Evaluating archive   log 3 thread 1 sequence 335372ARC1: Unable to archive log 3 thread 1 sequence 335372      Log actively being archived by another processFri Oct  4 03:20:51 2013ARC1: Evaluating archive   log 3 thread 1 sequence 335372ARC1: Unable to archive log 3 thread 1 sequence 335372      Log actively being archived by another processFri Oct  4 03:21:51 2013ARC1: Evaluating archive   log 3 thread 1 sequence 335372ARC1: Unable to archive log 3 thread 1 sequence 335372      Log actively being archived by another process Fri Oct  4 03:22:51 2013ARC1: Evaluating archive   log 3 thread 1 sequence 335372ARC1: Unable to archive log 3 thread 1 sequence 335372   Log actively being archived by another processFri Oct  4 03:23:51 2013ARC1: Evaluating archive   log 3 thread 1 sequence 335372ARC1: Unable to archive log 3 thread 1 sequence 335372      Log actively being archived by another processFri Oct  4 03:24:51 2013ARC1: Evaluating archive   log 3 thread 1 sequence 335372ARC1: Unable to archive log 3 thread 1 sequence 335372     

Log actively being archived by another process Trying to connect to the database is not possible - the database seems to be busy with something... We have two archiver processes for each primary database. One process of the primary database which can't be reached anymore seems to hang. Executing the command SQL> alter system archive log all;seems to run forever. Instead of shutting down ands starting the instance killing one of the archiver-processes solves the issue. Maybe some of you have made the same experiences? I do not understand why the one database doesn't have any issues (which is expected behaviour) but the other database is not reachable anymore due to some issue with the archiving process.

View 5 Replies View Related

Client Tools :: Spool Out Hangs After Successful Execution - How To Avoid It

Oct 17, 2011

My SQLPULS script below generates 2 million records and it works fine without any errors, which I run from my batch file. (sqlplus -S %CONNECT% @"SCRIPTSmysql.sql"

The script itself runs 2 hours and exits via sqlplus.exe properly without any errors based on errorlevel from my batch and proper generated data. However, I have noticed that spool output file stops generating any records after 1 hour. It appears that client sqlplus.exe is sleeping or verifying something after spooling completed exactly the same period of time that takes to generate my out file. Is there a way to configure my script to avoid this 1 hour sleep/verification process?

whenever sqlerror exit sql.sqlcode

alter session set current_schema=blah;
/
set linesize 1000
set feedback off

[Code]....

View 3 Replies View Related

Forms :: Hangs When Scroll Bar Reaches Last Record In Detail Block

Aug 9, 2010

I'm having a multi-record detail block in my form and after querying the form, when I scroll through the scroll bar, it is hanging (freezing) just before reaching the last record.

I have noticed that it never hangs if I use arrow keys to scroll between the records. It only hangs when I use scroll bar or page down key.

Regardless of the number of records fetched, it only hangs when I click the scroll bar (or press page down key) to fetch the last record.

It happens to all my custom forms and I'm developing this for Oracle EBS Apps 11.5.10.2. Oracle JInitiator Version is 1.3.1.21 and my form builder is Forms 6.0.8.28.0.

View 9 Replies View Related

Server Utilities :: Export Hangs At Exporting Cluster Definitions?

Dec 22, 2010

Sunddenly my exports hangs at 'exporting cluster definitions'. I had been using this database since last 4 years and it never cause a problem or hangs at this level. here i'm pasting my screen details. it is my production db.

[oracle1@wbh_as1 smbshare]$ exp wb/wb

Export: Release 9.2.0.1.0 - Production on Thu Dec 23 00:02:44 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > wb

(2)U(sers), or (3)T(ables): (2)U >

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user WB
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user WB
About to export WB's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions

View 11 Replies View Related

VNC Hangs While Installing 11.2.0.3.0 After Giving Finish Button To Install Database

Sep 20, 2012

My VNC Hangs after completing all the steps in dbca while installing 11.2.0.3.0 after giving finish button to install database.

Completed All software installation.

Red Hat version: Red Hat Enterprise Linux Server release 6.2 (Santiago)

Linux xxxxxxx.xxxxx.net 2.6.32-220.el6.x86_64 #1 SMP Wed Dec 7 10:41:06 EST 2011 x86_64 x86_64 x86_64 GNU/Linux

View 3 Replies View Related

Precompilers, OCI & OCCI :: Multithread Program Hangs In Oracle Library On Itanium

May 13, 2005

my Multithread-Program hangs in an oracle library while passing a prepare statement (also if i try an EXEC SQL SELECT statement...). Something seems to be wrong in the Program, but i cant's find the Error.The Program is build with:

proc parse=none code=cpp mode=oracle hold_cursor=yes maxopencursors=20 THREADS=YES sqlcheck=full iname=Precomp_all.pc oname=Precomp_all.h

problem: a first thread creates the connection to oracle with one global context (type sql_context) in the connection object:

connect()
{
EXEC SQL WHENEVER SQLERROR DO ORASQL_ERROR();
EXEC SQL WHENEVER SQLWARNING DO ORASQL_WARNING();
EXEC SQL WHENEVER NOTFOUND DO ORASQL_NOP();
[code]....

View 3 Replies View Related

Forms :: Installing Weblogic On Windows 7 X86 Machine / Restarting Admin-server Hangs

Jul 5, 2011

Im trying to install Weblogic/Forms on a Windows 7 x86 machine. Its for the developers to use.But when I run the config.bat and is creating the domain and installing Forms builder and the other stuff the installation hangs at: step restaring admin server started.

Log:

Stopping Derby Server...
Starting AdminServer
Starting the domain ...
DEBUG : Loading the 32 bit dll here
DEBUG : Loading DLL : D:/oracle/middleware/as_1/install/config/StartUtil.dll
DEBUG : Loaded DLL : D:/oracle/middleware/as_1/install/config/StartUtil.dll

View 1 Replies View Related

SQL & PL/SQL :: Trigger For Deleting

Jun 4, 2010

I have a table which has a trigger defined as such on the Main table.

before INSERT OR UPDATE OF USER_NM,COmpany_ID,UPDATED_BY_USER
OR DELETE ON MAIN TABLE OLD AS OLD NEW AS NEW
FOR EACH ROW

When I insert the 1st row (by user Scott)

MAIN TABLE

USER_NM CompnyID UPDATED_BY_USER
----------------------------------------------------------------
ABC1Scott

In the audit table I have this information:

USER_NM CompnyID UPDATED_BY_USER OPERATION_PERFORMED
----------------------------------------------------------------
ABC 1 ScottInsert

When an ADMIN user (other than Scott deletes the above row)

MAIN TABLE:

USER_NM CompnyID UPDATED_BY_USER
----------------------------------------------------------------row deleted....

In the audit table i have 2nd entry as:

USER_NM CompnyID UPDATED_BY_USER OPERATION_PERFORMED
----------------------------------------------------------------
ABC 1 ScottInsert
ABC 1 ScottDELETE

Here the updated_by_user is Scott and not ADMIN, even though I am doing the following in the trigger.

UPDATED_BY_USER := sys_context('USERENV','OS_USER')

I want to show the user who has deleted the record which should be ADMIN.

Since the user deleting may not be the samae as the one who inserted.Any clue?

View 3 Replies View Related

Best Way Of Deleting Data In Millions

Feb 11, 2013

I am trying to delete the one month data from the table , which contains the end customer sales data.The total data count in the table is 30 crores. And the data of one month is nearly 10 Crores.I am using oracle 10g , The date field to be used in the condition of delete is indexed.

best way to delete such huge data count.

View 2 Replies View Related

Deleting All Tables And Constraints

Oct 19, 2006

After creating all the tables and the constraints, and inputting data to the table.. i want to delete everything. i try using drop table but it doesn't get rid of the constraints.

View 10 Replies View Related







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