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

0
votes
1answer
85 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:\> ...
1
vote
2answers
80 views

Troubleshooting / traceroute within Oracle Instant Client?

I am troubleshooting a freshly reimaged backup server Win2000 / Oracle 10.1.0.5.0. Here's what I did: Installed Oracle 10g by copying Basic Instant Client package and ODBC package into same ...
1
vote
2answers
2k views

Achieve log transfer to standby (Data Guard)

I am not able to transfer my archive logs from primary to standby. I executed the following query: archive log list Database log mode Archive ...
0
votes
0answers
30 views

updating entire row with values from other table [duplicate]

I have two tables, namely product and product_bkp. Due to faulty mappings, some of the rows from table product got updated wrongly. Now I want to update them using the backup table product_bkp. I ...
1
vote
1answer
44 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
53 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
132 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 ...
3
votes
1answer
73 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 (TO_DATE('...
0
votes
1answer
99 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 "Local\Bequeath"....
0
votes
2answers
4k 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
44 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
vote
1answer
145 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
404 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
37 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
150 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
308 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 ...
1
vote
1answer
28 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
1answer
40 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 ...
1
vote
1answer
40 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 ...
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 ...
1
vote
2answers
56 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
1answer
31 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
124 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
20 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
41 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
vote
3answers
127 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
138 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 ...
2
votes
2answers
78 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
45 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
149 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
257 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 ...
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 ...
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
79 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, route....
0
votes
2answers
70 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
74 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
90 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
71 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
51 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 ...
4
votes
1answer
88 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
82 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
31 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
51 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
60 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
48 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
168 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 ...
4
votes
2answers
42 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 ...
2
votes
1answer
69 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 ...