Questions tagged [oracle]
All versions of Oracle database. Add a version-specific tag like oracle-11g-r2 if that context is important in the question. Do not use for Oracle products such as applications or middleware or other database products owned by Oracle, like MySQL or BerkeleyDB.
7,754
questions
4
votes
1
answer
1k
views
Is it possible to configure Oracle's CONNECT THROUGH based on roles?
I don't have too much administrative experience with Oracle DBs, so forgive me if I make a fool out of myself.
In our current setup, each of our clients has a database user on our Oracle database. ...
1
vote
1
answer
4k
views
bad bind variable in trigger
i'm trying to update the table whenever complaint has been registered
create or replace trigger complaint_advertise
BEFORE INSERT ON COMPLAINT_INFO FOR EACH ROW
DECLARE BEGIN
UPDATE ...
1
vote
1
answer
10k
views
restore database from dmp file
I have a situation:
I have a dmp file and a database with old data
server crashed and the latest data we have ar in the dmp file!
we managed to pull the server back but it has old data!
so we need to ...
1
vote
3
answers
3k
views
oracle crash with ORA-00942: table or view does not exist
we use oracle9i database. and since last week my oracle crash many times, after running for 30 hours more or less.
we see errors repeat in my trc file and alert log :
qmhGetHTTPError: Got unknown ...
4
votes
3
answers
868
views
Why keeps Oracle writing into WARNING_SETTINGS$?
For tests we have Oracle 11 instance running on a RAM disc. Since the RAM disc is limited in size the database must not do any auditing or anything that keeps increasing the size of anything.
We ...
2
votes
2
answers
7k
views
LEFT JOIN conversion to INNER JOIN, can't change FROM clause
I have 2 tables:
BASE is master and is linked with CHILD via field ID: Left outer Join / 1:N relation.
These 2 tables contain historised data, defined by 2 date-fields: start_date and end_date.
...
1
vote
2
answers
8k
views
ORA-39142: incompatible version number 3.1 error work-around
I am trying to import an Oracle backup .DMP file into a new Oracle DB and I'm getting the error "ORA-39142: incompatible version number 3.1". My research tells me that this is because the version of ...
2
votes
2
answers
811
views
Oracle Databases - need to export nightly for backup/DR purposes
I know nothing about Oracle databases, so if my question's path seems odd, feel free to tell me the right way to do things.
I have 2 ways I see currently that I can backup a server running 3 Oracle ...
1
vote
1
answer
1k
views
file not found - how to remove its configuration?
I run oracle 10.2.0.5.
I have the following:
SQL> select file#, status, error, recover, tablespace_name, name
2 from v$datafile_header
3 where recover='YES'
4 or (RECOVER IS NULL AND ...
2
votes
1
answer
2k
views
Oracle SQL Loader - load file where record is composed of "n" delimiters
How would you write a control file for Oracle SQL Loader that treats each record as being composed of 3 commas?
I have read the documentation, however I cannot find how to handle this scenario, ...
5
votes
1
answer
471
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 ...
3
votes
2
answers
678
views
How can I change three hundred procedures at once?
I need to change about 300 procedures and packages in the database, due to a migration that will accomplish this weekend. We will do a migration from one server to Exadata.
However the database has ...
8
votes
4
answers
101k
views
Convert a Unix timestamp to a DATETIME in a View
I have a table that stores a unix timestamp. To query this as a date, I'm attempting to convert this timestamp to a datetime type in a view. Unfortunately, I can only seem to get the DATE portion out.
...
1
vote
2
answers
367
views
Join on clustered tables shows greater cost than non-clustered tables?
I have created a cluster and have two tables emp and sep on the cluster.
When I do the simple query...
explain plan select * from emp_cluster join dep_cluster using (dno)
the cost is 26.
I created ...
20
votes
3
answers
20k
views
What does NVL stand for?
What does NVL stand for? I'm talking about the Oracle and Informix (perhaps some others too) function used to filter out non NULL values from query results (similar to COALESCE in other databases).
2
votes
1
answer
15k
views
Materialized view taking too much time
i create a materialized view fast refresh. it takes almost 45 min to create but it did not refresh in 24 hours. i tried it on with index and without index. i check the log of all table max record in ...
1
vote
1
answer
745
views
Improve performance for my plsql block
First of all let me make kind of a disclaimer here:
1) i'm a developer, not an administrator;
2) and i dont have admin rights on my database.
3) table specs :
-- oulp_path_attendance_base_t - 5....
1
vote
2
answers
622
views
What is the performance difference between local text index and domain text index in oracle?
What is the performance difference between local text index and domain text index in oracle? Also what is the cost of rebuilding the domain text index when I have to split just one partition? Does the ...
-6
votes
1
answer
17k
views
how to query multiple row data in single column in oracle?
i have a table in which the values are
id value
----------------
1 10
1 20
1 30
2 33
2 34
I want the output given below using sql:
id value
----...
6
votes
2
answers
2k
views
Making the Oracle optimizer use a virtual column to find out about a partition
I have a large table that has three columns like this:
"START_DATE" DATE,
"START_VALUE" NUMBER(10,7)
"START_DATE_VALUE" NUMBER(18,7)
GENERATED ALWAYS AS
(
(extract(YEAR FROM START_DATE) * ...
0
votes
1
answer
190
views
Best practices in securing Oracle Standard edition one [closed]
I have a database server with Oracle Standard Edition One. I'm interested what are the best practices to secure Oracle? I have Java web application installed in the same server. The Java application ...
6
votes
2
answers
23k
views
What units used in DBA_TABLESPACE_USAGE_METRICS?
What units are used in the usage metrics (USED_SPACE and TABLESPACE_SIZE columns) described in Oracle's DBA_TABLESPACE_USAGE_METRICS static data dictionary view?
The Oracle Database Reference only ...
8
votes
2
answers
30k
views
How Does Oracle Handle Multiple Concurrent INSERTs Against One Table
I am trying to understand Oracle 11g a little more closely. My question is simple: how does Oracle handle two sessions that are attempting to insert records into a single table at the same time.
For ...
1
vote
0
answers
101
views
sdo_tune.quality_degradation returning negative values
While running the sdo_tune.quality_degradation function on one of our Oracle databases most of the results were around 1 but a number had values like -556127.225.
Does this mean that these indexes ...
3
votes
1
answer
77
views
Discover SQL Time on Oracle10g
I'm using Oracle10g and I have a problem that is challenging me. The application has a timeout of 45 seconds. It accesses a user from my database and always performs the same action. The problem is ...
4
votes
1
answer
1k
views
Measuring the relative performance of queries given the assertion that "cost" isn't reliable
The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.
Source
Does this ...
13
votes
2
answers
637
views
Are there any benefits in certain ordering of columns when defining indexes
For example, if I have two indexes:
CREATE INDEX IDX_1 ON MY_TABLE_1
(ITEM, DATE, LOCATION)
COMPUTE STATISTICS;
CREATE INDEX IDX_2 ON MY_TABLE_1
(DATE, LOCATION, ITEM)
COMPUTE STATISTICS;
Would ...
3
votes
4
answers
2k
views
Is it possible to virtually vertical partition and oracle database table?
Just like the question asks; is it possible to transparently partition a table vertically, by using virtual partitions? I'd imagine if it was possible, that columns would just be stored in separate ...
7
votes
2
answers
9k
views
Oracle's Merge v. Select->Update/Insert
What is the faster statement? A MERGE or Select->Update/Insert depending on if it returns a value?
I'm trying to optimize a database, but I haven't found any speed tests comparing the two.
I found ...
3
votes
2
answers
5k
views
oracle tablespace character set
I'm working with Oracle databases from 10g-r1 to 11g-r2. They are set as ISO-8859-15 (WE8ISO8859P15) character set.
I need to create a tablespace set as UTF-8 (AL32UTF8) character set inside these ...
7
votes
2
answers
2k
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
1
answer
1k
views
Silent truncation of last character when using SSIS with Oracle
I am using SSIS to export data from an Oracle database. Sometimes when I export a VARCHAR2 column to NVARCHAR the last character gets truncated (silently). It's completely random as far as I can tell. ...
3
votes
1
answer
2k
views
Oracle RAC on Virtual machine
I have Oracle RAC on the very powerful servers and it can be said that these servers have extra memory,cpu and storage :) ...so my servers are much more powerful than it is needed.
My manager and ...
10
votes
2
answers
4k
views
Oracle Shutdown Method
Shutting down a database before doing an upgrade or a patch can be done several ways.
shutdown immediate;
or
shutdown abort;
startup restrict;
shutdown immediate;
or
shutdown abort;
startup ...
4
votes
1
answer
166
views
Rank Partition?
I have a table which holds customer information with an Account state (working or Deactivated) for every single day.
I'm trying to reduce this information by showing start and end dates for the ...
2
votes
1
answer
516
views
Error creating linked database to oracle
CREATE DATABASE LINK myLINK CONNECT TO username IDENTIFIED BY password USING 'PROD';
OPT=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.6.1.5)(PORT = 1521))
)
...
16
votes
4
answers
69k
views
Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes
Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple ...
0
votes
2
answers
2k
views
Commercial database systems versus open source database systems [closed]
Can anyone tell me what are the reasons for buying a commercial database system and not using an open-source free version?
The reason for my question is that:
Commercial systems cost upwards of 10....
2
votes
2
answers
27k
views
Reduce Size of SYSAUX
I am trying to reduce the size of our SYSAUX Table. Currently the largest four occupants are below. As you can see it is mostly the Server Manageability items.
select occupant_desc, ...
0
votes
1
answer
280
views
How do oracle date time work?
I have following where condition in query. What the time is between T1 and T2 means it includes or excludes the boundary. Also between T1 and T1 means what?I am confused as both returns result.
...
2
votes
2
answers
6k
views
How do I recreate a deleted logfile on an Oracle database?
For my database assignment we were looking into "database corruption".
I was asked to delete the second redo log file, which I have done with the command: rm log02a.rdo this was in the $HOME/ORADATA/...
15
votes
2
answers
95k
views
How to free up disk space? which logs/directories to clean?
I want to free up the disk space on my Linux machine. I've drill down the space usage and found that the following directories have a big size
/u01/app/11.2.0/grid/cv/log
/u01/app/11.2.0/grid/log/...
3
votes
2
answers
2k
views
Oracle explain for cascading delete is wrong
My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it.
If I hit the explain button on a statement like this ...
4
votes
1
answer
5k
views
How to create oracle linked server in oracle server itself?
Is it possible to create oracle linked server in other oracle server? I actually wanted to do this with sql server but in sql server I couldn't run long query (8000 limit).
I have access to remote ...
5
votes
1
answer
6k
views
ORA-01017: invalid username/password; logon denied
I've come across the abnormal situation, and can't understand what happens.
I am connecting databases using PL/SQL Developer 8.0.4.1514(I've also tried v7,but same) . Sometimes(When I am trying to ...
1
vote
2
answers
6k
views
How do I manage column order when using PL/SQL's %ROWTYPE records?
I have a table with a large number of columns, which has data gathered from several other tables. I want to write some PL/SQL like:
DECLARE
TYPE t_toptab_list IS TABLE OF toptab%ROWTYPE;
l_data ...
3
votes
1
answer
157
views
How to search for an ID and its descendants in this query?
I have the following tables:
Project Unit
------------- ---------------
ID | IDUnit ID | IdParent
I have to select all projects that have the IDUnit ...
1
vote
1
answer
2k
views
Error with Oracle trigger, invalid identifier
I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working.
DML statement
UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
NVL((SELECT F....
0
votes
1
answer
2k
views
export SQL statements from Oracle 11g database via command line
I'd like to export an Oracle 11g database as a series of SQL statements via the command line. I'd like the functionality of SQL Developer (the process described here https://dba.stackexchange.com/a/...
1
vote
1
answer
455
views
Update statement errors, parenthesis, failed validation ORACLE
i am writing an update query and it is giving me errors, what i am trying to do is to take a value multiply it added to another value.
Original statement
The statement below works flawlessly,
...