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) ...

learn more… | top users | synonyms

0
votes
0answers
60 views

Advice on DB Changes

Requirement: I'm required to prefix a couple of strings to the existing one. I.e. The current way, the Accounts been recorded in AccountID field looks as "621230093VN00" Under the new format ...
0
votes
2answers
1k views

Which SQL Developer Version will be compatible with Oracle 9i

My Company is using Oracle 9i. I want to connect to the database. I'm planning to use SQL Developer. Which version will be compatible Oracle 9i?
0
votes
1answer
290 views

How to find current roles available on a database

I am looking to provide grant to one of my function in my database. However, I am not sure what roles I should give access to. I searched for it but can't find list of roles for all the users. Any ...
17
votes
7answers
10k views

Oracle - Any way to view uncommited changes to a particular table?

I'm debugging through a batch process currently that does a lot of DML statements, but doesn't do a commit right away. It would be nice to be able to view the "pending" changes from another session ...
0
votes
3answers
104 views

Applying data changes from one database to another

I have an application running on Oracle 9i database and I was looking for something more practical to deploy data changes (not schema changes) from the development database to production. Currently I ...
2
votes
1answer
205 views

SQL from/where or join?

I was wondering performance wise what would be faster a join or a from and where query. I am trying merge and update query but performance is terrible?
3
votes
1answer
516 views

Why is SQL*Plus' desc table not showing not null constraints after using dbms_redefinition

I have the following table that I want to redefine: create table tq84_redefinition ( id number primary key, ts1 timestamp not null, ts2 timestamp ); Please note the not null constraint on the ...
0
votes
0answers
87 views

Viewing tables and data from a tablespace that was attached from another database

I'm using a database Oracle 11g. I have a DBF file of an old tablespace from a different database that I want to connect to the existing database. I've created a tablespace with REUSE caluse, and it ...
7
votes
1answer
372 views

Temporal Validity and Primary/Foreign Key Relationships

I've read through several of the oracle tutorials that show temporal validity and time features. However, in the examples I'v read, there is no primary key used in the demo tables. ...
1
vote
1answer
2k views

Why can't I log in to SYSTEM without AS SYSDBA

When loging in to sqlplus as SYSTEM, why does the login fail with an invalid username/password error if I do not include "AS SYSDBA" C:\Windows\System32>sqlplus /nolog SQL*Plus: Release ...
0
votes
1answer
1k views

determine redo log size, number of redo log files

Having 2 node RAC instance using ASM Log switches happening are too high about 100 per hour causing db to hang for few minutes sometimes GROUP# THREAD# MEMBER ...
1
vote
0answers
144 views

ORA-01115 error with data pump export [closed]

I exported DMP file but the end of the logfile displays this message: 0 rows ORA-31694: master table "CREG_OP"."EXPDP_TAMIN" failed to load/unload ORA-02354: error in exporting/importing data ...
0
votes
0answers
75 views

Query with large number of or'ed predicates on the same column?

At one place we need to query a table to determine whether any records exists that are in a subset of the same table. The columns that are queried are varchar columns containing file-paths, and we ...
-1
votes
1answer
228 views

What is Oracle Database Vault and how does it work?

I am new in oracle database.I am following oracle official site for learning oracle database vault.But i am not satisfied yet.Please help me for getting a preliminary knowledge about oracle database ...
2
votes
1answer
357 views

Any advantage of creating table in temporary tablespace - Oracle

My on-site DBA told me to create the particular table on temporary tablespace.Is there any advantage of such action?
1
vote
0answers
186 views

How to ignore optional prerequisites when installing Oracle database in silent mode? [closed]

I was referring to this PAGE to install Oracle 11g database in silent mode. When installing, there were some optional prerequisites which are ignorable: CAUSE: Some of the optional prerequisites ...
3
votes
1answer
99 views

How many blocks is my database

How can I write a query using Oracle SQL Developer to find how many blocks my database is using? (tables & indexes)
0
votes
0answers
265 views

Error ORA-06550

