I recompile a package in a session,and found the session is hang, I know there are have others session run the package,how can i find what session run by the package?
View 2 Replies
We a running oracle9i database and forms 6i application.
We have 4 gb ram size and sized the sga for 700mb pga for 400mb for past 4 months there is no problem.
Now from last week we encounter the server hang, and we do restart. How to check reason for hanging.what are all the check list to carried out.
when i modify a column of table tb_hxl_user,just as flowing:
Alter Table TB_HXL_USER Modify CREATE_DATE Not Null
now i select the table TB_HXL_USER,just as flowing:
select * from TB_HXL_USER;
it is hang,and i found the wait event is library cache lock in v$session_waits,why?how can i avoid this.
My server RAM is 12GB. i installed 10gr2 and implemented /3GB also. Eventhough my database is giving problem.
View 20 Replies View Relatedall of the client cann't connect the db,and using sqlplus / as sysdba also cann't login there are many error of TNS-12535 and in the listener.log
alert.log:
Sun Apr 29 18:10:36 2012
opidcl aborting process unknown ospid (42992060) as a result of ORA-604
Sun Apr 29 18:10:54 2012
opidcl aborting process unknown ospid (26280196) as a result of ORA-604
[code]...
When I am issuing below command from auxiliary database, getting ORA-00554.My tnsping is okay from both the side, remote login is exclusion. Password file is also okay.I ran UTLRP.sql twice to compile invalid packages.
From Auxiliary Database
[ashish@localhost dbs]$ rman target sys/oracle@suman auxiliary /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 11 19:04:03 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges
We are considering upgrading our current system from Oracle 9i to 11g. The current operating system is SUSE Enterprise 9.
The 11g requirements say an update to SUSE Enterprise 10 is required to support the Oracle 11g installation.
Will I need to recompile my existing application programs which use pro C precompiler to create code that accesses the Oracle database when I upgrade the operating system and database?
I am using: Oracle SQL Developer (3.0.04) Build MAin-04.34 Oracle Database 11g Enterprise Edition 11.2.0.1.0 - 64bit Production Sample dataTable
with t as (
select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',101, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 106, 105, 0, 'origin', 150, 200 from dual union all
[code]..........
Is there a way to check in that date grouping for matching ticket_origin and ticket_destination when there may be two or more rows difference between them that does not allow me to use Lead or Lag function. Is it also possible do so without using the amount column? I also would like to identify if they are in the same area when paired (this I believe works after getting table sorted like so below then use lead lag after having the order by done) I am trying to get something like this table with results as
select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124.', '124._', 105, 0, 106, 'destination', 150, 300 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '127_', '127', 108, 108, 0, 'origin', 50, 600 from dual union all
[code]...........
Version 10.2.0.4
CREATE TABLE T1 (I INT PRIMARY KEY);
CREATE TABLE T2 (I INT PRIMARY KEY, J INT);
ALTER TABLE T2 ADD CONSTRAINT T2_FK FOREIGN KEY (J) REFERENCES T1(I);Session 1
INSERT INTO T1 VALUES (1);Session 2
INSERT INTO T2 VALUES (1,1);Session 2 hangs. Why ?
I'm facing a problem with the starting of java programs on my Win XP system. The startup most often takes from 5 to 60min. Only in rare cases java programs start-up normally. I've these problems with Eclipse, Nitro and ProM which are all Java-based tools.
I have used STraceNT to find the cause of my problems but I couldn't figure it out. I've attached the STraceNT log file for the execution of ProM. The program runs until the last line in the log while starting without showing up anything. After 20min suddenly the programs shows up and works as it normally does. The last line in the log where the software hangs for this long time is a call to WaitForeSingleObject. Before that it seams to allocate memory. I could see similar patterns in the executions of the other java programs, but I still can't see where the problem lies. I also tried removing the JRE and install different versions.
May any one confirm that the below issue is not only to RAC but also on normal database enterprize edition 11.2.0.3 ? Bug 12865902 - NOWAIT lock requests could hang (like Parallel Queries may hang "enq: TS - contention") in RAC (Doc ID 12865902.8)
currently i am using for update nowait concurrently for 50 processes on similar table with similar query and getting performance issue.
SELECT txn_ID,status_FLAG
INTO txn_id,status_flag
FROM cp_txn_detail WHERE txn_ID = pi_txn_ID FOR UPDATE NOWAIT;
Just now my database is hang due to the archivelog full. I try to use RMAN to delete the expired one and free space but encounter the error:
RMAN>crosscheck archivelog all;
archive log filename=/geodata/oraclelog/archive_log/ORAGF45/archivelog/2013_06_04/o1_mf_1_1444_8tsxvwc2_.arc recid=1350 stamp=817182450
validation succeeded for archived log
archive log filename=/geodata/oraclelog/archive_log/ORAGF45/archivelog/2013_06_04/o1_mf_1_1445_8ttx2q6j_.arc recid=1351 stamp=817214416
[code].......
So I try to back it up first by using the below command:
RMAN> backup archivelog all;
Starting backup at 04-JUN-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
[code]......
I know that I have to extend the db_recovery_file_dest directory for "limit exceed" error but how can I safely delete the archivelog in order to bring up the database?
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 need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
We would take backup regulary by RMAN(catalog). Last two days we could not take backup. Backup start but after take backup some datafile then rman does not do anything.
View 4 Replies View RelatedI have a system that has issues with sending some emails.
When the email sending process is activated the pages just hang.
Are there any logs or anything like that anywhere that i could look at to try and make sense of it.
We had several unexplained lock-ups of APEX in our environment in the last months. This all happened after we moved to GlassFish with the APEX Listener. The symptoms were always that all connections in the JDBC connection pool were in use and we had to restart the GlassFish server instance to free them up.
The culprit is the caching folder that the APEX Listener uses for file caching. We use this to cache image downloads from the database through a procedure, and the caching folder was set to /tmp/apex/cache. I believe this is the default setting. All went well, when the procedure was called the images were downloaded from the database and cached under /tmp/apex/cache. However, for some (as yet unexplained) reason, Linux sometimes removes this folder from the /tmp file system. This seems to happen from time to time after an unknown period of inactivity. We didn't reboot the server. After the folder is gone, the APEX Listener is unable to recreate it, keeps on creating new database connections until the maximum configured is reached, and we need to restart the GlassFish instance to free them up. This also recreates the caching folder.
Our environment: GlassFish 3.1.1+3.1.2, APEX Listener 1.1.3, Oracle Linux 6.2. I didn't try out the new APEX Listener 1.1.4 yet so I'm not sure if this has been fixed in the last version.(kind of reminds me of the problem where the APEX config file is by default placed under /tmp and also disappears from time to time)
I am getting the below error when i am trying to import the dmp file into my oracle 11g.
impdp system/password@orcl dumpfile=aaa.dmp directory=datapump remap_schema=dev:user
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_
LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 949
ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"
I have written a shell script that will execute a procedure. The input parameters are constant.
#!/bin/ksh
sqlplus user@server.com<<EOF
set serveroutput on;
var Return_Code number;
var Return_Message varchar2(4000);
exec test_pkg.Insert_test('IDD', null, 'BATCH',:Return_Code, :Return_Message);
Now I have to call same procedure but the input is a csv file.
Is there a way to call a procedure using csv file, I cannot load the table using SQL Loader because there is a complicated logic.For every row in CSV there should be 3 rows inserted into table and 2 rows updated.
As of JAN 2, three rows have to be inserted with dates JAN2, JAN 3, JAN 4 and 2 rows ( 3ODEC and 29DEC have to be updated). Also these days have to be business days.
So all this code is in procedure that uses a DB2 package for business dates.Instead of using sql ldr , if would like pass the csv file as input param.
I am trying to restore a package only from a dump file that was exported by original exp command. I know you can do it with data pump, but unfortunately this is dump file is not exported by expdp. Can I do that. I really do not want to import the whole database.
View 5 Replies View RelatedI have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
I am try to import 4G dump in Oracle 11R2 version, in that we have around 9000+ Package Body which is taking huge time than other objects (about 8 to 12 hrs) and also it is expecting lots of system space (roughly about 10GB).
I have tried both parallel and non-parallel.how to improve speed of the package body import.
Details about the Schema & Import No. of objects in Schema
SQL> select object_type,count(1) from user_objects GROUP BY ROLLUP( object_type);
OBJECT_TYPE COUNT(1)
------------------- ----------
FUNCTION 248
INDEX 5161
JAVA CLASS 471
JAVA RESOURCE 1
JAVA SOURCE 16
LIBRARY 1
ORA-00933: SQL command not properly ended
i'm a student currently learning database administration security.
I need to create a tablespace for administration of database but i don't know what datafile settings are best suited for admin usage.
I have attached the schema that was given to me for this assignment.
My manager is asking to create a SQL scrip, which will provide following information.
Our database is 9.2.0.7.0 on Windows 2000 Server
-How many records Insert / Update or Delete daily in my Oracle database?
- Check Archive log / Redo Log switching information?
-Check database growth ?
i am trying to install Oracle 10.10.2.0 on Windows Server 2003 standard x64 Edition Service Pack, but when i try to run the installer or open DVD it gives me below error.
"The image file D: is Valid, but is for a machine type other than the current machine."
I was trying to delete the database in the test server. When i was deleting listener was already stopped, i continued deleting using dbca, it shown me some alert that datafiles cant be deleted because system could't find database, since listner was stopped so only service was deleted(the one showing in the windows administrator toolsservicesOracleServiceTEST).
All the datafile parameter files are still there. How can i delete the datafiles and parameter files belongs to that database or how to create the deleted service, so that i will start the listener and do the complete deleting of the database.
I have to migrate production database version 10gR2 on windows 2003 server to oracle 11gR2 on windows 2008 server . So far i have just installed the oracle software on 64bit windows 2008 server. How to move the data or migrate from old server to new server .
View 22 Replies View Relatedafter a long time,(i never remember when was the last time i installed 9i)i was asked to install 9i.So, whether i install server or cilent i get this error <attachment>
after i ignore it and finished the installation,The server installation gets abruptly ended for the client i get TNS errors.
I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.
1. My database runs on different server.
2. I invoked "sqlplus" from another unix box to login to the database.
3. I found that the process id (ps -ef |grep sqlplus).
4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?
SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID,
SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM,
SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID
FROM
SYS.GV_$PROCESS, SYS.GV_$SESSION
WHERE
SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST'
and SYS.GV_$SESSION.MACHINE like '%hostname%'
While creating the index we are getting the error "ORA-00603: ORACLE server session terminated by fatal error".
We have the space in tablespaces and also in the file systesm.
