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

0
votes
2answers
55 views

Decode Function in Oracle

I have a table named as transaction,in that table trans_type column is there.I want to check the trans_type like bellow if(trans_type = 'AR','AD','AF','AG') then trans_type:='A'; elsif trans_type = ...
0
votes
0answers
47 views

How to find the estimated time for ORACLE SQL Query

Help to find out the following I have 8 million rows in an Oracle table, and this is being queried by a BI tool every day and taking more time. Is there any like explain plan that will give me a ...
1
vote
3answers
67 views

Mysql to Oracle communication

I have a website running with php and mysql on Centos(Amazon instance). The newly brought ERP(to be integrated with existing system) uses Oracle as db(located on a separate Windows Server). The orders ...
0
votes
0answers
93 views

connect to oracle db server remotely

I have Oracle 9i db installed on machine connected to the internet behind nat ,and the Oracle DB server is listening on default port 1521,and when I connect over lan to DB server its worked ...
0
votes
0answers
252 views

Uninstalling Oracle Database Products: Which & How to?

Long time reader, first time poster. For our client's project we are using Oracle 11g. It's a simple C# web application so we are using Oracle's most basic components. Now my project team has been ...
0
votes
1answer
55 views

Error exp oracle XE on centos

i have a problem when i try to export ... Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR ...
1
vote
0answers
43 views

Oracle parallel query: finding out which Producer a Consumer is waiting for

One of our materialized view refresh's (part of a daily ETL routine) was hanging today; it usually completes within 5 minutes, but this time, I had to kill it after 23 hours. V$SESSION contained this ...
0
votes
0answers
51 views

Impdp over DB_LINK failing with ORA-31631

I am getting below error message while trying to execute impdp between our SIT and UAT environments: ORA-31631: privileges are required ORA-39149: cannot link privileged user to non-privileged user ...
1
vote
1answer
55 views

How can I insert records 'into null' in Oracle?

I'm looking for a way to insert records into a 'black hole'; i.e. execute a query for a certain table successfully, but make the records disappear. Something like INSERT INTO NULL, if you will. MySQL ...
2
votes
2answers
181 views

Can't drop UNDO TABLESPACE

We have oracle 11gr1 rac two instances on two different servers, new undo tablespace UNDOTBS20140508 created 30G, I want to remove the old one UNDOTBSX02, I did the following: CREATE UNDO TABLESPACE ...
0
votes
0answers
40 views

ORACLE 10.2.0.5 DB: How can I start replication of an existing schema into a new database with zero downtime

Please, note that I'm limited to Materialized view replication and datapump, so my question is whether the above can be done within my specified scope: Ensure no transactions in the database for ...
0
votes
2answers
181 views

ORA-00119 & ORA-00130 when trying to startup Oracle

I'm having a problem with an Oracle database. Yesterday everything was working fine, I had an application that was using the database and there was no problem. At the end of the day I installed MySQL ...
0
votes
3answers
231 views

Connect to sql plus from command line using connection string

Let's say I have a Oracle database. I have a username = x, password = y, database = z. Also I know the port = a, SID = b, Hostname = c. So how do I need to connect correctly? I used many options ...
1
vote
0answers
38 views

plsql procedure gets invalidated

procedure involving dblinks gets invalidated regularly .we need to recompile every time.How to find what is causing the problem in my case procedure p1()--resides in db1 is begin .... select col1 ...
0
votes
1answer
194 views

lsnrctl: cannot execute binary file: Exec format error [closed]

When I type lsnrctl from my command prompt the below error message is displayed: oracle@Gaurav:~$ lsnrctl bash: /u01/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl: cannot execute binary file: Exec ...
0
votes
0answers
53 views

Find out profile creation date

