How to limit a column in oracle to support only character

The below example shows test_name column can't take NUMBER values.


CREATE TABLE test_1
(
  test_id number,
  test_name varchar(50),
  CONSTRAINT
    name_test
    CHECK (
      regexp_like(test_name,'^([aA-zZ])+$')
    )
);

Table created.


SQL> insert into test_1 (test_id,test_name) values (200,'Akash')
  2  /

1 row created.

SQL> insert into test_1 (test_id,test_name) values (200,'Akash1');
insert into test_1 (test_id,test_name) values (200,'Akash1')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.NAME_TEST) violated

--------------------------------------------****************************--------------------------



Secondly, If we want to change all the column values to UPPER-CASE ,  this can be done as follows.

SQL> select * from test_1;

   TEST_ID TEST_NAME
---------- ------------------------------------------------
       200 Akash

SQL> UPDATE test_1
  2  set    test_name= upper(test_name);

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from test_1;

   TEST_ID TEST_NAME
---------- ------------------------------------------------
       200 AKASH

Master-Detail Relationship in Oracle forms Blocks

First create Master data Block , then detail data block.

From HR schema  : Master Data Block is Departments and Detail Data Block is Employees.

Layout Style 'Form' for Departments Block with 1 record displayed.

Layout Style 'Tabular' for Employees  Block with 5 record displayed.


Setting Relations: ( During Layout Wizard) 

( At runtime the window will look like the above )



By Default the in the Relation -Delete Record Behaviour property is Non-Isolated.

And likely below ON-POPULATE-DETAILS and ON-CHECK-DELETE-MASTER triggers gets automatically generated under the master data block.

If we will change the Delete Record Behaviour property , the triggers will automatically change accordingly.

1. Non-Isolated :( Triggers are automatically generated under the master data block)

a) ON-POPULATE-DETAILS
b) ON-CHECK-DELETE-MASTER

2. Cascading :( Triggers are automatically generated under the master data block)

a) ON-POPULATE-DETAILS
b) PRE-DELETE 

3. Isolated :( Triggers are automatically generated under the master data block)

a) ON-POPULATE-DETAILS






What does the above three properties mean basically ?


Non-Isolated : Prevents deletion of master record if detail record is present.

Cascading : Deletes the detail record once master record is deleted.

Isolated : Only deletes the master record.



Hope this helps.


Cheers
Rajani

Oracle session waiting , enque event , locked session , session killing

1. 
-- Title 'ENUQUE wait events sessions more than 30 minutes '

SELECT vsw.event, vsw.seconds_in_wait "Wait(Secs)", vs.sid, vsa.sql_text
FROM v$session vs,
v$session_wait vsw, 
v$sqlarea vsa
WHERE vs.sid = vsw.sid
AND vs.sql_hash_value = vsa.hash_value
AND lower(vsw.event) LIKE '%enqueue%'
AND vsw.seconds_in_wait > 1800
/

2. 
-- Title 'OTHER wait events sessions more than 30 minutes '
SELECT vs.username,vsw.event, vs.logon_time, vsw.seconds_in_wait "Wait(Secs)", vs.sid,o.object_name,vl.block,vl.type
FROM v$session_wait vsw,
v$session vs,
v$locked_object vlo,
dba_objects o,
v$lock vl
WHERE vsw.seconds_in_wait > 1800
AND vl.block <> 0
AND vs.sid = vsw.sid
AND vlo.session_id = vsw.sid
AND o.object_id = vlo.object_id
AND vl.sid = vsw.sid
/

3.-- Which session is blocking what session. 
-- 
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

4. To Kill a session:

SQL> select USERNAME,SID,SERIAL#,STATUS from v$session
 where USERNAME='USERNAME'; 

-- user name will start from OPS$ if configured in Unix system


USERNAME      SID       SERIAL#            STATUS
------------------------------ ---------- -           --------- --------
USERNAME     66            7730              INACTIVE


SQL> alter system kill session '66,7730';


5. Session information and lockwait:


SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s,
       v$process p
WHERE  s.paddr = p.addr

ORDER BY s.username, s.osuser;