rem Name:    clus.sql
rem Purpose: List clusters
rem Usage:   @clus <%owner.table%> <%tablespace%>
rem Subject: object:cluster
rem Attrib:  sql
rem SeeAlso: @clustat

@setup1
define ts="upper('&&2')"

column cname format a25 heading "CLUSTER_NAME"
column tablespace_name format a10 heading "TABLESPACE"
column pctfu format a6
column trans format a6
column key_size format 9990 heading "KSIZE"
column hashkeys format 9990 heading "HKEYS"
column pct_increase format 990 heading "%INC"
column degree format a3
column instances format a3
column cache format a1

select
  owner||'.'||cluster_name cname,
  tablespace_name,
  ltrim(to_char(pct_free))||'/'||ltrim(to_char(pct_used)) pctfu,
  ltrim(to_char(ini_trans))||'/'||ltrim(to_char(max_trans)) trans,
  hashkeys,
  key_size,
  cluster_type,
  lpad(ltrim(degree),3) degree,
  lpad(ltrim(instances),3) instances,
  ltrim(cache) cache
from &&ora._clusters
where owner like &&o1
and cluster_name like &&n1
and tablespace_name like &&ts
order by owner, cluster_name
;

undef ts

@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips