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
1answer
711 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 ...
14
votes
2answers
1k 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 ...
11
votes
5answers
8k 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?
17
votes
3answers
12k 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 ...
15
votes
2answers
833 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 ...
10
votes
2answers
209 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 ...
8
votes
2answers
493 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
2k 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 ...
7
votes
3answers
998 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 ...
4
votes
4answers
4k 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 ...
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 ...
5
votes
1answer
161 views
Map Oracle roles to Active Directory groups?
Is there any built-in way to map Oracle database roles to Microsoft Active Directory groups?
2
votes
2answers
188 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) ...
2
votes
2answers
620 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 ...
8
votes
1answer
242 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 ...
5
votes
2answers
634 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) * ...
5
votes
4answers
374 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 ...
5
votes
2answers
687 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?
4
votes
2answers
296 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
2answers
472 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 ...
2
votes
2answers
1k 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 ...
2
votes
1answer
545 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 ...
2
votes
5answers
260 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
3answers
4k 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 ...
1
vote
3answers
203 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 ...
0
votes
2answers
105 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: ...
0
votes
1answer
89 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 ...
0
votes
1answer
127 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.
...
-1
votes
1answer
76 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:
...
-4
votes
1answer
4k 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
...
7
votes
2answers
19k views
oracle - list users with access to certain tables
I'm sure this has been asked before but I can't seem to find the relevant details for the following.
Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
10
votes
4answers
9k views
Best way to delete very large recordset in Oracle
I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end. The database doesn't really do anything (no SProcs, no ...
6
votes
2answers
603 views
Database for building a realtime analytics system
I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on
Real time counting of unique URI visit/PV by different ...
14
votes
7answers
4k views
Oracle - Any way to view uncommited changes to a particular table?
I'm debugging through a batch proess 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 while the transaction ...
8
votes
2answers
2k views
Script out Oracle DDL in an automated fashion
Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention.
I know of DBMS_METADATA.get_ddl(), but have found that the ...
2
votes
1answer
760 views
Oracle 10gR2 to 11gR2 upgrade - performance hit [closed]
We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to 11gR2 (11.2.0.3) and we are noticing a significant hit in performance although execution plans are the same for the offended queries before and ...
7
votes
2answers
2k views
Why can't I read Oracles CLOB columns via SQL-SERVER 2008 linked server?
I want to access data in an Oracle 11g database from SQL-Server 2008
I set up a linked server and when I execute
select * from [Link_server_name]..Oracle_schema.Oracle_table
and Oracle_table ...
6
votes
3answers
277 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 ...
6
votes
2answers
887 views
Oracle 10g - How to prevent one user from hogging all resources?
I am wondering if there is any way to limit how much resources one user can take on my Oracle system. I want to limit some queries and systems from being able to hog all system resources when they run ...
6
votes
3answers
2k views
How to differentiate between SQL and PL/SQL?
I know the question might sound too stupid, but I never understood this part.
SQL*Plus works with both SQL and
PL/SQL. How do I know whether some
code is SQL or PL/SQL? If my code has
a for ...
4
votes
2answers
2k views
Database logon trigger
To block a particular user and IP combination on an Oracle Database , I created the following trigger, and compiled without errors.
Create or replace trigger you_may_not_login
after logon on database
...
4
votes
3answers
507 views
Is Oracle DB immune to the InnoDB deadlocks found in MySQL?
It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction"
Does Oracle DB work around this issue? After all, Oracle owns ...
4
votes
2answers
1k views
How to handle multiple Oracle homes?
On my desktop I have an Oracle client and and Oracle ODAC installed in different homes. I need ODAC because there ADO components off the Client installation don't work correct with CLOB data.
I think ...
3
votes
1answer
156 views
Oracle: Full database (user) restore requires drop?
In our development environment (Oracle 11g), we often have to restore databases (users) to previous versions to test upgrades, verify defects, etc.
We are using expdp/impdp to perform our ...
3
votes
1answer
5k views
How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?
I have an Oracle database, and I would like to create an ERD in Visio 2007 Pro.
I have so far looked at DBMS_METADATA.GET_DDL commands to generate ddl. I know that I can import an Access DB into ...
3
votes
2answers
2k views
How to catch and handle only specific Oracle exceptions?
From this and this i guess, that there is no predefined Named System Exceptions for ORA-00955.
How can I rewrite the following to catch only the error ORA-00955?
begin
EXECUTE IMMEDIATE ...
1
vote
2answers
447 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
2answers
1k views
Troubleshooting Oracle ORA-08103 - object no longer exists
I am getting this error from Oracle intermittently. A trigger is truncating a partition from a partitioned table while a long-running query is running against it.
Since I'm truncating a different ...
1
vote
2answers
4k 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 ...
1
vote
1answer
165 views
SQL from/where or join?
Hi 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?
