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

learn more… | top users | synonyms (1)

3
votes
1answer
429 views

How to get the Oracle Text Index's underlying Virtual XML Document?

The Oracle Text Reference 10g states that when we index our table using multi-column datastore, the system concatenates the text columns, tagging the column text, and indexes the text as a virtual XML ...
3
votes
2answers
213 views

Deleting an Oracle database without connecting to it?

I have two databases which were created using dbca. I made some changes in the second database, and I can't login to that database anymore, and, of course, I can't delete it by using dbca. I am using ...
3
votes
2answers
37k views

oracle: No more data to read from socket

We are using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 and we have an error on a query that gives us the error Error: No more data to read from socket SQLState: null ErrorCode: ...
3
votes
2answers
2k views

How does name resolution work in Oracle 10g?

In Oracle 10g (10.2.0), there are 4 types of names resolution: Easy Connect(10g added this new method) Local naming Directory External naming It is said that if Easy connect is used by the ...
3
votes
1answer
1k views

Oracle 10.2.0.1.0 listener process stopped

When I am checking my servers I see that there is an error about listener process stopped. I logged in and checked the status of listenere. please see the below status; LSNRCTL> status Connecting ...
3
votes
2answers
722 views

SQL*Plus default output format

I have noticed that using the same SQL*Plus client to connect to different Oracle database result in different outputs for the same SQL query. Database 1: C:\Oracle\instantclient_10_2>sqlplus ...
3
votes
1answer
7k views

Is shrink a safe online operation?

We are planning to run the following space management command: alter table XXX shrink space cascade on a production database (Oracle 10g). The XXX table is a transactional table heavily used for ...
3
votes
1answer
289 views

Can this be accomplished with Oracles Fine Grained Auditing?

We need to audit when a table is accessed (either select, update or delete) and I know Oracles Fine Grained Auditing can handle that. The issue we are having is that all of our users use the same ...
3
votes
1answer
3k views

Installing 10g client next to 11g database on same computer

I have Oracle 11.2.0.1.0 installed on Windows 7 64 bit I want to import data from warehouse which has Oracle 10.2.0.4.0 installed. When I try to import impdp usr/pass@WAREHOUSE TABLES=TABLE ...
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 ...
3
votes
1answer
8k views

Is it possible to do a datapump import with a different user than did the export?

I have one db dump below is the log file for that dump Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** ...
3
votes
1answer
1k views

Convert Oracle Query for Date to Mysql queries

Need to convert the following oracle queries to MYSQL queries. 1) select to_char(to_date(?,'DD-MM-YY')) from dual; 2) select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY')) from dual; 3) ...
3
votes
0answers
378 views

What do I need to do to avoid ORA-32696 errors?

I have a SELECT query using several WITH blocks. Sometimes it runs successfully. Very often it fails with the error "Lookup Error - Oracle Database Error: ORA-32696: HTI: No free slot". I Googled the ...
3
votes
2answers
2k views

Oracle imp crashes without error

I have Oracle 10g XE database locally. I export schema with something like exp %USER%/%PASSWD%@XE statistics=NONE owner=%USER% file=dump.dmp Exp is 10.2.0.1.0 version - so is the DB. Than I take ...
2
votes
2answers
1k views

Is it possible for me to be notified if an 'after insert or update trigger' stops working?

Last week, I found my after insert or update trigger wasn't working. After I disabled and enabled it, it started working again. I do not yet know why it stopped working. Is there any way to deal with ...
2
votes
2answers
4k views

How can I delete duplicates records in a table efficiently

I know there are several options to delete duplicate rows, eg: ROWID Analytical fn's like Rank(), row_number() What would be the most efficient way?
2
votes
1answer
1k views

Are multiple dbf files better than one?

Is it better to distribute a large database over several dbf files than over a single one?
2
votes
2answers
133 views

How do I pull a backup of an oracle database so I can send it to a vendor

First of all, I know squat about Oracle databases. A vendor who is looking at converting our data from an older piece of software with an Oracle DB back-end wants a dump of our database to see what ...
2
votes
3answers
13k views

Clone / Copy Oracle 10g & 11g

Hi I am absolute begginer in Oracle domain. I would like to learn way how to clone copy oracle database to som backup, use this backup on another server. Oracle is installed on Windows server 2003. ...
2
votes
3answers
58 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 ...
2
votes
1answer
4k views

ORA-00984: column not allowed here

I am running Oracle 10g. I am writing a script (a delta script over a created database) that will be executed in SQL*Plus. It is needed to fix a bug. I need to know if a row exist in a table, and if ...
2
votes
1answer
4k views

How do we free up disk space from allocated namespace? / ORA-03297

We are running out of disk space. The server has a 420 GB HDD, and runs a Microsoft SQL Server and an Oracle Server(10g). The Oracle server uses up to 315 GB space. We have just loaded a 100GB ...
2
votes
1answer
3k views

How do I export data from Oracle 11.1 to 10.1?

I have a 11.1 oracle server and I need to export a dump file for a client who has a 10.1 server. While reading the documentation, I figured out I needed the 10.1 Export Utility. I searched the ...
2
votes
3answers
1k views

