Oracle Database 10g (not 10g R2). Please also tag oracle for search purposes.

learn more… | top users | synonyms (1)

2
votes
3answers
48 views

Count is slow Oracle

this is my query : SELECT Count(last_upd) FROM S_CONTACT WHERE last_upd_by = '0-1' AND LAST_NAME <> 'Wait' AND last_upd + 1/24 > SYSDATE - (1 / (24 * 60)); It takes 84 seconds to return ...
0
votes
1answer
24 views

ora-13506 An attempt was made to perform operation with an invalid Start/End Snapshot Pair During AWR Creattion

When I'm create AWR Report then following error occurred plz help. Cause: An attempt was made to perform operation with an invalid Start/End Snapshot Pair.
0
votes
0answers
33 views

Find Bottleneck of Store procedure in Oracle 11g

I written a store procedure which retrieve data from 6 to 7 table using join and used sys-refcursor. Now problem is data fatch approximate 700 to 750 rows at 18 seconds but in some case take more ...
-1
votes
0answers
66 views

How to run an exe from an oracle trigger

I am new to Oracle came from SQL Background. I want to run an exe using trigger in oracle. As there is no direct way to do this, I have created a java procedure to run a trigger. my code is as ...
-2
votes
2answers
43 views

Remove oracle home base in windows

How to manually remove multiple the oracle home base in windows inorder to start with a fresh install of oracle server. I had unsuccessful attempts in deleting it using the Universal Installer as it ...
2
votes
0answers
225 views

Install Oracle 10.2.0.4 on Windows Server 2012 R2

I have problem with installing Oracle 10.2.0.4 on Windows Server 2012 R2. We are running old 10.2.0.4 instance on Windows Server 2008 R2. Now I have to migrate it to the Windows Server 2012 R2. ...
-1
votes
1answer
24 views

Uninstall Oracle

How to uninstall oracle 10g XE from pc. I have tried uninstalling from Control Panel>Programs but it didn't work. I have also deleted registry folders in Registory Editor but it didn't work. Actually ...
0
votes
2answers
35 views

PL/SQL block running without printing any output

I'm trying to exexcute a pl/sql block using dbms_output to obtain a console output but without reason the unique answer by SQL developer is 'Anonymous block finished'. declare v_count integer; ...
0
votes
2answers
46 views

More Efficient Use of Union

Looking for a more efficient way to achieve what my view is doing. This view joins tables to themselves to show where each part in the database is used. In other words if I am a part of a part list ...
0
votes
1answer
34 views

How to solve database is not mounted