Our DBA says that a specific profile was created but we cannot find out its name (and it wasn't provided by the DBA). We want to find out by looking at the profile creation date. How does one find ...
1
vote
1answer
60 views

Is it possible to do flashback after truncate?

Is there any possibility of doing flashback after truncate ? Thank you in advance.
1
vote
1answer
61 views

Oracle optional select value

I'm writing query to select students with their Program, Country and Teacher. So far, I have the following query: SELECT H.STUDENT_CODE, H.FIRSTNAME, H.LASTNAME, H.PROGRAM, ...
2
votes
1answer
152 views

Using OPENQUERY to execute a script

I have a SQL Server 2008 instance which I want to use to import data from an Oracle server. I have set up a linked server that works correctly when running simple queries like SELECT * FROM table. ...
1
vote
1answer
52 views

How can I set block size on Oracle 11 when creating a database instance

Is there any way to configure Oracle 11g's default block size on installation? I have tried Oracle 11g r2 and r3, during both installations block size is 8k and it is disabled (and can not be ...
0
votes
1answer
28 views

PHYSICAL_READ_BYTES_DELTA and PHYSICAL_READ_BYTES_TOTAL

What is the difference between PHYSICAL_READ_BYTES_DELTA column and PHYSICAL_READ_BYTES_TOTAL column in DBA_HIST_SQLSTAT table? Are they correlate? Please provide also an example.
5
votes
1answer
87 views

Why does the CBO not chose a lower cost skip scan without stats?

In the script below, the CBO chooses an INDEX FAST FULL SCAN over an INDEX SKIP SCAN (this is illustrated by the queries with the UNION ALL but you can demonstrate it is true with just explain plan ...
0
votes
1answer
117 views

Converting multiple SQL implicit joins into explicit joins

I am trying to convert several queries that use implicit joins into explicit joins, as per the standard ANSI/ISO Entry SQL-92 [1]. But after a lot of googling I could not find any examples or guides ...
0
votes
1answer
37 views

Oracle Recovery

Oracle server died. Built a new VM and mapped out the volumes to the SAN. Installed the same version of Oracle on the new server and applied the same patches. How can I "register" or "attached" the ...
0
votes
0answers
54 views

Archive log backup error and backup completed with errors

My prod DB backup completed with errors stating the below error. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ...
1
vote
1answer
818 views

Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )

I have already seen the questioned in the stack overflow that might relate to my problem but let me emphasize that none of them could resolve my issue. issue is: when I wanted to integrate OWASP ...
0
votes
1answer
139 views

How to calculate time difference in sql

I have one table named test. In that table I have a column named test_time of type DATE. I want to calculate the time difference between test_time and sysdate in minutes. select 1440 * ...
0
votes
1answer
110 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
40 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 ...
0
votes
1answer
23 views

Oracle GRANT ROLE error [closed]

I am trying to apply a role to a user and getting this error: SQL Error: ORA-00990: missing or invalid privilege. Here is where I created the role, which worked fine logged in as SYSTEM. CREATE ...
1
vote
1answer
174 views

Is SQL*Net message from dblink > 30% a performance issue?

I have a Java web-application running under Glassfish. App accesses Oracle using JPA (eclipselink). Last 2 days I'm observing some perfomance issues: some messages about long running queries (10 to ...
2
votes
1answer
61 views

How to make sqlplus prompt longer than 50 chars?

I've set a prompt in my sqlplus glogin.sql a little bit long, mainly because of some internal variables and control sequence. Starting sqlplus I get the following error: String beginning ...
2
votes
0answers
121 views

How can I connect to a network MySQL server using ODBC?

Somewhere on my company's network there's a (Oracle) MySQL server named FOO. I see FOO reference in SAS code written by my team's analysts, who use SAS's PROC SQL command to connect to it using ODBC. ...
1
vote
1answer
172 views

First query running in PL/SQL Developer results the error

When I run first query in PL/SQL Developer I got the error: Dynamic Performance Tables not accessible, Automatic Statistics disabled for this session You can disable statistics in the preference ...
1
vote
0answers
46 views

Refreshing Materialized view creates many child cursors

Doing some tests, I noticed that having REFRESH FAST ON COMMIT materialized view and flashbacks enabled cause Oracle to create many child cursors in some cases. For example, CREATE TABLE table1 ...
1
vote
1answer
36 views

Can somebody help me understanding the difference between view,materialized view and snapshot?

Can somebody help me understanding the difference between view,materialized view and snapshot?
1
vote
1answer
94 views

SQL Query Rewrite - Help Needed (Oracle)

Need help in re-writing the below SQL. The Query works fine for smaller database below 400 GB, but spends too much time for larger database above 600 GB. Oracle - 11.2.0.4 Database - Oracle ...
0
votes
2answers
60 views

How can I limit a hierarchical query to the point where there are multiple children?

This question is related to my previous question about hierarchical queries. I have a hierarchical query in Oracle 11gR2 that returns something like this: - Level 1 - Level 2 - Level 3 - ...
1
vote
0answers
19 views

How can I select rows from a hierarchical query with the lowest level? [duplicate]

Apologies for the cross-post from StackExchange DBA - I wasn't sure which site was best for this question. I have a hierarchical query in Oracle 11gR2 that returns something like this: Parent ...
2
votes
2answers
213 views

How can I select rows from a hierarchical query with the lowest level?

I have a hierarchical query in Oracle 11gR2 that returns something like this: Parent (Level 1) Child (Level 2) Grandchild (Level 3) Child (Level 2) Grandchild (Level 3) Grandchild (Level 3) ...
1
vote
0answers
48 views

Why does Oracle trim passwords? [closed]

While looking over the settings of an application, I saw a 'typo' in the password for the database connection - it was like password='secret ', but the connection worked fine anyway. I tried the same ...
0
votes
1answer
50 views

Wallet open failed

I have a oracle server installed in a machine and e oracle client on another machine. I have created a wallet using this commands C:\Users\MRBULL93>mkstore -wrl C:\VideoWallet -create ...
1
vote
0answers
74 views

Oracle equivalent of mysqldump [closed]

Is there a program that would let me dump a table as a set of insert statements. I do not want to use data pump as that generates a binary file, requires DB privileges and has a learning curve. For ...
2
votes
2answers
86 views

SQL query for next occurrence of specific date

I am working on a function for our database that contains contract data. Each contract expires on a certain date, and then each contract has an expiration type that determines what happens after that ...
0
votes
0answers
77 views

Bcrypt in Oracle

I am trying to save the user password using Bcrypt algorithm in Oracle 11g. I know this can be done in Java easily, but in this case i want to do this in DB side. Can any one help me in achieving ...
3
votes
2answers
238 views

ORA-00979: not a group by expression

I'm getting the error: 'ORA-00979: not a group by expression' when I try to run this query. select empno, empname from emp.employee group by empno, empname having empmsal > avg(empmsal); I'm ...
2
votes
3answers
452 views

Oracle 12c in-memory option

I've been told that there's an oracle in-memory option for 12c and that it uses columnar compression to get some great query speeds. I'd like to leverage it for some business intelligence stuff. I've ...
0
votes
1answer
73 views

How to generate current Oracle timestamp in perl?

I use DBI. And I want to make some like INSERT INTO ... VALUES (the_generated_timestamp, ...); What is the command? How could it be created, the perl ``ocaltimescalar value has a very complex, for ...
1
vote
0answers
65 views

Redefining a table with a user-defined type using DBMS_REDEFINITION

The application which we developed has one schema, USER1, which has tables, types, packages etc. I have to move some of the user-defined types to a different schema, The reason for doing this is to ...
0
votes
2answers
101 views

How to put Oracle Redo Log file at the outer edge of disk for performance?

I always read that the outer edge of disk is the fastest place. Somebody said "You better put your Oracle Redo Log file at the outer edge of disk". My question is: How to put Redo Log file at ...