Oracle is a RDBMS (Relational Database Management System) created by Oracle Corporation. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java. Also included is support for C (Pro*C, OCI and EXTPROC), C++ (OCCI) and Java (JDBC) ...

learn more… | top users | synonyms

14
votes
6answers
71k views

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the ...
2
votes
1answer
14 views

Deferrable Foreign Key Constraints

What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE? This answer on StackOverflow mentions the performance impact that comes with no ...
3
votes
2answers
454 views

Security for Oracle linked server from SQL Server

I'm trying to make the linked server from SQL Server 2005 to Oracle more secure by not having user/pass on "Be made using this security context:" fields so only a specific user can access the data ...
2
votes
1answer
278 views

Database design for a table with both bulk insert and single row insert

I have to design a table where records will be inserted in two ways. Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records ...
0
votes
1answer
2k views

SQL Server - linked Oracle server: EXECUTE permission denied on 'xp_prop_oledb_provider'

Inside of our SQL Server, we have created a linked server to an Oracle server. We ave mapped several users to the Oracle server and specified the credentials. On the Linked Server 'Security' ...
0
votes
1answer
113 views

Mutiple updates with FORALL

I have 2 tables, one is a fact table (100000000 records), and the other is a catalog (2000), I need to update the FK at the fact table, can I use for all in this case?. I wrote this but it doesn't ...
0
votes
0answers
9 views

Oracle: Recover source code/table structures from “utterly wiped out” development database

Given an Oracle 11.1 database used for development, running in NOARCHIVELOG mode, where all datafiles (including undo) have been wiped out; and only SYSTEM and SYSAUX datafiles and control file remain ...
2
votes
1answer
650 views

ORA-16000 when trying to perform select on read only Oracle database

My application's SQL encounters an ORA-16000 error when trying to access a read only Oracle database. ORA-16000: database open for read-only access ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: ...
1
vote
1answer
346 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, ...
1
vote
1answer
38 views

Oracle 12c upgrade - backwards compatible with 11g?

Are there any application code changes required for SQL or PL/SQL to continue to work in 12c, or is it backwards compatible with 11g? Are there any queries that would work in 11g that break in 12g? ...
1
vote
2answers
14k views

How to move csv file into oracle database using sql developer?

How to move csv file into oracle database without creating table firstly using sql developer rather than sql loader?
0
votes
1answer
42 views

Arithmetic operations on a table

can anyone give me some advice on the following situation: I have two basic tables: Then a view created by the two: select product.IDPRODUCT, sum(quantity) as totalOut, PRODUCTSTORAGE.DATAINPUT, ...
1
vote
1answer
258 views

One Materialized View in Two Refresh Groups

I have five materialized views that I want to refresh in two occasions, every Sunday and at the 1st of every month. I created a Refresh Group for the weekly and that works fine. But when I tried to ...
1
vote
1answer
828 views

Which packages are required to install Oracle 11.2.0.3 on Solaris 11?

Where can I find information on which packages are required to install Oracle 11.2.0.3 on Solaris 11? The documentation only mentions Solaris 10. I tracked down the packages required for Solaris 10, ...
0
votes
0answers
201 views

Oracle - Slow queries without prepending schema name or providing hint

I am logged in as the schema owner in Oracle 11gR2 on Linux. I have 55 million rows in table. select max(col) from table; - takes 300 seconds (index full scan) select max(col) from schema.table; - ...
5
votes
1answer
15k views

How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?

I have an Oracle database, and I would like to create an ERD in Visio 2007 Pro. I have so far looked at DBMS_METADATA.GET_DDL commands to generate DDL. I know that I can import an Access DB into ...
2
votes
3answers
61 views

SQL actually executed as planned in ORACLE?

I wonder if SQL is definitely executed as execution plan. I mean.. is the actual SQL execution process always consistent with execution plan, i.e. does the same SQL always give the same plan?
2
votes
2answers
2k views

Can't drop UNDO TABLESPACE