SQL> SQL> alter database mount; alter database mount * ERROR at line 1: ORA-00204: error in reading (block 3, # blocks 8) of control file ORA-00202: control file: ...
0
votes
0answers
23 views

Killed wrong session and cannot connect to oracle anymore

I guess I killed the wrong session. Trying to connect with SQL Developer, it says ORA-12505. Search results say you may set wrong SID, but that's not for my case. How can I get my oracle back? My ...
0
votes
1answer
48 views

Cannot fix ERROR:ORA-01031: insufficient privileges

I have tried almost every solution on the internet however I still cannot fix this problem. I cannot login as sysdba every time I try SQL>connect /as sysdba I get the error ERROR:ORA-01031: ...
1
vote
3answers
76 views

Whether Cold backup method can be used to “migrate oracle DB”?

I have to migrate one database. I have exact server replica. (Same directory structure on target as well as source database) Previously I have performed DB migration using export/import datapump (As ...
1
vote
1answer
22 views

Whether Cold backup method can be used to “migrate oracle DB”?

I have to migrate one database. I have exact server replica. (Same directory structure on target as well as source database) Previously i have performed DB migration using export/import datapump (As ...
1
vote
2answers
44 views

Sync data between 2 databases Oracle 10g

I have 2 databases DB_LIVE and DB_NEW, both have different credentials. I have a Data Read access on DB_LIVE and I am owner of DB_NEW which is a completely blank and raw db provided to me. I want to ...
0
votes
1answer
58 views

Increase sga_max_size on Oracle 10g Windows

i would like to increase the max memory size of one Oracle 10g database installed on Windows. The v$parameter shows sga_max_size 452984832 and sga_target 452984832 Note that the ...
0
votes
0answers
28 views

Set a variable outside loop and fetch it every item of loop

My oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi I'm trying to set a variable outside loop and make its value change as the each item of loop. (Just like we ...
0
votes
0answers
42 views

Properly configuring Oracle Charset and NLS parameters

I'm trying to figure out the reason why Oracle SQL Developer shows question marks/rubbish on non-latin symbols. Currently I have 2 DBs on separate severs/instances, one of which works fine, and the ...
0
votes
0answers
48 views

Optimizing DB for performance by archiving/partitioning

I have a table in my Database which would be loaded with huge amounts of data each day. These are basically status of the orders customers has placed. The UI in my application which calls the DB for ...
0
votes
0answers
41 views

automate process of importing excel data into Toad

Is there a way to automate the process of importing an excel sheet into a table using Toad? It should be like an import process, that means, it must start only when triggered, and shouldn't be a ...
0
votes
2answers
60 views

Is it possible to downgrade oracle 11.1.0.6.0 to 10.2.0.4.0?

Is it possible to downgrade oracle 11.1.0.6.0 to 10.2.0.4.0? The Compatible parameter need to be changed to "10.2.0"? Today I have Compatible set to 10.2.0.4.0. It's not clear on support.oracle if I ...
2
votes
1answer
56 views

I can't select directly from a table I created

My client created a user in his Oracle instance for me to install the objects I have created in my local instance. However, this strange behavior prevents me to deliver my project. I guess it's a ...
1
vote
1answer
226 views

Export Data Pump skipped packages bodies

I've got a BASH script for exporting from our Oracle 10G database. #!/bin/bash #This script should be run with the Oracle account. It exports the necessary files from the database for creating a ...
1
vote
1answer
17 views

Missing Oracle Schema in SSMA Object Explorer

We are currently working on a project to move all of our disparate DBs (MySQL, Oracle, older versions of MS SQL) to SQL Server 2014. So far, pretty much everything has gone smoothly, however, there is ...
0
votes
0answers
60 views

Oracle database doesn't go to upgrade mode

I am trying to upgrade/migrate from 10g to 11g. When I start the database in upgrade mode with: startup upgrade select status from v$instance STATUS -------- MOUNTED It doesn't go to upgrade ...
1
vote
1answer
129 views

Migrating from Oracle to MySQL via MySQL workbench: Table list is empty

MySQL workbench offers a migration tool that I'm using to migrate an Oracle database to MySQL. I'm using the migration wizard to migrate the complete Oracle schema to MySQL. I used that for the ...
1
vote
0answers
1k views

ORA-01157: cannot identify/lock data file 201

SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files; * ERROR at line 1: ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ...
0
votes
0answers
47 views

Varchar array as an input to Oracle stored procedure

I'm new to dynamic SQL execution. I'm working on an application which will take a list of input from an excel and generate a report out of it. I'm preparing the input data in the format required by ...
0
votes
1answer
243 views

Error converting data type nvarchar to float. Oracle Linked Server

Upon executing the following query select value, cast(value as float) float_value from OPENQUERY([ORACLESERVER], 'select 3/5 as value from table') I am getting the following error: Msg 8114, ...
1
vote
1answer
62 views

table or view does not exist when using insert into

I'm trying to insert into but getting the "table or view does not exist" error. It tells me the specific line where the error occurs but I can't figure out what the problem is: INSERT INTO c_ord_cur ...
1
vote
2answers
101 views

query to concatenate columns if record duplicate

My table has two column that is id and name. I want to do concatenate the columns if the if more than two names are same like: id ...
0
votes
1answer
125 views

Oracle Temp Table Space - Alias clean up ORA-01119

How to drop the temp table space including alias. SQL> CREATE TEMPORARY TABLESPACE TEMPRM_TEMP; SQL> ALTER TABLESPACE TEMPRM_TEMP ADD TEMPFILE ...
0
votes
0answers
16 views

Oracle 10g, Disable audit

I use oracle 10g(express) database, which I've tried to enable the auditing functionality for it. I failed (maybe not , but i couldn't see any audit information). It was some time ago, and now I want ...
0
votes
2answers
109 views

generate/analyze trace for error 1461

I am trying to generate a trace file when arise errors with code ORA-01461. I have executed the following statements: alter system set events '1461 trace name context forever, level 4'; from ...
0
votes
2answers
21 views

Separate indices for the field I want to get the MAX() and the ones in the WHERE clause

My customer's database has a gigantic table (hundreds of millions of records) and a SELECT that, by cumulative use, is eating a huge chunk of processing time. Each exec takes almost a minute in ...
2
votes
1answer
16 views

Is Oracle 10g smart enough to work on a WHERE clause identically as the equivalent INNER JOIN?

Way back when Michael Jackson was still alive, I made this question: http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause And now I'm performance-auditing a system in which the ...
1
vote
0answers
162 views

Set session query timeout

When I develop complex queries and I make a mistake (e.g. a wrong join that will make the query run for hours or days) Oracle becomes unusable (you can't even start a local SYS session from SQL*Plus). ...
0
votes
0answers
1k views

ORA-00911: invalid character error in insert statement

I created a table called TROUBLE in Oracle Database 10g Express Edition by this command create table TROUBLE ( City VARCHAR2(13) NOT NULL, SampleDate DATE NOT NULL, Noon NUMBER(3,1), Midnight ...
2
votes
2answers
66 views

Recursive Manager - Oracle 10g x 11g

We have a problem adapting a recursive SQL from 11g to 10g. Oracle 11g Query ( Working as expected ) WITH ADRECURSIVEUSERLEADER (CDLEADER,CDUSER,NMUSER,FGUSERENABLED) AS ( SELECT US.CDLEADER AS ...
0
votes
0answers
24 views

Oracle Drivers Install Error - Missing and Unknown files

I'm trying to run a VBScript on a machine that doesn't have Oracle installed; though I just needed the driver to use a connection string that does not use a TSNames.ora file (connecting to a remote ...
0
votes
1answer
217 views

Why would DBMS_METADATA.GET_DDL Fail when i have the SELECT_CATALOG_ROLE Role?

I am trying to get the DDL using METADATA_DBMS.GET_DDL for an object in a database, and I get the following error: ORA-31603: object "MY_PACKAGE" of type PACKAGE not found in schema "S1" ORA-06512: ...
0
votes
2answers
359 views

Purge ARCHIVELOG automatically

I have an Oracle 10g XE installation (exclusively used for development) which has accumulated so far 14 GB of files at ...\oraclexe\app\oracle\flash_recovery_area\XE\ARCHIVELOG. I do need that space ...
2
votes
1answer
190 views

Modify LISTAGG query for 10g

below mentioned code for cursor works only in Oracle 11g, as LISTAGG() function introduced starting version 11g. I need to adapt this code for Oracle 10g. Any help would be appreciated. SELECT ...
1
vote
0answers
367 views

Oracle 10.2.0.4 query hundreds times slower than the same query on Oracle 10.2.0.3

I have an issue with query on Oracle 11.2.0.4.0 (it takes forever to complete and CPU is at 100% all the time) so I was trying to change optimizer_features_enable to previous versions. It worked with ...
0
votes
2answers
416 views

Connect to Oracle using SQLDeveloper using OS Authentication on a remote machine

I reach my database server from my MacBook via SSH. You can login into this database as by using OS authentication. MacBook:~ mangeles$ ssh oracle@10.10.1.64 oracle@10.10.1.64's password: Last ...
1
vote
1answer
94 views

Generate rows based on value in 2 columns. Bug?

I have the following table in Oracle: ID START END _ID01 AB050 AB100 _ID01 AB101 AB150 _ID02 AB0251 AB0300 _ID01 AB00301 AB00302 I want to generate as many rows and every ...
0
votes
1answer
93 views

Critical tablespace usage UNDOTBS (98%)

This tablespace is in my case 8GB. I'm on oracle 10g and got critical warning: Tablespace usage critical: UNDOTBS (96%) I also executed this command: select sum (bytes/1048576) MB FROM DBA_SEGMENTS ...
0
votes
1answer
37 views

Right way to get references to an object in Oracle?

I want to find all the objects referencing my object. I came up with this query, Is this the best way to get all references to an object? (I know this doesn't include constraints, I handle that ...
3
votes
1answer
138 views

What is the difference between public_dependency and dba_dependencies?

I know that PUBLIC_DEPENDENCY has only the objects and references Ids, and the DBA_DEPENDENCIES does not have the Ids, but the Type, Owner, Name, etc of them. But beyond that, what is the difference ...
1
vote
1answer
52 views

Critical tablespace usage UNDOTBS (96%)

This tablespace is in my case 8GB. I'm on oracle 10g and got critical warning: Tablespace usage critical: UNDOTBS (96%) I also executed this command: select sum (bytes/1048576) MB FROM ...