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
41 views

Merging adjacent date intervals into a single row

Here is the scenario: If the employee transaction is continuous display as-is. if the employee got any gap display as a new row. Below is the sample table Emid Fromdate Todate ----- -------- ...
0
votes
1answer
28 views

Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?

DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider ...
0
votes
0answers
41 views

Upsert - Should I verify the row exists first, attempt an update, or try to insert first?

In a software that I'm working, I have a table with a few columns and a id. In some events in my software, I need to update some column of a record with a certain unique field (other column), but ...
1
vote
3answers
70 views

Finding “islands” of holiday periods for employees

I'm looking for some tips to find a nice solution for my problem: Let's say I have a table, where days of vacation of employees are stored. e.g. CREATE TABLE test_dates ( id NUMBER ...
0
votes
0answers
31 views

Fetching the data from the Oracle database is slow in Linux as compared to Windows

I am facing a performance issue in Linux while fetching data from the Oracle database. My scenario is as follows: 1) I have the backend server which is on UNIX with Oracle database running . The ...
4
votes
1answer
59 views

Oracle Database 12c: Unable to resize system tablespace

Problem: My java application is returning ORA-01691: unable to extend lob segment errors for every insert Cause: After running: SELECT * FROM dba_tablespace_usage_metrics ORDER BY used_percent ...
4
votes
1answer
57 views

How to install olaptrain sample schema on oracle 12c?

I need to install the sample schema available from the Oracle website: http://download.oracle.com/otndocs/products/warehouse/olap/olaptrain_install.zip I am trying to run the commands as given ...
0
votes
2answers
44 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 - ...
2
votes
2answers
28 views

Oracle Database 12c: TNS:permission Denied (CentOS)

(This post has a similar title to another post but the underlying cause and solution are totally different.) Problem: I can't login to sqlplus as sysdba but the listener seems to start ...
1
vote
0answers
16 views

Oracle 11g XE - Giving granting privileges to a stored procedure

I need to use a stored procedure to give certain privilges over a view to users. However I don't know how to give the procedure the necessary privileges to grant users privileges.
0
votes
1answer
18 views

Oracle 11g XE - Using a stored procedure to grant privileges to a table

I need to write a stored procedure that will grant certain privileges over a table to a user. However it appears that GRANT cannot be used within a Stored Procedure. My Curent code is as follows: ...
1
vote
1answer
27 views

Identify records with one identical field, but different timestamp

I am trying to process some data, stored in an Oracle DB, for a machine learning model I am working on. My current query uses this line in the SELECT statement to identify records that have a field ...
1
vote
0answers
33 views

SPOOLing gives extra rows, copy-paste does not

I am using oracle sql developer. My table has 58484 rows, but exporting to a csv gives 58532 rows! First I used this command (and tried fiddling with the set options for a bit to see what happened): ...
1
vote
0answers
38 views

DB CPU parameter in AWR - Oracle

What is the significance of DB CPU paramater in Automatic Workload Repository? Say in my case it's always above 100. Does it have benchmark metric as such to say that so and so number is the right ...
1
vote
4answers
85 views

What is the best way to create a table with 800 columns in a single script?

I prepared a script to create a table with 800 columns but SQLPlus and SQL Developer are not reading the whole script as they probably read only 7499/2499 characters max. As an alternative I am ...
3
votes
1answer
69 views

Display non-null and null count using case statement

In total there are 10 values in my table, 5 of which are null. I need to display the count using case statement in Oracle, like output below: Sample Data: +------+ | Eid | +------+ | 1 | | 2 ...
2
votes
2answers
32 views

Differentiate between a row modification and add?

Any recommendations into how one can figure out which rows were solely modified from an existing row (edit) and which one were solely inserted? I know minus can extract any rows that were modified ...
3
votes
1answer
50 views

Oracle sequences to MySQL

