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
3answers
475 views
do oracle redo logs need to be copied when all other data is copied to a new disks?
I have to migrate all data for an Oracle server from the present SAN to a new SAN. It is an 11g database in production on a Linux server. Database software is on local disk but all data (including ...
0
votes
3answers
424 views
Oracle returns different results when executing a sql with bind variable or with a fix value
The following two statements (strongly simplyfied) returning a different result:
with a fix value for rownmun:
select * from table
where rownum <= 12;
with bind variable for rownum:
select * ...
2
votes
1answer
167 views
Rman not replacing logs with same name on networked drive
Historically, rman saved logs by replacing files of the same name so that the most recent set was always there, and logs from backup of older than the most recent set simply went away.
Rman is ...
2
votes
1answer
1k views
RMAN & Archivelog Deletion Policy confusion
Never been able to fully grasp the notion of delete input/delete input all when backup up Archivelog with rman.
Tied to the same issue is the configure Archivelog deletion policy to backed up X times ...
2
votes
2answers
674 views
How does name resolution work in Oracle 10g?
In Oracle 10g (10.2.0), there are 4 types of names resolution:
Easy Connect(10g added this new method)
Local naming
Directory
External naming
It is said that if Easy connect is used by the ...
4
votes
2answers
330 views
Installing oracle RAC 10.2.0.1 install and patch to 10.2.0.5 directly or install CRS ASM and RDBMS then patch?
I'm installing my first RAC and I was wondering if I should install CRS 10.2.0.1 (we are still at 10g for compatibility reasons) then patch to 10.2.0.5 before I install ASM (then patch) and finally ...
1
vote
4answers
253 views
Frequent or Infrequent Commit
I'm trying to run an update query but still taking to long I have a commit in it.
Is it better to set the commit to a higher value (5000) or lower (1)?
I have to update 15 million records in a table ...
1
vote
1answer
133 views
Oracle, Stored and External procedures
Could someone give me a brief explication about these two mechanisms in oracle : stored and external procedures.
Are they distinct ? Are they differ when we integrate in some language (Java, C++ or C ...
5
votes
2answers
195 views
Implementing uniqueness with Oracle
For example, say I have something like
CREATE TABLE COMPANY(id int not null primary key, ...);
CREATE TABLE DEPARTMENT(id int not null primary key, company_id int not null,
CONSTRAINT ...
0
votes
1answer
120 views
Suggestions for SQL query repair tool or tips on building one?
Not sure if this is ontopic so I figure I would ask here and if its not liked then we can close it.
Our hosting provider for a legacy application that I inherited is no longer supporting Oracle 10 ...
1
vote
2answers
619 views
Move an Oracle database by moving the raw data files
I have a large Oracle database (~300 GB) and I need to move a copy of the database to a new environment in a short period of time. I was wondering if there is a way to just pick up the raw files ...
1
vote
0answers
225 views
Oracle Flashback Archive aka Total Recall in MySQL
What would you suggest as a replacement system to achieve the same functionality of Oracle Flashback Archive aka Total Recall in other DBMS, in general or specifically in MySQL or MariaDB?
...
2
votes
2answers
257 views
Oracle Enterprise Manager Jobs Failing Initialization
The Enterprise Manager jobs on our RAC system recently started intermittently failing initialization with the following error:
Error evaluating parameter sources/security checks/locks ORA-06508:
...
0
votes
1answer
416 views
Error during the installation of oracle 11gxe
When I tried to install Oracle 11g XE into my machine (Windows XP media center edition, version 2002 SP3) I'm getting an error as
0x80040702 Description: Failed to load DLL:chkportDll2
I've ...
2
votes
2answers
700 views
Is there any doc listing standard <=> Oracle character sets correspondencies?
I'm looking for a table listing Oracle and "standard" character sets, for example:
Oracle Standard
------------ ----------
WEISO98859P1 iso-8859-1
AL32UTF8 utf-8
etc. etc.
Does ...
5
votes
1answer
268 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 ...
4
votes
1answer
2k views
Connection to an oracle db from another computer
I have installed Oracle 11g to an ubuntu, I can connect to DB using sqlplus from the localhost, But I cannot connect from another computer, I have configured my tnsnames.ora as followed;
ELIF =
...
2
votes
1answer
2k views
when to use embedded PL/SQL gateway with Oracle APEX?
Oracle Application Express (APEX) requires one of the following three options for an HTTP server: (1) Oracle HTTP Server (with mod_plsql), (2) Oracle Application Express Listener, or (3) an embedded ...
5
votes
3answers
959 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 ...
0
votes
2answers
960 views
Oracle 11g setting for backwards compatibility? [closed]
Edit: Please close this topic.
Unfortunately, this issue can't be resolved as it turned out the question is very off-base to begin with. The actual issue was completely unrelated to the question I ...
3
votes
2answers
4k views
How do I find my current SCN?
Given any version of Oracle:
How do I find my current SCN?
What is the maximum possible SCN?
1
vote
1answer
485 views
How do you monitor specific SQL statement of a specific user ?
We have a few standalone users here which cause high server loads for several 11.1.0.7 Oracle's.
I spoke with them a few times but it seems that their queries are causing high loads and probably ...
1
vote
1answer
587 views
At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?
I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run.
I guess that it ...
3
votes
2answers
423 views
Efficiently finding distinct values
I have a number of tables with the primary key (month, year, number) and differing cardinalities differ somewhat. For the tuple (month, year) the history doesn't go back very far, this will probably ...
2
votes
1answer
384 views
Oracle 11g production database copy to USB drive
What is the correct commands for backing up a production Oracle 11g database (while still running) to an external USB drive or different directory.
I am new to Oracle and but have experience with SQL ...
2
votes
1answer
368 views
PL/SQL : query numeric data type by scale?
Wish to select rows with number fields having a certain amount of data after the decimal
eg
select number from table where number.scale > 4;
Is this possible to do directly, or must I cast to ...
5
votes
2answers
747 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 ...
1
vote
2answers
414 views
Dropped table not showing up in recycle bin
I tried dropping a table and then when i viewed the recycle bin nothing was there in it . Earlier too i dropped tables many a times but I was surprised to find no objects whatsover in it .
What's the ...
3
votes
1answer
2k views
Oracle 11g hunting down the deadlock, maybe foreign key?
I am trying to understand why a deadlock occurs in my database. The trace tells me that two updates on the same table are locking. In table is the first table to be written to in an transaction, only ...
2
votes
2answers
3k views
Oracle 11g SYSDBA login
I installed Oracle 11g on my win xp machine. Now I want to connect to the database. I was told that to connect to the database from command prompt I should use
SQLPLUS "/ AS SYSDBA". But I want to ...
0
votes
1answer
146 views
ORA-04020 on Oracle SE1 (11.2.0.1)
We've been experiencing some deadlock issues on our Oracle SE1 (11.2.0.1) database server (Red Hat 5/64bit)
Seeing ORA-04020 errors in the logs.
Is going to 11.2.0.2 the only way to resolve the ...
3
votes
3answers
269 views
What's the most cost effective or cheapest way for high availabity on Oracle Standard Edition One?
We are currently using Oracle Standard Edition One running on two physical database server.
If I wanted to get higher availability, what's the most cost effective way or cheapest way to achieve this?
...
3
votes
2answers
867 views
Copying Oracle user without dump file
I'm looking for a way to copy an Oracle user and its data to a new one without dump file.
Google search brings a tip by Steve Karam, which seems to be exactly what I want.
However the page is not ...
1
vote
0answers
40 views
What patch(es)/feature(s) to Oracle provide the column avg_hard_parse_time on v$sqlstats?
I have some profiling scripts which work in some environments, but not others; the odd thing is that it doesn't seem terribly consistent across versions, so I'm guessing there's some patch or feature ...
3
votes
2answers
1k views
How to free space after deleting the millions of rows from a table
I performed a delete operation on my table . It deleted around 10 millions of rows from it
How do I free the space associated with the deleted data.
I used the query:
alter table tablename ...
3
votes
1answer
913 views
Oracle: Read from CLOB column line by line and insert to the table
I have CLOB column, and wanna read its data line by line to insert it in another table.
Now let's create the same situation that I have:
Data in a Table MyTable_1
OWNER | TABLE_NAME | SQL
...
4
votes
1answer
79 views
Relationship between number of transactions and redo generation
If I execute some SQL statements over many transactions instead of just committing once after all statements, will this have an effect on the amount of redo log generated? And hence on the size of the ...
7
votes
3answers
402 views
Oracle's left join and where clauses errors
CREATE TABLE "ATABLE1"
(
"COLUMN1" VARCHAR2(20 BYTE),
"COLUMN2" VARCHAR2(20 BYTE)
);
CREATE TABLE "ATABLE2"
(
"COLUMN1" VARCHAR2(20 BYTE),
"COLUMN2" VARCHAR2(20 BYTE)
);
...
1
vote
1answer
96 views
Can a Database request automatically remove itself from the queue after a delay?
I'm using table locking in my Oracle database. I have a small program with a function that locks my table, does some things and unlocks it again. If many instances are run and requests are made ...
3
votes
1answer
1k views
Oracle11g password expiry issue
I have a user which has a default profile in oracle db.
I want to reproduce the following warning
ERROR:
ORA-28002: the password will expire within X days
In dba_profile I set following values.
...
-3
votes
1answer
213 views
Steps to create instance, parameter file , password file and database [closed]
_Hi everybody
_i wanna all steps to create instance, parameter file , password file and database
_manually in oracle 10g
_i hope from all to help me
0
votes
1answer
89 views
oracle local partition index
I have a table like this:
CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 ...
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 ...
0
votes
1answer
744 views
oracle global index vs local index on a partitioned table
I have a table that has range partition by month on a date column (created_time). I was wondering what is more efficient of creating a global index on this column or creating a local index on this ...
4
votes
1answer
368 views
How will Oracle fill a datafile added to a tablespace?
I have a tablespace with one datafile which is 50% used. If I add a new datafile to the tablespace -
will the first datafile continue to be used until it reaches full capacity and only then will ...
7
votes
2answers
55 views
Oracle 10g: Is it possible to designate a undo point?
I was wondering if it is possible to designate a point in time, then later "roll-it-back" to that state (undo DDL and DML changes). Maybe using a record of changes and then executing the reverse? Does ...
6
votes
3answers
195 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 ( ...
3
votes
2answers
505 views
Why Sqlcode 100 does not come with exception
I have following query in a PL\SQL procedure on Oracle 10.2:
This is the code
LOOP
BEGIN
SELECT a.poid_id0 into v_acc_account_poidid0
FROM account_t a
WHERE a.poid_id0=i_acct_id0
...
2
votes
2answers
5k views
ORA-01654: unable to extend index <name of the index> by 128 in tablespace <name of the Index tablespace>
I am not a DBA but have to resolve this issue.
There are 2 permanent tablespaces in my Oracle 9i database. One for the tables and the other for the Indexes other than the Temporary and UNDO ...
1
vote
1answer
919 views
unknown SET option beginning “oracle_sid…”
SQL> set oracle_sid=sid
SP2-0735: unknown SET option beginning "oracle_sid..."
SQL> emctl start dbconsole;
SP2-0734: unknown command beginning "emctl star..." - rest of line ignored.
SQL> set ...
