Ask Question
Help Others
Home › Blogs

Zooz's blog

Oracle SUBSTR Function

Mon, 07/14/2008 - 5:17pm — Zooz

The SUBSTR function is used to extract characters from a word or sentence.

For Example, I have run into many situations were I need to apply different programming logic based on characters in a string.

Suppose I have an employee table with an employee_id and employee_name (First, Last, middle) and I want to store the middle name in my database as middle initial only. So I only need the first character of the middle name.

I can simply do this by using a SUBSTR on the middle name field.

SELECT SUBSTR(middle_name,0,1) middle_initial FROM <employee>

Oracle Minus Operator

Mon, 07/14/2008 - 5:05pm — Zooz

I find the MINUS operator very handy and use it a lot to compare tables and find out missing things.

For example suppose you want to compare Table1 with Table2.

If the two tables have the same layouts (same column names and data content), you can simply do this

SELECT * FROM <Table 1>
MINUS
SELECT * FROM <Table 2>

This query will compare each record in Table 1 to a record in table 2.
The result returned will be records in table 1 that are not in table 2.
you can reverse the MINUS order to get records in table 2 that are not in table 1

Grant and Revoke privileges

Fri, 07/11/2008 - 6:40pm — Zooz

Typically in a database you have many users.

The owner shcema that owns the objects, usually decides the privileges given to other users on the objects it owns.

Lets call the schema owner "OWNER" and suppose we have another user in the database called "READER".

For the user "READER" to be able to view the data in a table owned by "OWNER", the owner has to grant the reader select priviliges.

GRANT SELECT ON <table name> TO READER;

Create a Database Link

Thu, 07/03/2008 - 12:26pm — Zooz

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

Thu, 07/03/2008 - 12:06pm — Zooz

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

Thu, 07/03/2008 - 11:55am — Zooz

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.