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) ...
6
votes
2answers
287 views
In Oracle, can we restore a database onto the same instance with a different name?
In SQL server I can restore a database onto the same instance with a different name. Eg: If there is a database DB_prod, I can take a full backup and restore it on the same instance with a different ...
6
votes
3answers
188 views
How do you protect against malicious PREPROCESSOR attacks in Oracle External Tables?
I'm a new DBA and I recently found out about the option of External Tables in Oracle using the PREPROCESSOR feature ( ...
6
votes
1answer
865 views
Why does an Oracle database require both the SYSTEM and the SYSAUX tablespaces?
Quoting from Oracle documentation about logical storage structures:
A database must have the SYSTEM and
SYSAUX tablespaces:
Why is it compulsory to have these tablespaces?
6
votes
1answer
577 views
What's the difference between a v$ view and its dba_ equivalent?
What's the general difference between a v$ view and its dba_ equivalent? Take, for example, v$tablespace and dba_tablespaces.
6
votes
2answers
2k views
How to extract Procedure, Function, etc. Source Code from an Oracle Export
I have generated an EXPDAT.DMP file of an Oracle database with the command:
exp userid=usr/pass@db owner=own rows=n compress=n
I then ran the following command to generate a file containing the ...
6
votes
2answers
214 views
Can Oracle be installed on Windows without being an administrator?
Can the oracle software be installed on windows without having administrator privileges?
6
votes
2answers
302 views
Oracle 11g: performance improvements of inserts
I have a table of 500 millions of rows (and growing)
I did the following to improve performance of inserts:
On database side:
dropped all indexes and constraints
disabled logging
On application ...
6
votes
2answers
150 views
Is it worth partitioning a Table by single value in Oracle?
Normally, if you have a column you frequently query a table by, you ought to stick an index on it. But is it worth also partitioning a table by this column, if all possible values are known in ...
6
votes
5answers
2k views
Tools to monitor Oracle Database Performance on Standard Edition
Here is what I am looking for:
Works on Standard Edition i.e. No Diagnostic Pack.
Provides both current and historical performance data.
Good visuals for easy spotting of trends and presentation ...
6
votes
1answer
67 views
Read ancient Oracle database into current Oracle version
An old computer with Oracle 8i is dead.
We have the daily backup of the database (C:\oradata).
We are unable to install this old Oracle version anywhere because we don't have any CD.
Can we read ...
6
votes
1answer
222 views
Duplicate an Oracle database user
We'll be having external auditors coming over to perform a review on our Oracle database. They will be running a tool to perform the review and for that, they require a user ID that can connect to the ...
6
votes
3answers
1k views
Why performance degradation with query hint FIRST_ROWS(1000)? [closed]
I have a query with user supplied arguments that can produce a very high amount of rows depending on the user's input.
In case the user supplies very unselective arguments, the query takes a lot of ...
6
votes
1answer
2k views
Oracle materialized view refresh schedule
Since I see that it is possible to specify an expression like sysdate + 1, here are my questions:
Is it possible to specify the exact time of the refresh for materialized views in oracle?
Is it ...
6
votes
3answers
149 views
Is it possible to see the (completed) SQL Statements executed by a specific session?
Is it possible to see a list of all past SQL statements executed by a specific session (or number of sessions)?
For example, the following query gives me all currently executing statements from all ...
6
votes
3answers
414 views
ALTER SYSTEM DISCONNECT SESSION doesn't work
Oracle 11g. The devs leave sessions connected overnight, sometimes certain cron jobs fail because of that. I am trying to kill all user sessions, but I can't.
I login as:
sqlplus system/[password] ...
6
votes
1answer
2k views
Introduce partitions into an existing non-partitioned table
What has to be done to introduce range partitions into an existing non-partitioned table in oracle? Is there an easy to do and safe way to do this?
Especially in cases where there are many ...
6
votes
1answer
218 views
Package state not re-intialized on raise_application_error
When a package has state and the header is changed, the first call gets an error stack something like this:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of ...
6
votes
3answers
6k views
Oracle returns an error ORA-01031: insufficient privileges
Oracle returns an error "ORA-01031: insufficient privileges"
When I run sqlplus / as sysdba but sqlplus sys/oracle123 as sysdba doesn't, why?
Oracle version: Oracle Database 10g Enterprise Edition ...
6
votes
2answers
191 views
How to modify an update in Oracle so it performs faster?
I have this query:
UPDATE (
SELECT h.valid_through_dt, h.LAST_UPDATE_TMSTMP
FROM ETL_FEE_SCH_TMP d, FEE_SCHEDULE_HISTORICAL h
WHERE h.FUND_ID = d.FUND_ID
AND ...
5
votes
3answers
720 views
Alternatives to an INSERT Trigger
Looking for an example code in Oracle to do the same. i.e.
Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like?
...
5
votes
4answers
373 views
Does an IOT guarantee order in a select?
We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1).
The priority does not matter ...
5
votes
2answers
161 views
How the indexes are managed?
I am trying to understand how the indexes are managed for below common data types:
1- Numeric (Integer, Decimals)
2- String (Varchar, Char)
3- DateTime
I have few questions:
1- How the indexes are ...
5
votes
3answers
219 views
For Oracle, we mount the instance to the database or mount the database to the instance?
The following text is extract from oracle documentation.
Start the instance, and mount and open
the database. This can be done in
unrestricted mode, allowing access to
all users, or in ...
5
votes
3answers
1k views
Why use explicit cursors instead of regular loops?
I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data:
for i in (select * from STUDENTS) ...
5
votes
2answers
161 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 ...
5
votes
2answers
679 views
What is the difference between terminating an Oracle sql statement with semicolon or with a slash on the next line?
The following two queries seem to be equivalent when executed in sql*plus:
1.
select * from user_tables;
2.
select * from user_tables
/
Is there any difference between both versions?
5
votes
2answers
3k views
Why do we need to backup Oracle archive logs along with datafiles
Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) says that
Archived redo logs are the key to
successful media recovery. Back them
up regularly.
But, I wonder, why backups of ...
5
votes
4answers
142 views
Replicating only changes made by a specific database user
I am performing parallel testing. We have upgraded the legacy batch jobs to a new framework which we need to test against the existing batch jobs.
In the existing setup, I have web application which ...
5
votes
3answers
4k views
Cannot drop nonexistent constraint and cannot create it either
While testing some migration scripts with a copy of production data (scripts run fine with development data) I found a curious situation. A CONSTRAINT has changed so I'm issuing DROP + ADD commands:
...
5
votes
3answers
462 views
Alternative for OPTIMIZER_FEATURES_ENABLE
I've installed a PHP driven application in a client's 9i server (Oracle9i Release 9.2.0.1.0 - 64bit Production). Certain queries are having an awful performance (it can use from 15 minutes to hours ...
5
votes
3answers
484 views
Database replication and consistency check
I got a question about 2 databases (oracle 10g) that I have, let's call them A and B. A have some information(in various tables) and I want B to get a partial copy of some tables from A, and ...
5
votes
3answers
825 views
PL/SQL: best way to count elements in an array?
Given this:
DECLARE
TYPE T_ARRAY IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
MY_ARRAY T_ARRAY;
V_COUNT INTEGER;
I would like to do:
BEGIN
-- ... some code filling the MY_ARRAY ...
5
votes
2answers
506 views
If I SET ARRAYSIZE 1 in SQL*Plus, the records are still fetched in pairs. Why?
I thought about writing a simple tail -f like utility to "trace" the progress of some figures within the database:
create or replace function tail_f return varchar2_tab pipelined as
n number;
...
5
votes
1answer
313 views
If an 'after' DDL trigger causes an error, is the DDL rolled back?
It has been suggested that DDL is logically performed something like this:
begin
COMMIT;
perform any appropriate pre-DDL trigger code;
do the ddl;
perform any appropriate post-DDL ...
5
votes
1answer
224 views
What is “SAP on Oracle”
OK, total newb question.
What is "SAP on Oracle", exactly. This link is talking about how SAP can run on Oracle (I think).
I'm not understanding how this is working.
My understanding:
SAP is a ...
5
votes
2answers
731 views
Oracle clean up
I have an Oracle database on a VMWare image that seems to be slowly eating all my available disk space. I would like to know if there is a command or script that would perform some kind of clean up of ...
5
votes
3answers
428 views
What could be the reason for disallowing a sub query in the values clause?
For example
SQL> create table dates(d date);
Table created.
SQL> insert into dates select sysdate from dual;
1 row created.
SQL> select * from dates;
D
---------
28-MAY-11
SQL> ...
5
votes
2answers
694 views
How to retrieve foreign key constraints data
I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema.
I've found this, but does not gives all ...
5
votes
2answers
149 views
Cardinality rule for bitmap indexes
The Oracle documentation includes the following advice:
A bitmap index should be built on each of the foreign key columns of
the fact table or tables
In that reference, there is even a bitmap ...
5
votes
1answer
180 views
Do I have to pay a license fee for Berkeley DB?
I'm planning to use Berkeley DB in a mobile application. Will I need a license even if the application is distributed freely in the iPhone and Android markets?
5
votes
1answer
81 views
Retrieve Indices of Associative Array as Collection
In PL/SQL, suppose I have some associative array defined as follows:
declare
type a_arr_t is table of PLS_INTEGER index by PLS_INTEGER;
a_arr a_arr_t;
I then, sparsely, populate the ...
5
votes
2answers
1k views
Restricting database access per IP addresses
I have filtered IP addresses to an access DB server like this:
Set the sqlnet.ora
tcp.validnode_checking = YES
tcp.invited_nodes = (localhost, 192.168.100.130, 192.168.100.186)
But in my ...
5
votes
4answers
491 views
Multiple operations using WITH
Is there a way to execute multiple operations using the WITH statement?
Something like
WITH T AS
(
SELECT * FROM Tbl
)
BEGIN
OPEN P_OUTCURSOR FOR
SELECT * FROM T;
SELECT COUNT(*) INTO ...
5
votes
2answers
380 views
Oracle Advanced Queuing LIFO
With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
5
votes
2answers
6k views
How do I allow users to see grants, view definitions, PL/SQL, etc in a database without granting them access to change those objects
As a developer, I need to be able to access the design of the database/database objects from production so I can ensure I'm working with the latest and greatest version of the objects I'm developing ...
5
votes
1answer
127 views
Oracle: DB *partial* constraint?
I am pretty new to Oracle and the database world. I am not sure if the title makes any sense at all, so I'll just go ahead and explain my situation.
I currently have a table at hand that records ...
5
votes
2answers
112 views
Product of n Rows
From this data (assuming the number of rows is not known in advance):
with q1 as (select mod(ora_hash(level),5) c1 from dual connect by level <=4)
select * from q1;
/*
C1
--
2
1
4
1
*/
I ...
5
votes
1answer
250 views
Are there performance benefits to turning off archivelogging? Is it wise?
The first part of the question is pretty simple; has anyone run a DB in both archivelog and noarchivelog mode? Were there any measurable performance benefits to turning off logging that you could ...
5
votes
1answer
553 views
Do I need to enumerate the columns used in a materialized view when creating the MV log?
I'm currently creating materialized view logs on a whole bunch of tables in order to support materialized views with FAST REFRESH. The materialized views we're creating contain approx 30 columns from ...
5
votes
1answer
908 views
Oracle 10g express database link to Oracle 8i
I upgraded a 9i install to 11g with only one problem. Database links between 11g and our legacy 8i database are no longer supported. I have to transfer data from 11g to 8i two or three times a day ...
