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

3
votes
2answers
621 views

What exactly is “pinning” in relation to indexes?

When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions. It is part of a test question, where ...
6
votes
1answer
3k views

Comments in EXPDB parameter file

Is there a way to put comments in a data pump export parameter file?
1
vote
1answer
748 views

Enterprise Manager Database Control on host with an underscore in it's name

I know it sounds awkward, but Enterprise Manager is refusing to be set up because of an underscore in the server name: C:\>emca -deconfig dbcontrol db -repos drop STARTED EMCA um 30.05.2012 ...
2
votes
3answers
35k views

Move data from one oracle db to another db using toad

I am new to oracle and this might be a simple question for many. I need to take data from one db and insert it into other db. The databases are on the same version and edition. I am currently ...
2
votes
1answer
1k views

Creating Bitmap Indexes on a Materialized View

I've been reading this article, discussing the benefits, tradeoffs, and potential problems with Bitmapped Indexes. It is mentioned: Also, remember that bitmap indexes are only suitable for static ...
1
vote
1answer
80 views

Oracle DB administrator-specific security features

Yesterday I was presenting an internet banking solution to a potential client management. My offer is based on using MS SQL Server 2008 RDBMS. One of the client's officers mentioned, that Oracle's ...
0
votes
1answer
477 views

How to troubleshoot/debug Oracle permission problems

An application is connecting to Oracle to do stuff. It's not working but I don't get any ORA errors in the application itself. I'm thinking its a permission problem. So my question is what is the best ...
9
votes
1answer
448 views

SQL substitution feature in Oracle 10g

Three or four years ago I read in an Oracle blog somewhere that a DBA had used for an emergency incident resolution an Oracle 10g feature of real time SQL substitution. Basically, he configured Oracle ...
2
votes
1answer
695 views

When using expdp to create a snapshot of an oracle schema, how do I ensure that no post-export additions are retained upon using impdp to revert?

I want to create a snapshot of an Oracle schema that I can revert back to after testing. Impdp and expdp seem to be the best fit as far as how I'd like to accomplish it, but the problem is that impdp ...
2
votes
1answer
2k views

Oracle, error while exporting schema

I'm using a command: $ exp userid=apex_public_user/apex_public_user OWNER=apex_public_user DIRECT=Y CONSISTENT=Y FILE=/home/oracle/apex_public_user.dmp and getting following output: Export: ...
0
votes
1answer
606 views

Tools for synchronizing database schema with Oracle, SQL-Server and Netezza databases [closed]

I'm looking for software tools to move database schema for Oracle, SQL Server and Netezza. We use Oracle for our ODS, Netezza for our Data Warehouse (for it's bulk loading capabilities) and SQL ...
0
votes
2answers
14k 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 ...
2
votes
1answer
206 views

May I Convert Non RAC db to an RAC DB and add on a Node?

Currently my work env as 1 node none RAC database 11gR2, we are getting another machine (Linux), so I need to add on this node to our currently env and becoming a RAC db. What is the best way/route to ...
1
vote
1answer
653 views

DML operation against synonym oracle

I have been reading the GoldenGate 11g R2 installation guide and it states: Non-supported objects and operations in Oracle DML: [...] Synonyms [...] But I see in the next section that ...
1
vote
1answer
86 views

Filter On Indexed, Formatted DATE

How do I write my calling query (without modifying V_SPECIAL_SAMPLES) to use the index on sample.sample_date (filtering by date first)? I believe it's the TO_CHAR and TO_DATE conversions that prevent ...
4
votes
4answers
987 views

How are foreign key indexes helpful?

How are foreign key indexes helpful? What is the criteria for keeping a foreign key index?
1
vote
0answers
89 views

Dealing with Invisible Referencers

Using Oracle database, suppose I have a parent table parent defined in schema A, and a child table child defined in schema B. To achieve this, schema A granted references privilege on parent to schema ...
3
votes
2answers
1k views

My database dramatically increased in size. Why?

I found that my database, specifically the USERS tablespace, dramatically increased in size. How can I find what DML operations are performing on the objects located in this tablespace?
3
votes
1answer
517 views

