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) ...
0
votes
1answer
20 views
Role audit in Oracle 10g
Is it possible with Oracle auditing to find out if the role can be revoked from particular user without loosing any privileges that he actually uses?
Something like AUDIT webadmin_role BY webapp_user ...
0
votes
1answer
14 views
How to automate Oracle DB installation (like a 'hands-free' install)?
I installed the Oracle 11g2 'standard' edition using the following zip-files:
linux.x64_11gR2_database_1of2.zip
linux.x64_11gR2_database_2of2.zip
I used the installation guide by Oracle which I ...
2
votes
1answer
245 views
Find fragmented space free below HWM Oracle
That High Watermark Value always eludes me when calculating free space in my tablespaces.
Will the following query consider fragmented free spaces of object below the HWM value for each TS?
select * ...
1
vote
1answer
23 views
Is it possible to define package procedure/function outsize of package body block (for Oracle)?
Is it possible to define package procedure/function outsize of package body block (for Oracle)?
Simplified syntax for defining package body is:
create or replace package body <package_name> IS
...
0
votes
0answers
15 views
Bulk inserts in Oracle 11g SAN cluster environment
A Large Volume of Data from a table need to populated into another table this is taking a very long time Using SQL Developer on an Oracle 11g SAN Cluster Environment. We experience Connection Time-out ...
0
votes
2answers
62 views
How to quickly startup/shutdown Oracle 11?
I am wondering what is the quickest way to properly startup/shutdown a Oracle DB daemon (Oracle 11.2, installed on a test machine).
I need it for C/C++ programs that use the OCI/Pro*C API.
Currently ...
-1
votes
0answers
23 views
script to startup oracle database [closed]
I found out below script that helps me to start up a Oracle database:
set serveroutput on;
WHENEVER SQLERROR EXIT SQL.SQLCODE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MY_DB" NORESETLOGS ...
1
vote
4answers
124 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.
-2
votes
1answer
48 views
what's the fastest method to export a table through sql query ? 11g - performance
I'm connected to the oracle server remotely. I need to export a table data(the whole table) with millions of records but due the hosting restrictions I can only use SQL (no exp , expdp) . Therefore ...
2
votes
2answers
121 views
Loading XML documents to Oracle 11g DB with control file
I am using Oracle 11g XML database and trying to load XML documents to this DB with a control file and the sqlldr utility. All these XML files have an element that contains a date string with time ...
0
votes
0answers
19 views
where is oracle session timezone set?
I have a logging table
CREATE TABLE EMAIL_LOGGING
(
ID NUMBER(9) NOT NULL,
SEND_DATE TIMESTAMP(6) DEFAULT LOCALTIMESTAMP NOT NULL,
...
1
vote
0answers
17 views
BLOB storage in rows
I have a database that has several BLOB columns scattered across multiple tables. Since I am a developer and am looking at making application code writing easier, I wanted to turn these columns into ...
0
votes
1answer
25 views
Filter on a window function without writing an outer SELECT statement
Since window functions cannot be included in the WHERE clause of the inner SELECT, is there another method that could be used to write this query without the outer SELECT statement? I'm using Oracle. ...
2
votes
3answers
33 views
Oracle error : Shutdown immediate error
echo $ORACLE_SID
YBRD
conn sys/hello as sysdba
SQL>shutdown immediate
Then I was waiting for it to shutdown for a couple of minutes. It didn't, so I closed my window.
Then I again logged in and ...
0
votes
2answers
41 views
How to forcibly flush the undo log/tablespace of UNDOTBS1
I'm looking for a way to flush the undo logs so that the UNDOTBS1 doesn't give me an error saying that my tablespace isn't large enough. I tried using a commit, but without success, it still gives me ...
2
votes
1answer
59 views
How do I identify the remote db agent name to use in create_database_destination on Oracle 11gR2?
I am trying to setup DBMS_SCHEDULER in Oracle 11g to run a remote database job.
I have a remote Oracle 11g R2 database on unix and a local one on Windows.
I read that you can install the oracle ...
1
vote
1answer
132 views
oracle streams apply: how to get a reason why LCR message was not applied
I've set up bidirectional oracle streams replication (11gR1) using identical scripts on both machines (DB1 and DB2). Although changes from DB1 are being applied to DB2, changes from DB2 to DB1 aren't.
...
0
votes
1answer
423 views
Why won't Oracle alter the size of a column that is used for sub partitioning?
I am trying to resize a varchar2 column, when I get an ORA-14265 error:
ORA-14265: data type or length of a table subpartitioning column may not be changed
With Oracle providing the unhelpful:
...
2
votes
1answer
42 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 ...
1
vote
1answer
435 views
Replicating data from Oracle to MySQL
I work with a vendor that does data analytics, and they currently receive a replication stream from some of our databases using a product called Goldengate (which is very expensive). Goldengate has ...
0
votes
1answer
203 views
How to connect to a Database made by Oracle SQL Database?
So I am fairly new at this, so if you could keep that in mind in your answers, it would be much appreciated. I installed Oracle SQL Database on my Windows PC. It came in two zip files. I installed it ...
7
votes
3answers
47 views
Tablespace on ephemeral storage
For performance reasons in some scenarios, e.g. Amazon EC2, you have access to a faster and cheaper storage device, which loses all its data on reboots, so it is called "ephemeral".
This question is ...
4
votes
4answers
2k views
Over use of Oracle With Clause?
I'm writing many reporting queries for my current employer utilizing Oracle's With clause to allow myself to create simple steps, each of which is a data oriented transformation, that build upon each ...
0
votes
1answer
27 views
Oracle transactions deadlock
How to rollback all active transactions on Oracle DB?
I execute query and see 4 transcations in ACTIVE status.
SELECT *
FROM V$TRANSACTION
1
vote
1answer
77 views
Deleting folders after Oracle upgrade
I upgraded from Oracle 10g to Oracle 11g in Windows Server 2003.
New Oracle home : C:\app\oracle\product\11.2.0\db_1
Old Oracle 10g was installed on: C:\oracle\product\10.2.0\
Questions
Is the ...
2
votes
1answer
85 views
Oracle: How to gather stats in a logical standby database?
I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced.
Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On ...
-1
votes
0answers
27 views
How to correct my Oracle SYSTIMESTAMP?
My database's timestamp has 8 second difference with my OS system time (as well as time from the internet). This caused some bug in my code. How to correct this?
0
votes
0answers
17 views
Oracle Grid Control
How can I implement all the mentioned below points for monitoring in Oracle Grid Control?
Datasource Monitoring
JMS Monitoring
Composites (State (Stop/Retired/Undeployed)
Physical servers (Virtual ...
0
votes
1answer
62 views
Replicating Results of Queries on Oracle
I wan to setup a database such that its data come from queries run on a remote database. Basically the source database is very slow and we have no control over it. So, I want to create a second ...
5
votes
3answers
256 views
How to store many smallish time-series in a relational DB?
Input Data
Multiple Tests Beds generate measurement data of various complexity.
In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
1
vote
2answers
6k views
How do you get the explain plan?
Can some one please explain a bit about explain plan, and how does one get the explain plan for a query. Also, if there is any sql script which can be used as utility for this, that would be great.
0
votes
1answer
589 views
Listener failed to start
I have installed Oracle 11g on my Window7 computer; whenever I try to create a connection in Oracle SQL developer I get this error TNS:listener does not currently know of SID given in connect ...
0
votes
1answer
355 views
Problem with dropping materialized views and mv logs taking a long time (or never) to complete
Apologies if it's a simple question, DBA is not my main area of expertise!
I have a materialized view that's fast refreshable, joining two tables.
When I try to drop either the MV itself or logs ...
-4
votes
1answer
39 views
Oracle database and data has been corrupted. Is there a way to get it back? [closed]
The Oracle database has been corrupted and both the data and backups were lost in the corruption. Is there anything I can do to recover my data? It would be terrible if it were all lost.
1
vote
1answer
140 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 ...
4
votes
1answer
74 views
General tool to load dump files
I am a big fan of Postgres both for its price but also for its features.
I am going to have to need to upload into it both Oracle dump and SQL Server files.
I will try to ask and beg for plain .csv ...
0
votes
1answer
52 views
Creating oracle database physical files from migrated DB
I migrated a database from SQL Server to Oracle 11g and there doesn't seem to be any physical files created after migration.
The client requires that they receive a copy of the database so they can ...
1
vote
2answers
53 views
Need an Oracle query to get the Oracle Home directory for a particular instance
I am searching for a query to get the Oracle Home for a particular instance name, say Eagle (if there are more than one instance on the host).
0
votes
1answer
48 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 ...
1
vote
2answers
259 views
Oracle Linked Server query error
We have a problem with a query from a SQL Server 2008R2 - 64bit
to an Oracle Linked Server.
We use the Oracle 64-bit driver for Windows.
When we query a certain table we get the following error:
...
1
vote
0answers
26 views
Is it possible to use 'restore UNTIL SCN' with rman and Oracle 9i in NOCATALOG mode?
I'm trying to do a hot backup with rman that I can copy to a USB drive so I can recover it on a new server.
The backup was done two days ago and the restore database command was working on the same ...
0
votes
3answers
46 views
Built-in XML functions
I am trying to use built-in XML functions, but I'm getting an %s: invalid identifier error. Selects works fine without the XML functions.
Any ideas where is the mistake?
select id,full_name from ...
2
votes
1answer
51 views
What's the most appropriate NLS_SORT parameter to cover all/most European languages?
I'm developing an Oracle 11g-based application which is likely to be used by clients in many different countries (mostly European). I want the program to change its NLS_SORT so that it's case ...
0
votes
2answers
63 views
How find all the data loaded on previous day?
Issue: Data Loading into development environment from production database
My production database is 40gb but I do not want all of those 40gb of data into my dev env and so my question is
How can I ...
0
votes
1answer
29 views
How to use database users as a foreign key field in any table
I want to create a table where I can have a column username which will be the foreign key and it will only contain usernames of existing users on that database. For example, if I want to insert 'John' ...
0
votes
1answer
31 views
Error in trigger: table is mutating
CREATE OR REPLACE TRIGGER "TRANSACTION"
before
update of totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD
for each row
begin
declare
mportfolioname varchar2(20);
...
2
votes
1answer
18 views
can rman backup a subset of a table
I have been asked to make incremental backups of a table based on the value of one of the fields in the table (called groupid). They want a separate incremental backup for groupid=1, groupid=2, ...
1
vote
3answers
48 views
Should I use two databases or two schemas?
We are currently having one Oracle instance on RAC with one database created. A new requirement came to our database team to temporarily (1-2 months) host a new application that is independent of ...
0
votes
0answers
53 views
PL/SQL extremely slow FETCH performance
I have a stored procedure that includes (among many other things) a dynamic query with a LOOP and a FETCH. The fetch has to potential retrieve a large amount of data as one column contains spatial ...
0
votes
2answers
67 views
Use an Alias in Where Clause Subquery
I need to show some fields from another table in Oracle. Here is my query:
SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
...
