1 package dbms_space AUTHID CURRENT_USER as
2 ------------
3 -- OVERVIEW
4 --
5 -- This package provides segment space information not currently
6 -- available through the standard views.
7
8 -- SECURITY
9 --
10 -- The execution privilege is granted to PUBLIC. Procedures in this
11 -- package run under the caller security. The user must have ANALYZE
12 -- privilege on the object.
13
14 OBJECT_TYPE_TABLE constant positive := 1;
15 OBJECT_TYPE_NESTED_TABLE constant positive := 2;
16 OBJECT_TYPE_INDEX constant positive := 3;
17 OBJECT_TYPE_CLUSTER constant positive := 4;
18 OBJECT_TYPE_LOB_INDEX constant positive := 5;
19 OBJECT_TYPE_LOBSEGMENT constant positive := 6;
20 OBJECT_TYPE_TABLE_PARTITION constant positive := 7;
21 OBJECT_TYPE_INDEX_PARTITION constant positive := 8;
22 OBJECT_TYPE_TABLE_SUBPARTITION constant positive := 9;
23 OBJECT_TYPE_INDEX_SUBPARTITION constant positive := 10;
24 OBJECT_TYPE_LOB_PARTITION constant positive := 11;
25 OBJECT_TYPE_LOB_SUBPARTITION constant positive := 12;
26 OBJECT_TYPE_MV constant positive := 13;
27 OBJECT_TYPE_MVLOG constant positive := 14;
28 OBJECT_TYPE_ROLLBACK_SEGMENT constant positive := 15;
29
30 SPACEUSAGE_EXACT constant positive := 16;
31 SPACEUSAGE_FAST constant positive := 17;
32
33 ----------------------------
34
35 ----------------------------
36 -- PROCEDURES AND FUNCTIONS
37 --
38 procedure unused_space(segment_owner IN varchar2,
39 segment_name IN varchar2,
40 segment_type IN varchar2,
41 total_blocks OUT number,
42 total_bytes OUT number,
43 unused_blocks OUT number,
44 unused_bytes OUT number,
45 last_used_extent_file_id OUT number,
46 last_used_extent_block_id OUT number,
47 last_used_block OUT number,
48 partition_name IN varchar2 DEFAULT NULL
49 );
50 pragma restrict_references(unused_space,WNDS);
51
52 -- Returns information about unused space in an object (table, index,
53 -- or cluster).
54 -- Input arguments:
55 -- segment_owner
56 -- schema name of the segment to be analyzed
57 -- segment_name
58 -- object name of the segment to be analyzed
59 -- partition_name
60 -- partition name of the segment to be analyzed
61 -- segment_type
62 -- type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
63 -- Output arguments:
64 -- total_blocks
65 -- total number of blocks in the segment
66 -- total_bytes
67 -- the same as above, expressed in bytes
68 -- unused_blocks
69 -- number of blocks which are not used
70 -- unused_bytes
71 -- the same as above, expressed in bytes
72 -- last_used_extent_file_id
73 -- the file ID of the last extent which contains data
74 -- last_used_extent_block_id
75 -- the block ID of the last extent which contains data
76 -- last_used_block
77 -- the last block within this extent which contains data
78 procedure free_blocks (segment_owner IN varchar2,
79 segment_name IN varchar2,
80 segment_type IN varchar2,
81 freelist_group_id IN number,
82 free_blks OUT number,
83 scan_limit IN number DEFAULT NULL,
84 partition_name IN varchar2 DEFAULT NULL
85 );
86 pragma restrict_references(free_blocks,WNDS);
87
88 -- Returns information about free blocks in an object (table, index,
89 -- or cluster).
90 -- Input arguments:
91 -- segment_owner
92 -- schema name of the segment to be analyzed
93 -- segment_name
94 -- name of the segment to be analyzed
95 -- partition_name
96 -- partition name of the segment to be analyzed
97 -- segment_type
98 -- type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
99 -- freelist_group_id
100 -- freelist group (instance) whose free list size is to be computed
101 -- scan_limit (optional)
102 -- maximum number of free blocks to read
103 -- Output arguments:
104 -- free_blks
105 -- count of free blocks for the specified group
106
107 -- PROCEDURES AND FUNCTIONS
108 --
109 procedure space_usage(segment_owner IN varchar2,
110 segment_name IN varchar2,
111 segment_type IN varchar2,
112 unformatted_blocks OUT number,
113 unformatted_bytes OUT number,
114 fs1_blocks OUT number,
115 fs1_bytes OUT number,
116 fs2_blocks OUT number,
117 fs2_bytes OUT number,
118 fs3_blocks OUT number,
119 fs3_bytes OUT number,
120 fs4_blocks OUT number,
121 fs4_bytes OUT number,
122 full_blocks OUT number,
123 full_bytes OUT number,
124 partition_name IN varchar2 DEFAULT NULL
125 );
126 pragma restrict_references(space_usage,WNDS);
127
128 -- Returns information about space occupation in an object (table, index,
129 -- or cluster).
130 -- Input arguments:
131 -- segment_owner
132 -- schema name of the segment to be analyzed
133 -- segment_name
134 -- object name of the segment to be analyzed
135 -- partition_name
136 -- partition name of the segment to be analyzed
137 -- segment_type
138 -- type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
139 -- Output arguments:
140 -- unformatted_blocks
141 -- total number of blocks that are unformatted
142 -- unformatted_bytes
143 -- the same as above, expressed in bytes
144 -- fs1_blocks
145 -- number of blocks that have atleast 0 to 25% free space.
146 -- fs1_bytes
147 -- same as above, expressed in bytes
148 -- fs2_blocks
149 -- number of blocks that have atleast 25% to 50% free space.
150 -- fs2_bytes
151 -- same as above, expressed in bytes
152 -- fs3_blocks
153 -- number of blocks that have atleast 50% to 75% free space.
154 -- fs3_bytes
155 -- same as above, expressed in bytes
156 -- fs4_blocks
157 -- number of blocks that have atleast 75% to 100% free space.
158 -- fs4_bytes
159 -- same as above, expressed in bytes
160 -- full_blocks
161 -- total number of blocks that are full in the segment
162 -- full_bytes
163 -- the same as above, expressed in bytes
164
165 procedure space_usage (segment_owner IN varchar2,
166 segment_name IN varchar2,
167 segment_type IN varchar2,
168 segment_size_blocks OUT number,
169 segment_size_bytes OUT number,
170 used_blocks OUT number,
171 used_bytes OUT number,
172 expired_blocks OUT number,
173 expired_bytes OUT number,
174 unexpired_blocks OUT number,
175 unexpired_bytes OUT number,
176 partition_name IN varchar2 DEFAULT NULL
177 );
178 pragma restrict_references(space_usage,WNDS);
179 -- Returns information about space usage in Securefile segment
180 -- Input arguments:
181 -- segment_owner
182 -- schema name of the segment to be analyzed
183 -- segment_name
184 -- object name of the segment to be analyzed
185 -- partition_name
186 -- partition name of the segment to be analyzed
187 -- segment_type
188 -- type of the segment to be analyzed
189 -- Output arguments:
190 -- segment_size_blocks
191 -- number of blocks in the segment
192 -- segment_size_bytes
193 -- number of bytes in the segment
194 -- used_blocks
195 -- number of used blocks in the segment
196 -- used_bytes
197 -- number of used bytes in the segment
198 -- expired_blocks
199 -- number of expired blocks in the segment
200 -- expired_bytes
201 -- number of expired bytes in the segment
202 -- unexpired_blocks
203 -- number of unexpired blocks in the segment
204 -- unexpired_bytes
205 -- number of unexpired bytes in the segment
206
207 procedure space_usage (segment_owner IN varchar2,
208 segment_name IN varchar2,
209 segment_type IN varchar2,
210 suoption IN number,
211 segment_size_blocks OUT number,
212 segment_size_bytes OUT number,
213 used_blocks OUT number,
214 used_bytes OUT number,
215 expired_blocks OUT number,
216 expired_bytes OUT number,
217 unexpired_blocks OUT number,
218 unexpired_bytes OUT number,
219 partition_name IN varchar2 DEFAULT NULL
220 );
221 pragma restrict_references(space_usage,WNDS);
222 -- Returns information about space usage in Securefile segment
223 -- Optionally gets space usage faster by caching and retreiving
224 -- data from memory.
225 --
226 -- Input arguments:
227 -- segment_owner
228 -- schema name of the segment to be analyzed
229 -- segment_name
230 -- object name of the segment to be analyzed
231 -- partition_name
232 -- partition name of the segment to be analyzed
233 -- segment_type
234 -- type of the segment to be analyzed
235 -- suoption
236 -- SPACEUSAGE_EXACT: Computes space usage exhaustively
237 -- SPACEUSAGE_FAST: Retrieves values from in-memory statistics
238 --
239 -- Output arguments:
240 -- segment_size_blocks
241 -- number of blocks in the segment
242 -- segment_size_bytes
243 -- number of bytes in the segment
244 -- used_blocks
245 -- number of used blocks in the segment
246 -- used_bytes
247 -- number of used bytes in the segment
248 -- expired_blocks
249 -- number of expired blocks in the segment
250 -- expired_bytes
251 -- number of expired bytes in the segment
252 -- unexpired_blocks
253 -- number of unexpired blocks in the segment
254 -- unexpired_bytes
255 -- number of unexpired bytes in the segment
256
257
258 procedure isDatafileDroppable_Name(
259 filename in varchar2,
260 value out number);
261 pragma restrict_references(isDatafileDroppable_Name,WNDS);
262
263 -- Checks whether datafile is droppable
264 -- Input args:
265 -- filename - full filename of datafile
266 -- value - 1 if droppable, 0 if not droppable
267
268
269 procedure create_table_cost (
270 tablespace_name IN varchar2,
271 avg_row_size IN number,
272 row_count IN number,
273 pct_free IN number,
274 used_bytes OUT number,
275 alloc_bytes OUT number
276 );
277 pragma restrict_references(create_table_cost,WNDS);
278
279 procedure create_table_cost (
280 tablespace_name IN varchar2,
281 colinfos IN create_table_cost_columns,
282 row_count IN number,
283 pct_free IN number,
284 used_bytes OUT number,
285 alloc_bytes OUT number
286 );
287 pragma restrict_references(create_table_cost,WNDS);
288
289
290 procedure create_index_cost (
291 ddl IN varchar2,
292 used_bytes OUT number,
293 alloc_bytes OUT number,
294 plan_table IN varchar2 DEFAULT NULL
295 );
296
297
298 function verify_shrink_candidate (
299 segment_owner IN varchar2,
300 segment_name IN varchar2,
301 segment_type IN varchar2,
302 shrink_target_bytes IN number,
303 partition_name IN varchar2 DEFAULT NULL
304 ) return boolean;
305 pragma restrict_references(verify_shrink_candidate,WNDS);
306
307 type verify_shrink_row is record
308 (
309 status number
310 );
311 type verify_shrink_table is table of verify_shrink_row;
312
313 function verify_shrink_candidate_tbf (
314 segment_owner IN varchar2,
315 segment_name IN varchar2,
316 segment_type IN varchar2,
317 shrink_target_bytes IN number,
318 partition_name IN varchar2 DEFAULT NULL
319 ) return verify_shrink_table pipelined;
320 pragma restrict_references(verify_shrink_candidate_tbf,WNDS);
321
322 -- Primary task is to check if shrinking a segment by the given
323 -- number of bytes would result in an extent being freed or an
324 -- extent being truncated, and if so return true. If the segment
325 -- is not bitmap managed, then the function also returns false.
326 -- However, to properly check for proper segment type and segment
327 -- attributes (e.g. row movement enabled) to allow shrink, the
328 -- user is expected to use the ALTER ... SHRINK CHECK statement.
329 --
330 -- Input arguments:
331 -- segment_owner
332 -- schema name of the segment to be analyzed
333 -- segment_name
334 -- object name of the segment to be analyzed
335 -- partition_name
336 -- partition name of the segment to be analyzed
337 -- segment_type
338 -- type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)
339 -- Returns:
340 -- True if shrinking the segment will likely return space to the
341 -- tablespace containing the segment.
342
343 -- EM Special. Used to parse the data returned by segment advisor.
344 procedure parse_space_adv_info(info varchar2,
345 used_space out varchar2,
346 allocated_space out varchar2,
347 reclaimable_space out varchar2);
348 pragma restrict_references(parse_space_adv_info,WNDS);
349
350 procedure object_space_usage (
351 object_owner IN varchar2,
352 object_name IN varchar2,
353 object_type IN varchar2,
354 sample_control IN number,
355 space_used OUT number,
356 space_allocated OUT number,
357 chain_pcent OUT number,
358 partition_name IN varchar2 DEFAULT NULL,
359 preserve_result IN boolean DEFAULT TRUE,
360 timeout_seconds IN number DEFAULT NULL
361 );
362 pragma restrict_references(object_space_usage,WNDS);
363
364 type object_space_usage_row is record
365 (
366 space_used number,
367 space_allocated number,
368 chain_pcent number
369 );
370 type object_space_usage_table is table of object_space_usage_row;
371
372 function object_space_usage_tbf (
373 object_owner IN varchar2,
374 object_name IN varchar2,
375 object_type IN varchar2,
376 sample_control IN number,
377 partition_name IN varchar2 DEFAULT NULL,
381 pragma restrict_references(object_space_usage_tbf,WNDS);
378 preserve_result IN varchar2 DEFAULT 'TRUE',
379 timeout_seconds IN number DEFAULT NULL
380 ) return object_space_usage_table pipelined;
382
383
384 type asa_reco_row is record
385 (
386 tablespace_name varchar2(30),
387 segment_owner varchar2(30),
388 segment_name varchar2(30),
389 segment_type varchar2(18),
390 partition_name varchar2(30),
391 allocated_space number,
392 used_space number,
393 reclaimable_space number,
394 chain_rowexcess number,
395 ioreqpm number,
396 iowaitpm number,
397 iowaitpr number,
398 recommendations varchar2(1000),
399 c1 varchar2(1000),
400 c2 varchar2(1000),
401 c3 varchar2(1000),
402 task_id number,
403 mesg_id number
404 );
405 type asa_reco_row_tb is table of asa_reco_row;
406
407 function asa_recommendations (
408 all_runs in varchar2 DEFAULT 'TRUE',
409 show_manual in varchar2 DEFAULT 'TRUE',
410 show_findings in varchar2 DEFAULT 'FALSE'
411 ) return asa_reco_row_tb pipelined;
412
413
414 --
415 -- DBFS_DF : The function returns the free space in the
416 -- storage used by the tablespaces.
417 -- PARAMETERS : userid - user id of the user that can use the tablespaces
418 -- ntbs - number of tablespaces
419 -- ints_list - list of tablespace ids
420 -- RETURNS : Sum of free space in KB allocatable in the list of tablespaces
421 -- Free space in each tablespace is the number of KB available to theuser
422 -- for creation of new objects and growth of existing objects.
423 --
424 -- It does not account for space already allocated to the segments
425 -- in the tablespaces.
426 --
427 -- Functionality not supported for the following
428 -- 1. Undo tablespaces
429 -- 2. Temporary tablespaces
430 -- 3. Dictionary managed tablespaces
431 -- 4. Tablespaces with autoextensible files in file system storage.
432 -- The return value for unsupported tablespaces will be 0.
433 --
434
435 function dbfs_df (
436 userid IN number,
437 ntbs IN number,
438 ints_list IN tablespace_list) return number;
439
440 -- content of one row in dependent_segments table.
441 type object_dependent_segment is record (
442 segment_owner varchar2(100),
443 segment_name varchar2(100),
444 segment_type varchar2(100),
445 tablespace_name varchar2(100),
446 partition_name varchar2(100),
447 lob_column_name varchar2(100)
448 );
449
450 -- dependent_segments_table is a table of dependent_segment records. There
451 -- is one record for all the dependent segments of the object
452
453 type dependent_segments_table is table of object_dependent_segment;
454
455 function object_dependent_segments(
456 objowner IN varchar2,
457 objname IN varchar2,
458 partname IN varchar2,
459 objtype IN number
460 ) return dependent_segments_table pipelined;
461 -- pragma RESTRICT_REFERENCES(object_dependent_segments,WNDS,WNPS,RNPS);
462
463 -- objowner - owner of the object
464 -- objname - object name
465 -- partname - name of the partition or subpartition
466 -- objtype - object name space
467
468 -- object_growth_trend_row and object_growth_trend_table are used
469 -- by the object_growth_trend table function to describe its output
470 type object_growth_trend_row is record (
471 timepoint timestamp,
472 space_usage number,
473 space_alloc number,
474 quality varchar(20)
475 );
476
477 type object_growth_trend_table is table of object_growth_trend_row;
478
479 -- object_growth_swrf_row, object_growth_swrf_table,
480 -- object_growth_swrf_cursor, object_growth_trend_curtab,
481 -- and object_growth_trend_test_swrf are internal to the
482 -- implementation of object_growth_trend but need to be declared
483 -- here instead of in the private package body. These internal types
484 -- and procedures do not expose any internal information to the user.
485
486 type object_growth_swrf_row is record
487 (
488 timepoint timestamp,
489 delta_space_usage number,
490 delta_space_alloc number,
491 total_space_usage number,
492 total_space_alloc number,
493 instance_number number,
494 objn number
495 );
496
497 type object_growth_swrf_table is table of object_growth_swrf_row;
498
499 type object_growth_swrf_cursor is ref cursor return object_growth_swrf_row;
500
501 function object_growth_trend_i_to_s (
502 interv in dsinterval_unconstrained
503 ) return number;
504
505 function object_growth_trend_s_to_i (
506 secsin in number
507 ) return dsinterval_unconstrained;
508
509 function object_growth_trend_curtab
510 return object_growth_trend_table pipelined;
511
512 function object_growth_trend_swrf (
513 object_owner IN varchar2,
514 object_name IN varchar2,
515 object_type IN varchar2,
516 partition_name IN varchar2 DEFAULT NULL
517 ) return object_growth_swrf_table pipelined;
518
519
520 function object_growth_trend (
521 object_owner IN varchar2,
522 object_name IN varchar2,
523 object_type IN varchar2,
524 partition_name IN varchar2 DEFAULT NULL,
525 start_time IN timestamp DEFAULT NULL,
526 end_time IN timestamp DEFAULT NULL,
527 interval IN dsinterval_unconstrained DEFAULT NULL,
528 skip_interpolated IN varchar2 DEFAULT 'FALSE',
529 timeout_seconds IN number DEFAULT NULL,
530 single_datapoint_flag IN varchar2 DEFAULT 'TRUE'
531 ) return object_growth_trend_table pipelined;
532
533
534 function object_growth_trend_cur (
535 object_owner IN varchar2,
536 object_name IN varchar2,
537 object_type IN varchar2,
538 partition_name IN varchar2 DEFAULT NULL,
539 start_time IN timestamp DEFAULT NULL,
540 end_time IN timestamp DEFAULT NULL,
541 interval IN dsinterval_unconstrained DEFAULT NULL,
542 skip_interpolated IN varchar2 DEFAULT 'FALSE',
543 timeout_seconds IN number DEFAULT NULL
544 ) return sys_refcursor;
545
546 procedure auto_space_advisor_job_proc;
547
548 end;
[Home] [Help]