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

1
vote
1answer
46 views

Grant privilege to see column names in null constraint violations when inserting in views

My database has two schemas, one with the actual tables and one that is used by an application. The application schema doesn't have any privileges on the actual tables. Instead there is a view for ...
0
votes
0answers
88 views

Why is there a difference in CSV Export Results of Oracle SQL Developer and SQL Plus Spool

When I export data from SQL Developer GUI, setting format: csv, left enclosure: ", right enclosure: ", and Encoding: UTF8, I get data exactly the way that I need it for processing in Python. ...
3
votes
1answer
78 views

How to maintain a global index on a large table undergoing regular partition purges?

Problem I've got giant table haystack weighing in at nearly 3 TB, partitioned by month (date) and subpartitioned by client. I need to create a global index that spans all clients to facilitate ...
0
votes
0answers
50 views

SQL Loader Pass Dynamic Value

I'm newer to Oracle so I hope what I am trying to explain makes sense. I am required to used SQL Loader to batch load CSV files. There are about 20 organizations uploading around 15 CSV files each. ...
1
vote
1answer
37 views

Oracle 10 xe; backing up oradata folder enough to recreate a duplicate later?

Let us say, I backup of the the oradata folder. Is that enough to recreate the exact same database? E.g., I reinstall oracle xe 10 on the same pc, using the same installer, into the same folder, and ...
0
votes
0answers
45 views

What is the appropriate GCC version I should use for building my External Procedure?

When working with Oracle External procedures, what is the GCC version I should use to build my procedure? Does Oracle get shipped with a GCC version and libraries that it keeps in one of its own ...
1
vote
1answer
58 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
88 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 ...
0
votes
1answer
60 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, ...
2
votes
3answers
72 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?
0
votes
2answers
89 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 ...
2
votes
1answer
193 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? ...
0
votes
1answer
107 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
35 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 ...
2
votes
0answers
54 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 ...
1
vote
1answer
58 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 ...
1
vote
2answers
555 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
1answer
26 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?
-1
votes
1answer
239 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
1answer
73 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
58 views

Startup oracle database 12c manually with SQL*Plus command not when OS loads (solved)

I changed oracle instance to manually start from services.msc (service name is "OracleServiceORCL"), Because I want to start it up manually with SQL*Plus commands whenever I want, not with OS load. ...
0
votes
2answers
62 views

How to block external connections for an Oracle database?

I have an oracle database (11g-r2) on my (windows) computer for development, and I am trying to forbid any outside connections. Is there a way to do this?
0
votes
1answer
44 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 ...
0
votes
1answer
30 views

A question about oracle upgrade from 12.0.0.1 to 12.0.0.2 using dbua

I found this guide for upgrade. After the upgrade, can I delete safely the old DB product dir /var/oracle/app/oracle/product/12.1.0 ?
1
vote
0answers
49 views

Excel and Oracle SQL Connection

I am connecting to an Oracle DB and return data with Excel 2010. I have setup the connection in my workbook and my connection string looks as follows, ...
2
votes
3answers
73 views

Query to find users with passwords same as their username in Oracle

I'm writing an Oracle audit script where I need to find users in a database who have set their passwords to be the same as their username, e.g. dbaadmin/dbaadmin. The query would need to get the list ...
0
votes
1answer
63 views

Oracle database for learning purposes

I would like to know if I can download Oracle Database on my Mac OS X and use it for learning purposes? I know that Oracle Database Express Edition 11g is free, but is it free for OS X?
0
votes
1answer
48 views

Oracle Equivalent of the following SQL Server syntax

I'm looking for the Oracle equivalent of the following SQL Server syntax: select Cast(0x41424344 as varchar(max)) In SQL Server, it selects 'ABCD'. 0x41424344 is the hex of 'ABCD'
0
votes
1answer
49 views

RMAN restore from tag

