Oracle Corporations version 10 of its flagship RDBMS product.

learn more… | top users | synonyms (1)

2
votes
1answer
20 views

Tracing 'Invalid Number Error' from millions of rows using a large, complicated query

Background I am working on fixing a large query that I inherited. The query consists of over 200 lines of DML statements. The search queries the database restricting the data to a single month. Most ...
0
votes
2answers
2k views

Database creation on Linux with dbca: “command not found”

I have installed an Oracle on my machine. When I open a session with VNCServer and type dbca, either as root or as oracle, I get these errors: -bash : -dbca : Command Not found Xlib: connection to ...
0
votes
1answer
361 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 happen exactly. ALTER TABLE MYTABLE1 ADD MYEXPORT9 ...
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: ...
1
vote
1answer
254 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, ...
0
votes
2answers
942 views

How can I find who has allocated a user lock via dbms_lock?

We have a print queue process that acquires a user lock via dbms_lock.allocate_unique. It performs the work and then releases the lock. The process works very well. Today, we have a process that ...
2
votes
1answer
262 views

How does Oracle 10g use indexes?

I have table called result: CREATE TABLE result (id_graduate NUMBER NOT NULL, id_sml NUMBER NOT NULL, last_update DATE NOT ...
0
votes
1answer
68 views

How to know the timestamp of when the query was executed?

1)How to find when a query was executed.I ran a query couple of days ago,im not sure about the exact time, I want to know at what date and time the query was executed. Is it possible to get that ...
1
vote
1answer
1k 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 ...
2
votes
1answer
394 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 * ...
3
votes
1answer
587 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) ...
1
vote
1answer
462 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
1answer
172 views

Unable to create Central Inventory

I am Traying to install Oracle 11g on my Linux a have alrady config the varible and X-host but can,t able to Create Central Inventory my Central Inventory location is : /u01/app/u0 ** and ...
0
votes
1answer
36 views

ORA-27100: shared memory realm already exists oracle

I'm trying to start our db after performing a shutdown abort on a oracle 10g server, but atm i'm getting the following message: ORA-27100: shared memory realm already exists Linux Error: 17: ...
0
votes
2answers
27 views

Create a test enviroment in Oracle 10g

I have a production enviroment in a Oracle 10g database which I would like to authomatically duplicate in a test enviroment. My production enviroment is just a number of tables in which I have usually ...
0
votes
1answer
46 views

Is it impossible to iterate table rows in Oracle PL/SQL without using a cursor?

I wanted to figure out if it was possible to avoid using cursor to iterate the table rows in PL/SQL and tried the following script: set serveroutput on; declare v1 tbl_plant.plant_id%type := 0; ...
0
votes
0answers
53 views

How can I optimize a hierarchical query with multiple paths from child to root Oracle SQL?

I am trying to build a query that gives me the hierarchical history of events for a given child event_id. The events are related to each other by some from_id and to_id and the order is determined by ...
2
votes
2answers
7k views

Return many rows on a plsql Oracle10g

I'm doing a little project and I need to do a plsql to return many rows. I have someting like: Table(id, data1, data2, from) And the user gives me the value of from and I have to return all that. ...
0
votes
0answers
21 views

How to debug ORA-42016 with DBMS_REDEFINITION?

I am writing a number of scripts to transform some tables using DBMS_REDEFINITION. Is there an easy way to debug the "ORA-42016: shape of interim table does not match specified column mapping" error? ...
0
votes
0answers
27 views

Accessing values of dymanic cloumn

