Oracle Corporations version 10 of its flagship RDBMS product.
5
votes
1answer
388 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
310 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
131 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
127 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
882 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
255 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
275 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
143 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
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 ...
1
vote
1answer
627 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
145 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
115 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:
...
3
votes
1answer
277 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 ...
1
vote
0answers
103 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
245 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
114 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
230 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
256 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
5k 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
424 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 * ...
2
votes
2answers
673 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
329 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
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 ...
2
votes
2answers
100 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
373 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
49 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
140 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
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 ...
1
vote
2answers
393 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 ...
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
1answer
235 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
173 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
611 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
944 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
286 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
65 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
524 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
289 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
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] ...
1
vote
1answer
172 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 ...
2
votes
1answer
586 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
946 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 ...
2
votes
1answer
254 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 * ...
1
vote
2answers
441 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
2k 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 ...
