All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

3
votes
5answers
5k views

Is it safe to delete dummy tables Oracle 11g XE

i created a database schema in 11g express edition, however there are some APEX tables and some dummy data... Is it safe to delete them? also i would like to know what does apex means and why they ...
1
vote
3answers
93 views

Oracle union default sort is weird [on hold]

As you know union removes duplicated rows. I thought it removes from the latter ones, but it doesn't. I found out that if there's no order by clause, Oracle sorts the merged dataset by the first ...
0
votes
0answers
9 views

How to use an external encryption key in an Oracle function?

I am storing People data in an Oracle 11g database. I am implementing symmetric encryption on one sensitive data column in my People table. For this, I created an Encrypt function based on the example ...
0
votes
1answer
16 views

Does Oracle randomly switch execution plans to check if there is a better solution?

I had a talk with an external colleague about oracle "swinging in" its performance. In detail it was about, that oracle optimizes its execution plans for the same query by time. I don't talk about ...
-1
votes
0answers
17 views

Decomposition of complex queries [on hold]

select c.ssn,c.cname,c.address,c.ph,c.age from customer c,account a,acc_cust ac,loan_cust lc where c.ssn=ac.ssn and a.accno=ac.accno and c.ssn=lc.ssn and a.atype='joint' group by ...
1
vote
1answer
151 views

Type with nested table in oracle

I want to create a type with a nested table. I have a table called assignment_tbl which I want to put a type assignment_status. This assignment_status contains primitive types but also contains a ...
1
vote
2answers
106 views

Oracle RAC VIP issue

I have a two node RAC setup where my scan ips are registered in DNS and my public Ips are also registered in the dns. Now while connecting from my windows machine through oracle client in sql ...
1
vote
3answers
91 views

Avoid repeated traversing of hierarchical data when parsing a big tree frequently

I have a hierarchical/tree data structure in Oracle/RDBMS where I need to traverse the tree frequently. The tree is huge and therefore parsing it every time will not be efficient. A better way could ...
0
votes
1answer
134 views

“Oneway” Replication from Oracle DB to TimesTen

currently I'm really stuck into a problem and I not able to finde a nice way to realize that. I need a suggestion to replicate from oracle db (some tables) to a TimesTen. A connection is only ...
1
vote
1answer
138 views

What is the fastest way to clone/copy oracle databases on the same machine?

I want to clone oracle databases on the same machine and I need to change user etc. from e.g. PROD to DEV. For that I use expdp/impdp, but this tends to be slow with increasing database size. I need ...
1
vote
1answer
377 views

Tracing failed login attempts in Oracle

I have some users who are logging in with a wrong password and their accounts get locked. Now I need to find the source of the failed login attempt. The machine, username etc. Is it possible to find ...
2
votes
2answers
50 views

RMAN DUPLICATE from backup without Target or Catalog: How is the aux instance created?

I am trying to duplicate an Oracle database based on backups to test if they can be used in case of complete system failure. Hence, I want to do this without a connection to the source database. I'm ...
0
votes
0answers
36 views

Using a trigger to check whether a matching record exists

I have a database model set up as in the picture above, modelling a student's enrolment into a unit with an associative entity to facilitate the m:n relationship. I wish to code a trigger that will ...
3
votes
1answer
31 views

Golden Gate realtime replication basic question

I have a task to replicate unidirectional real time data from source database A to target database B. I do not need all the table from A, only few tables and few columns inside those tables. Say 10 ...
0
votes
2answers
128 views

Pentaho Kettle - Unable to connect to virtual Oracle Database

I'm attempting to connect to a virtualized Oracle database but I am receiving the following error: Error connecting to database: (using class oracle.jdbc.driver.OracleDriver) Listener refused the ...
0
votes
3answers
250 views

Replacing the results of a row

I'm working with Microsoft Reporting Services 2008. In our database, we have a small group of confidential students that need to be taken into account for several queries. If they are a confidential ...
1
vote
1answer
337 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 ...
0
votes
1answer
2k 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 ...
0
votes
1answer
250 views

