FilesExpand file tree

 master

/

space.sql

Copy path

BlameMore file actions

BlameMore file actions

Latest commit

 

History

History

History

49 lines (39 loc) · 1.05 KB

 master

/

space.sql

Top

File metadata and controls

• Code
• Blame

49 lines (39 loc) · 1.05 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

set linesize 32000

set pagesize 1000

set long 2000000000

set longchunksize 1000

set head off;

set verify off;

set termout off;

column u new_value us noprint;

column n new_value ns noprint;

select name n from v$database;

select user u from dual;

set sqlprompt &ns:&us>

set head on

set echo on

set termout on

set trimspool on

spool &ns.space.log

-- get the hourly tablespace size and used space

-- from the AWR views

-- With input from https://mydbaspace.wordpress.com/2013/03/08/unit-size-in-dba_hist_tbspc_space_usage/

set define off

select

snap.END_INTERVAL_TIME,

sum(tsu.TABLESPACE_SIZE*dt.BLOCK_SIZE)/(1024*1024*1024) total_gigabytes,

sum(tsu.TABLESPACE_USEDSIZE*dt.BLOCK_SIZE)/(1024*1024*1024) used_gigabytes

from

DBA_HIST_TBSPC_SPACE_USAGE tsu,

DBA_HIST_SNAPSHOT snap,

V$TABLESPACE vt,

DBA_TABLESPACES dt

where

tsu.SNAP_ID = snap.SNAP_ID and

tsu.DBID = snap.DBID and

snap.instance_number = 1 and

tsu.TABLESPACE_ID = vt.TS# and

vt.NAME = dt.TABLESPACE_NAME

group by snap.END_INTERVAL_TIME

order by snap.END_INTERVAL_TIME;

spool off