Find a Tablespace where particular ROW created

Today i come across one thread at ORAFAQ site for the following.

Question: How to find a tablespace where particular ROW is created?
Answer: dbms_rowid.rowid_to_absolute_fno package

Click here to find more about dbms_rowid package
Click here for the OraFAQ thread for example

Find a Column Value is Numberic or Alpha Numeric

SQL> create table numalpha ( value varchar2(20));
Table created.

SQL> begin
2 insert into numalpha values (1);
3 insert into numalpha values (2);
4 insert into numalpha values (3);
5 insert into numalpha values ('1a');
6 insert into numalpha values ('2b');
7 insert into numalpha values ('3c');
8 insert into numalpha values ('1a2b');
9 insert into numalpha values ('3c4d');
10 end;
11 /
PL/SQL procedure successfully completed.

Now there are couple to findout non - numeric value in column here I will show you both queries which work with 10g or 11g or previous release.

Example for work in all oracle release(8i to 11g)

1. how to findout non numberic value in column
--Create One function

CREATE OR REPLACE FUNCTION get_valid_str(string_in IN VARCHAR2, valid_chars_in IN VARCHAR2)
RETURN VARCHAR2
IS
tmp VARCHAR2(2000);
BEGIN tmp := TRANSLATE(string_in, chr(0)valid_chars_in, chr(0));
return TRANSLATE(string_in, chr(0)tmp, chr(0));
END;

SQL> select * from numalpha
where value <> get_valid_str(value,'0123456789');
VALUE
--------------------
1a
2b
3c
1a2b
3c4d

2. How to find numberic value in column
--Create one function

create or replace function get_to_number(p_number varchar2)
return number
as
begin
return to_number (p_number);
exception when others then return null;
end;

SQL> select * from numalpha
where get_to_number(value)=value;
VALUE
--------------------
1
2
3

Example for work only in 10g or 11g
Regular Expression

SQL> select
2 value,
3 case when regexp_like(value,'[[:alpha:]]+') then 'A' end
4 case when regexp_like(value,'[[:digit:]]+') then 'N' end AN
5 from numalpha;

VALUE AN
-------------------- --
1 N
2 N
3 N
1a AN
2b AN
3c AN
1a2b AN
3c4d AN

How to replace NON-numeric to '0'

SQL> update numalpha
2 set value = regexp_replace(value,'[[:alpha:]]+',0);
8 rows updated.

SQL> select * from numalpha;
VALUE
--------------------
1
2
3
10
20
30
1020
3040

8 rows selected.

How to remove NON-NUMERIC character in number column

SQL> update numalpha
2 set value = get_valid_str(value,'0123456789')
3 where value <> get_valid_str(value,'0123456789');
5 rows updated.

SQL> select * from numalpha;
VALUE
--------------------
1
2
3
1
2
3
12
34
8 rows selected.

Split Column

Old method.

SQL> select name from test;
NAME
------------------------------
MOHAMMAD TAJ
BABU B
NIVEDITA SHARMA
RASHIDA BASTAWALA
RADO DENISA

SQL> column firstname format a15
SQL> column lastname format a15

SQL> select
2 substr(name,1,instr(name,' ' )-1) as firstname,
3 substr(name, instr(name,' ',-1)+1) as lastname
4 from test;

FIRSTNAME LASTNAME
--------------- ---------------
MOHAMMAD TAJ
BABU B
NIVEDITA SHARMA
RASHIDA BASTAWALA
RADO DENISA

New Method with 10g or 11g.

Regular Expressions
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_regexp.htm#ADFNS1013

About ORATAB, ORACLE_HOME, ORACLE_BASE, ORACLE_SID

1. ORATAB

Oratab is a colon-delimited text file on Unix and Linux systems that associates ORACLE_SID and ORACLE_HOME values.

The last field contains a "Y" or "N" for database startup when the machine boots up. One can also use hashes (#) to start comment lines.

This file is either found in the /var/opt/oracle or the /etc directories.


http://www.orafaq.com/wiki/Oratab

2. ORACLE_HOME

ORACLE_HOME refers to either:

directory where the Oracle software is installed; or
environment variable pointing to the directory where the Oracle software is installed.

http://www.orafaq.com/wiki/ORACLE_HOME


3. ORACLE_BASE

ORACLE_BASE is an environment variable used as base directory for an OFA installation. On Unix systems, this is usually /app/oracle or /u01/app/oracle.


http://www.orafaq.com/wiki/ORACLE_BASE


4. ORACLE_SID

The Oracle System ID (SID) is used to uniquely identify a particular database on a system. For this reason, one cannot have more than one database with the same SID on a computer system.

When using RAC, all instances belonging to the same database must have unique SID's.

http://www.orafaq.com/wiki/ORACLE_SID

Difference between "PRIMARY KEY" or "UNIQUE KEY"

Primary Key

1. we can create only one primary key per table.
2. primary key create cluster index
3. primary key provide two constriant inforceness "unique + not null"
4. we can reference primary key for foreign key constraint.

Unique Key

1. we can create more than one unique key per table
2. unique key create non-cluster index
3. unique key provide only "uniqueness for table" ( means we can't insert Duplicate value in same column.)
4. we can reference unique key for foreign key constraint.

Not Null
1. we can create more than one not null constraint per table
2. not null provide NON-NULL value in table ( we can insert NULL value in table)
3. we can't reference NOT NULL key for foregin key constraint.

According Foriegn key constraint reference.
We can use both "Primary key OR (NOT NULL + UNIQUE KEY).
But all three constraint have difference purpose and Definition.

Welcome !!!

Dear Friends,

Welcome to my new blog "developertaj" specially for newbie developer which learn to plsql,sql.
I will try to my best to keep this blog up to date and share my exprience with all of you.


I want your support & love.

Thanks
Taj