Oracle Corporations version 10 of its flagship RDBMS product.

learn more… | top users | synonyms (1)

4
votes
1answer
535 views

Why does it take a long time to drop a function based index in Oracle?

I have a function based index that takes 25 minutes to create on a table of 94 Million rows. When I drop the index, it takes 18 minutes. Why does it take so long? I would have thought the drop would ...
1
vote
0answers
31 views

What might cause all objects in an Oracle database to suddenly have quoted identifiers?

I have an Oracle schema in which, at some point in the past ("the event"), all of the procedural code objects in the database have been re-created with quoted identifiers. All of the objects are ...
1
vote
0answers
156 views

Can not open oracle 10g database when migrated from oracle sql developer 1.5x to 3.1x

I have migrated from Oracle sql developer tool 1.5x to 3.1x and imported my connection settings with database 10g. When I try to open a connectionstring in the developer tool I get this message: ...
2
votes
1answer
382 views

Access Data that is stored in Primary FileGroup On SQL Server through DBLinks on Oracle

I am using dbLinks to fetch data from SQL Server 2008 to Oracle (10g)database. When I fire the command : select * from "test_table"@dbLink Oracle throws error : select * from "test_table"@dbLink ...
0
votes
0answers
186 views

Merge two packages into one package in Oracle 10g

I want to combine two different packages into one single new package in Oracle 10g. Is there some way to accomplish this?
2
votes
3answers
496 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
0answers
370 views

Isssue with Database Links converting OEM Job to DBMS_Scheduler Job

We had an OEM job that would run a PL/SQL block using a particular database users credentials. The credentials are the same for two different databases, so there was no problem for the block to use a ...
5
votes
2answers
7k 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 ...
2
votes
1answer
11k 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 ...
0
votes
3answers
941 views

Oracle returns different results when executing a sql with bind variable or with a fix value

The following two statements (strongly simplyfied) returning a different result: with a fix value for rownmun: select * from table where rownum <= 12; with bind variable for rownum: select * ...
3
votes
2answers
1k 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 ...
4
votes
2answers
478 views

Installing oracle RAC 10.2.0.1 install and patch to 10.2.0.5 directly or install CRS ASM and RDBMS then patch?

I'm installing my first RAC and I was wondering if I should install CRS 10.2.0.1 (we are still at 10g for compatibility reasons) then patch to 10.2.0.5 before I install ASM (then patch) and finally ...
7
votes
2answers
58 views

Oracle 10g: Is it possible to designate a undo point?

I was wondering if it is possible to designate a point in time, then later "roll-it-back" to that state (undo DDL and DML changes). Maybe using a record of changes and then executing the reverse? Does ...
2
votes
2answers
123 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 ...
1
vote
0answers
681 views

What can cause [DataDirect][ODBC Oracle Wire Protocol driver]Connection refused.?

The Title speaks for itself. We have this error message on the client side of the application. The devs suspect a problem on the database. The alert log on the database shows no errors at all. The ...
0
votes
1answer
59 views

Oracle: calling udf that modifies the data. How to do it right?

Say I have a function : create or replace function test_function return integer as PRAGMA AUTONOMOUS_TRANSACTION; t_test integer :=1; begin update test1 set id = id+1 ; // table test1 has, for ...
2
votes
1answer
347 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 ...
3
votes
1answer
2k 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 ...
1
vote
2answers
709 views

Is Using Execute Immediate Bad Practice in Oracle?

So, I've been writing a script which creates a sequence (if it doesn't exist) then copies some data including a value from that sequence before deleting the original source data. As the sequence ...
8
votes
5answers
3k views

Oracle sort varchar2 column with special characters last

How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...
1
vote
1answer
382 views

Using a sequence created in the same script in Oracle

SQL Server developer working on Oracle so apologies in advance if I'm fundamentally coming at this incorrectly from an Oracle perspective. I've got a script I've written which: Checks ALL_SEQUENCES ...
1
vote
1answer
476 views

How to create an UTC index from a 'DATE' typed column with Oracle?

I have a Oracle database with columns of type 'DATE' which store timestamps in local time zone. I need to query this column with timestamps in UTC timezone. How do I create an index on the column ...
2
votes
2answers
873 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 ...
5
votes
1answer
1k views

Oracle 10g express database link to Oracle 8i

I upgraded a 9i install to 11g with only one problem. Database links between 11g and our legacy 8i database are no longer supported. I have to transfer data from 11g to 8i two or three times a day ...
1
vote
0answers
435 views

Error starting up OC4J_EM: java.sql.SQLException: Logical handle no longer valid

I'm trying to launch the OPMN-managed process OC4J_EM, which is needed for Oracle's Enterprise Manager. When I run use the startall or startproc command, I get an error: failed to start a managed ...
2
votes
1answer
67 views

Oracle 10g Administration

