Oracle Corporations version 10 of its flagship RDBMS product.

learn more… | top users | synonyms (1)

7
votes
1answer
776 views

Can I anchor my data type to system type?

The following code: create or replace type sqlids_t is table of sys.v_$sql.sql_id%type; ...gives me... Error(2,31): PLS-00329: schema-level type has illegal reference to SYS.V_$SQL I get the ...
1
vote
1answer
634 views

ORA-07445: An unexpected error: core dump

We use oracle 10g. We found the following error in a trace file: *** 2012-05-07 11:50:56.421 ksedmp: internal or fatal error ORA-07445: An unexpected error: core dump [ACCESS_VIOLATION] ...
3
votes
1answer
216 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
0answers
220 views

changing Virtual IP interface names on RAC nodes - how to reconfigure

We are adding an extra interface on our nodes and we are going to bond these so the VIP interface will change from eth5 to bond3. Can someone point on documentation on what to do to reconfigure the ...
1
vote
1answer
4k views

restore database from dmp file

I have a situation: I have a dmp file and a database with old data server crashed and the latest data we have ar in the dmp file! we managed to pull the server back but it has old data! so we need to ...
1
vote
2answers
538 views

Oracle Databases - need to export nightly for backup/DR purposes

I know nothing about Oracle databases, so if my question's path seems odd, feel free to tell me the right way to do things. I have 2 ways I see currently that I can backup a server running 3 Oracle ...
3
votes
2answers
398 views

How can I change three hundred procedures at once?

I need to change about 300 procedures and packages in the database, due to a migration that will accomplish this weekend. We will do a migration from one server to Exadata. However the database has ...
1
vote
2answers
249 views

Join on clustered tables shows greater cost than non-clustered tables?

I have created a cluster and have two tables emp and sep on the cluster. When I do the simple query... explain plan select * from emp_cluster join dep_cluster using (dno) the cost is 26. I ...
1
vote
1answer
4k views

Materialized view taking too much time

i create a materialized view fast refresh. it takes almost 45 min to create but it did not refresh in 24 hours. i tried it on with index and without index. i check the log of all table max record in ...
1
vote
1answer
1k views

Solution to error NID-00135 in Oracle 10g, Solaris 10

We are trying to build a test version of a production database by restoring the production backup using RMAN and renaming the database to test. We've successfully restored and opened the database ...
2
votes
0answers
254 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 ...
4
votes
1answer
576 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
164 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
417 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
193 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
570 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
391 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 ...
6
votes
2answers
9k 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
12k 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
1k 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
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 ...
4
votes
2answers
496 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
729 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
422 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
759 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
417 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
554 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
903 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
450 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
68 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
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. ...
1
vote
2answers
564 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
23k 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
330 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
1k 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
690 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
810 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
6k 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
891 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 ...