Oracle to PostgreSQL

I'm having som test databases running Oracle 10 & 11 that I plan to try to migrate to PostgreSQL. Some googling hinted that the perl script "ora2pg" could be of use, and free of charge. However, ...
1
vote
2answers
3k views

How to schedule function in Oracle?

I want to schedule function in Oracle. begin dbms_scheduler.create_job(job_name => 'aud_clear', job_type => 'PLSQL_BLOCK', ...
3
votes
2answers
35k views

What do the columns in SYS.USER$ represent?

In the SYS.USER$ view, what do the CTIME, PTIME, and LTIME columns represent? Where can I find Oracle's documentation for this and other system views?
1
vote
0answers
709 views

FATAL ERROR IN TWO-TASK SERVER: error = 12571

oracle9i/windows2003,java application. oracle crash, alert log file contains sdh_ora_4636.trc which contains: *** 2012-05-05 13:53:32.000 *** SESSION ID:(100.50539) 2012-05-05 13:53:32.000 FATAL ...
12
votes
1answer
55k views

How do I declare and use variables in Oracle?

My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL: declare @startDate int select @startDate = 20110501 And I get this ...
16
votes
3answers
19k views

What is the difference between Oracle's VARCHAR and VARCHAR2 datatypes?

When migrating tables coming from other DBMSs to Oracle, one of the standard tasks is to replace all VARCHAR(n) fields with VARCHAR2(n) fields (provided n <= 4000). Why does Oracle call this ...
1
vote
1answer
881 views

ORA-07445: An unexpected error: core dump

We use oracle 10g. We found the following error in a trace file: *** 2012-05-07 11:50:56.421 ksedmp: internal or fatal error ORA-07445: An unexpected error: core dump [ACCESS_VIOLATION] ...
4
votes
2answers
735 views

Migrating to Oracle Flashback for Historization on Oracle 11g

Our application built using Oracle 11g requires that we keep historic data. The historization is legally mandated, and is mainly used for manual queries, when investigating problems, etc. Basically ...
3
votes
1answer
310 views

Can this be accomplished with Oracles Fine Grained Auditing?

We need to audit when a table is accessed (either select, update or delete) and I know Oracles Fine Grained Auditing can handle that. The issue we are having is that all of our users use the same ...
8
votes
3answers
6k views

I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created?

I have powerful machine with 70 GB RAM. I created one Oracle instance with 20 GB as sga_target. I am not able to create another Oracle instance with sga_target > 10G even when I keep the ...
0
votes
1answer
559 views

Is any possible way to get warning message through by oracle [closed]

I am working in developing oracle data base tool, I want display warning message when executing function. For example: CREATE OR REPLACE FUNCTION FN_OEDS_CONVERTDATE2( V_DATE DATE) RETURN VARCHAR2 ...
1
vote
2answers
1k views

Changing the default schema of a user

In order for an application user (APP_USER) to use another schema (SCHEMA_OWNER) without the need for prefixing queries with the schema name, I have implemented a logon trigger that changes the ...
0
votes
1answer
2k views

Upgrading 10g database to 11g database

Is doing a full data export (expdp) from an Oracle 10g (10.2.0.1.0) database and then doing an import of that dump file (impdp) into an 11.2.0.2 database a valid and acceptable upgrade path from 10g ...
5
votes
0answers
5k views

SQL*Plus, @, and relative paths

Somehow, it seems that SQL*Plus (at least on Windows) is unable to locate a script with a relative path when called with @@ and when the path starts with a single or double dot. For example, under ...
2
votes
1answer
248 views

Where to put glogon.sql in 32-bit ODAC (11.2.0.2.1)?

I installed the 32-bit ODAC (11.2.0.2.1) from here. The Oracle Universal Installer version contains Oracle SQL*Plus 11.2.0.2.0. But I'm missing the sqlplus/admin/ folder in the installation, where I ...
3
votes
2answers
4k views

How do I limit Oracle to use specific ports?

I've built a new Oracle 9iR2 instance on a Windows 2003 32-bit cluster. Of course, it's version 9i for reasons out of my control. Several application servers connect to this instance, and each opens ...
1
vote
3answers
210 views

A beautiful trigger issue. Flawed logic? Updating Orderheader when orderlines are complete

THe bussiness rule is as such: The orderlines must be updated with a date stamp when all the related orderlines are marked as 'Y' denoting a completed order line. THe trigger thus far is: CREATE OR ...
2
votes
1answer
4k views

Best practices for ensuring up to date sequences after table import

We perform weekly table moves between several schema using expdp and impdp. Some tables have sequences associated with the ID columns and I'd like to ensure that the sequences are always up to date on ...
2
votes
1answer
133 views

Improving Oracle SQL Query

I'm trying to improve the below code. The unique identifier in this code is result_label_seq, but I still need to make sure that it's uniquely identified with facility_id and service_code. The ...
2
votes
1answer
787 views

Alias Names and Two Table Query Problems

I'm quite new to querying databases, I've only been doing it for about 2 weeks in the current job that I'm in and have never touched SQL prior to this. I'm working on trying to use alias names to ...
4
votes
1answer
144 views

Rank Partition?

I have a table which holds customer information with an Account state (working or Deactivated) for every single day. I'm trying to reduce this information by showing start and end dates for the ...
0
votes
1answer
172 views

Creating Indexes to optimize query

I have this query and I want to increase its performance by adding appropriate Indexes. DELETE FROM MYTAB1 WHERE MYID1 IN ( SELECT MYID2 FROM MYTAB2 ); I am not familiar with the ...
4
votes
3answers
545 views

Why keeps Oracle writing into WARNING_SETTINGS$?

For tests we have Oracle 11 instance running on a RAM disc. Since the RAM disc is limited in size the database must not do any auditing or anything that keeps increasing the size of anything. We ...
-2
votes
1answer
332 views

Important distinctions between DBMSs [closed]

DBA is a career I'm strongly considering after school, but my limited experience mostly involves some PL/SQL Oracle web programming, some basic PHP/MySQL web pages, and some queries in SQL Server. ...
1
vote
1answer
20k views

Why does DBMS_JOB.SUBMIT execute immediately when NEXT_DATE is in the future?

I wanted to create a job that will run tomorrow at noon and recur every day at noon, so I executed this script: DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what ...
3
votes
1answer
74 views

Why would you need to put a domain name when install a Oracle database?

When you install an Oracle database or instance, you are able to assign a domain name. An Example: SID: orcl Domain: world.com Service: orcl.world.com Why would it be necessary to add or ...
3
votes
1answer
665 views

Is it possible to configure Oracle's CONNECT THROUGH based on roles?

I don't have too much administrative experience with Oracle DBs, so forgive me if I make a fool out of myself. In our current setup, each of our clients has a database user on our Oracle database. ...
1
vote
2answers
3k views

LEFT JOIN conversion to INNER JOIN, can't change FROM clause

I have 2 tables: BASE is master and is linked with CHILD via field ID: Left outer Join / 1:N relation. These 2 tables contain historised data, defined by 2 date-fields: start_date and end_date. ...
1
vote
1answer
2k views

bad bind variable in trigger

i'm trying to update the table whenever complaint has been registered create or replace trigger complaint_advertise BEFORE INSERT ON COMPLAINT_INFO FOR EACH ROW DECLARE BEGIN UPDATE ...
1
vote
1answer
7k views

restore database from dmp file

I have a situation: I have a dmp file and a database with old data server crashed and the latest data we have ar in the dmp file! we managed to pull the server back but it has old data! so we need to ...
1
vote
2answers
711 views

Oracle Databases - need to export nightly for backup/DR purposes

I know nothing about Oracle databases, so if my question's path seems odd, feel free to tell me the right way to do things. I have 2 ways I see currently that I can backup a server running 3 Oracle ...
3
votes
1answer
861 views

In a two-server dataguard setup, can I store the recovery catalog in the same database?

We currently have an Oracle 11gR2 database that is backed up with rman, with the rman records stored in the control files. Control file auto-backup is turned on. Backups are to disk (and, ultimately, ...