We have Oracle 11gR1 RAC two instances on two different servers, new undo tablespace UNDOTBS20140508 created 30G, I want to remove the old one UNDOTBSX02, I did the following: CREATE UNDO TABLESPACE ...
2
votes
1answer
442 views

Historical SQL Monitoring

Is there a built-in Oracle feature that extends Real-Time SQL Monitoring to historical activity? I am specifically interested in getting wait events aggregated at the SQL operation level, not just ...
0
votes
1answer
114 views

How to perform same DML statements on two different servers (Oracle and SQL-Server) simultaneously

I have two servers, Oracle and SQL-Server. I have same database on both the servers. I want to perform DML (insert,update,delete). How can I perform same DML statements on both the servers ...
0
votes
2answers
10 views

Oracle database not open and ORA-00600 error

Whenever I try to open Oracle, I get following error : ORA-01109: database not open So as suggested by various blog and forum, I tried the following series of commands c:> sqlplus /nolog ...
-1
votes
1answer
29 views

Oracle Alter table add partition by reference gives “invalid datatype” error but create table with partition works

I have a parent and child tables. Parent table has a partition and a sub partition. Child table needs to have reference partition based on parent partition. Problem: If I create a new child table and ...
0
votes
0answers
21 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
36 views

Oracle 12c client installer error

I'm trying to install Oracle client 12.1.0.2.0 (32-bit) on a completely clean copy of windows Server 2012, and I'm hitting a problem. The installer runs fine until it gets to the final part - the net ...
1
vote
2answers
3k views

Trouble setting up Nagios to monitor Oracle services

I've got an install of Nagios XI that doesn't seem to want to talk to any of my Oracle services here. I've pulled out the monitoring command and am running it manually, after setting ORACLE_HOME and ...
0
votes
1answer
927 views

Export, based on query, into INSERT text file

I need to export some data into a file. The data are selected with a query that will often be made of multiple joins. I want the import operation to be as easy as possible. I was thinking of building ...
-2
votes
0answers
19 views

Difference between Tablespace, Segment, Blocks, Extent, Schema and Datafile in Oracle [on hold]

In Oracle databases there is all those confusing terms, can anyone please tell me the difference between them ?
7
votes
1answer
449 views

Designing Simple Schema for Disaggregation of Demand Forecast

I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case: I have a parent-child hierarchy of products (example, Raw ...
0
votes
1answer
11 views

is it possible to extract 'LifeCycle Status' using emcli ( Oracle Enterprise Manager )

It's easy to set the LifeCycle status ( and other properties ) with emcli, emcli set_target_property_value -property_records="sometestdatabase:rac_database:LifeCycle Status:Test" but getting the ...
1
vote
1answer
190 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
0
votes
1answer
16 views

Installing Oracle Forms and Reports on a 64bit machine

I'm trying to install Oracle Forms and Reports on a new server 64 bit (Ubuntu Trusty Tahr). (I'm only actually interested in the report server component). I can't find a version that I can install ...
0
votes
1answer
39 views

How do I find gaps between ranges of numbers?

Consider the following table: T_ID | T_START | T_END -----+---------+------ 1 | 0.25 | 0.5 2 | 0.8 | 1 3 | 0.4 | 0.6 4 | 0.2 | 0.3 5 | 0.7 | 0.8 T_ID is unique. ...
0
votes
1answer
24 views

Passing filter params to a sub query

I'd like to create a view from the query below, but the query contains a sub query which I need to apply a filter to (the place is marked with a comment). I'm wondering if it's possible somehow to ...
1
vote
1answer
40 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 ...
0
votes
1answer
34 views

How to set correctly utf8 on oracle?

I check my db,and seems to be UTF8 complaint select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; PARAMETER |VALUE NLS_CHARACTERSET |AL32UTF8 But when i do a search ...
2
votes
0answers
27 views

How to get status of “opatch prereq CheckApplicable” via script

