FilesExpand file tree

 master

/

spaceused.sql

Copy path

BlameMore file actions

BlameMore file actions

Latest commit

 

History

History

History

54 lines (44 loc) · 1000 Bytes

 master

/

spaceused.sql

Top

File metadata and controls

• Code
• Blame

54 lines (44 loc) · 1000 Bytes

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

50

51

52

53

54

set linesize 1000

set pagesize 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.spaceused.log

-- List by tablespace the space used by the table and its indexes.

UNDEFINE TABOWNER

UNDEFINE TABNAME

select tablespace_name,

sum(bytes) Bytes,

sum(bytes)/(1024*1024) Megabytes,

sum(bytes)/(1024*1024*1024) Gigabytes

from

(select tablespace_name,bytes

from dba_segments

where

(owner,segment_name) in

(('&&TABOWNER','&&TABNAME')) and

segment_type in ('TABLE','TABLE PARTITION')

union

select s.tablespace_name,s.bytes

from dba_segments s, dba_indexes i

where

(i.table_owner,i.table_name) in

(('&&TABOWNER','&&TABNAME'))

and

i.owner=s.owner and

i.index_name=s.segment_name

and

segment_type in ('INDEX','INDEX PARTITION')

)

group by tablespace_name

order by tablespace_name;

spool off