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

learn more… | top users | synonyms (1)

0
votes
0answers
29 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
300 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
649 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
380 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
417 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
556 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
130 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
110 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
43 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
178 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
58 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 ...
0
votes
0answers
147 views

How to downgrade Oracle Database from 11gr2 to 10g?

In certain situation i have to downgrade from 11gr2 to 10g but coudn't find right way to downgrade. Can you please give a right direction and possible causes to Database.!
0
votes
2answers
198 views

Why is Oracle's number datatype so HUGE? and why do I seem to have no alternatives?

Oracle has a favorite numerical class called NUMBER. It is a 22 byte value that just seems ludicrous in an index. I have some values that would easily fit in a 6 byte integer and would therefore fit ...
0
votes
2answers
39 views

Adding counts inside a COALESCE

So I have a continuation of this question. New requirements have thrown in a third table. So here's the breakdown of how the tables relate: TabA(TabA_id, desc) TabB(TabB_id, TabA_id) TabC(TabC_id, ...
1
vote
0answers
726 views

Clarification on usage of Serially Reusable Pragma for Oracle 10g

I need to make packages for Oracle 10g that can be changed while users are still logged in an using an application that makes database calls through PL/SQL. Unfortunately, anytime a package is updated ...
0
votes
1answer
95 views

Getting sum result for last year divided quarterly

I am trying to get financial data grouped by each quarter of last year and also grouped by the last three weeks. Also, if it is possible, I want to have the type as a separate column with the type as ...
0
votes
2answers
232 views

Oracle custom week of the year numbering based on first Monday of the year

I have following sql for generating dates, I wanted to change week of the year numbering based on 'First Monday of the Year'. Hence numbering would be, 01-05-15 ---> 01 01-12-15 ---> 02 ...
0
votes
2answers
337 views

Access Oracle GTT table data from another session?

I have using Oracle GTT tables with condition ON COMMIT DELETE ROWS. Now i know GTT tables data is only session specific. 1 session cannot see the GTT data of other session in General. But is their ...
1
vote
1answer
1k views

How to find out the segment behind a corrupted block when the database cannot be opened?

Short Version: alter database open fails with ORA-00600. alert.log and dbv both point out that one data block (type: 6) is corrupted. I cannot select from dba_extents as that requires an opened ...
2
votes
2answers
3k views

Restoring an Oracle Backup Properly

I've been trying to do a proper restore of a database for weeks now and I'm convinced that I'm doing something simple wrong. I took a cold backup of the database before an update to an application ...
0
votes
1answer
79 views

Error while migrating SQL Server database to Oracle

I am getting this error message while loading database capture script output in Oracle SQL Developer. The source is SQL Server 2000 and destination is Oracle 11g. java.sql.BatchUpdateException: ...
1
vote
1answer
426 views

Rman controlfile and rotation

I got an Oracle 10g server with a bash script running every night which extract each datafile and create an rman script, which is launched from the bash script itself and make backups. CONFIGURE ...
0
votes
1answer
1k views

Dynamic ORDER BY clause in PL/SQL cursor