CREATE OR REPLACE PROCEDURE pro_department_report AS v_did department.deptid%TYPE; v_dname department.dname%TYPE; v_sname student.sname%TYPE; v_count NUMBER(2); v_sdid student.deptid%TYPE; CURSOR ...
0
votes
3answers
70 views

Get records which doesn't have duplicate records

I have a table with polcies and class codes, I want to get all policies which doesn't have duplicate class codes. Do you have any idea? One policy may have one or more class codes. If any class code ...
1
vote
1answer
108 views

Does Oracle 10g Express Edition have enough storage? [closed]

I am going to develop a simple library database with less than 10 tables. Each table would only keep text records. The library has about 10,000 volumes of books and about 2,000 members. Would the ...
4
votes
2answers
9k views

How to get a list of locked accounts / check that account is locked?

I can use following statement to unlock an account: ALTER USER username ACCOUNT UNLOCK But which statement can I use to verify that account is currently locked out?
1
vote
2answers
84 views

CONNECT BY using 3 tables(2 DIM and 1 FACT to get the Level and dependencies)

Below is my scenario and any help in this regard would be highly appreciated. I need to get the list of Managers and reporting persons to that manager with LEVEL at ID level. But the Fact table ...
5
votes
1answer
18k 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%' ...
1
vote
1answer
640 views

OEM 11g alerts and notifications arrive together

I setup the OEM with the Idea to receive Critical messages (emails when the DB, listener, agents and host are down/up) but it doesn't seem to be working. If I "shutdown abort" the database I wont ...
2
votes
2answers
204 views

What are the performance benefits of segregating database objects into different tablespaces?

As the question states, what are the benefits of doing this given the following information/constraints: I will not be using partitioning I will not be placing the tablespaces and its datafiles into ...
1
vote
1answer
84 views

Emulating Oracle flashback functionality [closed]

I'm considering going from Oracle Enterprise Edition (EE) to SE/SE1 but want to retain the flashback database and flashback table features from EE. Does anyone know how to emulate these features in ...
0
votes
0answers
230 views

My SQLs are taking too long to execute for first time everyday in Oracle 11g [duplicate]

whenever I make a new request in my application it takes to long to respond for the first time and all the subsequent requests are faster. If I do it again after some hours or tomorrow again it takes ...
1
vote
0answers
136 views

Is there are way to use Fine Grain Auditing to record Varray bind values?

I have an application, and would like to use Oracle's FGA (Fine Grained-Auditing) to record varray bind information. Specifically, I am using Oracle Spatial and need to know at what extent users are ...
2
votes
1answer
217 views

Query Previous Records (Oracle)

Consider an Oracle database that has a person, a date and a value in every record. When I pull a record, I want to very quickly and easily get the previous record where person is the same in both the ...
0
votes
0answers
99 views

Oracle APEX Button creating ORA-00920

I have a table for user registration with a column for account_status which I filled with the content from SYS.dba_users. I also have a APEX application for this table and some other stuff and want to ...
0
votes
1answer
124 views

Sequences Vs Count for auto-increment

Can some one please tell me the exact difference between the following statements , INSERT INTO table1 VALUES ((select count(column-name) from table-name2)+1); here i am using the count function to ...
0
votes
1answer
106 views

Trying to count multiple values and total

SELECT COUNT (DISTINCT competition.genre_id) genrecount, COUNT(DISCTINCT band.band_id), genre.name, genre.genre_id FROM genre, competition_band, competition, band WHERE genre.genre_id = ...
0
votes
1answer
68 views

How to add up a column depending on data from another column

I have a table which has results and a table that has band_name and band_id. Now I need to create a query which will add up the total points for each band and display the band name and ID, so far I ...
0
votes
2answers
560 views

Warning before issuing delete/update command in mysql/oracle , then executes

Is there any utility , function either in mysql /oracle that if i issue some delete/update command wrongly , a message must prompt at command_line* (if possible in ubuntu) asking whether "you sure to ...
18
votes
10answers
44k views