Check Oracle Service is Started from Batch Command

Uses: Oracle9i Standard Ed; I have a schedule which performs a Dump Import and afterwards the System Reboots itself, which is happening without any problem. After the System had rebooted, I have ...
2
votes
3answers
3k views

Oracle 11g installation stuck at installation

I'm trying to install oracle 11g on fedora by following this tutorial but it stuck after I click Next in step 8. The screen does not go away even after several hours. There's no log file in ...
4
votes
3answers
2k views

Can you time how long an Oracle import takes?

I'm on a Windows machine with Oracle Client 11.1.0, and am going to import a database dump. I'm importing from the command line with the "imp" command. Is there an option to time how long the import ...
2
votes
1answer
65 views

Oracle Min - ORA-00937 not a single-group group function

I try to find companies whose fare has minimum fare of destination which more than one companies use that destination. My command; SQL> select tickets.bus_id bus, tickets.fare fare, ...
0
votes
2answers
66 views

How to get past account lock information in oracle database?

Is it possible to query the history of previously locked/unlocked Oracle database user accounts? I've tried dba_user but there is no option to get history.
1
vote
2answers
64 views

How to get the DDL for Oracle object without extra characters

When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script. For example, if I create this sample ...
0
votes
2answers
74 views

Error during Oracle database 12c Release 1 installation

I am trying to install Oracle database 12c on Oracle Linux 7 version. But it is keep on failing with the below error. [INS-30131] Initial setup required for the execution of installer validations ...
1
vote
2answers
58 views

Sanity Test on Oracle Upgrade Database

My oracle sever has been upgraded from Oracle 10g to 12c. DBA has created a box for that now my question is how developers will perform testing on that.. Can anyone provide me the sanity or unit ...
2
votes
2answers
37 views

why I can't set oracle sessions value = 35?

I want to simulate the error "ORA-00018.Maximum number of sessions exceed",so I want to limit sessions = 35 and I am going to open serverl sqlplus to simulate this error,but sessions number didnt ...
5
votes
1answer
87 views

Oracle - physical writes to an index

We have a large UPDATE (affects about 200K records) on a huge table (80M records). Due to a coding mistake, the UPDATE updates the same set of rows (200K) all over again with the exact same value. ...
3
votes
1answer
77 views

