Oracle Corporations version 10 of its flagship RDBMS product.

learn more… | top users | synonyms (1)

0
votes
0answers
8 views

Varchar array as an input to Oracle stored procedure

I'm new to dynamic SQL execution. I'm working on an application which will take a list of input from an excel and generate a report out of it. I'm preparing the input data in the format required by ...
3
votes
1answer
868 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) ...
0
votes
1answer
39 views

Error converting data type nvarchar to float. Oracle Linked Server

Upon executing the following query select value, cast(value as float) float_value from OPENQUERY([ORACLESERVER], 'select 3/5 as value from table') I am getting the following error: Msg 8114, ...
1
vote
2answers
802 views

Restoring an Oracle Backup Properly

I've been trying to do a proper restore of a database for weeks now and I'm convinced that I'm doing something simple wrong. I took a cold backup of the database before an update to an application ...
3
votes
2answers
22k 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 ...
1
vote
1answer
30 views

table or view does not exist when using insert into

I'm trying to insert into but getting the "table or view does not exist" error. It tells me the specific line where the error occurs but I can't figure out what the problem is: INSERT INTO c_ord_cur ...
0
votes
1answer
13 views

Oracle Temp Table Space - Alias clean up ORA-01119

How to drop the temp table space including alias. SQL> CREATE TEMPORARY TABLESPACE TEMPRM_TEMP; SQL> ALTER TABLESPACE TEMPRM_TEMP ADD TEMPFILE ...
0
votes
2answers
2k views

Oracle schema import is not importing all the tables present in the schema dump file

I have exported an existing oracle schema from another machine and then imported it in my local machine. Import was successful, but some tables which are present in the export dump file are not ...
1
vote
2answers
60 views

query to concatenate columns if record duplicate

My table has two column that is id and name. I want to do concatenate the columns if the if more than two names are same like: id ...
1
vote
0answers
36 views

Dynamically passing column name to unpivot [closed]

I have a table enroll like this- Students Class1 Class2 Class3 student1 1 0 1 student2 ...
2
votes
2answers
63 views

Recursive Manager - Oracle 10g x 11g

We have a problem adapting a recursive SQL from 11g to 10g. Oracle 11g Query ( Working as expected ) WITH ADRECURSIVEUSERLEADER (CDLEADER,CDUSER,NMUSER,FGUSERENABLED) AS ( SELECT US.CDLEADER AS ...
2
votes
1answer
372 views

Help with Oracle Pipelined function

I have a pivot where I have a contract with tank numbers separated by ; and I've been given this example to pivot them down into rows (which works): SELECT oks_c.contract_number, ...
2
votes
1answer
692 views

Finding why an Oracle listener got automatically restarted

I got the following message in the <listnername>.log file. I want to find out the reason why it got automatically shutdown and restarted again with different PID. 03-MAR-2013 03:00:57 * ...
8
votes
2answers
15k 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 ...
0
votes
0answers
11 views

Oracle 10g, Disable audit

I use oracle 10g(express) database, which I've tried to enable the auditing functionality for it. I failed (maybe not , but i couldn't see any audit information). It was some time ago, and now I want ...
0
votes
2answers
614 views

Unable to create Central Inventory

I'm trying to install Oracle 11g on Linux and have already configured the variables and X-host but can't able to create Central Inventory. My Central Inventory location is /u01/app/u0 and group name ...
0
votes
2answers
62 views

generate/analyze trace for error 1461

I am trying to generate a trace file when arise errors with code ORA-01461. I have executed the following statements: alter system set events '1461 trace name context forever, level 4'; from ...
0
votes
1answer
762 views

Alter table add column query blocking a table or taking so much time

I'm trying to add a new column to an existing table but it takes so much time or the query is blocking the table, I'm really confused about what's happening exactly. ALTER TABLE MYTABLE1 ADD ...
1
vote
1answer
218 views

Rman controlfile and rotation

I got an Oracle 10g server with a bash script running every night which extract each datafile and create an rman script, which is launched from the bash script itself and make backups. CONFIGURE ...
0
votes
2answers
21 views

Separate indices for the field I want to get the MAX() and the ones in the WHERE clause

My customer's database has a gigantic table (hundreds of millions of records) and a SELECT that, by cumulative use, is eating a huge chunk of processing time. Each exec takes almost a minute in ...
2
votes
1answer
13 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 ...
0
votes
0answers
51 views

Set session query timeout

