Skip to main content
Share Your Experience: Take the 2024 Developer Survey
14 votes
Accepted

Composite indexes: Most selective column first?

From AskTom (in 9i, there is a new "index skip scan" -- search for that there to read about that. It makes the index (a,b) OR (b,a) useful in both of the above cases sometimes!) So, the order ...
atokpas's user avatar
  • 8,634
8 votes
Accepted

Is using association tables for One-to-Many relationships common practice?

For the simple case you are right and there is no need to have the separate link table for a one<->many relationship. Off the top of my head I can only think of a couple of examples where it might ...
David Spillett's user avatar
7 votes

impdp stuck on index import

It's not stuck. It just does not print every rebuilded index. You can view status of the process using DBA_RESUMABLE. select * from dba_resumable;
Dmitry Lukyanov's user avatar
6 votes

How are these null values stored in a NOT NULL column?

What you described is not normal. As already mentioned in comments, in Oracle databases, empty/zero-length strings are treated as NULL. https://docs.oracle.com/cd/B19306_01/server.102/b14200/...
Balazs Papp's user avatar
  • 40.7k
5 votes

How do I declare and use variables in Oracle?

SQL*Plus supports an additional format: DEFINE StartDate = TO_DATE('2016-06-21'); DEFINE EndDate = TO_DATE('2016-06-30'); SELECT * FROM MyTable WHERE DateField BETWEEN &StartDate ...
Jon of All Trades's user avatar
5 votes

Composite indexes: Most selective column first?

When choosing index column order, the overriding concern is: Are there (equality) predicates against this column in my queries? If a column never appears in a where clause, it's not worth indexing(1) ...
Chris Saxon's user avatar
  • 2,541
5 votes
Accepted

System account locked, insufficient privileges to log in as sysdba

The windows user account belongs to Administrator and Ora_DBA Windows groups. Double check this, because you get the above error, when you are not a member of the ora_dba group. Check %ORACLE_HOME%...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

Need help with this policy function: Shows compilation errors

You have just ended the single quote near 'cus_id in(select cus_id from employee where comp_id = NVL(SYS_CONTEXT(' so that the compiler is expecting the ; to end the statement but it has found ...
atokpas's user avatar
  • 8,634
4 votes
Accepted

Oracle "matches" multiple columns

One method is to use the MULTI_COLUMN_DATASTORE. Below is an example: drop table t1 purge; create table t1 (column1 varchar2(100), column2 varchar2(100)); insert into t1 values ('HELLO', 'WORLD'); ...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

How to count the number of rows by datetime field with the division into time intervals

variable interval_minutes number; exec :interval_minutes := 10; select trunc(registered_at, 'HH24') + trunc(to_char(registered_at,'mi')/:interval_minutes)*:interval_minutes/1440, count(*) ...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

How to solve ORA-39710 problem

39700, 00000, "database must be opened with UPGRADE option" *Cause: A normal database open was attempted, but the database has not been upgraded to the current server version. *...
atokpas's user avatar
  • 8,634
4 votes
Accepted

impdp stuck on index import

During the impdp process, it doesn't import indexes, in fact it runs number of CREATE INDEX statements. There could be various reasons of slow index creations such as inefficient PGA memory ...
atokpas's user avatar
  • 8,634
4 votes

Oracle Can a Dropped View Be Recovered?

Sure. For example: use flashback query on DBA_VIEWS. SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'; Session altered. SQL> create or replace view v1 as select * from dual; ...
Balazs Papp's user avatar
  • 40.7k
3 votes
Accepted

Table creation along with dynamic partitioning (interval partitioning) in Oracle 10g EE

I'm afraid that interval partitioning was introduced on Oracle 11g.
vercelli's user avatar
  • 756
3 votes
Accepted

ORA-01017: invalid username/password; logon denied from sql developer but sqlplus logins

The problem was that I had too many instant clients and Oracle clients on my machine (one oracle client 'x86'). The instant clients were too old. The only working instant client for me is the '12.1....
Cavva79's user avatar
  • 151
3 votes
Accepted

Auto update a column every 24 hours in Oracle database

