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
3k views

Not receiving archivelogs from production to remote side

I have a scenario in which we have Designed data guard. Where we have 2 standby sides. One standby is located locally and 2nd standby is located on remote side. The distance between local and remote ...
2
votes
1answer
552 views

Reorganize Objects in OEM - Host Credentials do not have enough privileges

When I try to re-organize objects from OEM (11g) and enter my host credentials (not "oracle"), I get the message: "An error occurred verifying the host credentials. Make sure the credentials are ...
1
vote
1answer
26 views

Flat similar rows on few columns - Oracle

I wonder is there any way to flat data like this using sql query maybe you can help me The data looks like this: id name customer_code column1 column2 ----------------------------------------- 1 ...
0
votes
1answer
18 views

Query on Asmlib

I have Oracle Linux running on vmware hosting an Oracle database with ASM (single instance). There isn't any operating system device persistence and therefore whenever my OS reboots, the disk number ...
0
votes
2answers
277 views

List full text indexes in Oracle database

Using SQL Server, it's possible to execute a query that returns all the columns that contain a full text index. How can I do something similar using Oracle? Is it possible?
1
vote
0answers
55 views

Getting email notification when jobs are created under user_schema rather than sys?

I log in as sys and set the smtp for scheduler: BEGIN DBMS_SCHEDULER.set_scheduler_attribute('email_server', '10.1.1.15'); DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'user@compny.com')...
0
votes
1answer
251 views

Keeping messages in a backup table after dequeue is completed

I would like to transfer the enqueued messages to a backup table after they have been dequeued. Currently we are using DBMS_AQ.DEQUEUE with mode dequeue_mode := DBMS_AQ.REMOVE. So after the message ...
0
votes
1answer
184 views

“Credential retrieval failed” during recovery of an Oracle database

When starting up automatic instance INSTANCE: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-...
0
votes
1answer
481 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
2answers
155 views

Difference between TDE/VPD/Data Masking/OLS - Oralce

I've come across many kinds of data masking techniques used in Oracle database.. some are licensed some are included in enterprise edition itself.. leaving the licenses thing behind as it is ...
0
votes
0answers
38 views

Oracle 11g Auditing and Collecting the SQL Text with AUDIT_TRAIL=OS

I'm certainly no Oracle DBA, so please forgive any ignorance on my part. I've done a fair bit of research, but I'm a bit stuck with something at the moment. Hopefully someone can help. I'm currently ...
-2
votes
1answer
42 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 E:\APP\ADMINISTRATOR\ORADATA\...
0
votes
1answer
25 views

How monitor TEMP space allocation for a particular object type like Clob\Blob

I am new to Oracle and i wanted to know if there is a way to monitor Clob\Blob objects allocation in Oracle Temp Space. With JDBC 4.0, we have clob.free() and blob.free() methods and i want a way to ...
1
vote
1answer
45 views

How can I fix these broken synonyms?

When I run the following query: SELECT S.OWNER AS SYN_OWNER, S.SYNONYM_NAME AS SYN_NAME, S.TABLE_OWNER AS OBJ_OWNER, S.TABLE_NAME AS OBJ_NAME, CASE WHEN O.OWNER IS NULL THEN '...
2
votes
4answers
2k views

How do I tell the actual IOPS used by Oracle?

This is hopefully a simple question. We have a large DB deployment that we need to scale up over the next year. For scaling we have been relying on our vendor, but recent developments have caused us ...
0
votes
2answers
49 views

Why can I execute this query but not see its execution plan?

Consider the following query: SELECT table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' ||owner ||'.' ||table_name)),'/ROWSET/ROW/C')) AS COUNT FROM ...
3
votes
0answers
29 views

Dataguard: preventing logical standby to loose track after flashback database on primary

We have 2 Oracle databases; a primary and a logical standby, configured with Dataguard. Whenever we have an application rollout on the primary, we create a restore point in case anything goes wrong. ...
0
votes
1answer
1k views

00911. 00000 - “invalid character”

I am completely new to Oracle DB Administration an PL/SQL. I get an "invalid character" error trying to execute the following code in Oracle SQL Developer. This is my first statement in PL/SQL. SET ...
0
votes
2answers
60 views

Oracle EM 11g - query to find out CPU utilization

in OEM 11g, if you see performance, there are graph about cpu utilization, memory utilization, and disk I/O utilization. my question is: what is the script to see CPU-Utilization history ? Thank ...
0
votes
0answers
34 views

Archive and Logging issues in oracle 12c

When I want to enable archive log on oracle till 11g i used alter database add supplemental log data(Primary key,unique) columns In 12c I can run the same on the PDB or on the core. I need the ...
7
votes
4answers
32k views

How to find the actual space being consumed by indexes on a table in Oracle?

I want to find the actual space being consumed by indexes on a table in oracle 10g . I do not intend to include the space reserved by oracle for future usage. (The overhead by oracle should not be ...
0
votes
3answers
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
562 views

Connect by prior - multiple fields and query efficiency

I have 2 tables one of which defines the hierarchy for the other equipment id desc 1 item1 2 item2 3 item3 4 item4 5 item5 6 item6 7 item7 8 item8 ...
0
votes
0answers
36 views

DBMS_Scheduler is running fine at regular intervals but procedure within scheduled program not getting executed

DBMS_Scheduler is running fine at regular intervals but the procedure within scheduled program is not getting executed. We have created one scheduled program which has PL-SQL block contains stored ...
0
votes
1answer
21 views

Insert data into oracle table with loop?

How to use sql to insert line detail data into header with the output like below. line detail MO_ID SEQ_NO QTY LEVEL 121M 2 3 121M 3 9 125M 2 6 125M 3 7 125M ...
2
votes
3answers
3k views

Delete backups from old incarnation in rman

I did a refresh on a non-production database and these are the incarnations: List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time 1 1 ...
0
votes
1answer
47 views

PUBLIC privilege potential security threat - Oracle database

Recently, audit findings at my site found that PUBLIC being by default assigned to all the schemas that are created is a big risk since when a new user is created and even if we were to assign only ...
0
votes
2answers
54 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
738 views

Internal query on CDEF$ and CON$ very CPU bound

I am checking an application (Infor ERP LN) that loads a lot of data into an oracle 10.2 database. While checking one of the user sessions with SQL_TRACE and its output with tkprof, I found that the ...
0
votes
1answer
419 views

Oracle 10gR2 - fix for SSL version following Poodle attack

We use Oracle utl_http to send https requests from within our database (10gR2). As SSLv3.0 is now considered insecure (POODLE) we need to upgrade to TLS. How can we ensure TLS usage when sending ...
0
votes
1answer
79 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
74 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
1answer
29 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
131 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
97 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
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
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
402 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
304 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
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
39 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 ...