oracle 11.2.0.4 on redhat 6 SERV1 is prod server running database MAZ, SERV2 is developemt server with no databases on it. i have noted the dbid and last SCN, and then taken a backup on SERV1 using ...
0
votes
0answers
91 views

Out of memory error from Java stored procedure

I have a Java stored procedure inside Oracle 11g database. The procedure uses lots of memory, so I'm setting the max memory size in same session: create or replace function setMaxMemorySize(num ...
3
votes
1answer
104 views

Oracle paging query performance

I'm new to Oracle and I've noticed that pagination queries take the format. select * from ( select /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum from ( your_query_goes_here with ...
0
votes
1answer
86 views

PL/SQL recursive lookup with cursor

I basically have a need for recursing through data in my Oracle table so I can find related rows and send them out. It is a single table, a single ANSI SQL query seems not possible due to turin ...
0
votes
1answer
23 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
0answers
52 views

Oracle 12c database cannot log in as a local user

I met a problem from Oracle 12C Database, when I try to login to the user account with commandconn test/test, I got prompt of "invalid username/password, logon denied". when I use conn ...
1
vote
0answers
17 views

oracle bulk loader processing columns with newline characters

I'm trying to load a file with description columns which may contain newline characters. The file is Unix format utf16, tab separated and description columns are enclosed in ". The problem is bulk ...
0
votes
0answers
49 views

Is there a way to check which views have data? Oracle 11g

I'm undergoing a cleanup process for a data mart design. However, I want to get rid of any views (in the design) which result in having 1 or less rows of data. Instead of manually going through ...
0
votes
0answers
43 views

TAF vs SCAN in Oracle 11gR2

We are planning to use Oracle 11gR2 RAC. I am wondering if TAF is required to setup even when using SCAN in Oracle 11gR2 RAC. Please let me know if there are any advantages of using TAF along with ...
0
votes
1answer
62 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 ...
3
votes
3answers
86 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 ...
0
votes
2answers
129 views

OLE DB Driver sql 2008r2 to Oracle failing

I have a SQL Server R2 Installation running on Windows Server 2008 R2. I have installed Oracle Instant Client, registered the OraOLEDB.Oracle driver(orasql12.dll), and set the PATH in environment ...
0
votes
2answers
210 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 ...
0
votes
1answer
130 views

Oracle RAC, unable to connect to node vip [closed]

I have a new oracle RAC installation running 11.2.0.4 on redhat 6. I can connect to sqlplus from a client using the node name but it is timing out when i use the node vip eg. first one works, second ...
0
votes
1answer
22 views

Unable to configure oracle 11g net server

I installed Oracle 11g on my Windows Server 2008 workstation. After I try to configure oracle net server with port number 1521. But ONS error say to me as "Use another port number. This port is ...
1
vote
1answer
89 views

Compact Spool file from Sqlplus

I have scripts that I run in Oracle SQL Developer and then export the result to my pc as a csv. I now started using sqlplus with a .bat to run my scripts and spool the result to my pc. But I noticed ...
0
votes
0answers
129 views

php oracle 11g client side character encoding

My oracle 11g database has utf8 character encoding. And data like ä ö ü etc. Oracle is not on the same host as my php application. The database host can be a Windows 7 machine (in Windows-1252) and a ...
6
votes
2answers
88 views

Will the Oracle optimizer still plan a join if I'm not reading any columns in the joined table?

Suppose I have the following view: CREATE VIEW dummy AS SELECT a.some_column, b.other_column FROM a LEFT JOIN b USING (key_column); When I do a SELECT some_column FROM dummy, is there a way to ...
0
votes
1answer
56 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 ...
0
votes
1answer
27 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
72 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
2answers
186 views

ORA-00600 argument description

I am experiencing several ORA-00600 error events, presumably during exceptionally high I/O. Does anyone know where I can lookup the meaning of the following arguments for ktspfmdb:objdchk_kcbnew_3? ...