Redo log files are not multiplexed in our database. Redo log blocks are corrupted in group 2, and archiving has stopped. All the redo logs are filled and database activity is halted. Database writer ...
1
vote
1answer
998 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. ...
1
vote
2answers
506 views

Analyzing an Oracle dump

I have a full Oracle dump, I know I have to imp the dump with the fromuser touser clause. However, I don't know the names of the schemas included in the dump. How can I open the dump created with ...
2
votes
3answers
21k 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: ...
4
votes
2answers
4k views

Export Oracle tables based on owner + all users from database

I would like to export all user and roles objects from Oracle 10g database to another. I googled it but I cannot find out how to do it. First I use this command: exp system/root@[DB_Name] ...
1
vote
1answer
298 views

How to implement a low resource privileged user on Oracle 10g using Resource Manager?

In my database I have users and their respective grants using the least amount of privileges principle. How do I start taking advantage of resource manager to prevent users from hogging all the ...
3
votes
2answers
982 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 ...
6
votes
2answers
2k views

Oracle 10g - How to prevent one user from hogging all resources?

I am wondering if there is any way to limit how much resources one user can take on my Oracle system. I want to limit some queries and systems from being able to hog all system resources when they run ...
2
votes
2answers
3k views

Generate sample data for an Oracle database [closed]

Good morning, What free [or trial] software can I use to generate sample data for an Oracle database? I've been trying with various tools, but none work. Thanks for all suggestions, Alec Taylor
2
votes
1answer
3k views

In Oracle, if parallel_max_servers is set to be significantly less than the DOP can the explain plan be sub-optimal?

For tables that have DEGREE DEFAULT, the degree of parallelism (DOP) is calculated by Oracle using number of CPUs and number of threads per CPU amongst other things. As I understand it, when costing ...
2
votes
1answer
574 views

Find fragmented space free below HWM Oracle

That High Watermark Value always eludes me when calculating free space in my tablespaces. Will the following query consider fragmented free spaces of object below the HWM value for each TS? select * ...
2
votes
2answers
755 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 ...
1
vote
1answer
5k views

Read Oracle BLOB type via SQL

I have an oracle table with field type BLOB, length 4000, data type "2004". I know that this field simply contains some XML. How can I read the value of the BLOB, using an SQL select statement? Is ...
1
vote
1answer
840 views

How to drop materialized view logs if underlying log table ($MLOG_…) is dropped?

In an attempt to work around another problem, I have mistakenly dropped the underlying tables for a materialized view log, and now I can neither drop that log nor the MV relating to it. Our DBA says ...
0
votes
4answers
6k views

What's a good client tool to access Oracle RDBMS? [closed]

I'm looking for a good client tool to replace "Oracle SQL Developer" (the tool I currently use) which supports: Oracle DB 10g, 10gR2, 11g Running SQL code Debugging SQL code (with walk-through... ...
4
votes
2answers
501 views

Learning Oracle, does it matter if I install 10g or 11g?

I'm taking a class in Databases, they're using Oracle 10g in class, but I'm having some trouble installing the database here in Windows 8 (auth problems), so I thought I'd attempt 11g. Is 11g ...
0
votes
1answer
2k views

SQL keyword difference between MySQL and Oracle 10g?

Good afternoon, Are there differences between Oracle 10g and MySQL with the CREATE, ALTER, DROP, REF, PRIMARY KEY, FOREGIN KEY and attribute variable type keywords? The reason I ask, is I'm planning ...
1
vote
1answer
211 views

Oracle Application Server 10g Installation Process

I downloaded Oracle Application Server 10g from this Oracle site. It has 4 .cpio files for the Linux installation. How do I unzip and install it on VMWare?
7
votes
2answers
259 views

Why is Oracle using the index here?

Name Null Type -------- -------- --------- ID NOT NULL NUMBER(4) GROUP_ID NUMBER(4) TEXT CLOB There is a btree index on group_id. Here's how many ...
1
vote
1answer
6k 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 ...
6
votes
2answers
3k views

How Long Would Take to Upgrade an 1T Oracle DB from 10g to 11g?

How long would take to upgrade an Oracle DB which has 1T data from 10g to 11g usually/roughly? I need to estimate the down time for it since it is a prod db. Thanks much!
9
votes
4answers
9k 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: ...
4
votes
1answer
366 views

Can Oracle Text query return the terms found?

I am querying an Oracle 10g database. The database contains a table with documents that has an oracle text index. I query the database using a query like SELECT SCORE(1), title from news WHERE ...
1
vote
1answer
4k views

Cleaning Oracle In-Active Sessions

There a are huge number of in-active sessions persisting in our database.How can we clean these in-active sessions? Database - Oracle 10g R2 with ASM DB Size - 5TB SGA - 78GB PGA - 10GB Maximum ...
5
votes
3answers
6k views

Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)

Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn. First, I do this: select max(ora_rowscn) from ...