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

learn more… | top users | synonyms (1)

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 ...
8
votes
2answers
19k views

How do I find an object that doesn't seem to exist in the objects table?

I have an object called cot_ntn_pi_v. I was told this was a synonym. It doesn't appear in the all_synonyms table. It looks like a view or table but I can't find it in the all objects table. I can ...
0
votes
0answers
15 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
890 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
2answers
106 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
1answer
1k views

Alter table add column query blocking a table or taking so much time

I'm trying to add a new column to an existing table but it takes so much time or the query is blocking the table, I'm really confused about what's happening exactly. ALTER TABLE MYTABLE1 ADD ...
1
vote
1answer
367 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
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
157 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 ...
0
votes
0answers
23 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 ...
1
vote
0answers
346 views

Reconfigure the registry for new Oracle installation

I have the following oracle homes installed on my Windows machine Oracle Developer Suite Oracle Discoverer Oracle 10G Client I installed Oracle 10G database manually by following the following ...
0
votes
0answers
216 views

How to load XML data into an oracle db table?

Is there a way to load the XML format data into an Oracle 11g DB table directly ? Can we write a stored procedure to accomplish the same in a more efficient way with the best performance? Can we ...
0
votes
0answers
573 views

How to detect when ORA-00604 occurred?

Today, between 3:00pm-3:30pm, my Oracle 10g database on Windows hanged twice. Error messages were related to ORA-00604. I'm pretty sure it was some query or queries that some user run as owner of some ...
1
vote
1answer
889 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 ...
3
votes
4answers
10k views

Sqlplus parameterized query via file

I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file. Is there a way to do this? The query has more parameters than is reasonable for a ...
2
votes
1answer
185 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 ...
0
votes
2answers
349 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 ...
1
vote
0answers
365 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
411 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
93 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 ...
10
votes
5answers
17k views

Cannot drop nonexistent constraint and cannot create it either

While testing some migration scripts with a copy of production data (scripts run fine with development data) I found a curious situation. A CONSTRAINT has changed so I'm issuing DROP + ADD commands: ...
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
136 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 ...
0
votes
1answer
92 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 ...
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 ...
0
votes
0answers
90 views

Can we Audit Synonym Usage in Oracle?

When Auditing is enabled in Oracle, is there a way to differentiate Private Synonyms access from actual object access? For example If I have Table: S1.My_TABLE Private Synonym: S2.My_TABLE ...
2
votes
1answer
2k views

Fixing wildcard expansion resulting in too many terms error (DRG-51030)

How can I resolve the wildcard_maxterm problem in Oracle 10g? My index creation syntax is: begin ctx_ddl.drop_preference('SUBSTRING_PREF'); end; / begin ...
0
votes
0answers
57 views

non-default connect roles and proxy authentication

I've been reading up on proxy authentication and found the following to bits of Oracle documentation that I can't seem to implement. Page 8 of this PDF of new forms features: ...
0
votes
0answers
123 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.!
1
vote
1answer
1k views

Exporting from Oracle enterprise edition to standard edition reaches schemas limit

So, I'm trying to export an oracle database(10.2) from EE to SE over a networklink. It all works fine except that I'm hitting a limit. The database has 1335 schemas and it seems that I can't transfers ...
0
votes
2answers
152 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
0answers
30 views

What types of files can be stored on raw device in oracle database?

What are the other file types that one can store on a raw device in addition to placing CRD controlfiles,redologs,datafiles in oracle database.....
0
votes
2answers
6k views

Dividing comma separated strings into columns

I have a problem with dividing comma separated values. I have 2 columns with comma separated values, for ex: ID Name 1,2,3 Ab,cd,ef I want the columns divided as: Id Name 1 ab 2 ...
0
votes
2answers
36 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, ...
0
votes
2answers
255 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 ...
0
votes
2answers
834 views

Changes in Oracle Database implicit conversion rules for select statement

Due to hardware failure after a power outage we had to recreate the database for one of our 3rd party applications from an dumpfile (created via exp). The old hardware had Oracle 10G R2 running while ...
0
votes
1answer
91 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 ...
1
vote
1answer
1k views

ORA-00449: background process 'MMAN' unexpectedly terminated with error 7446

One of our Oracle servers went down rather badly last night, and we have been unable to bring it online since. Attempting to startup each instance results in: SQL> Connected to an idle instance. ...
0
votes
2answers
172 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 ...
1
vote
1answer
766 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 ...
0
votes
1answer
77 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: ...
0
votes
1answer
905 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 ...
1
vote
0answers
1k views

How do I disable oracle's MAX_ENABLED_ROLES limit? [closed]

How do I disable oracle's MAX_ENABLED_ROLES limit or expand the limit?
1
vote
2answers
516 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 ...
0
votes
1answer
240 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
182 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
582 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
1k 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 ...