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.

Filter by
Sorted by
Tagged with
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. ...
efdee's user avatar
  • 143
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 ...
cool_ravi's user avatar
  • 113
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 ...
Data-Base's user avatar
  • 357
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 ...
snow8261's user avatar
  • 221
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 ...
Jens Schauder's user avatar
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. ...
NDT's user avatar
  • 29
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 ...
user avatar
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 ...
TheCleaner's user avatar
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 ...
Nicolas de Fontenay's user avatar
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, ...
kloppers's user avatar
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 ...
Vite Falcon's user avatar
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 ...
Rainier Morilla's user avatar
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. ...
Josh Smeaton's user avatar
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 ...
zainshah120's user avatar
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).
newenglander's user avatar
  • 1,055
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 ...
hafiz zahid's user avatar
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....
Alex Peta's user avatar
  • 111
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 ...
Sean Nguyen's user avatar
-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 ----...
sabyasachi's user avatar
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) * ...
Sean Nguyen's user avatar
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 ...
Peter Penzov's user avatar
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 ...
Danilo Piazzalunga's user avatar
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 ...
Dustin's user avatar
  • 81
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 ...
kenneedham's user avatar
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 ...
Rainier Morilla's user avatar
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 ...
Lewis Norton's user avatar
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 ...
Lewis Norton's user avatar
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 ...
Josh Smeaton's user avatar
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 ...
TomJ's user avatar
  • 171
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 ...
Alsciende's user avatar
  • 133
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; ...
René Nyffenegger's user avatar
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. ...
josjo's user avatar
  • 91
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 ...
kupa's user avatar
  • 1,707
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 ...
Leigh Riffel's user avatar
  • 23.9k
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 ...
Rob's user avatar
  • 41
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)) ) ...
kinkajou's user avatar
  • 479
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 ...
Leigh Riffel's user avatar
  • 23.9k
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....
Victor Blaga's user avatar
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, ...
user739866's user avatar
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. ...
kinkajou's user avatar
  • 479
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/...
Daniel's user avatar
  • 21
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/...
kupa's user avatar
  • 1,707
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 ...
Franz Kafka's user avatar
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 ...
kinkajou's user avatar
  • 479
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 ...
kupa's user avatar
  • 1,707
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 ...
Glenn Moss's user avatar
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 ...
BrunoLM's user avatar
  • 3,463
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....
David Garcia's user avatar
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/...
user1090661's user avatar
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, ...
David Garcia's user avatar

1
141 142
143
144 145
156