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

Find who and when changed a specific value in the database

We recent found some record in a table of an oracle database has been updated to another value.and it has been cause very important mistake. we want to find out who and when has done this? could ...
1
vote
1answer
2k views

Is it possible in Oracle to trace SQL statements that result in errors?

We have Oracle 11g in production. Application system is still under active development. It will be very handy to get SQL statements which cause any error. Does Oracle provide a standard function to ...
1
vote
2answers
4k views

Oracle SQL, how to filter on a set by a rank?

I am struggling with the following SQL query right now: http://sqlfiddle.com/#!4/736a7/12 select uc.* from usercredential uc inner join users u on u.userid = uc.userid inner join credential c on ...
0
votes
1answer
500 views

How to permanently delete a row in oracle forever [duplicate]

I want to delete a record from a table and no one should ever be able to recover the deleted data. I have done delete operation but the record is still in the undo segments. And one can do flashback ...
0
votes
1answer
1k views

Exporting and importing a database

I have setup an Oracle 11g Database and have a question about exporting and importing a database. I am using Enterprise Manager and have browsed to the following: Data Movement->Export to Export ...
5
votes
2answers
1k views

How do I execute an Oracle SQL script without sqlplus hanging on me?

For an automated task I would very much like to run some SQL scripts and make sure that sqlplus does not hang under any circumstancees, i.e.: If the script contains any named substitution variable ...
1
vote
0answers
930 views

How do I disable oracle's MAX_ENABLED_ROLES limit? [closed]

How do I disable oracle's MAX_ENABLED_ROLES limit or expand the limit?
0
votes
6answers
10k views

How can I convert an Oracle dump file into SQL Server?

I want to ask about converting Oracle dump files (.dmp) into SQL Server files (.bak) where during conversion I don't have to be connected to any database server. I've searched for related ...
1
vote
0answers
5k views

impdp failing with ORA-01031: insufficient privileges

I'm trying to import a dmp file provided by our dba to my dev database. I've been able to export the contents to a SQLFILE and the contents look ok. I tried importing the schema and it kept on failing ...
1
vote
2answers
16k views

How to check if Oracle DB process is running in Unix and where Oracle DB is installed

I am new to Unix and trying to find out a command that checks if Oracle DB is installed in my Unix box. I came across commands ps -ef|grep smon and ps -ef|grep pmon in net. But which is the correct ...
1
vote
1answer
523 views

Multiple like conditions for same column?

How do I write a query for a Oracle database when I have several "like" values for the same column (in my example data "name")? My example data for table "address": name like "John" name like ...
1
vote
2answers
5k views

Granting privileges to dbms_crypto

I'm trying to grant proper privileges for using DBMS_CRYPTO. Whenever I call it from package it gives me error: PLS-00201: identifier 'DBMS_CRYPTO' must be declared User already has privileges: ...
0
votes
0answers
478 views

Temporarly disable cache in oracle 11g

I want to disable the caching for my session in oracle 11g to see how long a query takes. Is this possible or is there a better way for meassuring the time a query takes to run?
2
votes
1answer
102 views

Oracle Database: View schema without access to data

In Oracle Database 10g, I need to create a user with these permissions: user can access schema to be able to read table names and columns user can not select (get) any data from any table Is this ...
1
vote
1answer
151 views

Can a production Oracle DB server also have a dataguard active standby for a different production database?

If you have a production oracle database (PRIM1) protected using Oracle Dataguard to a standby (STBY1) physical server in a different data centre, the standby server needs to be a similar ...
1
vote
1answer
99 views

Oracle error handling strategy [duplicate]

I am developing a project using oracle. I am writing functions and stored procedures to handle CRUD statements. My question is in addition to oracle check constraints do I have to check error ...
0
votes
0answers
294 views

Use cases for NoSQL databases: Time series

I need to set up a database that will record temporal series data (sensors). A friend of mine has recommended me to use a NoSQL database instead of using Oracle (for instance). There're also some ...
0
votes
2answers
1k views

Oracle database uses wrong parameter file

When I try to startup my Oracle database I get the following error: LRM-00109: could not open parameter file '/opt/app/oracle/product/11.2.0/db_1/dbs/initjicdbaex.ora' The /dbs directory in my ...
0
votes
1answer
612 views

Recommended Approach to Programmatically Backing Up/Restoring Oracle DB

Supposing I have an ever-growing Oracle DB on one server, and I want to duplicate this schema and the data on another server - what would be the best approach to achieving this as part of a bespoke ...
3
votes
1answer
3k views

Oracle: Full database (user) restore requires drop?

In our development environment (Oracle 11g), we often have to restore databases (users) to previous versions to test upgrades, verify defects, etc. We are using expdp/impdp to perform our ...
0
votes
0answers
100 views

file extension of the file types cdump, incident, trace

I need to know the file extension of the file types cdump, incident, trace, alert. Actually I want to create files of the above types manually using mkfile command. There are no existing files of ...
3
votes
1answer
1k views

Is it possible to use Oracle XMLTABLE and %type column type selector?

I am creating a stored procedure which gets an xml input and inserts the data to a table using XMLTABLE. I want to know how can i specify a field type according to a table column type when passing ...
0
votes
0answers
522 views

Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records. create or ...
0
votes
1answer
732 views

Oracle's UTL_FILE

I am learning Oracle pl/sql by myself. I need to create a procedure that exports a table to a csv file. For that I am thinking of UTL_FILE.Is there any good book or a site to get me started? I did ...
3
votes
1answer
566 views

Is it possible to re-initialize sequence

How do I re-initialize the sequence value without drop and recreating them again For example, the current sequence value is 3, now I want to restart it with 1 again.
9
votes
2answers
612 views