When I develop complex queries and I make a mistake (e.g. a wrong join that will make the query run for hours or days) Oracle becomes unusable (you can't even start a local SYS session from SQL*Plus). ...
0
votes
0answers
73 views

ORA-00911: invalid character error in insert statement

I created a table called TROUBLE in Oracle Database 10g Express Edition by this command create table TROUBLE ( City VARCHAR2(13) NOT NULL, SampleDate DATE NOT NULL, Noon NUMBER(3,1), Midnight ...
0
votes
0answers
11 views

Oracle Drivers Install Error - Missing and Unknown files

I'm trying to run a VBScript on a machine that doesn't have Oracle installed; though I just needed the driver to use a connection string that does not use a TSNames.ora file (connecting to a remote ...
0
votes
1answer
45 views

Why would DBMS_METADATA.GET_DDL Fail when i have the SELECT_CATALOG_ROLE Role?

I am trying to get the DDL using METADATA_DBMS.GET_DDL for an object in a database, and I get the following error: ORA-31603: object "MY_PACKAGE" of type PACKAGE not found in schema "S1" ORA-06512: ...
1
vote
0answers
334 views

Reconfigure the registry for new Oracle installation

I have the following oracle homes installed on my Windows machine Oracle Developer Suite Oracle Discoverer Oracle 10G Client I installed Oracle 10G database manually by following the following ...
0
votes
0answers
201 views

How to load XML data into an oracle db table?

Is there a way to load the XML format data into an Oracle 11g DB table directly ? Can we write a stored procedure to accomplish the same in a more efficient way with the best performance? Can we ...
0
votes
0answers
554 views

How to detect when ORA-00604 occurred?

Today, between 3:00pm-3:30pm, my Oracle 10g database on Windows hanged twice. Error messages were related to ORA-00604. I'm pretty sure it was some query or queries that some user run as owner of some ...
0
votes
0answers
99 views

Create table with select Query on DB link

I'm trying to execute the following query, it is taking more than usual time. I'm retrieving data from db link. Without the create table statement the query is giving results. For Create table also if ...
1
vote
1answer
559 views

Special characters in Oracle utl_http request

When calling a web service using utl_http, the calls fail when the SOAP envelope contains characters like ¿, ± or º. IS http_req utl_http.req; http_resp utl_http.resp; request_env ...
3
votes
4answers
9k views

Sqlplus parameterized query via file

I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file. Is there a way to do this? The query has more parameters than is reasonable for a ...
1
vote
1answer
70 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 ...
0
votes
2answers
60 views

Purge ARCHIVELOG automatically

I have an Oracle 10g XE installation (exclusively used for development) which has accumulated so far 14 GB of files at ...\oraclexe\app\oracle\flash_recovery_area\XE\ARCHIVELOG. I do need that space ...
1
vote
0answers
252 views

Oracle 10.2.0.4 query hundreds times slower than the same query on Oracle 10.2.0.3

I have an issue with query on Oracle 11.2.0.4.0 (it takes forever to complete and CPU is at 100% all the time) so I was trying to change optimizer_features_enable to previous versions. It worked with ...
0
votes
2answers
129 views

Connect to Oracle using SQLDeveloper using OS Authentication on a remote machine

I reach my database server from my MacBook via SSH. You can login into this database as by using OS authentication. MacBook:~ mangeles$ ssh oracle@10.10.1.64 oracle@10.10.1.64's password: Last ...
1
vote
1answer
62 views

Generate rows based on value in 2 columns. Bug?

I have the following table in Oracle: ID START END _ID01 AB050 AB100 _ID01 AB101 AB150 _ID02 AB0251 AB0300 _ID01 AB00301 AB00302 I want to generate as many rows and every ...
10
votes
5answers
15k 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: ...
0
votes
1answer
28 views

Right way to get references to an object in Oracle?

I want to find all the objects referencing my object. I came up with this query, Is this the best way to get all references to an object? (I know this doesn't include constraints, I handle that ...
3
votes
1answer
81 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 ...
0
votes
1answer
61 views

Critical tablespace usage UNDOTBS (98%)

This tablespace is in my case 8GB. I'm on oracle 10g and got critical warning: Tablespace usage critical: UNDOTBS (96%) I also executed this command: select sum (bytes/1048576) MB FROM DBA_SEGMENTS ...
1
vote
1answer
41 views

Critical tablespace usage UNDOTBS (96%)

This tablespace is in my case 8GB. I'm on oracle 10g and got critical warning: Tablespace usage critical: UNDOTBS (96%) I also executed this command: select sum (bytes/1048576) MB FROM ...
0
votes
0answers
49 views

Can we Audit Synonym Usage in Oracle?

When Auditing is enabled in Oracle, is there a way to differentiate Private Synonyms access from actual object access? For example If I have Table: S1.My_TABLE Private Synonym: S2.My_TABLE ...
2
votes
1answer
2k views

Fixing wildcard expansion resulting in too many terms error (DRG-51030)

How can I resolve the wildcard_maxterm problem in Oracle 10g? My index creation syntax is: begin ctx_ddl.drop_preference('SUBSTRING_PREF'); end; / begin ...
0
votes
0answers
34 views

non-default connect roles and proxy authentication

I've been reading up on proxy authentication and found the following to bits of Oracle documentation that I can't seem to implement. Page 8 of this PDF of new forms features: ...
0
votes
0answers
77 views

How to downgrade Oracle Database from 11gr2 to 10g?

In certain situation i have to downgrade from 11gr2 to 10g but coudn't find right way to downgrade. Can you please give a right direction and possible causes to Database.!
1
vote
1answer
937 views

Exporting from Oracle enterprise edition to standard edition reaches schemas limit

So, I'm trying to export an oracle database(10.2) from EE to SE over a networklink. It all works fine except that I'm hitting a limit. The database has 1335 schemas and it seems that I can't transfers ...
0
votes
2answers
104 views

Why is Oracle's number datatype so HUGE? and why do I seem to have no alternatives?

Oracle has a favorite numerical class called NUMBER. It is a 22 byte value that just seems ludicrous in an index. I have some values that would easily fit in a 6 byte integer and would therefore fit ...
0
votes
0answers
30 views

What types of files can be stored on raw device in oracle database?

What are the other file types that one can store on a raw device in addition to placing CRD controlfiles,redologs,datafiles in oracle database.....
0
votes
2answers
6k views

Dividing comma separated strings into columns

I have a problem with dividing comma separated values. I have 2 columns with comma separated values, for ex: ID Name 1,2,3 Ab,cd,ef I want the columns divided as: Id Name 1 ab 2 ...
2
votes
2answers
3k views

Oracle - abort within a “before insert” trigger without throwing an exception

I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these ...