I want to check if a patch is Applicable via opatch prereq CheckApplicable -oh <oracle home> -ph <patch home> but the returncode is always 0! It doesn't matter if the text output says ...
0
votes
1answer
109 views

Running Oracle Export Utility within a Shell script

I have wasted a lot of time on this. Any ideas of what I am doing wrong here? Script in bash #!/usr/bin/bash . . . /export/home/app/oracle/product/10.2.0/bin/exp $uname/$passw \ ...
1
vote
1answer
17 views

Oracle: How to get a list of CTXSYS.CONTEXT indices?

I can get a list of indices by querying USER_OBJECTS. How can I distinguish which of these indices are of type CXTSYS.CONTEXT?
0
votes
1answer
25 views

ORA-01461 in a msaccess linked table

In an Oracle 11 database I have a table that all fields are varchar2. This table is linked in an access database, and when I try to do an insert from a data aggregate query, it throws the error ‘can ...
0
votes
1answer
76 views

Rows to columns

I am new to Oracle. I know MySQL. I wrote below query in MySQL: Tables: CREATE TABLE Ass ( asset_id BIGINT(20) NOT NULL, caption VARCHAR(64) DEFAULT NULL, description VARCHAR(4000) DEFAULT ...
2
votes
1answer
2k views

Oracle 10g: Optimizing an update query that uses a subquery

I have the following query within a stored procedure, and I've been trying to improve its performance to no avail. UPDATE TBL_REPORTING REP SET (REP.ISSUER_ID, REP.ISSUER_NAME ) = ( ...
3
votes
3answers
71 views

Why does the CBO eliminate `distinct` and not `group by` in this query?

The CBO chooses to eliminate the distinct in the 'slow' query - presumably it can tell that the operation isn't needed because of the outer group by. My questions are: 1) Why does it choose to do so ...
2
votes
2answers
7k views

Adding subpartitions to existing partitioned table

In Oracle 11g, is there a way to introduce subpartitions into an existing partitioned table? I cannot seem to find a combination of EXCHANGE and SPLIT partition that does the right thing. SPLIT will ...
2
votes
3answers
10k views

Why can't we write ddl statement directly into the PL/SQL block

Why can't we write ddl statements directly in PL/SQL block, for example when i write CREATE OR REPLACE PROCEDURE test IS BEGIN truncate table table_name; // error END test; / But, CREATE OR ...
0
votes
1answer
248 views

How to know when a query was executed?

How to find out when a query was executed? I ran a query couple of days ago, I'm 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 ...
-2
votes
1answer
17 views

Why does the user only get prompted once [closed]

DECLARE invoice_date INVOICE.INV_DATE%TYPE := '11-MAY-2014'; customer_code CUSTOMER.CUS_CODE%TYPE := 10010; invoice_terms INVOICE.INV_TERMS%TYPE := 30; invoice_status INVOICE.INV_STATUS%TYPE := ...
0
votes
0answers
25 views

How to configure, implement, and manage a Data Guard configuration to simulate a standby database environment [closed]

I need to do an assignment but I have no idea how to do it. Before I continue, let me get one thing straight. I don't want anybody to do my assignment for me. That's not why I'm here. I just want a ...
1
vote
2answers
9k views

Why an object became invalid in oracle databse.

There are some invalid views and functions in my database. I am not sure what changes caused these objects to became invalid since many people are working on the same database. How can I see which ...
0
votes
1answer
472 views

Toad for Oracle 64-bit : 1603 - Fatal error during installation

I'm trying to install Toad for oracle client, my Toad version is 12.1 for 64 bit system, i have succeeded to install oracle client 12c for 64 bit system, but i have stumbled by a problem when ...
0
votes
2answers
283 views

Error in trigger: table X is mutating, trigger/function may not see it

Having looked at the following Trigger, still getting the Row 2 error: "ORA-04091: table SUMMITTEST.APL_LOG is mutating, trigger/function may not see it" Please can anyone provide any help in ...