Migrating from Oracle to MySQL we've found some many incompatibility issues. We've some workarounds for many of them, however we still have one: The Sequences We have this table CREATE TABLE ...
2
votes
1answer
37 views

Split brain scenario - RAC and PXC

Even though split brain scenario occurs in both Oracle RAC and Percona's XtraDB Cluster, a two node cluster is allowed and split brain scenario is resolved in RAC but a two node is not recommended in ...
1
vote
1answer
47 views

Oracle intermittently throws “ORA-12516, TNS:listener could not find available handler with matching protocol stack”

While testing the Oracle XE connection establishing mechanism I bumped into the following issue. Although connections are closed on each iteration, after 50-100 connections Oracle starts throwing ...
1
vote
1answer
37 views

Optimize join - group by Oracle SQL filter

I have a few tables: CHEST: id fk_id_rap fk_id_ses_rap 1 1 1 2 2 1 3 3 1 SES_RAP: id an 1 2015 2 2016 RAP: id name 1 rap1 2 rap2 3 ...
1
vote
0answers
33 views

Dynamically passing column name to unpivot [closed]

I have a table enroll like this- Students Class1 Class2 Class3 student1 1 0 1 student2 ...
1
vote
1answer
33 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
0answers
22 views

Migrating schema from Entity-Attribute-Value (EAV) model to proper tables

I have an Entity-Attribute-Value (EAV) database in Oracle 11. For many, many reasons, I would like to model this using actual tables. Changing all the client code at once is not possible. Existing ...
3
votes
1answer
51 views

Segmenting data by percentage of a column total

I need to separate a list of records by the percentage of a column when they are summed to all records larger than the current record until a MAX percentage. Records are sorted DESC in column A, and ...
0
votes
1answer
39 views

Mirror copies of redo logs

I'm running an two node RAC database with ASM having two DGs one for data and one for redo. I have only one copy of redo and I have the need to have another copy of redo logs. 1. How to go ahead with ...
0
votes
2answers
49 views

PowerShell script to access oracle system views

I am using PowerShell script to access oracle 11g's system views as SELECT COUNT (*) AS COUNT FROM v$session but I keep getting "ORA-00942: table or view does not exist" I have created a user with ...
1
vote
1answer
32 views

Oracle client in Windows 7: how to change looking path for sqlnet.ora

For some security reasons I'm not able to amend the file sqlnet.ora, must meet security policies applied. Is it possible to change the search path of this file to some other drive/folder? Thank you!
2
votes
2answers
67 views

What will happen if I drop SYSAUX tablespace from Oracle 11G

As there are two mandatory tablespaces in Oracle SYSTEM and SYSAUX. They have mention on docs 1 " You cannot drop the SYSTEM tablespace. You can drop the SYSAUX tablespace only if you have the SYSDBA ...
1
vote
1answer
23 views

Parsing out date_entered field to capture TIME 'hh:mm:ss' based on time range

Based on the date_entered column, which is a data_type of 'DATE' how would I parse out the data to pull the time. Values in the DATE_ENTERED field (DATA_TYPE is DATE) looks like this 5/15/2015 ...
0
votes
1answer
48 views

What is “CSS operation: data query” wait event in an Oracle StatsPack report?

Has anyone seen the CSS operation: data query Oracle wait event and know what it means? There was a bug 8299200 in 11.2.0.1 which is fixed in 11.2.0.3. STATSPACK report for Database DB Id ...
0
votes
0answers
19 views

resource manager consumer group OTHER_GROUPS is not part of top-plan Test_plan

I want to use resource manager to limit execution time, with this parameter MAX_EST_EXEC_TIME. but running the below is giving me following error. how to fix it ? ORA-29382: validation of pending ...
1
vote
1answer
29 views

Oracle Enterprise Manager - stats split by user (schema)

Is there any way to split all the stats by user (schema) when looking at the Performance tab in the Oracle 11g Enterprise Manager? or effectively some other tool to retrieve that information - e.g. ...
1
vote
2answers
75 views

