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
0answers
51 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
59 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
59 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
138 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
48 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
26 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
85 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
116 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
52 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
698 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
117 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
96 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
145 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
56 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
109 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
147 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
42 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
34 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
93 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
53 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
194 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
46 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
67 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
79 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
73 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
216 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
358 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
68 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
60 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
100 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 ...
1
vote
2answers
108 views

Average CPU utilization oracle database [closed]

I'm interested in understanding how DBAs think about the balance between Oracle's strict licensing requirements and the growing compute capacity of today's machines. Oracle's insistence that ...
2
votes
1answer
58 views

constraint in oracle

I'm trying modeling things like this using oracle: An account can have 1~5 members So I'll have a account table and a member table with a FK of account. But is that possible to check if the account ...
0
votes
1answer
45 views

How to prepare executon plan for given sql query on a server with 4 processors (Oracle database)

4 processors means the query runs in parallel. Select /*+ parallel(4) */ T1.A FROM T1,T2,T3 WHERE T1.A = T2.A AND T1.B=T3.B AND T1.C=T3.C Tables T1,T2 and T3 have 4,8, and 16 partitions ...
0
votes
1answer
51 views

Sort within row

I wonder, if there is a way to sort data inside each row? For example, I have table val1, 3, 2, 1 val2, 2, 3, 1 val3, 3, 1, 2 val4, 1, 3, 2 val5, 1, 2, 3 and want to select it sorted, as val1, 1, ...
0
votes
0answers
29 views

Where can I download jvm files for oracle on ubuntu

I want to install Java packages for oracle, and I found following files in the internet. @?/javavm/install/initjvm.sql; @?/xdk/admin/initxml.sql; @?/xdk/admin/xmlja.sql; @?/rdbms/admin/catjava.sql; ...
1
vote
1answer
56 views

How does direct path load work when dataguard is enabled?

There's a few posts about direct path and dataguard but I just want to understand exactly how it actually works. How maybe I made an incorrect assumption, but if direct path means it writes to the ...
1
vote
1answer
26 views

Locating a schema that owns a role

Let's say we've logged on under the A schema. I am able to execute the following statement grant select on wph_pden to LANDING_R; However, the LANDING_R object can not be located in the schema A. ...
1
vote
1answer
108 views

Parking Lot table for employees

I am having dificulty designing a table that stores the information about a parking lot with 100 spaces that are arranged by 5 columns x 20 rows for employees, guests, handicap, executives, ...
1
vote
1answer
169 views

Oracle Automatic Memory Management - SGA_MAX_SIZE

When using Automatic Memory Management, what value should SGA_MAX_SIZE be? Should it be 0? These are my current settings, and I'm unsure whether to leave SGA_MAX_SIZE or set it to 0. ...
0
votes
1answer
67 views

Error java.sql.SQLException: ORA-01555

Hey I am running a select * from query against ORACLE database. So is there a way we can avoid this kind of error. The query is fetching a large amount of data. It can take weeks to get that amount ...
0
votes
1answer
94 views

How to create and start a PDB for RCU in Oracle 12c

So I've made a fresh install on a virtual machine based on OEL 6.5 x64. All my installs are in /opt/apps I installed a Oracle Database 12c, with the Desktop Version selected (not server), UNICODE ...
0
votes
1answer
34 views

Schema Migration for application upgrades

I have an application which is running with an application schema on Oracle DB. The application needs to be upgraded to new version of Schema on Oracle DB with changes in Schema structure (Table ...
1
vote
1answer
87 views

Why does this simple select statement require quotes?

Why does the following statement select * from expertrule where rule_id = '1'; Return this error ORA-00904: "RULE_ID": invalid identifier While select * from expertrule where "rule_id" = '1'; ...
0
votes
0answers
25 views

How to make a role from schema A “grantable” in schema B

There are two schemas in the same database: A and B. Schema A has a role A_ROLE. I want to be able to give grants to A_ROLE from schema B ( so that the users with A_ROLE were able to query tables from ...
0
votes
1answer
31 views

Are the contents of all control files the same?

I understand that one needs to multiplex and back up the control file. Also one can have multiple control files in the database instance parameter setting. If one of these files get corrupted or is ...