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

5
votes
2answers
219 views

Where to start with Oracle?

This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL. You may find your question has been closed as a ...
8
votes
1answer
555 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 ...
5
votes
2answers
1k views

ORA-04031: unable to allocate bytes of shared memory (“”,“”,“”,“”)

I am receiving this error in a development database. It happens on some specific queries (simple inserts and deletes via .NET application). The database is used by one user, and error happens ...
8
votes
1answer
2k views

Duplicate an Oracle database user

We'll be having external auditors coming over to perform a review on our Oracle database. They will be running a tool to perform the review and for that, they require a user ID that can connect to the ...
0
votes
1answer
2k views

Resolving ORA-01033 “initialization or shutdown in progress”

I followed the instructions at http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php to create a database export. I used: expdp scott/tiger@db10g full=Y directory=TEST_DIR ...
18
votes
2answers
2k views

How to make a transition from SQL Server DBA to Oracle?

As an interest, if I would to transition from a SQL Server DBA to Oracle what will be the major learning or unlearning that I would have to do? I would assume the concepts are the same and the ...
19
votes
2answers
1k views

As a DBA, how would I go about transitioning from Oracle to SQL Server?

I'm an Oracle DBA that also has Sybase experience. What are the major architectural and conceptual differences between the two RDBMS platforms? An answer similar to the SQl Server->Oracle question ...
13
votes
5answers
11k views

Oracle Creating ER Diagrams and Data Dictionaries

I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?
6
votes
3answers
30k views

How do I generate a tnsnames.ora file to connect to an Oracle Database?

I have installed Oracle 11g, and I can connect as sysman to the Oracle database, but there is no tnsnames.ora file that I can find. Do I need to generate the tnsnames.ora file myself? If so, where do ...
22
votes
3answers
20k views

What is the difference between sys and system accounts in Oracle databases?

There are two ways to connect to Oracle as a administrator using sqlplus: sqlplus sys as sysdba sqlplus system/manager These accounts should be uses for different purposes, I suppose. Which tasks ...
12
votes
2answers
425 views

forensically deleting/updating data

I have a need to forensically remove data from oracle. If I just delete it, my understanding is the data will still actually be in the data file until that space is reused. I'm not concerned about the ...
5
votes
2answers
14k views

How do you show SQL executing on an Oracle database?

How can you show the SQL that is currently executing on an oracle db? Extra information that would be useful would include user, session id etc.
15
votes
7answers
6k 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 ...
10
votes
3answers
3k views

How do indices impact query performance?

Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the ...
9
votes
2answers
820 views

Recovering logical space from a tablespace

I have a tablespace called DATA, and it is setup with auto-extend as false. This tablespace has two datafiles and is set up so it takes up 350 GB of physical space. A week ago I queried ...
8
votes
3answers
1k views

Database backups in Oracle - Export the database or use other tools?

One of the "practices" that I've seen about being performed by DBA's in my organization is to treat a full database export using tools like exp / expdp as backup. Would this be a good practice ? What ...
5
votes
4answers
6k views

Building Dynamic Oracle Where Clause

I'm working on application that uses dynamic query to do a select statement based on user input, after discussing security with DBAs they want me to convert my dynamic select statement into Stored ...
4
votes
1answer
1k views

Update oracle sql database from CSV

I tried google at first but no luck. Is it possible to update tables from csv file? I am using SQL developer and I am using a script to export edited rows to a csv file. I would like to update the ...
2
votes
3answers
79 views

Move Complete ORACLE from one system to another

I have 2 identical server with exact disk partitions (OS CENTOS 6.4 both ) [My college central oracle server]. Entire ORACLE HOME ( bin , control files, data files everything on oracle) is on 2nd ...
2
votes
2answers
8k views

Grant create permission on a specific schema in Oracle 11g

I have two users A and B. I want to grant B the permission to create, drop, etc. all tables in A's schema. As far as I can see, I can grant B full access to all schemas not a specific one. Is this ...
11
votes
5answers
1k views

What features of Oracle make it an attractive choice for small projects?

Given Oracle's licensing handling[a] (and, to a lesser extent, cost) I have always been wondering what the deciding factors would be for choosing Oracle over PostgreSQL or MySQL. My company is nearly ...
7
votes
1answer
337 views

Map Oracle roles to Active Directory groups?

Is there any built-in way to map Oracle database roles to Microsoft Active Directory groups?
7
votes
2answers
1k views

What is the difference between terminating an Oracle sql statement with semicolon or with a slash on the next line?

The following two queries seem to be equivalent when executed in sql*plus: 1. select * from user_tables; 2. select * from user_tables / Is there any difference between both versions?
6
votes
3answers
2k views