I have a table which has 16 fields whose of alternate name to city. Below is my table structure: CREATE TABLE CITY_DATA_TO_CHK ( COUNTRY_CODE VARCHAR2 (100), REGION ...
0
votes
0answers
32 views

Is it possible to alias a column with a constant variable defined in the package header of a PL/SQL package?

I am trying to alias some columns that are being used to interface with a 3rd party data aggregation system. This company has stated that they might change their naming scheme in the near future, so I ...
0
votes
0answers
33 views

Sorting and Grouping by year, month and sums

Im new at Oracle, and am trying to write a query to show sums of Hours for the last 3 months. First Id like to display the year/month, then the sum of a specific type of hour worked for that ...
0
votes
1answer
13 views

Compare sysdate month with a column data

im application developer and we are using Oracle database for this project. I never used oracle before so i want some help to build a simple query. I have a CARD table with these columns EXPIREMONTH ...
0
votes
0answers
22 views

Error While Restoring Standby Database from RMAN Backup of Primary Database

My Standby Database was corrupted. So i took primary database backup using RMAN. Transferred to my standby database. On standby, on startup nomount, restored standby control file then on mount stage, ...
2
votes
1answer
62 views

How would I implement interprocess communication from Oracle (10g R1) trigger using DBMS_ALERT?

Let me state up front that I'm not proficient in Oracle at all, MS SQL yes but not Oracle; which is why I'm asking this. Current Setup Oracle 10g Release 1 running on Red Hat Ent. 3 (no support) ...
1
vote
1answer
90 views

Finding parameter names for binding stored procedure parameters

For unit testing purposes I am attempting to call the DBMS_OUTPUT.PUT_LINE procedure from c# using parameters that are bound by name. Since this DBMS_OUTPUT package seems to be a standard on Oracle ...
0
votes
0answers
53 views

Performance degradation after partioning tables using DBMS_REDEFINITION package

We have created partitions on per-existing tables in my application using DBMS_REDEFINITION package. just for the sake of improving performance. Now , We are facing an issue in the performance ...
0
votes
1answer
27 views

Drastic change in query speed when changing from Oracle 9i to ANSI outer join syntax

I am working on simplifying a query originally written for Oracle 9i. In the process I changed the join syntax from the Oracle 9i syntax to ANSI. The names of the tables and columns have been changed ...
9
votes
4answers
10k 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
0answers
41 views

How to populate Oracle 10g tables related via foreign key?

As the title says, how do i fill random data in tables, which have foreign key references within each other. I want to then practice all the SQL commands, like, SELECT commands, JOINS, SET FUNCTIONS ...
1
vote
1answer
206 views

What does it mean? ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Q 1: Whenever an Oracle SP wants to return error code/message, it fails returning below exeption: OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: ...
1
vote
1answer
70 views

Am I using the correct data types on SQL Server side to call an Oracle 10g function?

When calling an Oracle function with the following in parameter types NUMBER(7,0) VARCHAR2(8 BYTE) VARCHAR2(4 BYTE) DATE and return type NUMBER(14,5) from SQL Server 2008R2 via a linked Oracle ...
0
votes
1answer
44 views

Oracle Recovery

Oracle server died. Built a new VM and mapped out the volumes to the SAN. Installed the same version of Oracle on the new server and applied the same patches. How can I "register" or "attached" the ...
0
votes
1answer
32 views

Sub query with not equal

Sub query with not equal table_1 col1 SV0362 RU0177CSPG NO0124CSPG VN3582 AT9923CSPG BZ0324 PE0309 AS4293EXPT ML0331OMKT table 2 colA OO56128665 OO56128665 OO56128665 OO56128665 US0170 US0170 ...
0
votes
0answers
16 views

Optimization of an old query multiple unioned subqueries

I am currently in the process of optimizing a large query that was written for oracle 8i. My company currently uses oracle 10g which gives us a lot of options for optimization. Please Reference the ...
0
votes
1answer
39 views

How to get desired type of records for desired number of times in oracle

I want to get get 6 "double_scoring" records and 4 "singe_scoring" records from my Profiles table based on SCORING_RULE column values. Can you please let me know how can I do this with SELECT query ...
1
vote
2answers
3k views

Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR?

Starting from this question and this example I've tried to create an event based dbms_scheduler job, which waits for event JOB_OVER_MAX_DUR raised from jobs, which exceeded their max_run_duration. ...
0
votes
1answer
208 views

What does a green icon mean and why do the package bodies not expand when they have it?

In SQLDeveloper 4 some 10g PL/SQL packages have a regular crate while others have a green dot. Those with the green dot do not expand their bodies (might be just a coincidence). What is the ...
0
votes
1answer
53 views

Can't connect using SqlManager for Oracle, but Sql+

I'm using EMS Sql Manager for Oracle to connect to some Oracle databases but I receive the error ORA-12154: TNS:could not resolve the connect identifier specified I just re-setup my PC, due to SSD ...
0
votes
0answers
149 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 ...
0
votes
1answer
66 views

Table statistics on database

Is there any way that I could find how many tables has statistics on them,and how many tables which doesn't have statistics on them.
0
votes
1answer
32 views

Delete all types created by a user

In Oracle 10g, how do I delete all the types owned by a specific user? This is the view that gives me the types I want to drop:SELECT type_name FROM user_types;
0
votes
1answer
51 views

General question on dbms_stats

General question on dbms_stats and auto sample size? what is the impact of having IOTs with overflows and you switch to auto_sample_size and enable approximate NDV . How does it impact?
0
votes
2answers
1k views

What is the difference between Schema and Database in Oracle?

In oracle, Datafile are system files where actual data is stored.The collection to datafile make Tablespace and atlast database is collection of tablespaces Correct me if I am wrong on the concepts ...
1
vote
0answers
45 views

Testing equivalence in strings with different character sets

I have to synchronize a number of tables with string columns between two databases: the source Oracle 10.2.0.3 NLS_CHARACTERSET WE8MSWIN1252, NLS_LENGTH_SEMANTICS BYTE the destination Oracle ...
1
vote
1answer
214 views

Object changed during datapump

If some object is being changed when data pump is run how will that object go into datapump file?
1
vote
1answer
126 views

Export synonyms content to tables

I have a distant database to replicate having huge data but I have access only to synonyms and I would like to use Oracle export/import tool to import these data into tables of local database. What ...
0
votes
1answer
572 views

How to know which process is causing high memory usage on DB?

I've got one Onesight alert mail stating below: Short Description: Onesight Alert: ZEUS DB Monitor (zeus04) Description: Solaris 5.8: vmstat memory(free) - Free Memory for zeus04 2065112 8:06:02 ...
0
votes
3answers
56 views

Invalid create index syntax

I cannot create index with the where clause. Refer the below query: CREATE INDEX c_bplocation_isdefault ON c_bpartner_location (ad_client_id,c_bpartner_id,isdefault) WHERE isdefault = 'Y';
0
votes
0answers
48 views

My stored procedures often get invalidated

The reason for invalidation should be because of some DDL activity, but I don't have any DDL in the SPs. I need to find what is that DDL statement. If the object is in an invalid state, when you call ...