Oracle Common Script Collation

The amount of space (table capacity) of statistical tables

/* Generally, the table occupies three parts: Table data, table index, table blob field data */
--1 Statistics the space occupied by a single table with (BLOB field)

SELECT table_name, SUM (SIZE_MB)
From (SELECT A.segment_name as TABLE_NAME,
SUM (BYTES)/1024/1024 SIZE_MB
From User_segments A
GROUP by A.segment_name--Text information capacity
UNION All
SELECT A.table_name, SUM (b.bytes)/1024/1024 SIZE_MB
From User_lobs A, user_segments B
WHERE A.segment_name = B.segment_name
GROUP by A.table_name)--lob field capacity
WHERE table_name = ' &table_name '
GROUP by table_name
ORDER by 2 DESC;

--2 Statistics the space occupied by a table with (BLOB fields)

SELECT table_name, SUM (SIZE_MB)
From (SELECT A.segment_name as TABLE_NAME,
SUM (BYTES)/1024/1024 SIZE_MB
From User_segments A
GROUP by A.segment_name--Text information capacity
UNION All
SELECT A.table_name, SUM (b.bytes)/1024/1024 SIZE_MB
From User_lobs A, user_segments B
WHERE A.segment_name = B.segment_name
GROUP by A.table_name)--lob field capacity
GROUP by table_name
ORDER by 2 DESC;

--3 the space occupied by a table that does not contain LOB fields

SELECT A.segment_name as TABLE_NAME,
SUM (BYTES)/1024/1024 SIZE_MB
From User_segments A
WHERE a.segment_type = ' TABLE '
GROUP by A.segment_name;

Two, Statistics file usage
Select b.file_name physical file name,
          B.tablespace_name tablespace,
          b.autoextensible,
           b.maxbytes,
           b.bytes/1024/1024 size M,
          (B.bytes-sum (NVL ( A.bytes, 0))/1024/1024 used M,
       substr ((B.bytes-sum (NVL (a.bytes, 0))/(B.byt ES) * 100, 1, 5) utilization       
  from Dba_free_space A, dba_data_files b
 where A.fil e_id = b.file_id
 --and b.tablespace_name in (' tbs_crj_sq ', ' Tbs_crj_rz_index ')
 group by B.tablespace _name, B.autoextensible,b.maxbytes, B.file_name, B.bytes
 order by B.tablespace_name

Third, see if the tablespace is full
Select
A.tablespace_name,trunc (SUM (a.bytes)/1024/1024) Total,
Trunc (SUM (a.bytes)/1024/1024-sum (b.bytes)/1024/1024) used,
Trunc (SUM (b.bytes)/1024/1024) free,
To_char (Trunc ((SUM (a.bytes)/1024/1024-sum (b.bytes)/1024/1024)/(SUM (a.bytes)/1024/1024), 4) *100) | | % ' pused,
To_char (Trunc ((SUM (b.bytes)/1024/1024)/(SUM (a.bytes)/1024/1024), 4) *100) | | ' % ' Pfree
From
(select sum (bytes) Bytes,tablespace_name from Dba_data_files Group by Tablespace_name) A,
(select sum (bytes) Bytes,tablespace_name from Dba_free_space Group by Tablespace_name) b
where A.tablespace_name=b.tablespace_name (+)
GROUP BY A.tablespace_name
Order BY To_number (RTrim (pused, '% ')) desc;

Iv. the process of killing

Select A.machine,a.program,a.sid,a.serial#,a.status,b.sql_text,
' ALTER system kill session ' | | a.sid| | ', ' | | a.serial#| | " immediate; ' Kill_sql
From V$session A, V$sql b
Where a.username is not null
and a.sql_id=b.sql_id;

Five, SCN query
Select version,
To_char (sysdate, ' yyyy/mm/dd HH24:MI:SS ') date_time,
((((((((((() ((((((To_number (To_char (sysdate, ' YYYY ')-1988) *12*31*24*60*60) +
((To_number (To_char (sysdate, ' MM '))-1) * 31 * 24 * 60 * 60) +
(((To_number (To_char (sysdate, ' DD '))-1) * 24 * 60 * 60) +
(To_number (To_char (sysdate, ' HH24 ')) * 60 * 60) +
(To_number (To_char (sysdate, ' MI ')) * 60) +
(To_number (To_char (sysdate, ' SS ')))) * (16 * 1024))-
Dbms_flashback.get_system_change_number)/
(16 * 1024 * 60 * 60 * 24)) Headroom
From V$instance;

--select x.ksppinm NAME, Y.KSPPSTVL VALUE, X.ksppdesc describ from X$ksppi x, X$KSPPCV y WHERE x.indx = y.indx and X.ksppi NM like '%_external_scn_rejection_threshold_hours% ';
--alter system Set "_external_scn_rejection_threshold_hours" = 8;

Vi. Database Users

--1, user hangs (lock user)
Alter user Crjapp account lock;

--2, user unlock
Alter user Crjapp account unlock;

--3, deleting users
Drop user user_name cascade;

Oracle Common Script Collation