I am not sure if this is simply impossible or if I need to approach this from a different angle. Simplified, here is what I am trying: create or replace FUNCTION someFunction(SortCol1 varchar2 ...
0
votes
1answer
277 views

ORA-08102 while creating a view

I am learning PL/SQL programming. I'm trying to create a view with the following statement: create or replace view viewName as select ID from employees; The view creation throws the following ...
1
vote
2answers
649 views

Unexpectively slow data loading into Oracle

Big amount (4.000.000+) of files (1-4Mb each) are loading into Oracle 10g for about several days. Table is allocated in big tablespace, which was created on special volume (labeled BIG_VOL) over ...
2
votes
2answers
232 views

SQLPlus : default define settings

My workstation is Ubuntu 14.04. I have Oracle Express 11g installed. Whenever i run the define command in SQLPlus, is see the define _editor = "ed". Is there a way to change that permanently to gedit ...
1
vote
1answer
673 views

Can we migrate oracle database 10gR2 to cross platform

I want to know can we migrate Linux based oracle database 10gR2 (10.2.0.1.0) to windows server 2003 with oracle database 10gR2 (10.2.0.3.0). I have seen the documentation related to database migration ...
0
votes
0answers
2k views

How to compare between two values in one field depending on another field in SQL* / Oracle Reports?

I have three fields in a table named table_family. One is "relation" it has 3 kinds of values 1 for person, 2 for parents, 3 for son. Other field is "age" and it contains the ages of all the ...
1
vote
1answer
88 views

Oracle performance loss from 10.1.0.2 to 10.2.0.5

I am testing an application on two database servers with the same data : 10.1.0.2 on Windows (for development) 10.2.0.5 on Solaris (for validation). We have a script which recreates a huge table ...
0
votes
2answers
591 views

update column based on sum of data from another table

I have two tables : Table1 (col1, col2, col3, col4, val1, status) Table2 (col1, col2, col3, col4, val2) For Table1 and Table2, The columns (col1, col2, col3, col4) are the composite primary key. ...
2
votes
1answer
455 views

Tracing 'Invalid Number Error' from millions of rows using a large, complicated query

Background I am working on fixing a large query that I inherited. The query consists of over 200 lines of DML statements. The search queries the database restricting the data to a single month. Most ...
1
vote
1answer
1k views

ORA-27100: shared memory realm already exists oracle

I'm trying to start our db after performing a shutdown abort on a oracle 10g server, but atm i'm getting the following message: ORA-27100: shared memory realm already exists Linux Error: 17: ...
0
votes
2answers
41 views

Create a test enviroment in Oracle 10g

I have a production enviroment in a Oracle 10g database which I would like to authomatically duplicate in a test enviroment. My production enviroment is just a number of tables in which I have usually ...
0
votes
1answer
643 views

Is it impossible to iterate table rows in Oracle PL/SQL without using a cursor?

I wanted to figure out if it was possible to avoid using cursor to iterate the table rows in PL/SQL and tried the following script: set serveroutput on; declare v1 tbl_plant.plant_id%type := 0; ...
1
vote
0answers
226 views

How can I optimize a hierarchical query with multiple paths from child to root Oracle SQL?

I am trying to build a query that gives me the hierarchical history of events for a given child event_id. The events are related to each other by some from_id and to_id and the order is determined by ...
0
votes
0answers
396 views

Is it possible to alias a column with a constant variable defined in the package header of a PL/SQL package?

I am trying to alias some columns that are being used to interface with a 3rd party data aggregation system. This company has stated that they might change their naming scheme in the near future, so I ...
0
votes
1answer
197 views

Compare sysdate month with a column data

im application developer and we are using Oracle database for this project. I never used oracle before so i want some help to build a simple query. I have a CARD table with these columns EXPIREMONTH ...
2
votes
1answer
364 views

How would I implement interprocess communication from Oracle (10g R1) trigger using DBMS_ALERT?

Let me state up front that I'm not proficient in Oracle at all, MS SQL yes but not Oracle; which is why I'm asking this. Current Setup Oracle 10g Release 1 running on Red Hat Ent. 3 (no support) ...
0
votes
0answers
204 views

Performance degradation after partioning tables using DBMS_REDEFINITION package

We have created partitions on per-existing tables in my application using DBMS_REDEFINITION package. just for the sake of improving performance. Now , We are facing an issue in the performance ...
1
vote
1answer
495 views

Finding parameter names for binding stored procedure parameters

For unit testing purposes I am attempting to call the DBMS_OUTPUT.PUT_LINE procedure from c# using parameters that are bound by name. Since this DBMS_OUTPUT package seems to be a standard on Oracle ...
0
votes
1answer
86 views

Drastic change in query speed when changing from Oracle 9i to ANSI outer join syntax

I am working on simplifying a query originally written for Oracle 9i. In the process I changed the join syntax from the Oracle 9i syntax to ANSI. The names of the tables and columns have been changed ...
2
votes
1answer
476 views

Am I using the correct data types on SQL Server side to call an Oracle 10g function?

When calling an Oracle function with the following in parameter types NUMBER(7,0) VARCHAR2(8 BYTE) VARCHAR2(4 BYTE) DATE and return type NUMBER(14,5) from SQL Server 2008R2 via a linked Oracle ...
0
votes
1answer
66 views

Sub query with not equal

Sub query with not equal table_1 col1 SV0362 RU0177CSPG NO0124CSPG VN3582 AT9923CSPG BZ0324 PE0309 AS4293EXPT ML0331OMKT table 2 colA OO56128665 OO56128665 OO56128665 OO56128665 US0170 US0170 ...
0
votes
0answers
88 views

Optimization of an old query multiple unioned subqueries

I am currently in the process of optimizing a large query that was written for oracle 8i. My company currently uses oracle 10g which gives us a lot of options for optimization. Please Reference the ...
0
votes
2answers
258 views

Can't connect using SqlManager for Oracle, but Sql+

I'm using EMS Sql Manager for Oracle to connect to some Oracle databases but I receive the error ORA-12154: TNS:could not resolve the connect identifier specified I just re-setup my PC, due to SSD ...
2
votes
1answer
2k views

What does a green icon mean and why do the package bodies not expand when they have it?

In SQLDeveloper 4 some 10g PL/SQL packages have a regular crate while others have a green dot. Those with the green dot do not expand their bodies (might be just a coincidence). What is the ...
1
vote
1answer
1k views

Special characters in Oracle utl_http request

When calling a web service using utl_http, the calls fail when the SOAP envelope contains characters like ¿, ± or º. IS http_req utl_http.req; http_resp utl_http.resp; request_env ...
0
votes
1answer
123 views

Table statistics on database

Is there any way that I could find how many tables has statistics on them,and how many tables which doesn't have statistics on them.
0
votes
2answers
1k views

Unable to create Central Inventory

I'm trying to install Oracle 11g on Linux and have already configured the variables and X-host but can't able to create Central Inventory. My Central Inventory location is /u01/app/u0 and group name ...
0
votes
1answer
85 views

General question on dbms_stats

General question on dbms_stats and auto sample size? what is the impact of having IOTs with overflows and you switch to auto_sample_size and enable approximate NDV . How does it impact?