ACCEPT and PROMPT commands

Usage of ACCEPT and PROMPT commands in SQL script.

SQL> PROMPT 'Please enter your name:'

SQL> ACCEPT name CHAR FORMAT a20 

Here is a script which prompts for username and accepts it.

It also creates user with that username and a table

 UNDEF username

ACCEPT username PROMPT 'Enter username:'

--Create user
CREATE USER &username IDENTIFIED BY passwd;
GRANT CREATE SESSION TO &username;
GRANT CREATE TABLE TO &username;
ALTER USER &username QUOTA 5M ON users;

-- Create table
CREATE TABLE &username..test AS SELECT * FROM dual;

SQL> @create.sql
Enter username:harish
old   1: CREATE USER &username IDENTIFIED BY passwd
new   1: CREATE USER harish IDENTIFIED BY passwd

User created.

old   1: GRANT CREATE SESSION TO &username
new   1: GRANT CREATE SESSION TO harish

Grant succeeded.

old   1: GRANT CREATE TABLE TO &username
new   1: GRANT CREATE TABLE TO harish

Grant succeeded.

old   1: ALTER USER &username QUOTA 5M ON users
new   1: ALTER USER harish QUOTA 5M ON users

User altered.

old   1: CREATE TABLE &username..test AS SELECT * FROM dual
new   1: CREATE TABLE harish.test AS SELECT * FROM dual

Table created.

Please not that I have used two dots in create table script after schema name.

Password reset

DBMS_RANDOM package can be used to generate an alphanumerical string that can be used to reset a password.

Example 1:

SELECT INITCAP(DBMS_RANDOM.STRING('X',8)) FROM DUAL;

NEW_PWD
-----------------------------------------------------------------
Xzcf996c

Example 2:

 
SELECT DBMS_RANDOM.STRING('A',5)||ROUND(DBMS_RANDOM.VALUE(100,999),0) FROM DUAL;

NEW_PWD
----------------------------------------------------------------------------------------------------------------------------
cPwER827

Useful SQL statements

1. To create a script to copy all the datafiles to new location:

select 'cp 'name' /newpath/'substr(name,instr(name,'/',-1,1)+1))' &'

from v$database;

2. To create a script to copy all the datafiles, controlfiles and redolog files to new location:

select 'cp 'name'/new_path/'
substr(name,instr(name,'/',-1,1)+1))' &'
from (
select name from v$datafile
union all
select name from v$controlfile
union all
select member from v$logfile
)

3. To get the sql statements to Rename all datafiles and redolog files:

select 'alter database rename file '''name ''' to
''/new_path/'substr(name,instr(name,'/',-1,1)+1)''';'
from (
select name from v$datafile
union all
select member from v$logfile
)

4. Copy datafile

select 'cp ' file_name

decode(substr(file_name,1,instr(file_name,'/',-1,1)),

'/Old_path1/','/New_path1/',

'/Old_path2/','/New_path2/'

)substr(file_name,instr(file_name,'/',-1,1)+1)

' &' file_name

from dba_data_files;

5. Copy redolog file

select 'cp ' member ' '

decode(substr(member,1,instr(member,'/',-1,1)),
'/Old_path1/','/New_path1/',
'/Old_path2/','/New_path2/'
)substr(member,instr(member,'/',-1,1)+1)' &' member

from v$logfile;

6. Rename data file

select ' Alter database rename file '''file_name ''' to '''

decode(substr(file_name,1,instr(file_name,'/',-1,1)),
'/Old_path1/','/New_path1/',
'/Old_path2/','/New_path2/'
)substr(file_name,instr(file_name,'/',-1,1)+1) ' ;' file_name
from dba_data_files;

7. Rename redolog file

select 'alter database rename file ''' member ''' to '''
decode(substr(member,1,instr(member,'/',-1,1)),

'/Old_path1/','/New_path1/'

,'/Old_path2/','/New_path2/')

substr(member,instr(member,'/',-1,1)+1) ''' ;' member
from v$logfile;

8. Datafile Offline drop

select 'alter database datafile 'file_id' offline drop;'

from dba_data_files;

Bloking Locks

SQL query to identify blocking locks:

1.
select s.username "Blocking Username",l1.sid "Blocking SID", l2.sid "Blocked SID",s2.username "Blocked Username"
from v$lock l1, v$lock l2,v$session s,v$session s2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2
and l1.sid=s.sid
and l2.sid=s2.sid;

2.
select l.sid SID,
decode(l.type,'TM','DML','TX',
'Trans','UL','User',l.type) Lock_Type,
decode(l.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive', l.lmode) Lock_Held_In,
decode(l.request,0,'None',1,'Null',2,'Row-S',3,'Row-X',
4,'Share',5,'S/Row-X',6,'Exclusive',l.request) Lock_Req_In, l.ctime Duration_Seconds,
decode(l.block,0,'NO',1,'YES') Blocking
from v$lock l
where l.request != 0 or l.block != 0
order by l.id1, l.lmode desc, l.ctime desc;


Kill The Blocking Session

Find the 'serial' number of bloking session:
select
s.sid sid, s.serial# serial, s.osuser osuser, s.username username, s.module module,p.spid spid, s.process process, s.machine machine, last_call_et active_length, to_char(s.logon_time, 'mm/dd/yy hh24:mi:ss') logontime, s.status status
from v$process p, v$session s
where s.paddr = p.addr (+) and s.sid = '&sid';

Use alter system command to kill the session:
Alter system kill session '115,10366' immediate;