What is the simplest way of excluding the current row from the result of an analytic?

I want to do something like the following: with w as ( select level as foo, decode(level,8,1,mod(level,4))*100 as bar from dual connect by level<9 ) select foo, ...
2
votes
2answers
1k views

Oracle 10g XE GUI

I've designed a database on Oracle 10g xe (Windows Platform) and I want to export the tables I created in a sql file. I've googled all night and didn't find anything. I also tried to install a Windows ...
2
votes
1answer
465 views

Oracle Database Block Size

One of our Production database has a rather peculiar block size of 16KB. This database was created about 2 years ago and at that time the size of the db was approx 1TB only.However over the past 2 ...
2
votes
2answers
6k views

How to debug “IO Error: End of TNS data channel” errors?

I am seeing sporadic reports of an application failing certain queries with an "End of TNS data channel" error from the Oracle (10g) server. Are there any queries I can run (e.g., with SQL*Plus or ...
2
votes
3answers
303 views

Oracle error : Shutdown immediate error

echo $ORACLE_SID YBRD conn sys/hello as sysdba SQL>shutdown immediate Then I was waiting for it to shutdown for a couple of minutes. It didn't, so I closed my window. Then I again logged in and ...
2
votes
1answer
129 views

Oracle Database: View schema without access to data

In Oracle Database 10g, I need to create a user with these permissions: user can access schema to be able to read table names and columns user can not select (get) any data from any table Is this ...
2
votes
1answer
2k views

Recompiling dependent objects in Oracle

I wonder what is the best practice to recompile Oracle 10g programmable objects that become invalid due to changes in one of them. To be more specific, I have a few packages, that use schema level ...
2
votes
1answer
462 views

Oracle function based index is not used in query

I cannot figure out why the following is not working as I expect it : CREATE TABLE table1(table1_id INT NOT NULL PRIMARY KEY, col1 INT NOT NULL, val1 VARCHAR(50)); CREATE INDEX ...
2
votes
1answer
2k views

Oracle Create Materialized View Script - Takes 12 minutes in DEV Schema, 5+ days in QA Schema

I have a materialized view in my Oracle 10g database that I need to rebuild. So I'm doing something like this... drop materialized view ViewName; CREATE MATERIALIZED VIEW ViewName NOCACHE LOGGING ...
2
votes
2answers
577 views

Encountering exception ORA-01555

I was given a problem to solve , in which there is table called Scenarios in the Master Db which contains the details of all the Tablespace for which I have to find the size. The O/P should contain ...
2
votes
2answers
7k views

Execute PL/SQL script block in C#

I am trying to execute a PL/Sql block by using the OracleClientProvider in .Net . The language I am using is c#, the DB is oracle10g What I am actually doing is the following : //ConnectionSting ...
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 ...
2
votes
1answer
193 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 ...
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 ...
2
votes
2answers
14k views

Return many rows on a plsql Oracle10g

I'm doing a little project and I need to do a plsql to return many rows. I have someting like: Table(id, data1, data2, from) And the user gives me the value of from and I have to return all that. ...
2
votes
1answer
19k views

SQL Developer cannot connect to Oracle database, yet sqlplus can!

I am having a problem connecting sql developer to my Oracle 10g database. sqlplus is able to connect fine, but when I try to open the connection in sql developer, I get 'ORA-12514 TNS does not know ...
2
votes
1answer
11k views

What to do with those invalid JAVA CLASS object type objects in SYS prior to the upgrade the oracle db from 10g to 11g?

I am ready to upgrade the oracle 10g prod db to 11g, but we have 130 invalid objects in SYS (all are belong to JAVA CLASS object_type). And from the best practice of oracle upgrade from 10g to 11g, it ...
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 ...
2
votes
1answer
323 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 ...
2
votes
1answer
328 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) ...
2
votes
1answer
449 views

How does Oracle 10g use indexes?

I have table called result: CREATE TABLE result (id_graduate NUMBER NOT NULL, id_sml NUMBER NOT NULL, last_update DATE NOT ...
2
votes
2answers
4k views

Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR?

Starting from this question and this example I've tried to create an event based dbms_scheduler job, which waits for event JOB_OVER_MAX_DUR raised from jobs, which exceeded their max_run_duration. ...
2
votes
1answer
810 views

Is it possible to automate the configuration of dataguard broker?

We deploy a number of oracle databases every year and a good percentage of them require a dataguard configuration. I've developed a script which is able to perform the database synchronization very ...
2
votes
2answers
360 views

Recreate indexes and constraint when redefining table with DBMS_REDEFINITON

I have a table A witht the following characteristics: multiple indexes constraints (null, unique, check, foreign key) 2 triggers (before update) primary key If I were to redefine the table with ...
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 ...
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 ...
2
votes
1answer
189 views

Locking records in Oracle during update

I had a deadlock issue which was easy to fix by changing application logic, but I was under impression Oracle would behave differently, and the deadlock would never happen. I have two tables, for ...