Oracle Corporations version 10 of its flagship RDBMS product.
3
votes
2answers
2k views
Using Same CASE WHEN Conditions For Multiple Query Columns
Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once?
See the example below.
SELECT
CASE ...
0
votes
2answers
1k views
oracle 10g xe to 11g xe upgrade
The oracle 10g DB that is being currently used has exceeded the limit of 4GB. For the application to continue the db needs to be upgraded to 11g. But the upgrade doc requires a data pump export to be ...
3
votes
0answers
124 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 ...
5
votes
1answer
365 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 ...
2
votes
2answers
1k views
How do I declare and use variables in Oracle?
My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL:
declare @startDate int
select @startDate = 20110501
And I get this ...
1
vote
1answer
297 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
127 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 ...
1
vote
1answer
798 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
238 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
270 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
1answer
2k 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 ...
2
votes
0answers
134 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 ...
0
votes
0answers
112 views
Table Creation Fail in Oracle 10g Express [closed]
I'm trying to learn oracle and i'm very new with databases. when i tried to create a simple database table, it gave this error message:
...
1
vote
0answers
100 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:
...
4
votes
2answers
326 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 ...
3
votes
1answer
262 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
28 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 ...
2
votes
1answer
238 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
...
2
votes
1answer
129 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
3answers
217 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, ...
0
votes
0answers
112 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
0answers
247 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 ...
3
votes
2answers
2k 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
4k 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
378 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 * ...
4
votes
1answer
211 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 ...
0
votes
1answer
48 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 ...
1
vote
0answers
362 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 ...
2
votes
1answer
64 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 ...
2
votes
2answers
99 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
1answer
170 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 ...
7
votes
2answers
55 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 ...
3
votes
1answer
1k 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 ...
7
votes
5answers
1k 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
2answers
372 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 ...
1
vote
1answer
224 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
1k 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 ...
5
votes
1answer
914 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 ...
2
votes
1answer
562 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
607 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 ...
1
vote
1answer
159 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 ...
1
vote
0answers
271 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 ...
1
vote
1answer
488 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.
...
2
votes
1answer
11k 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
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] ...
6
votes
2answers
879 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
1k 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
2k 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 ...
1
vote
1answer
170 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?
1
vote
2answers
414 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 ...