Pivot rows into multiple columns

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data: ╔══════════╦══════════╗ ║ ...
5
votes
3answers
3k views

Bulk database update/insert from CSV file

I am implementing application specific data import feature from one database to another. I have a CSV file containing say 10000 rows. These rows need to be inserted/updated into database. There ...
4
votes
2answers
6k 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
2answers
243 views

SQL Data aggregation

I have a problem with sql data aggregation problem.here is the sample sql.I am using oracle database. select pm.city cityid, to_char(pm.sampledate, 'yyyy-MM') timepoint, count(distinct me.id) ...
3
votes
2answers
1k views

How does name resolution work in Oracle 10g?

In Oracle 10g (10.2.0), there are 4 types of names resolution: Easy Connect(10g added this new method) Local naming Directory External naming It is said that if Easy connect is used by the ...
1
vote
2answers
1k views

How do you correct a corrupted index in Oracle?

I have this ORA error in our logs: Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921) ORA-01110: data file 8: ...
8
votes
3answers
13k views

Difference between database vs user vs schema

I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)? If they are same then, what are the similarities between them? ...
8
votes
1answer
385 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 ...
6
votes
2answers
883 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) * ...
6
votes
4answers
521 views

Does an IOT guarantee order in a select?

We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1). The priority does not matter ...
4
votes
2answers
874 views

Oracle extents - How can I have so many with this small size?

This is a different question than the one with the almost-identical title. Again, this is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents ...
4
votes
2answers
438 views

Tracking SQL queries and rows operated on for documentation/audit

Is there a way to record the SQL queries (and rows that were acted upon) in a session for documentation/audit purposes? For example, in deleting duplicate rows from a table I use the following SQL ...
3
votes
1answer
74 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)
3
votes
1answer
1k 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
5answers
355 views

Query optimization [Oracle]

The Query: select JOIN_RESULT.batch_id as BATCH_ID, JOIN_RESULT.unique_doc_id as DOCUMENT_ID, JOIN_RESULT.brand as BRAND, JOIN_RESULT.message_date_time as UPLOAD_DATE, ...
2
votes
2answers
264 views

Why does the same query run faster the second time I run it?

I noticed when running a SQL query it is faster the second time than the first. So my question is, in Oracle SQL Developer where can you see the statistics that explain this behaviour?
2
votes
3answers
6k views

ORA-09925: Unable to create audit trail file

I'm using Oracle 11.1.0.6.0 db, on Linux 64 bit server on Amazon cloud. I'm getting following error when I run the connect command SQL> connect / as sysdba ERROR: ORA-09925: Unable to create ...
2
votes
3answers
292 views

RAM Impact on Oracle database operations

We are seeing drastic change in the amount of time taken for db operations depending on ram size. The same content when inserted into a db running on a 4gb ram machine takes about 600% more than into ...
1
vote
1answer
47 views

How to generate create table script without primary and foreign key in Oracle

I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well. SELECT ...
1
vote
0answers
65 views

How to display '0' where no data found in PL/SQL Oracle Reports

I have an Oracle Report (11g) in which I have selected a number field containing amount and have mentioned the format mask as 999,999,999,999. The data is displayed as follows. Data1 ...
1
vote
1answer
48 views

Oracle - Date of Selection on table

how to get last date when someone perform selection on table? Last accessed in terms of select statement only not last updated date
1
vote
1answer
133 views

how to write pl/sql named programme for this [closed]

I have a table M_PARAMETER which has a column MMRUN. When this MMRUN='Y' then the User can not modify any transaction in the app's front end. When MMRUN='N' then the User can do the modification. ...
0
votes
1answer
629 views

How to recreate or restore Corrupted Data Files in Oracle 11g

Few days back ago my power cable went down and I was working in Oracle. So after power cable restoration when I tried to login to SQL PLus: It did not let me to login and I got error "Oracle ...
0
votes
1answer
84 views

How to rollback last executed sql script fired as sysdba

I have fired one sql script as sysdba which I have to fire using other oracle user. I have already created some user and each application running on each oracle user. By mistake I had fired a script ...
0
votes
1answer
251 views

Oracle trigger to update columns daily

I'm trying to create a trigger that would run twice daily and do updates based on a specific condition. To be run on Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi We have Table A ...
-3
votes
1answer
244 views

Windows Server 2008 R2, Active Directory, and Oracle Database Server

I have a group of clients, Windows server 2008 R2(Active Directory Installed in it), and an oracle database Server I Want to know how to make the configuration of the following Diagram in this link: ...
-5
votes
1answer
5k 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 ...