Eliminate duplicates in ListAgg (Oracle)

Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to ...
0
votes
2answers
346 views

table does not exist

CREATE OR REPLACE PROCEDURE ProcedureX() TYPE AVERAGE_COUNT_TBL IS TABLE OF INTEGER; AVERAGE_COUNT AVERAGE_COUNT_TBL; BEGIN INSERT INTO AVERAGE_COUNT VALUES(10); END ProcedureX; It ...
1
vote
1answer
226 views

Why does Oracle still not support Multi-Row insert? [closed]

According to Wikipedia's article about INSERT in SQL, multi-row inserts are standard since SQL-92. Oracle (up to 11g at least) does not support this. Why? -- Valid insert into some_table (magic) ...
-1
votes
1answer
4k views

What is the Difference between Archiving and Purging in Oracle database?

I would like to know the main difference between Archiving and Purging in any database we use.
2
votes
4answers
657 views

Tool to monitor database data

I would like to tool that when record data changed inside all tables. so later i would get a report like table user added record - 3,user3.. deleted record - 2,user2..
1
vote
2answers
121 views

distinguish single-block vs multi-block reads in Oracle explain plan

If I am looking at a query plan in Oracle 11g, is there any way I can tell which operations will result in single-block reads and which will result in multi-block?
1
vote
1answer
449 views

ORACLE file I/O withouth explicit flushing on 10g and 11g

what happens, if I open a file (foo := utl_file.fopen(... ), write multiple times to a file ( utl_file.put_line (foo,bar) ) and close it ( utl_file.fclose(foo) ) without any flushing? Is there a ...
0
votes
1answer
4k views

How to change SESSION_CACHED_CURSORS for all sessions

how Can I do to change SESSION_CACHED_CURSORS for all sessions? I found on google and oracle website: Alter Session... but I need to changed it for all the session and not only for one of them. How ...
0
votes
1answer
499 views

how to clone or mirror oracle asm disks to new sets of disks

We have EMC CLARiiON disks/storage arrays that are near their end of support life cycle, so we need to move Oracle ASM disks on them to a new sets of vendor disks. We are trying to minimize the ...
1
vote
1answer
232 views

Recover oracle 10g database from old data files

I have an Oracle 10g database. Unfortunately, our machine and system crashed and all the backups and Oracle archive logs, but we have old image copies of our data files and one of the control file ...
3
votes
2answers
3k views

Where to start learning to become a successful Oracle DBA [duplicate]

I am IT Graduate with good knowledge on SQL and C. I have done most of the SQL programming on Microsoft SQL Server in my academics. But i want to become Oracle DBA. Could you please suggest me a ...
1
vote
2answers
34 views

Oracle Stored Procedures / schema maintenace but still keep service online, how?

I got a service running on a classic 3 tier model: 2 web servers and 2 application servers connecting to a single Oracle database instance. Most of the business logic are written as stored procedures ...
1
vote
3answers
12k views

Shutdown one node in RAC

I want to shutdown one node in RAC. I did the following steps: Shutdown database instance export ORACLE_SID=mydb1 sqlplus / as sysdba shutdown immediate; shutdown ASM instance . oraenv +ASM1 ...
2
votes
1answer
2k views

Oracle alter table modify column from NVARCHAR2 to VARCHAR2

Having a column FIRSTNAME NVARCHAR2(30) converted from VARCHAR2(30) with alter table ANGESTELLTER modify FIRSTNAME NVARCHAR2(30) -- worked! I tried to restore the original. However alter table ...
0
votes
0answers
183 views

How does tablespace sizing works in Oracle

Let's say I have an input of 20 gigabytes of raw data (text file) which in turn I would into an external table and would then swap partition in order to place them on my database. The question now is ...
2
votes
2answers
2k views

Why is important to write down the DBID in Oracle DB?

I've always heard that one of the first things you should do in Oracle DB is to write down the DBID, so in case you lost the controlfile you'll need to set it in RMAN, and then you will be able to ...