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

1
vote
1answer
35 views

how do I change regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm') to only read 132 characters each time

how do I change the query below so it only reads and displays 132 characters each line with w(id, line#, line, rest) as ( select id, 1, regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'), ...
4
votes
2answers
2k views

How to separate out individual sql statements from Oracle SQL script for execution from java code

I have Oracle DB 11g Enterprise Edition and I want to execute sql script from my java code. I am reading this sql script from a .sql file and the script contains statements such as create table, ...
3
votes
2answers
43 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
1answer
126 views

fastest way to generate permutations of a table with n columns and put in a new table added one more question to same how to remove duplicate rows

As i am from mathematics background and require to do some huge permutations to start in my work kind help. If i have a table by name sample1 with n columns what is fastest way to get all the ...
0
votes
0answers
23 views

How to connect to oracle 12c from sqlplus without password in command line

This may seem as weird question: If i have user called demo with password Pass1234 When connecting to oracle 11g I can run sqlplus like this with password in command line: on DB host: C:\> ...
-3
votes
0answers
28 views

Oracle Restoring Database on Existing Database or rename database

I want to change my DB name in Oracle. I am using this command: nid TARGET=SYS/welc0me12$@CSPROD DBNAME=CSGOLD SETNAME=Y After that I am getting an error: Datafile ...
3
votes
1answer
58 views

Drop 15 days records in monthly partition

I have created a monthly partition on my table like below: PARTITION BY RANGE (PARTITION_KEY) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION MayPartition VALUES LESS THAN ...
0
votes
1answer
56 views

How to configure sql developer with oracle 12c?

I'm trying to connect sql developer to a fresh installation of oracle: I can connect to sys user via sqlplus I can connect to sys user via sql developer when I select Connection Type ...
0
votes
2answers
3k 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 ...
0
votes
2answers
35 views

RMAN Overhead on the database

What is the overhead of RMAN hot backup (full backup including archive logs) being performed during business hours? Does it actually affect the performance of the database? What is the case when ...
-1
votes
1answer
40 views

Singe database account for all users [on hold]

Welcome everyone, I'm wondering if one common database account for all users of web application is enough or I should create database account for each user in order to carry out some querying? Can ...
1
vote
1answer
138 views

Oracle dbms_scheduler: Can I create a job that will not overlap with itself?

How can I ensure that a dbms_scheduler job will never have more than one instance running? For example, if I have a job that is scheduled to run once a minute, but it actually runs for three minutes, ...
1
vote
1answer
375 views

how to prevent an oracle block corruption from duplicating a database with a few nologging tables

I got a few data corruptions in a test db when I performed a duplicate from a production db where a few tables have the nologging option. Oracle support suggested to turn on the force logging option ...
0
votes
1answer
21 views

Oracle 12c : Unable to install sample schemata on Windows 10

I've gone through lots of trial and errors in installing the sample schemata for Oracle 12c and still can't get it right. I have a pluggable database named PDB01. I also have an instantclient ...
0
votes
1answer
148 views

Converting sql into mongodb nosql

I am trying to convert oracle sql into its mongodb equivalent version. create table my_hll as select mod(ora_hash(n), 1024) bucket, max(num_zeroes(trunc(ora_hash(n)/1024)))+1 val from previous_data ...
2
votes
1answer
281 views

Estimating tablespace size

I'm trying to estimate the size of a tablespace using these steps: Space Estimations for Schema Objects and I was little confused about some of the steps. In step 3 for calculating the rowsize I ...
2
votes
1answer
1k 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 * ...
1
vote
1answer
24 views

Statement specific auditing - Oracle

I have enabled default auditing in one of my database. Oracle 11g. Audit_trail is set to DB, other than that for a particular schema 'test' I have set: audit all on test by access With the above ...
0
votes
2answers
7k views

sqlplus startup - failure in processing system parameters

I am trying to do the startup command This is what I have done: set oracle_sid=ORCL sqlplus /nolog SQL> conn /as sysdba startup It shows this error message: failure in processing system ...
0
votes
1answer
36 views

Mixed mode auditing - 12c

Oracle 12c database comes with default Unified auditing enabled even if we set it to FALSE which has ORA_SECURECONFIG policy enabled by default. Apparently, the audit options for that particular ...
3
votes
1answer
773 views

Do OR conditions in Oracle SQL queries spoil index use / cause full table scan?

I have a query on Oracle 11g of the following type, which results in a series of highly inefficient full table scans: select t.Id, t.ObjectId, t...[other Tasks columns] , od1...[ObjectData1 ...
0
votes
1answer
22 views

How to export own objects using expdp without special privileges?

As per this post on Oracle forums expdp can be used to export your own schema without any special privileges: It all depends on what you want that user to be able to export/import. If all that ...
1
vote
1answer
32 views

Why doesn't Oracle show my privileges?

I have a user that is able to drop, alter, create tables and indexes. But when I list the privileges for my Oracle user it does not show any CREATE TABLE or other such privileges. How am I able to do ...
-1
votes
0answers
17 views

Grant access schema to user [on hold]

Getting error while writing the following query SQL>grant select, insert, update, alter, delete on schema::schema_name to user; showing as missing keyword –
5
votes
3answers
26k views

Grant create permission on a specific schema in Oracle 11g

I have two users A and B. I want to grant B the permission to create, drop, etc. all tables in A's schema. As far as I can see, I can grant B full access to all schemas not a specific one. Is this ...
0
votes
2answers
24 views

expdp full export not exporting all schemas

On Oracle 12c, fairly new database with APEX installed. I do a expdp \"/ as sysdba\" directory=dp_dir full=y dumpfile=full.dmp logfile=full.log And I am getting just a few tables from SYS and ...
0
votes
0answers
7 views

JMX interface to an API that provides Oracle Database performance statistics [on hold]

Do we have any API that provides Oracle DB performance statistics ? I am looking for such an API and how to connect to it using JMX (if it has any JMX interface)
1
vote
1answer
1k views

ORA-01122: database file 1 failed verification check

I moved data file and database started fine but I did the following steps: SQL> shutdown SQL> startup nomount SQL> alter system set control_files='/var/oracle/oradata/control.dbf' ...
0
votes
1answer
18 views

ORACLE - Trying to return MAX or MIN based on a CASE statement

I'm working on a query to give me either a MAX or a MIN based on whether another column is divisible by 2. SELECT CASE mod(ROLE_TYPE, 2) WHEN 1 THEN MAX(SALARY) ELSE ...
3
votes
3answers
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 ...
0
votes
3answers
115 views

Oracle union default sort is weird [closed]

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
19 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
35 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
23 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
152 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
107 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
116 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
139 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
391 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
61 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
40 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 ...
0
votes
2answers
130 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
251 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
251 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
67 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, ...