All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

2
votes
1answer
361 views

What logical structure is used for storing rows in Oracle?

From the logical data separation point, there are tablespaces that consist of segments that consist of extend that consist of oracle blocks that consist of OS blocks. What logical structure actually ...
-3
votes
1answer
6k views

What are performance benefits when using number type instead of using char/varchar?

From various posts, I have summarized about using number type instead of char/varchar for PK: If you choose char/varchar type for PK and your values are short (just some alpha numberic codes) then it ...
5
votes
4answers
179 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 ...
9
votes
1answer
960 views

Column level security

I am in need of a solution to hide specific columns in a table. We have people who need to build reports against this database and specifically some of these tables with confidential information but ...
7
votes
4answers
806 views

Can this business logic be enforced by a conditional database constraint?

I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to ...
9
votes
3answers
3k views

How can I ensure that only one copy of a procedure is running in Oracle?

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error. Whats the best method ...
2
votes
1answer
4k views

Why Is oracle ignoring my index?

This query is not too complex: select trunc(created_date, 'MONTH') as created_date, al.op_name, al.region, al.alarm_type, COUNT(1) as total_new, SUM( (SELECT ...
1
vote
2answers
830 views

Would existing constraints and indexes applied to an old table, apply to a new table renamed like the old one after it's dropped?

everything is in the question. I have a table named tableA on which we have indexes and foreign keys and constraints. I have to modify content in a column called column1. This being a production ...
4
votes
1answer
591 views

How to I interleave logs from two different Oracle tables?

I have two log tables, which are populated from two different PL/SQL packages. It's often useful to keep the data separate, but sometimes I'd like to "interleave" the two tables so I can see what's ...
3
votes
3answers
5k views

Oracle - abort within a “before insert” trigger without throwing an exception

I know that this is an awful thing to do for many reasons, but I am in Get Things Done mode and we have a piece of software, which we can't modify, that writes records to a table. Some of these ...
4
votes
3answers
2k views

Can you time how long an Oracle import takes?

I'm on a Windows machine with Oracle Client 11.1.0, and am going to import a database dump. I'm importing from the command line with the "imp" command. Is there an option to time how long the import ...
2
votes
2answers
3k views

Oracle regular expression regexp_like

I have simple question. why would : select regexp_like( (select 'https://www.hotmail.com' from dual) ,'(f|ht)tps?:') from dual ; not work?
5
votes
2answers
8k views

Help to understand explain plan in Oracle

I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with ...
2
votes
2answers
26k views

Comparing date and timestamp variables in a where clause in a procedure

I have a variable in an Oracle stored procedure of DATE type (from user interface), and another of TIMESTAMP type (database is more precise than what the user would enter in the code that is executing ...
1
vote
1answer
196 views

Does Oracle server installation include client components?

I installed Oracle personal edition on a Windows Server 2008 virtual machine for testing purposes. My intention is to test Linked server functionality with SQL Server. Does Oracle Server installation ...
2
votes
1answer
5k views

How do we free up disk space from allocated namespace? / ORA-03297

We are running out of disk space. The server has a 420 GB HDD, and runs a Microsoft SQL Server and an Oracle Server(10g). The Oracle server uses up to 315 GB space. We have just loaded a 100GB ...
9
votes
2answers
40k views

What is the easiest way to move data from Oracle to SQL Server?

One of our products supports both Oracle and SQL Server as database backend. We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition ...
3
votes
1answer
2k views

Oracle integer to number conversion

According to the Oracle documentation, creating a table with a column of ANSI type INT, Oracle will convert it to NUMBER(38). According to the same document, NUMBER(38) is supposed to be a NUMBER ...
2
votes
2answers
98 views

Regular Expression Difficulty

I'm trying to extract 456 from the string :123:456: as follows: select regexp_substr(':123:456:', ':(\d+):', 1, 2, 'i', 1) from dual However, this query returns null. What am I doing wrong?
10
votes
2answers
3k views

Does Oracle PL/SQL have a standard ASSERT routine?

I'd like to use an ASSERT routine functionally similar to the one found in other languages, i.e. a construct (be it a procedure, syntax...) ASSERT( <condition>, <msg>) such that when ...
6
votes
2answers
634 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 ...
1
vote
1answer
7k views

Arabic characters not displaying in oracle database

We have a Oracle database 8.1.7 running for many years on Win-2000. We wanted to move the database on a newer machine running Win 2003. I did the following I made a cold backup of old database. ...
2
votes
1answer
5k views

Oracle not available error with Oracle XE 11.2.0-1.0

I have downloaded Oracle XE 11.2.0-1.0 and installed it on a CentOS 6.3 virtual machine with 1.5GB memory and 3GB of swap. SYS and SYSTEM set to have password 123456. I have set ...
4
votes
1answer
2k views

Oracle High-water Mark: how relevant it is when using ASSM tablespace for tables?

I have several questions: 1) If Oracle manages both High-water Mark and Low High-water Mark in ASSM tablespace then do we have to think about that when thinking about performance? I mean, does ...
7
votes
2answers
10k views

SQL Server Express vs. Oracle Express Edition vs DB2 Express-C

I have been trying to compare the features of the SQL Server Express, Oracle XE and DB2 Express-C, but find it difficult to compile all the differences into one place. Especially the current ...
5
votes
1answer
169 views

how do I configure a new Oracle 11g instance to match the configuration of the old Oracle 10g server

How can I compare two Oracle database servers which are on two different machines? We need to ensure that the new server is setup correctly. For example, one machine is running Oracle 10g on ...
-1
votes
1answer
267 views

Selecting just one table from Oracle view [closed]

I am creating a view to ease my manual selecting because I only want to fetch data from DB where it is meeting certain criteria which are present in several tables, so it is containing a lot of joins. ...
3
votes
1answer
3k views

Simple Oracle query hangs depending on WHERE clause

Not used to using Oracle, but we have a large database where a non-unique query like SELECT * FROM employees where department = 'HR' is working, results list up no problem. But when I do ...
3
votes
0answers
479 views

Oracle sqldeveloper Database Diff - not seeing indexes/sequences

I am using Oracle SQL Developer's database diff tool to compare schemas. (SQL developer version : 3.2.09) It works great on some environments, but in one, I can only see differences in tables - the ...
5
votes
2answers
2k views

Using Multi-table Insert for Parent and Child Table

Is it safe to use Oracle's multi-table insert statement to insert into a (foreign key constrained) parent and child table? With minimal examples, I've found that it works as long as the parent table ...
2
votes
0answers
9k views

Oracle 11g .DMP viewer

I have received a dump of a Oracle 11.2.0.2.0 db, which I'd like to export to some readable format like plain text, CSV, excel... Is that possible in some sort of a simple way? I've found a few ...
36
votes
5answers
25k views

Why not use a table instead of a materialized view?

I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some ...
3
votes
1answer
2k views

How To dump data from Oracle to Vertica

I want to transfer data from Oracle to Vertica and I want to know what is best way to do it? I s there a tool for it? I tried to dump the Oracle data and load it to Vertica using 'Copy Direct' ...
3
votes
2answers
6k views

Linux Error: 29: Illegal seek in LSNRCTL for Linux Version 11.2

I'm trying to get Oracle Database 11g Express set up on my server so that it is accessible from the outside. I can talk to the DB locally using SQLPlus, so the database is definitely running. I ...
2
votes
2answers
4k views

Querying a database efficiently for a huge chunk of data

Here is my query SELECT * FROM ( SELECT a.*, rownum rnum FROM ( SELECT id, data FROM t ORDER BY id ) a ...
1
vote
1answer
307 views

Size of an empty database

Continuing with my school-project, now I have to estimate the size of the database from here to 5 years. They give me a formula, so, in that formula, one of the values is the size of the empty ...
3
votes
1answer
1k views

How far should bind variables go in Oracle?

I am currently examining all database users's SQL code in our department for not using bind variables. We do experience an ORA-04031 error within a constant period of time. Now, I have a quite firm ...
4
votes
2answers
12k views

Estimate a Database Size

I have to do some project, and need to estimate the DB size. I'm gonna to use Oracle in the project, because will have a lot of transactions and size. But in the "presentation" I need to specify, in 5 ...
4
votes
2answers
16k views

Updating Multiple Rows with Data from Column A into Column B?

I have about 60,000 rows that I'm needing to update the information from column_a to column_b. For example, 60,000 customers need their arrival_time to match their departure_time. I understand that ...
12
votes
4answers
2k views

Reason to not use nullable number in Oracle?

Our company is interfacing with another software company for a joint project, and we were told that, if a particular value should not be displayed, we should pass in a -5000 (their arbitrary sentinel ...
3
votes
2answers
2k views

Oracle: deleting data from tables leads to data file fragmentation?

When randomly deleting ~5GB from a ~10GB Oracle table, I suspect that disk space is left in fragments. Is there a way to know about the disk state and any query to defragment or shrink the space?
2
votes
2answers
5k views

Oracle.exe (SHAD) process keeps increasing

I have an Oracle Server 11g that we use for integration tests. For some reason, the processes count [select count(*) from v$process;] keeps increasing up to the limit, causing ORA-12514: TNS:listener ...
5
votes
4answers
1k views

optimizing a compare query

Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
10
votes
1answer
34k views

Why Oracle sessions are still alive after two weeks being Inactive

DB: Oracle 10g O/S: Windows Server 2003 64 bits I query list of Oracle sessions generated by web applications (filter by program=w3wp.exe) select * from V$session where UPPER(program) LIKE '%W3%' ...
2
votes
4answers
1k views

Limiting Generated Sequence Values in Multi-table Insert

In Oracle, given the following objects: create table a (x number ); create table b (val number ); create table c (val number ); create sequence my_seq; Suppose I populate table a as follows: ...
2
votes
1answer
1k views

impdp import fails with object already exists in oracle 10g?

I exported the dump using EXPDP command. After exporting I dropped the user and the associated tablespaces by logging is as / AS SYSDBA. Then I re-created the User and the Tablespaces. Then I ...
2
votes
2answers
9k views

Oracle startup error: “end-of-file on communication channel”

The Oracle instance I am working with is not coming up after issuing a startup. All I get is the following: SQL> startup ORACLE instance started. Total System Global Area 1.2300E+10 bytes Fixed ...
4
votes
1answer
4k views

Drop user did not reduce size of tablespace file

I have a development environment in which I frequently copy a certain schema. When I'm done with the copy, I will use OracleEM to drop that user/schema. My problem is that this does not seem to ...
3
votes
2answers
2k views

Character Set Database Migration

This is a follow-up question to a previous thread I had posted. We're in the process of migrating to a new character set. Now, I am a programmer and we have DBA's who will be performing the work but ...
4
votes
4answers
6k 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 ...