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

learn more… | top users | synonyms (1)

3
votes
3answers
1k views

When are Archived Redo Logs generated?

We have an instance in Oracle 10g. Only one schema is used for a web application (Internet-facing), which is only user for reading information, no adding or modifying data. The rest of schemas are ...
3
votes
1answer
112 views

Using Inteval Datatype to store result of calculation

I want to create a table which has hiredate and Interval of hiredate and sysdate. How to create a column that automaticlaly stores the calculation.
3
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 ...
3
votes
1answer
430 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
250 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
40k 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
1answer
8k 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
306 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
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
2answers
756 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
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
171 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
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 ...
3
votes
1answer
911 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. ...
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
134 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
141 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
5k 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
4k 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
469 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
218 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 ...
2
votes
3answers
309 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
138 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
492 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
583 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
8k 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
17 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
307 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
15k 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
20k 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
57 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
414 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
360 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
471 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
5k 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
894 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
362 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
1answer
624 views

Drop Oracle data file with 0 bytes and in offline (needs recovery) state

What can I do to drop a data file mistakenly created and currently with 0 byte and in offline (needs recovery) state?