For NVARCHAR2 and VARCHAR2 maximum size is 4000 bytes, or 32767 bytes if the MAX_STRING_SIZE initialization parameter is set to EXTENDED. This is useful you have to allocate more data to a variable.
This is cheat sheet for basic database monitoring PL SQL queries.
Tables
ALL_TABLES describes the relational tables accessible to the current user. To gather statistics for this view, use the DBMS_STATS package.
SELECT table_name FROM all_tables
WHERE tablespace_name = 'EXAMPLE' ORDER BY table_name;
DBA_TABLES describes all relational tables in the database.
SELECT * from DBA_TABLES;
USER_TABLES describes the relational tables owned by the current user. This view does not display the OWNER column.
SELECT * from USER_TABLES;
To check the table size in the current schema.
select segment_name,segment_type, sum(bytes/1024/1024/1024) GB
from dba_segments
where segment_name='&TableName'
group by segment_name,segment_type;
References
Sessions
To kill a session in Oracle
SELECT OBJECT_NAME, 'ALTER SYSTEM KILL SESSION ''' || VS.SID || ',' || VS.SERIAL# || ''' ;' ,
AO.OWNER, MACHINE
FROM V$SESSION VS
LEFT JOIN V$LOCK VL ON VS.SID = VL.SID
LEFT JOIN ALL_OBJECTS AO ON VL.ID1 = AO.OBJECT_ID
LEFT JOIN USER_TABLES UT ON AO.OBJECT_NAME = UT.TABLE_NAME
WHERE OBJECT_NAME LIKE '%TABLE_NAME%';
SELECT s.sid, s.serial#, p.spid, s.username, s.program
FROM v$session s JOIN v$process p ON p.addr = s.paddr
WHERE s.type != 'BACKGROUND';
What are your thoughts on this post?
I’d love to hear from you! Click this link to email me—I reply to every message!
Also use the share button below if you liked this post. It makes me smile, when I see it.