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) ...
0
votes
3answers
346 views
Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?
I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
9
votes
2answers
1k views
Updating 700 million rows to same value
I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows.
I don't have admin access, or access to an admin, so this needs to be accomplished with ...
9
votes
2answers
1k views
Security for Application Developers doing PL/SQL work in Oracle
How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need ...
8
votes
1answer
485 views
Do Oracle databases use Java internally?
My question is fairly simple. Is Java used internally by Oracle in their DBMSs?
7
votes
1answer
237 views
Is there an execution difference between a JOIN condition and a WHERE condition?
Is there a performance difference between these two example queries?
Query 1:
select count(*)
from table1 a
join table2 b
on b.key_col=a.key_col
where b.tag = 'Y'
Query 2;
select ...
7
votes
1answer
123 views
Cardinality is so far off from reality
I've got a table where the cardinality for plans just seems waaaaaay too high - despite updating stats and even trying dynamic sampling for Oracle 11gR2.
select count(*)
from table1
where col1 = ...
6
votes
1answer
617 views
Materialized views in oracle
How are materialized views implemented in oracle? I've created a materialized view in oracle. What I now see is a quite normal table with a job. Are materialized views translated into tables & ...
5
votes
4answers
497 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
381 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
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 ...
4
votes
3answers
830 views
Is (and if how) it possible in Oracle to “insert into <table> values <rowtype-variable>” if <table> has virtual columns
Here's a table
create table tq84_virtual_test_without (
col_1 number,
col_2 number,
col_3 number,
col_4 number,
col_5 number
);
with the rule that col_5's value is the sum of the other ...
4
votes
1answer
512 views
What is Sql Server Clustered Index in Oracle terminology
A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on.
If you look up with a name Ozgur you start O then z etc.
I would like a simple explaination for this ...
4
votes
1answer
3k views
oracle format specifiers: to_number vs to_char
SQL> select TO_NUMBER(123.56,'999.9') from dual;
select TO_NUMBER(123.56,'999.9') from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select TO_CHAR(123.56,'999.9') ...
4
votes
1answer
935 views
How do I horizontally partition an oracle database table, and should I?
We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment.
Create ...
4
votes
1answer
519 views
What is the difference between these two create table statements?
a)
SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE);
Table created.
SQL> desc xml_tab1;
Name Null? Type
----------------------------------------- -------- ...
4
votes
2answers
1k views
What's a free client for Oracle 8 on Linux / Ubuntu?
I need to connect to a legacy Oracle 8 database.
Oracle SQL Developer would be my tool of choice, but the current version doesn't support Oracle 8 anymore.
I tried Squirrel too, but the connection ...
3
votes
2answers
1k 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 ...
3
votes
1answer
3k views
Is commit necessary after DML operation in Function/Procedure?
I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure?
Example:
create or replace function test_fun
return number is
begin
delete from a;
return ...
3
votes
4answers
2k views
Synchronizing data between MySQL and Oracle databases?
I want to sync data between Oracle & MySQL databases. What is the best possible way to achieve this?
Are there any tools/software available?
This is not a one-time but an ongoing operation. The ...
3
votes
5answers
3k views
Copying Oracle 11g express Database from one server to another
Basically, I am a MS-SQL developer,and relatively new to Oracle.
Even after great deal of research, I was unable to find any way of copying entire database from one server instance to another.
In ...
3
votes
1answer
308 views
What is an Effective Way to Count the Number of Pages in Oracle?
We've implemented paging in our PL/SQL procedures by building a dynamic SQL query and then selection only a portion of results.
We build implement paging by putting the original query inside a ...
3
votes
4answers
1k views
Why does = NULL not work in Oracle?
I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL.
Why does = NULL not work here?
3
votes
2answers
5k views
Oracle 10g dump file push into Oracle 11g database
Currently when executing the imp command with the following parameters: file=my.dmp log=my.log fromuser=myuser touser=myuser
The following errors occur:
IMP-00058: ORACLE error 12560 ...
3
votes
2answers
265 views
Resources for SQL Server developer migrating to Oracle
What resources can you recommend for a SQL Server developer wanting to learn Oracle basics?
I am looking for a comprehensive whitepaper or blog post describing differences between these systems and ...
2
votes
2answers
607 views
How to set Password for sqlplus /as sysdba
I am new to Oracle. I would like to know how to set login password for sqlplus / as sysdba in oracle 11g.
I want to set the password for my next login, i dont want to login as sqlplus / as sysdba ...
2
votes
1answer
2k 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, ...
2
votes
1answer
146 views
Does 'LGWR' writes to online redo log files strictly in circular fashion?
Does 'LGWR' writes to online redo log files strictly in circular fashion? I saw this sentence from Oracle documentation.
If archiving is disabled (the database is in NOARCHIVELOG mode), then when ...
2
votes
2answers
1k views
How can I catch the output of DBMS_OUTPUT.PUT_LINE when executing a block using [Oracle.DataAccess.Client]?
In my oracle Database if have the following table:
Create table Test_call_count (
count number(10)
);
insert into Test_call_count values (0);
Now I can run the following PowerShell script
if ...
1
vote
1answer
115 views
Find who and when changed a specific value in the database
We recent found some record in a table of an oracle database has been updated to another value.and it has been cause very important mistake.
we want to find out who and when has done this?
could ...
1
vote
1answer
96 views
Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index
One of the largest tables in our system has its primary key setup as a globally ranged partitioned (in huge, useless, multi-billion value ranges) non-unique index. Its values are just a sequence ...
1
vote
1answer
271 views
How to open Oracle listener for three database instances?
I have 3 database instances:
orcl
JIP02T
JIP04T
When I start the database, does it start all 3 instances?
When I start the Oracle listener, does it start a listener for all 3 instances?
When I ...
1
vote
1answer
854 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 ...
1
vote
1answer
372 views
Repartition Primary Key Index online in Oracle
I have an 24x7 OLTP (11gR2) application that endures heavy inserts on particular tables, traffic has increased to the point where we are getting heavy index contention. We use sequences for the ...
1
vote
1answer
141 views
Is there an Oracle rdbms monitoring API?
We have about a thousand customers with our application installed out of which 500 have an oracle database.
Monitoring the database is only one of the requirements.
Installing Oracle Enterprise ...
1
vote
3answers
235 views
Compare content of databases in Oracle
we have a request to build an ASP.Net application that will be used as interface to launch commands on Oracle databases.
We need to find a way to compare the content of 2 databases. We prefer not to ...
1
vote
2answers
463 views
Can I implement a 'gapless' identity column in Oracle?
In SQL Server it is so easy to write:
create table #tmp (
id integer identity(1,1) primary key,
message varchar(256)
);
When I try to migrate this to Oracle, I end up with:
CREATE ...
0
votes
1answer
109 views
What is the best way to insert a XML input to an oracle table in stored procedure?
I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this:
<rowset>
<row>
...
0
votes
2answers
472 views
“ORA-01017: invalid username/password” : Why is that?
What is the difference between the two :
SQLPLUS / AS SYSDBA
and
SQLPLUS
Enter user-name: SYSDBA
Enter password:
If I use the first command I get connected and see the prompt SQL> but upon ...
0
votes
3answers
374 views
ORA-00905: missing keyword when trying to create a synonym
I have a problem when I try to create a synonym like this:
CREATE SYNONYM B2H.BILLING_USER_ACTIVITY@PACOMNET
FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM;
Why does this happen and how can ...
0
votes
1answer
3k views
How to take oracle 10g backup? [duplicate]
Possible Duplicate:
How to take oracle 10g backup?
I followed this http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php link to create a db backup. I used this command to create ...
0
votes
2answers
443 views
Copy complete structure of a table in Oracle without using Backup
When you create do a CTAS (create table as select) of a table you only get the structure, but lose the index, PK, FK, etc.
Example:
create table t1 select * from table2;
How can a copy of the ...
0
votes
1answer
232 views
Preloading a Java library in DB2, similar to ORACLE's loadjava
Edit to add details to clarify, based on Chris' comment:
A Java stored procedure depends on external libraries. Locally it compiles successfully, since they are added to the classpath. But when ...
0
votes
1answer
590 views
Listener failed to start
I have installed Oracle 11g on my Window7 computer; whenever I try to create a connection in Oracle SQL developer I get this error TNS:listener does not currently know of SID given in connect ...