Can Oracle TimesTen (or 12c's In-Memory) benefit from GPU?

I have looked into TimesTen and In-Memory recently. They claim they use SIMD to speed up queries. We use HP DL380 G9 Servers for our DB. Since This series are limited to the Intel Core architecture, ...
0
votes
2answers
28 views

Can multiple Oracle databases share a single parameter file?

I'm a developer working on an application team that supports a system with one production database and 4 test databases (2 Dev, a SIT and UAT environment). The prod database exists on it's own Unix ...
1
vote
2answers
43 views

How can I stop Oracle Shutdown command?

I used Shutdown normal, instead of Shutdown immediate. Now I want to stop the process, because its taking too long. How can I do it?
3
votes
1answer
35 views

Oracle 12c Rman Duplicate Archive Log Not Verified

I am trying to duplicate a DB from live to dev but it is failing. Can anybody advise what is going wrong? Both databases are running Oracle 12c. All database files seem to be copied over but it fails ...
1
vote
1answer
5k views

Oracle 11g R2 on CentOS 6.3, and Net Configuration Assistant

This is my first time installing Oracle 11g R2 on Linux. I followed all the instructions in the Oracle documentation, but in the installation process, the Net Configuration Manager is failed. I ...
1
vote
1answer
45 views

Oracle index with REGEXP_REPLACE is it a good idea?

I have a table of contacts with 1 million registers and now the user wants to search in it by the telephone. Trying to avoid a fullscan I decided to create a new index in this column, but the trick ...
6
votes
2answers
138 views

ANALYZE TABLE..VALIDATE STRUCTURE runs forever

So, as I've mentioned before, I am a SQL Server DBA/Consultant who is helping out a customer with their Oracle databases. Mostly, I've been able to figure out what I need to and work through things ...
0
votes
0answers
15 views

Oracle schema for educational Purpose [on hold]

Is there any schema other than HR in the oracle database.? I am learning something, Just wanted to explore other scenarios other than HR. Thanks AK
3
votes
2answers
30 views

How to assign a constant to a value on all rows, if that value is adjacent to some value on any row

I have data like this (STATUS, and NAME are the column headers): STATUS NAME ACTIVE A1 INACTIVE A1 INACTIVE B1 INACTIVE B1 I want to include a CUSTOM field that returns a ...
1
vote
0answers
27 views

Oracle 11g Administration Tools [on hold]

i'm recently install oracle 11g XE in linux mint using this guide: https://mikesmithers.wordpress.com/2011/11/26/installing-oracle-11gxe-on-mint-and-ubuntu/ (doing all it's works, and yeah i know why ...
0
votes
2answers
782 views

RMAN 11g/12c Client compatibility

I'm having a Recovery catalog database 12c version. While I'm trying to register the target database (11g) with the catalog I get the following error: Recovery Manager: Release 12.1.0.2.0 - ...
1
vote
1answer
126 views

Oracle connection from Squirrel SQL

I am trying to set up Squirrel SQL for the first time on my Ubuntu Desktop. Here is the SQLPlus command I use to connect to my DB: sqlplus64 "user/pw@pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com" ...
1
vote
1answer
9k views

JDBC pooling Oracle driver Socket read timed out

I run Java EE application on Glassfish server v3 together with Oracle 12 DB on the same machine under Windows Server 2012 64bit. I use latest ojdbc7 driver. Connection pool config: ...
2
votes
1answer
55 views

CRON and RMAN Backups

We run RMAN backups via CRON jobs, but find the out-of-the-box emails with CRON to almost do what we want. I know we can channel the RMAN output to /dev/null so no email is sent, which for any ...
2
votes
1answer
757 views

How do I delete a file that has been locked by UTL_FILE?

I wrote a flakey PL/SQL script that crashed out and forgot to close the file handle (type UTL_FILE.file_type). Now I can't delete the file. Short of restarting the instance or rebooting the box, is ...
0
votes
1answer
980 views

How to select data out of an Oracle collection/array?

Due to needing some complex data manipulation and not being able to use PHP, I crafted a procedure (not stored, it's just run when we run the query) in Oracle to manipulate the data and store it in an ...
7
votes
4answers
22k views

Why use explicit cursors instead of regular loops?

I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data: for i in (select * from STUDENTS) ...
2
votes
1answer
2k views

sqlplus is not starting

I have a strange error on a Red Hat machine. InstantClient is installed in and ORACLE_HOME is set to /opt/oracle/product/112030_cl_64/cl/ The user which installed the client was oracle:oinstall. My ...
0
votes
1answer
178 views

RMAN catalog database

I'm about to create a RMAN catalog database for my setup, what are the general precautions or major pre-requisites as in DB parameters, compatibility,etc that I should take care while creating the ...
0
votes
1answer
113 views

Better ways than rownum limits on a union all for fallback-style queries?

I have three queries that generates some "reference" statistics and should be tried in order until one returns a result. These are currently coded as three separate calls to the DB, but I want to ...
1
vote
2answers
1k views

Specify NLS-Settings during Oracle installation under Windows

Is there an easy way to specify NLS parameters for the database during the installation? We want to install a new database (environment: Windows). For testing purpose we need NLS settings for ...
1
vote
1answer
29 views

ORACLE: Changing the grouping of a data to be based on month breakdowns

To keep this short and to the point, I am looking at modifying the grouping for this SQL query to go from NAME to Month/yy Here is a example of the script, and screenshots of the output file This is ...