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) ...
2
votes
1answer
114 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.
-2
votes
1answer
35 views
How to learn oracle business intelligence enterprise (OBIE) from scratch? [closed]
Hi I am a electronics and communication graduate but i want to learn Oracle Business Intelligence Enterprise(OBIE).I have some theoretical knowledge of RDBMS and SQL which i learnt during my ...
1
vote
1answer
57 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
38 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
27 views
how to use different types of flashback
I'm very new to Flashback technology, I found on this website that, there are 3 types of flashback
Row Level
Table Level
Database Level
But, I don't know, how to enable them and use them. Please ...
0
votes
0answers
54 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
1answer
41 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 ...
0
votes
0answers
25 views
What value should oracle.install.db.config.starterdb.memoryLimit be set to in the Oracle response file for a silent installation?
What happens when oracle.install.db.config.starterdb.memoryLimit is not set in the Oracle response file for a silent installation?
I noticed that when recording the response file for a silent Oracle ...
0
votes
1answer
44 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
141 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 ...
9
votes
2answers
121 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
0answers
33 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
0answers
21 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 ...
0
votes
0answers
72 views
pl/sql developer show errors with arabic letter
I have pl/sql on my PC but when an error occurred while I write an incorrect sql query the oracle error message show with Arabic letter as below
ORA-00936:???? ????? ??????
I am using oracle-client ...
3
votes
1answer
50 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
1answer
86 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>
...
0
votes
0answers
21 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 ...
4
votes
3answers
62 views
STARTUP command fails : Why is that?
I just installed Oracle Database Express Edition 11g Release 2.This is the first time I am using Oracle. Earlier I have used MySql and MSSql and I bet this one is a lot different !
These are the ...
0
votes
0answers
108 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
149 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
0answers
23 views
Refresh ORACLE OLAP cube
I am new at Oracle OLAP programming, and just wanted to know about Cube´s Data Update.
For example, right now I maintained and processed the cube for 2012 data; 2013 data is ready on the fact table ...
1
vote
0answers
42 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 ...
13
votes
9answers
15k views
Eliminate duplicates in ListAgg (Oracle)
Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to ...
0
votes
0answers
47 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
0answers
23 views
Multiple principals in a single kerberos keytab file?
I have a situation where I have multiple keytab files (different principal accounts) and my application is going to use these different service
principal accounts and connect to one or more Oracle ...
2
votes
1answer
279 views
column precision exceeded. ORA-01438 is so unmeaningful
I am not sure if this is a question to StackExchange DB Admin group. please let me know if its not.
Let's suppose you have a Java batched prepared statement writing data to database. If one of the ...
1
vote
3answers
103 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 ...
3
votes
4answers
3k views
Simplified/automated datafile free disk space reclaim
On Oracle version 11g:
After Googling, I can't find a simple way to reclaim free space after deleting a table.
I've found a lot of explanations, telling how the datafile becomes fragmented, the big ...
-1
votes
1answer
63 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 ...
0
votes
0answers
30 views
Why can't OUI detect system packages when they are already installed?
I am installing Oracle 11g 32bit on Ubuntu Linux and the OUI shows following error message on Prerequisite Checks screen -
This is a prerequisite condition to test whether the package "make-3.80" is ...
0
votes
1answer
101 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 ...
5
votes
2answers
160 views
Defining constraints in `CREATE TABLE` statements
Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the ...
2
votes
1answer
113 views
Oracle Backup and Recovery Issue
I have backed up database located on Host1 and restored/recovered it on Host2.
After a while, I backed up DB on Host1 again.
Now I want DBs on Host1 and Host2 be the same using last backup.
How can ...
1
vote
3answers
231 views
Instance Startup Modes & Shutdown Modes
So, I know that there are 3 modes of startup modes available for the instance which is NOMOUNT, MOUNT, and OPEN.
What I want to know is in NOMOUNT mode it is stated that the instance started but still ...
0
votes
0answers
44 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
votes
2answers
72 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 ...
1
vote
0answers
118 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 ...
2
votes
1answer
54 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? and can i select all ...
1
vote
1answer
104 views
Update oracle sql database from CSV
I tried google at first but no luck.
Is it possible to update tables from csv file?
I am using SQL developer and I am using a script to export edited rows to a csv file. I would like to update the ...
0
votes
1answer
49 views
List partitioning or filtering a non-partitioned table, which incurs lower cost?
Consider a list partitioned table PARTITIONED__SOURCE_TBL on column period, and non-partitioned table SOURCE_TBL also having the same columns. Assume all tables are adequately indexed.
Set 1:
...
0
votes
2answers
41 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
1answer
63 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
0answers
84 views
Oracle Connection Issue - TNS Protocol Adapter Error
I'm trying to connect to an Oracle database from a client machine and I'm getting an ORA-12560: TNS:protocol adapter error from a python script using pypyodbc as my ODBC library.
I have researched ...
0
votes
1answer
51 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 ...
2
votes
1answer
115 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
72 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
)
...
0
votes
0answers
74 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 ...
3
votes
1answer
232 views
Oracle insert through dblink performance issue
I'm trying to do an insert as select to a table via dblink. The query looks like this:
Insert into MARS_SUPERVISORS_PUB@EDHPUB
select
{...}
from edhper a, edhper b
where ...
1
vote
1answer
58 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
4answers
111 views
Copying my Oracle 10g Express database to another PC
I have Oracle 10g Express. How can I make a copy of my database and application?
I don't want to make a back up, I want to move my DB and application to another PC.