Sure, just create a job. For example, update column every midnight: begin dbms_scheduler.create_job ( job_name => 'UPDATE_TABLE', job_type => 'PLSQL_BLOCK', ...
Balazs Papp's user avatar
  • 40.7k
3 votes

ANALYZE TABLE..VALIDATE STRUCTURE runs forever

UPDATE: What Worked... So after reading the link from @Raj and reading @ora-600's answer I tried to validate the database with the RMAN command backup check logical validate database;. While this ...
RBarryYoung's user avatar
  • 3,041
3 votes

Troubleshooting / traceroute within Oracle Instant Client?

There is no trcroute for the instant client, but even if you had the full administrator client, trcroute may not return anything useful. By the way, from the command line I can ping the destination ...
Balazs Papp's user avatar
  • 40.7k
3 votes
Accepted

Can we export Selected Row with Subquery using data pump

Can we use subquery in datapump? Yes we can. The following example shows how to do it. I have created a parameter file to get this job done. In my test case I have two tables namely tbl1 and tbl2 ...
atokpas's user avatar
  • 8,634
3 votes

Migrating from Oracle to MySQL via MySQL workbench: Table list is empty

Like @bauerInHsv said, Oracle has a vested interest in providing minimal support for migrating off of its enterprise offering (Oracle) and onto its open-source RDBMS (MySQL). As an alternative to ...
The Aelfinn's user avatar
3 votes

Oracle Historical Session Information

Is not nice, is not elegant, but still it works. Hope this helps to any lost DB Adventurer in a desperate crusade for a sessions historical report. COL METRIC_NAME FOR A40 COL BEGIN_TIME FOR A20 COL ...
Ricardo Luis Hernandez Chavez's user avatar
3 votes

Oracle - abort within a "before insert" trigger without throwing an exception

Answering a 10 year old question may seem useless. But as the web doesn't forget, I - facing the same challenge as Paolo - found this thread. Others in the same situation may appreciate an update, ...
Marcel's user avatar
  • 31
3 votes
Accepted

Combined column data display only unique value

There is no easy and supported built-in method for string aggregation in 10g. (Do not use WM_CONCAT, as it is not supported, and it is not available anymore in 12c.) You need to write your own string ...
Balazs Papp's user avatar
  • 40.7k
3 votes

Composite indexes: Most selective column first?

Most selective first is useful only when this column is in the actual WHERE clause. When the SELECT is by a larger group (less selective), and then possibly by other, non-indexed values, an index ...
Erik Hart's user avatar
  • 510
3 votes

restore database using impdp full back

Data Pump uses directory objects. Read this: Default Locations for Dump, Log, and SQL Files First create the directory object as: create directory dump_dir as '/home/oracle/rmanscripts/dumbbackup'; ...
Balazs Papp's user avatar
  • 40.7k
3 votes
Accepted

ps-ef|grep pmon (Instance Name Only)

$ ps -ef | grep pmon | grep -v grep oracle 23955 1 0 Jan16 ? 00:00:33 asm_pmon_+ASM oracle 27366 1 0 Jan16 ? 00:00:31 ora_pmon_S112 $ ps -ef | grep pmon | grep -v grep | ...
Balazs Papp's user avatar
  • 40.7k
3 votes

Adding sequence number to existing rows based on ID and Date in PLSQL Oracle

You can use MERGE: merge into tableX t using ( select t.*, row_number() over (partition by refno order by startd) as rn from tableX t ) r on ( t.refno = r.refno and t....
ypercubeᵀᴹ's user avatar
2 votes
Accepted

How can I find who has allocated a user lock via dbms_lock?

These locks can be selected from V$LOCK (or GV$LOCK for RAC) SELECT * FROM gv$lock WHERE lock_type = 'UL' AND '1073741825' like id1||'%' 1073741825 - is lockid column from dbms_lock_allocated table ...
Anton Zaviriukhin's user avatar
2 votes

Oracle Historical Session Information

DBA_ACTIVE_SESS_HISTORY is only from active sessions, but you can query dba_hist_sysmetric_summary as I explain in this article: Oracle Historical Session Information
fdominguez.bbdd's user avatar
2 votes

Install Oracle 10.2.0.4 on Windows Server 2012 R2

With no success with installing Oracle 10.2.0.4, we've tried 10.2.0.5. The installation worked without any problem.

Only top scored, non community-wiki answers of a minimum length are eligible