SQL

Meta Data Queries

This section of the book will contain all metadata related SQLs.

Create a Database Link

In many cases you might need to compare data between tables across different databases. For example your test instance and production instance.

You can easily create a database link between the 2 instances and be able to access the production data from the test database.

CREATE DATABASE LINK <Link Name>
 CONNECT TO <Schema Name>
 IDENTIFIED BY <PWD>
 USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <Host Name>)(PORT = <port#>))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <Database Name>)
    )
  )'
;

Get Table Sizes in your Schema

If you want to see how much space your tables are taking in the database, log in to the shcema and execute this SQL.

SELECT   segment_name table_name, MAX (tablespace_name) tablespace_name,
         SUM (BYTES) / (1024 * 1024) table_size_mb
    FROM user_extents
   WHERE segment_name IN (SELECT tname
                            FROM tab
                           WHERE tabtype = 'TABLE')
GROUP BY segment_name
ORDER BY tablespace_name

The Above query tells you the table name, the table space it is residing in and the size in MB.

Thanks,

Zooz

Enable Row Movement Between Table Partitions

If you have a table partitoned on a certain column and on one of your loads you try to update that column on already existing records. You might run into this oracle error

Oracle error 14402 : ORA-14402: updating partition KEY COLUMN would cause a partition CHANGE

The above error was caused because when you updated the record, it now belongs to a different partition but since row movement between partitions is not enabled on the table, you got that error.

Find the First Day or Last Day of a Specific Month

First Day of a Month
An SQL that takes a date value such as 01/15/2008 and returns the first day of that month. In our example, the first day for Jan of 2008 is Tuesday.

SELECT TO_CHAR (TRUNC (TO_DATE (:1, 'mm-dd-yyyy'), 'MM'), 'DAY')
  FROM DUAL;

The TRUNC function in the above example will truncate the passed date value to be always the begging of the moth and the TO_CHAR function with 'DAY' will return the day (Tuesday).

Last Day of a Month

Traversing the Dept Security tree

We can use the connect by prior function provided by Oracle to traverse the Dept security Tree . Suppose an Organization has 7 levels in Dept tree i.e. 1 to 7 .
We want to traverse the dept tree from level 3 to level 6.
Also we know the deptid at the level 3 say 'LVL3-DPT' .

We can use the following SQL to traverse the whole tree (3-6) starting from 'LVL3-DPT'. Only child of 'LVL3-DPT' will be seen in sql output. The starting deptid has to be used with "START WITH " clause.

SELECT TR.TREE_NODE , TR.TREE_LEVEL_NUM        

FROM PSTREENODE TR

Compare Data from the Same Table in two Different Environments

One of the requirements I have lately is to compare table data between two different environments (Development and Test).
The Oracle SQL below compares table1 that has 2 key fields and 3 regular fields.

Note: For the SQL below to work, your password needs to be the same in both environments. If not, then
a connection using the below will not be possible.

FROM table1 tst, table1@dev_database dev

-- Compare data from the same table in two different environments
SELECT   tst.fieldkey1, tst.fieldkey2,
         (CASE tst.field1

Compare data in different tables with same structure

Here is the SQL to compare the differences in the data in different tables with same structure. Will be helpful for comparing the data between databases. This works in MS SQL Server if both databases are on same server or if there is a linked server configured for the other database.

SELECT  * FROM
(SELECT Max(TableName) AS TableName, FIELD1, FIELD2, FIELD3
FROM (SELECT 'HRDB1..PS_MY_RECORD' AS TableName, FIELD1, FIELD2, FIELD3
FROM HRDB1..PS_MY_RECORD
UNION ALL SELECT 'HRDB2..PS_MY_RECORD' AS TableName, FIELD1, FIELD2, FIELD3
FROM HRDB2..PS_MY_RECORD ) A  

Rowcounts in PS tables

Here is the SQL that you can use to fetch the number of rows in the PS tables in MS SQL Server database.

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
-- Declare cursor
DECLARE TABLES CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME LIKE 'PS_%'
-- Create the table
CREATE TABLE #tables (
tablet CHAR (255),
RecordCount INT)
-- Open the cursor
OPEN TABLES
-- Loop through all the tables in the database
FETCH NEXT
FROM TABLES
INTO @tablename

WHILE @@FETCH_STATUS = 0

Oracle SQL Developer 1.5

Oracle SQL Developer 1.5 Early Adopter is out. I have been using 1.2 for a while and started testing 1.5 couple days ago. As this is still not the official release, I would have to say things are going pretty good and no bugs so far.