Why is a “non-single-group group function” allowed in a subselect but not on it's own?

Why does the first query not fail with the same error as the second: with w as (select 1 product_id, 10 units from dual union all select 2, 5 from dual) select sum(units) from (select product_id, ...
2
votes
1answer
2k views

Enabling/disabling/changing Oracle auditing without a shutdown?

I have a large database that needs auditing on a very detailed level (every select, update, insert, and delete, along with the actual text of the statement) for about half the users. I know how to do ...
0
votes
1answer
1k views

Error AWR Operation failed: CATPROC not valid when creating database with dbca

I have a fresh installation of Oracle 11g enterprise edition on CentOS 6, both 64 bits. I have installed Oracle and so far everything looks fine. I have already added an started a listener. The ...
1
vote
1answer
9k views

What is the best way to insert a XML input to an oracle table in stored procedure?

I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this: <rowset> <row> ...
1
vote
0answers
371 views

Calling a function in remote database inside a stored procedure

There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I ...
0
votes
0answers
157 views

How can we hide our procedures in Oracle?

How can we hide our procedures in Oracle? Is there any other way except Wrap utility?
0
votes
2answers
2k 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 ...
-1
votes
1answer
507 views

What must I already know to get profound knowledge in Oracle [closed]

I've been working with relational database systems for about 4 years, 2 years in Oracle particularly. I've been able to do pretty much everything I've needed so far but I know that the knowledge I ...
1
vote
1answer
16k views

ORACLE restore - how to restore specific .bkp file?

I just started working with oracle and I have some issue. My database is NONARCHIVELOG mode. I made two backups: they are at locations: ...
0
votes
2answers
6k views

Oracle Undo tablespace users

Can anybody help me on "what all users are using the undo tablespace and how much" in oracle database 11g r2. any pointers would be much appreciated.
0
votes
1answer
249 views

Object-oriented programming CRUD design pattern

At first I have posted this question at http://cstheory.stackexchange.com/questions/17170/object-oriented-programming-crud-design-pattern - that's because I think it's a subect question, and it can ...
1
vote
3answers
646 views

How to enforce full integer (no floating point)?

Why is the following allowed INSERT INTO c VALUES (123.2,'abcdefghijklmnopqrstuvwxyzabcdef', 'abcdefghijklmnop'); When table contains CustomerID NUMBER(3,0) -- That's the 123.2 entry In ...
0
votes
0answers
377 views

Oracle Alternative to EXISTS EXCEPT

I wonder if there is an equally elegant solution for Oracle for the problem posted and answered here: Comparing Columns that can contain NULLS - is there a more elegant way? I can't get this ...
1
vote
1answer
3k views

Registry error while trying to install Oracle 11g on Win 2008 R2

I am new to Oracle and trying to install Oracle database 11g Enterprise Edition on Windows 2008 Server R2 but getting this error all the time... It gives me error when updating registry key ...
5
votes
3answers
2k views

Do roles have an owner in oracle?

Suppose that I have logged in to oracle database using user X, then user X creates some roles using create role command. I want to know that is user X the owner of the roles? Can I select all roles ...
-1
votes
2answers
97 views

Combine two SQL statements to a single statement [closed]

My question is related to SQL. I need to pull values from two different tables. I am not successful in doing that. I am able to join the two tables with only a single condition but not many values ...
3
votes
1answer
603 views

Convert SQL Server UPDATE Statement for Use in Oracle

I can not get this UPDATE statement to work in an Oracle environment. It was written for SQL server. I am looking for some guidance on how to convert it. UPDATE SOB SET COA = CASE ...
0
votes
2answers
158 views

Multiple instances in Oracle

I'm really confuse with the term instance and database. So, my first question is how database and Instance are related in Oracle, I mean, whether an instance is associated with one/multiple ...
3
votes
2answers
4k views

Reset every Oracle sequence to the MAX value of its target table PK?

I finally got around to migrating from MySQL to Oracle and was pleasantly surprised just how well of a job the SQLDeveloper Migration tool did. It even took my AUTOINCREMENT fields and created ...
0
votes
1answer
460 views

PL/SQL for Updating 1 Column in Table A Where PK = a row in table B

Using an oracle DB, 11g r2, I need to write a pl/sql which updates one column in table A where the PK exists in table B, with commits ever ~50,00 rows. 3200000 rows are involved in the update, and ...
0
votes
1answer
174 views

Oracle equiv of T-SQL UNION ALL query

In T-SQL I can accumulate the results of two unioned queries using the following syntax: SELECT Q.a, SUM(Q.b) FROM ( SELECT f1 as a, f2 as b FROM TBL1 UNION ALL SELECT f1 as a, f2 as b FROM TBL2 ) ...
5
votes
1answer
518 views

What must be in place to validate a XMLTYPE against a schema?

I have a procedure that generates an XMLTYPE and I want to validate it against a schema. The problem is that there seems to be a permissions issue running createSchemaBasedXML because when I run the ...
0
votes
0answers
576 views

DBA group in Linux

When we need to install Oracle database in Linux then the user should be in dba group. What is this group and why the user should be in this group for installing Oracle database. What happens if the ...
1
vote
1answer
564 views

Using Oracle hierarchical queries for transposing field value into rows

One column in my table stores list of values separated by comma (I know it's a poor design, but refactoring is not an option at the current moment). I seem to come up with a working solution but I'm ...
1
vote
1answer
251 views

Oracle 11g exception on Fedora 14

Hi. I am new to Oracle database. I have installed Oracle using this tutorial on my Fedora operating system. It worked fine after installation, and I have made some sample queries and made a new ...