How does one identify if Oracle Data Guard is enabled in Oracle database?

How does one identify if the Oracle Data Guard feature is enabled in Oracle database? If this can be determined by a query, what is the query for finding the same?
0
votes
0answers
21 views

Need to Sync Oracle DB table data with mysql [duplicate]

I have a table with some data in oracle which i want to sync with mysql table once in a day, like a cron job. Is there any tool available which i can use as a cron job in Linux?. Thanks,
0
votes
1answer
22 views

Configure dbms_metadata.get_ddl() to leave off schema?

Can I configure dbms_metadata.get_ddl() to leave off the schema name of generated objects? I set these transform parameters begin ...
2
votes
1answer
46 views

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

How can I ensure that a 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, I don't want a ...
0
votes
2answers
34 views

How to match regular expressions in Oracle?

I'm using the following query: select * from table WHERE POSTAL_CODE LIKE '[a-Z][0-9][a-Z] [0-9][a-Z][0-9]'; However, this is not capturing anything. For example, a typical POSTAL_CODE looks like ...
4
votes
3answers
125 views

Oracle - restore a single table

In a large database, an activity was performed to upload the data. Due to some mistake, a month's data was entered again, which was left in the temp holding table. So, to cover this up, we took dump ...
0
votes
0answers
31 views

Materialized View does not reflect changes after fast refresh

I have a query with many aggregate functions working on almost a million records every time, it's taking up to (ten of) minutes to complete the query so I created a materialized view for it, and since ...
0
votes
0answers
33 views

How to call Oracle Function from POSTGRESQL

I am sorry if this topic already exists. I have: PostgreSQL 9.3 , Oracle 11gR2 on Linux . I used oracle_fdw to query Oracle table from Postgres but oracle_fdw can not call function. Questions: How ...
1
vote
0answers
14 views

Cannot update parent rows with trigger on the same table

I currently have the following two tables in my database CREATE TABLE folders ( id NUMBER NOT NULL, name VARCHAR2(100) NOT NULL, bytes NUMBER DEFAULT 0 NOT NULL, parentId NUMBER, CONSTRAINT ...
1
vote
2answers
53 views

Oracle regexp_like square brackets inside character set

This questions is in regards to Oracle, pl/sql, and the regexp_like function. I am trying to build a character set that will match on all typical special characters. My character set currently looks ...
0
votes
2answers
55 views

Is it possible to record all the SQL executed by a session?

There is a web application which is connected to my Oracle database server via JDBC. When a user does some actions on that web application, I want to know what SQL statements were submitted to ...
1
vote
2answers
45 views

Oracle: Can a user log in without a password?

I'm writing a sqplus-like program. I assume the connect string contains the password if it contains a slash, e.g. scott/tiger or scott/tiger@orcl. if connect string does not contain "/": prompt ...
0
votes
1answer
37 views

What is the advantage of pluggable database over normal databases?

Why creating pluggable database over normal database is good for system performance.
0
votes
0answers
18 views

.dmp file generating problem on oracle

I have a server windows2003 with oracle 10g and developer 6i. My experience is when I install developer 6i first and then install database 10g then .dmp file generate perfectly using imp or exp. but ...
0
votes
2answers
48 views

Oracle Error: the sga requires more space than was allocated for it

(Oracle SQL 12c running on Windows 10) My installation has previously been working fine. I was trying to set up one of my tables to use the In-memory option available in 12c. I was following this ...
0
votes
2answers
55 views

Unique constraint error Oracle

I have two databases - one is 11g and another 12c. 12c db has the exact data of 11g (expdp/impdp). Now I'm facing an error in one of the tables with unique constraint error. I have checked the ...
2
votes
1answer
30 views

What does Oracle use for handling a client connection (process or thread)?

SQL Server 2016 and MySQL 5.7 use thread-based connection handling. PostgreSQL 9.5 uses one operating system process for each individual connection. According to Oracle 12c documentation it's not ...