Oracle – How to Find All Unique Columns That Are Allowed Null

oracle

I am trying to write a query that displays all unique columns that are allowed to be null in Oracle.

The information I need is in these 3 tables:

ALL_TAB_COLUMNS
ALL_IND_COLUMNS
ALL_INDEXES

I am having difficulty joining these tables to display the information I'm looking for. Any help would be appreciated.

Looking for output such as:

OWNER, COLUMN_NAME, INDEX, UNIQUENESS, NULLABLE

WHERE NULLABLE='Y' AND UNIQUENESS='UNIQUE'

Best Answer

Try this one:

SELECT ind.OWNER AS INDEX_OWNER, ind.INDEX_NAME,
    tab.OWNER AS TABLE_OWNER, tab.table_name,
    tab.column_Name,
    UNIQUENESS, NULLABLE
FROM ALL_INDEXES ind
    JOIN ALL_IND_COLUMNS col 
        ON ind.OWNER = INDEX_OWNER 
            AND ind.INDEX_NAME = col.INDEX_NAME
    JOIN ALL_TAB_COLUMNS tab 
        ON tab.owner = col.table_owner 
            AND tab.table_name = col.table_name 
            AND tab.column_Name = col.column_Name
WHERE NULLABLE='Y' AND UNIQUENESS='UNIQUE';

Related Solutions

Oracle 11g hunting down the deadlock, maybe foreign key

Deadlocks in Oracle with logically disjoint transactions usually involve unindexed foreign keys:

There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.

Locks in Oracle are managed at the row level. Concurrent disjoint transactions should not interfere with one another. Unindexed foreign keys are an exception, since it can result in a complete TABLE LOCK.

You should get the SQL in the trace file of the deadlock and that should help you narrow down which table / foreign key is responsible for the lock. Once you know which table is affected by the deadlock, make sure that all foreign key references to this table are properly indexed. E.G in your example T2.t1_id should be indexed if it points to T1.t1_id.

Alternatively you could use Tom Kyte's script from the above link to determine if you have any unindexed foreign key.

If you're locked on inserts, it usually means that you're trying to insert rows that have the same values for a set of columns that are UNIQUE, for example:

CREATE TABLE T1 (col1 NUMBER NOT NULL, col2 NUMBER);
ALTER TABLE T1 ADD CONSTRAINT T1_UNIQUE UNIQUE (col1, col2);

session1> INSERT INTO T1 VALUES (1, NULL);
session2> INSERT INTO T1 VALUES (2, NULL);
session2> INSERT INTO T1 VALUES (1, NULL); -- <= this will wait on session1
session1> INSERT INTO T1 VALUES (2, NULL); -- <= Deadlock !

Sql-server – Required to use Oracle Performance Manager to monitor SQL Server

I think you are probably looking for Oracle Enterprise Manager or OEM as it is normally referred as, a good starting point for more information is here.
- a. Oracle database starting with 10G+ do their own data collection (Automatic workload Repository), however, you need to be licensed to access much of that data. You can still use statspack (which is free). OEM actually uses this collected performance metrics and uses them for display via its pages.
- b. Live views are available to use (the V$ and GV$) in Oracle, no experience with SQLServer, but I suspect you have similar views available.
- c. OEM can send you alerts on events and conditions that exceed user defined thresholds. This is configurable via templates or custom metrics (aka User Defined Metrics).
- d. OEM has some performance reporting built-in, but you can use OEM repository to roll your own reports, it is not very difficult (have done it in the past). OEM repository is simply a different database, the SYSMAN schema contains tables and views which expose all collected data. You can adjust retention, only constrained by available storage. It can provide your historic information as well as realtime performance for Oracle. You will have to check the plug-in for similar capability for SQLServer.
OEM can (with using optional plugins) monitor SQL Server, check this link for a brief overview. In my shop there is no SS, so I don't have first hand experience in using these plugins.
Licensing information for OEM is here, I'd definitely recommend reading this manual to you and your customers.
There could be, but once you read through the information linked above, you will get a better idea. Then a quick chat with your Oracle Sales team will yield more information as well.
Some of the extension packs for OEM are listed here.

Hope this gives you enough information to get started on it.

Best Answer

Related Solutions

Oracle 11g hunting down the deadlock, maybe foreign key

Sql-server – Required to use Oracle Performance Manager to monitor SQL Server

Related Question