function get_tablespace_tbl
\nRETURN t_table IS
\nl_table t_table := t_table ();
\nBEGIN<\/p>\n
SELECT t_record (TABLE_SPACE, used_mb, free_mb, total_mb, max_mb, pct_free, max_pct_free)
\nBULK COLLECT INTO l_table
\nFROM (
\nSELECT
\nfs.tablespace_name AS TABLE_SPACE,
\n(DF.TOTALSPACE – FS.FREESPACE) as USED_MB,
\nFS.FREESPACE as FREE_MB,
\nDF.TOTALSPACE as TOTAL_MB,
\nDF.MAXSPACE as MAX_MB,
\nROUND(100 * (FS.FREESPACE \/ DF.TOTALSPACE)) as PCT_FREE,
\ncase when DF.MAXSPACE > 0 then
\nROUND(100 * ((DF.MAXSPACE – (df.totalspace – fs.freespace)) \/ DF.MAXSPACE))
\nelse 0
\nend MAX_PCT_FREE
\nfrom
\n(select
\nTABLESPACE_NAME,
\nROUND(SUM(BYTES) \/ 1048576) TOTALSPACE,
\nround(sum(maxbytes) \/ 1048576) MaxSpace
\nfrom
\ndba_data_files
\ngroup by
\ntablespace_name
\n) df,
\n(select
\ntablespace_name,
\nround(sum(bytes) \/ 1048576) FreeSpace
\nfrom
\ndba_free_space
\ngroup by
\ntablespace_name
\norder by
\ntablespace_name
\n) fs
\nWHERE
\nDF.TABLESPACE_NAME = FS.TABLESPACE_NAME
\n);<\/p>\n
RETURN l_table;<\/p>\n
end;<\/p>\n
procedure get_tablespace_info(table_owner IN VARCHAR2 default ‚%‘)
\nIS
\nRC SYS_REFCURSOR;<\/p>\n
begin
\nopen rc for select
\nfs.tablespace_name „Tablespace“,
\n(df.totalspace – fs.freespace) „Used MB“,
\nfs.freespace „Free MB“,
\nDF.TOTALSPACE „Total MB“,
\nDF.MAXSPACE „Max MB“,
\nROUND(100 * (FS.FREESPACE \/ DF.TOTALSPACE)) „Pct. Free“,
\ncase when DF.MAXSPACE > 0 then
\nROUND(100 * ((DF.MAXSPACE – (df.totalspace – fs.freespace)) \/ DF.MAXSPACE))
\nelse 0
\nend „Max Pct. Free“
\nfrom
\n(select
\nTABLESPACE_NAME,
\nROUND(SUM(BYTES) \/ 1048576) TOTALSPACE,
\nround(sum(maxbytes) \/ 1048576) MaxSpace
\nfrom
\ndba_data_files
\ngroup by
\ntablespace_name
\n) df,
\n(select
\ntablespace_name,
\nround(sum(bytes) \/ 1048576) FreeSpace
\nfrom
\ndba_free_space
\ngroup by
\ntablespace_name
\norder by
\ntablespace_name
\n) fs
\nwhere
\nDF.TABLESPACE_NAME = FS.TABLESPACE_NAME
\nand FS.TABLESPACE_NAME like table_owner
\n;<\/p>\n
dbms_sql.return_result(rc);<\/p>\n
end get_tablespace_info;<\/p>\n","protected":false},"excerpt":{"rendered":"
function get_tablespace_tbl RETURN t_table IS l_table t_table := t_table (); BEGIN SELECT t_record (TABLE_SPACE, used_mb, free_mb, total_mb, max_mb, pct_free, max_pct_free) BULK COLLECT INTO l_table FROM ( SELECT fs.tablespace_name AS TABLE_SPACE, (DF.TOTALSPACE – FS.FREESPACE) as USED_MB, …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,4],"tags":[11],"class_list":["post-36","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","tag-tablespace"],"_links":{"self":[{"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/posts\/36","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=36"}],"version-history":[{"count":1,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/posts\/36\/revisions"}],"predecessor-version":[{"id":37,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=\/wp\/v2\/posts\/36\/revisions\/37"}],"wp:attachment":[{"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=36"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=36"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mein-it-wissen.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=36"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}