Add ASM Disk and check if Standby is recovering

##command to check provisioned disk ##
SQL> select 'ALTER DISKGROUP ADD DISK ''ORCL:'||label
||''''||' SIZE '||total_mb||' M;' gen_statement
from v$asm_disk
where header_status in ('PROVISIONED', 'CANDIDATE', 'FORMER')
order by 1;
2 3 4 5
GEN_STATEMENT
----------------------------------------------------------------------------------------------------------------------------
ALTER DISKGROUP ADD DISK 'ORCL:ASM34' SIZE 92952 M;
ALTER DISKGROUP ADD DISK 'ORCL:ASM35' SIZE 92952 M;
ALTER DISKGROUP ADD DISK 'ORCL:ASM36' SIZE 92952 M;



##command to add ##
ALTER DISKGROUP DATA01 ADD DISK 'ORCL:ASM34' SIZE 92952 M;

## Check ASM Disk group sizes ##

SQL> select TOTAL_MB,FREE_MB,USABLE_FILE_MB,NAME from V$asm_diskgroup;

pztbw_uxusnorw400_XPT.cargill.com
Archived Redo Log
TOTAL_MB FREE_MB USABLE_FILE_MB File Name
---------- ---------- -------------- -------------------------
2788560 92749 92749 DATA01
92952 7909 7909 MIRROR01
92952 5945 5945 ONLINE01
185904 33449 33449 RECOVER01




SQL> select TOTAL_MB,FREE_MB,USABLE_FILE_MB,NAME from V$asm_diskgroup
2 ;

TOTAL_MB FREE_MB USABLE_FILE_MB NAME
---------- ---------- -------------- ------------------------------
3067416 212906 212906 DATA01
92952 21164 21164 MIRROR01
92952 43453 43453 ONLINE01
371808 114877 114877 RECOVER01



## Command to start up recovery process ##
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

## Check if MRP# is up and running ##
$> ps -ef | grep mrp | grep oracle

oracle 10248 16867 0 21:28 pts/2 00:00:00 grep mrp
oracle 29268 1 0 Nov16 ? 00:07:42 ora_mrp0_eminus

eminus's Activities | RunKeeper

eminus's Activities | RunKeeper

Query columns used by views

this SQL block will tell you all the columns being used by a view.

SET serveroutput ON
DECLARE
-- local variables, create a cursor for the records
CURSOR c1
IS
SELECT text,view_name FROM dba_views WHERE owner = &&OWNER1;
CURSOR c2
IS
SELECT table_name,
column_name
FROM dba_tab_columns
WHERE owner =
&&OWNER1
ORDER BY 1,2;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
FOR r2 IN c2
LOOP
FOR r1 IN c1
LOOP
IF (instr(lower(r1.text),lower(r2.column_name)) > 0) THEN
dbms_output.put_line(r1.view_name || ',' ||r2.column_name ||','||r2.table_name);
dBMS_OUTPUT.NEW_LINE;
END IF;
END LOOP;
END LOOP;
END;

useful when resizing datafile

this script i got from asktom.com is a usefull script to determine what object/s are in the end of the datafile which will cause for you to not to shrink it the way you want it to shrink :) (I mean if there is a big free space between that object and the other object)

column tablespace_name format a20
column "Name" format a45
break on file_id skip 1
ttitle &1
select file_id, block_id, blocks,
owner||'.'||segment_name "Name"
from sys.dba_extents
where tablespace_name = upper('&1')
UNION
select file_id, block_id, blocks,
'Free'
from sys.dba_free_space
where tablespace_name = upper('&1')
order by 1,2,3
/

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
set pages 3000
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

Undo Rollback time

this query will show you how much time remaining for your rollback session.

for 9i and up

SET lines 500 pages 500 col PCT FOR a5
SELECT state ,
undoblocksdone ,
undoblockstotal ,
cputime ,
undoblocksdone / undoblockstotal * 100 percent,
TO_CHAR(((((undoblockstotal - undoblocksdone) / (undoblocksdone/cputime) / 3600) / 24) + sysdate),'dd-mon-yyyy hh24:mi:ss') timetocomplete
FROM v$fast_start_transactions;
--just a note if your doing a parallel rollback the values does not give the correct values

for 10g and up

SELECT ADDR,
ROUND(((KTUXESIZ*
(SELECT value FROM v$parameter WHERE name = 'db_block_size'
)/1024/1024)),2) "Undo Size",
KTUXESTA "Status" ,
KTUXECFL
FROM sys.x$ktuxe
WHERE KTUXESTA='ACTIVE'

Oracle: how to get the DDL of a certain object

I have a sql script here to extract DDL of all the objects in a schema, the example below will extract the indexes of a certain schema. this is already formatted so you can spool it in a sql file and later run it.

set feedback off echo off
set long 200000 pages 0 lines 400 trimspool on linesize 400
column txt format a400 word_wrapped
variable ind_owner varchar2(100);

begin
:ind_owner := &schema;
end;
/

exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
spool $ORACLE_SID-get_idx_ddl.sql
select dbms_metadata.get_ddl('INDEX',u.INDEX_NAME,:ind_owner) txt from dba_indexes u where owner = :ind_owner;
spool off
exit

view open cursor usage in Oracle

--count open cursors by username, SID
break on report
comp sum of curs on report
select user_name, SID, count(*) cursors from V$OPEN_CURSOR group by User_Name, SID order by User_Name, SID;

--displays information on cursor usage for the current session
select * from V$SESSION_CURSOR_CACHE;

--displays information on cursor usage for the system.
select * from V$SYSTEM_CURSOR_CACHE;

--displays open_cursors usage in details
select user_name,to_char(sysdate,'hh24:mi:ss') col1, sql_text from V$OPEN_CURSOR

Linus Torvalds on Windows 7 ... nice

Resolving Flashback Database Error: (ORA-38760: This database instance failed to turn on flashback database)

Resolving Flashback Database Error: (ORA-38760: This database instance failed to turn on flashback database)

Unable to allocate flashback log of 1634 blocks from
current recovery area of size 42949672960 bytes.
Recovery Writer (RVWR) is stuck until more space is
available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.
Use ALTER SYSTEM SET db_recovery_file_dest_size command
to add space. DO NOT manually remove flashback log files
to create space.


so I brought down the db (aborted it)

SQL:.:(.):.> startup mount;
ORACLE instance started.

Total System Global Area 1694498816 bytes
Fixed Size 2139448 bytes
Variable Size 673405640 bytes
Database Buffers 889192448 bytes
Redo Buffers 129761280 bytes
Database mounted.
SQL:.:(.):.> select estimated_flashback_size,flashback_size,oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

ESTIMATED_FLASHBACK_SIZE FLASHBACK_SIZE OLDEST_FLASHBACK_SCN OLDEST_FL
------------------------ -------------- -------------------- ---------
0 4.2926E+10 0

SQL:.:(.):.> SELECT NAME, SCN, TIME,
GUARANTEE_FLASHBACK_DATABASE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES'; 2 3 4

NAME SCN TIME GUA
------------------------------ ---------- --------------------------------------------------------------------------- ---
GRP_1 9.2118E+12 21-AUG-09 02.21.03.000000000 PM YES
GRP_2 9.2138E+12 26-AUG-09 08.29.42.000000000 AM YES

SQL:.:(.):.> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
2
OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------
0


SQL:.:(.):.> flashback database to restore point GRP_2;

Flashback complete.

SQL:.:(.):.> select count(1) from v$datafile_header where fuzzy = 'YES';

COUNT(1)
----------
0

SQL:.:(.):.> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database


in the log
---------------------------------------------------------------------------------------------------------------
Flashback Restore Complete
Flashback Media Recovery Start
parallel recovery started with 15 processes
Flashback Media Recovery Log /sbclocal/app/oracle/admin/TESTDB/arch/TESTDB_0001_0695484339_0000000024.arc
Fri Aug 28 10:13:39 2009
Incomplete Recovery applied until change 9213823318145
Flashback Media Recovery Complete
Completed: flashback database to restore point GRP_2
Fri Aug 28 10:24:07 2009
alter database open resetlogs
Fri Aug 28 10:25:21 2009
RESETLOGS after incomplete recovery UNTIL CHANGE 9213823318145
Resetting resetlogs activation ID 4173768512 (0xf8c6a740)
Fri Aug 28 10:27:56 2009
Setting recovery target incarnation to 3
Fri Aug 28 10:27:56 2009
ORA-38760 signalled during: alter database open resetlogs...
Fri Aug 28 10:52:29 2009
---------------------------------------------------------------------------------------------------------------



SQL:oradbhost:(TESTDB):PRIMARY> drop restore point GRP_1;

Restore point dropped.


SQL:oradbhost:(TESTDB):PRIMARY> select status from v$instance;

STATUS
------------
MOUNTED

SQL:oradbhost:(TESTDB):PRIMARY> select count(1) from v$datafile_header where fuzzy = 'YES'l
2 .
SQL:oradbhost:(TESTDB):PRIMARY> select count(1) from v$datafile_header where fuzzy = 'YES';

COUNT(1)
----------
0


SQL:oradbhost:(TESTDB):PRIMARY> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL:oradbhost:(TESTDB):PRIMARY> flashback database to restore point GRP_2;
flashback database to restore point GRP_2
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.


SQL:oradbhost:(TESTDB):PRIMARY> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database


SQL:oradbhost:(TESTDB):PRIMARY> alter system set db_recovery_file_dest_size=50G;

System altered.

SQL:oradbhost:(TESTDB):PRIMARY> flashback database to restore point GRP_2;
flashback database to restore point GRP_2
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.


SQL:oradbhost:(TESTDB):PRIMARY> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database


-things to do if you got these scenario-
1) turn off the flashback database
2) drop the GRP
3) open the database normally(no resetlogs)
4) create a new GRP from that point

Check log sequece required for recovery in Oracle

select sequence# from V$log_history where 9151813979322 between first_change# and next_change#

Open cursor usage in Oracle

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;

check Undo users and uncommited transactions in Oracle

set lines 300
set pages 30000
SELECT a.sid, a.username, b.xidusn, b.used_urec Num_of_records, b.used_ublk USED_BLOCKS,(b.used_ublk * ( select value from v$parameter where name = 'db_block_size'))/1024/1024 MB_USED
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;


select
(select sum(bytes/1024/1024)
from dba_data_files
where tablespace_name = (select upper(value)
from V$parameter where name = 'undo_tablespace')) TOTAL_UNDO_SIZE_MB,
nvl((SELECT sum(b.used_ublk * ( select value from v$parameter where name = 'db_block_size')/1024/1024)
FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr),0) TOTAL_UNDO_USED_MB,
(select sum(bytes/1024/1024)
from dba_data_files
where tablespace_name = (select upper(value)
from V$parameter where name = 'undo_tablespace')) - nvl((SELECT sum(b.used_ublk * ( select value from v$parameter where name = 'db_block_size')/1024/1024)
FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr),0) FREE_MB_UNDO
from dual;


--#uncommited transactions

col osuser for a10
col username for a10
set lines 300
set pages 300
col sql_text for a60


select V$SESSION.OSUSER,spid,V$SESSION.status,
V$SESSION.USERNAME,V$SESSION.SID,
V$SESSION.SERIAL#,
V$SESSION.PROGRAM,
V$SESSION.MACHINE,
v$transaction.start_time,
to_number(sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 run_time
from
V$SESSION , v$transaction,V$process
WHERE v$transaction.ses_addr = V$SESSION.saddr and V$process.addr = V$SESSION.paddr;


select OSUSER,V$SESSION.status,
USERNAME,SID,
SERIAL#,
SQL_TEXT,
v$transaction.start_time,
to_number(sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 run_time
from v$transaction ,
V$SESSION ,
V$SQLAREA
WHERE( (V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS and V$SESSION.sql_hash_value = V$SQLAREA.HASH_VALUE )
OR ( V$SESSION.PREV_SQL_ADDR = V$SQLAREA.ADDRESS and V$SESSION.PREV_hash_value = V$SQLAREA.HASH_VALUE ))
and v$transaction.ses_addr = V$SESSION.saddr;

Temporary tablespace usage in Oracle

--
set lines 200
col sid_serial for a15
col osuser for a10
col module for a20
col program for a30
col tablespace for a10
-- Listing of temp segments.
--
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

--
-- Temp segment usage per session.
--
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

--
-- Temp segment usage per statement.
--
SELECT S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

Calculate Optimal Undo in Oracle

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/



SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/

checking long operations in Oracle RAC

#longopsrac.sql#
#--------------#
SELECT s.sid,
s.serial#,
s.machine,
TRUNC(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
TRUNC(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#
AND s.inst_id = sl.inst_id
AND sl.sofar < sl.totalwork
;

View RMAN jobs in Oracle

set lines 150 pages 30000
col ins format a10
col outs format a10
col TIME_TAKEN_DISPLAY format a10
select SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,input_type,
INPUT_BYTES_PER_SEC_DISPLAY ins,
OUTPUT_BYTES_PER_SEC_DISPLAY outs,
TIME_TAKEN_DISPLAY,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,elapsed_seconds/3600 hrs
,status
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

select output
from v$rman_output
where session_key = &session_key
order by recid;

@longopsrac

Views on Flashback Area Usage in Oracle

alter session set nls_date_format='DD-MON-YYYY HH24:MI:ss';
col OLDEST_FLASHBACK_SCN for 999999999999999
col name for a80
set lines 140 pages 3000

select name,SPACE_LIMIT/1024/1024 limit_mb,SPACE_USED/1024/1024 used_mb,round((SPACE_USED/SPACE_LIMIT)*100,2) percent_usage from V$RECOVERY_FILE_DEST;

select * from V$FLASH_RECOVERY_AREA_USAGE;

select estimated_flashback_size,flashback_size,oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

SELECT * FROM gv$flashback_database_stat;

set heading off

select '---------------' from dual;
select 'Flashback usage' from dual;
select '---------------' from dual;

set heading on


@/home/casenaem/flash_back_free.sql

set heading off

select '---------------' from dual;
select 'Restore Points' from dual;
select '---------------' from dual;

set heading on

select * from V$RESTORE_POINT;

check Library Cache Lock in Oracle

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'
order by lock_mode_held desc
/

Backup passwords of users in a database

select 'alter user ' || username || ' identified by values ''' || password ||''';' from dba_users order by username;

Check Session Details in Oracle

set pages 49999
set lines 300
col PID for a7
col SID for 9999999999
col ser# for a7
col box for a29
col status for a9
col username for a20
col os_user for a12
col program for a25
col LOGON_TIME for a14
col module for a14
col machine for a10
select b.status as status,sql_hash_value,prev_hash_value,module,machine,
to_char(a.spid) as pid, to_char(b.sid) as sid, to_char(b.serial#) as "ser#", substr(b.machine,1,25) as box, b.username as username,
b.osuser as os_user, b.program program, to_char(b.LOGON_TIME,'dd-mon hh24:mi') LOGON_TIME from v$session b, v$process a where
b.paddr = a.addr
and type='USER'
and b.sid in (&session_id)
order by 3;

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
col SQL_TEXT for a100

SELECT b.sid,a.sql_text
FROM v$sqltext_with_newlines a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid in (&session_id)
ORDER BY b.sid,a.piece;

PROMPT
SET PAGESIZE 14