1 package dbms_space_admin is
2 ------------
3 -- OVERVIEW
4 --
5 -- This package provides tablespace/segment space administration
6 -- not available through the standard sql.
7
8 -- SECURITY
9 --
10 -- run with SYS privileges. thus any user who has privilege to execute the
11 -- the package gets to manipulate the bitmapes.
12
13 -- CONSTANTS to be used as OPTIONS for various procedures
14 -- refer comments with procedure(s) for more detail
15
16 SEGMENT_VERIFY_EXTENTS constant positive := 1;
17 -- used to verify that the space owned by segment is appropriately reflected
18 -- in the bitmap as used
19 SEGMENT_VERIFY_EXTENTS_GLOBAL constant positive := 2;
20 -- used to verify that the space owned bu segment is appropriately reflected
21 -- in the bitmap as used and that no other segment claims any of this space
22 -- to be used by it
23 SEGMENT_MARK_CORRUPT constant positive := 3;
24 -- used to mark a temp segment as corrupt whereby facilitating its
25 -- elimination from the dictionary (without space reclaim)
26 SEGMENT_MARK_VALID constant positive := 4;
27 -- used to mark a corrupt temp segment as valid. Useful when the corruption
28 -- in the segment extent map or elsewhere has been resolved and the segment
29 -- can be dropped normally
30 SEGMENT_DUMP_EXTENT_MAP constant positive := 5;
31 -- dump the extent map for a given segment
32 TABLESPACE_VERIFY_BITMAP constant positive := 6;
33 -- verifies the bitmap of the tablespace with extent maps of the segments
34 -- in that tablespace to make sure everything is consistent
35 TABLESPACE_EXTENT_MAKE_FREE constant positive := 7;
36 -- makes this range (extent) of space free in the bitmaps
37 TABLESPACE_EXTENT_MAKE_USED constant positive := 8;
38 -- makes this range (extent) of space used in the bitmaps
39
40 SEGMENT_VERIFY_BASIC constant positive := 9;
41 SEGMENT_VERIFY_DEEP constant positive := 10;
42 SEGMENT_VERIFY_SPECIFIC constant positive := 11;
43 HWM_CHECK constant positive := 12;
44 BMB_CHECK constant positive := 13;
45 SEG_DICT_CHECK constant positive := 14;
46 EXTENT_TS_BITMAP_CHECK constant positive := 15;
47 DB_BACKPOINTER_CHECK constant positive := 16;
48 EXTENT_SEGMENT_BITMAP_CHECK constant positive := 17;
49 BITMAPS_CHECK constant positive := 18;
50
51
52 TS_VERIFY_BITMAPS constant positive := 19;
53 TS_VERIFY_DEEP constant positive := 20;
54 TS_VERIFY_SEGMENTS constant positive := 21;
55
56 SEGMENT_DUMP_BITMAP_SUMMARY constant positive := 27;
57
58 NGLOB_HBB_CHECK constant positive := 12;
59 NGLOB_FSB_CHECK constant positive := 13;
60 NGLOB_PUA_CHECK constant positive := 14;
61 NGLOB_CFS_CHECK constant positive := 15;
62 HEAT_MAP_MAXSIZE_UNLIMITED constant positive := 16;
63
64
65
66 ----------------------------
67 -- PROCEDURES AND FUNCTIONS
68
69 procedure segment_verify(
70 tablespace_name in varchar2 ,
71 header_relative_file in positive ,
72 header_block in positive ,
73 verify_option in positive DEFAULT SEGMENT_VERIFY_EXTENTS
74 );
75 --
76 -- Verifies the consistency of the extent map of the segment
77 -- Input arguments:
78 -- tablespace_name - name of tablespace in which segment resides
79 -- header_relative_file - relative file number of segment segment header
80 -- header_block - block number of segment segment header
81 -- verify_option - SEGMENT_VERIFY_EXTENTS or
82 -- SEGMENT_VERIFY_EXTENTS_GLOBAL
83 --
84 procedure segment_corrupt(
85 tablespace_name in varchar2 ,
86 header_relative_file in positive ,
87 header_block in positive ,
88 corrupt_option in positive DEFAULT SEGMENT_MARK_CORRUPT
89 );
90 --
91 -- Marks the segment corrupt/valid so that appropriate error recovery
92 -- can be done/skipped. Only for segments already temporary.
93 -- Input arguments:
94 -- tablespace_name - name of tablespace in which segment resides
95 -- header_relative_file - relative file number of segment segment header
96 -- header_block - block number of segment segment header
97 -- corrupt_option - SEGMENT_MARK_CORRUPT or
98 -- SEGMENT_MARK_VALID
99 --
100 procedure segment_drop_corrupt(
101 tablespace_name in varchar2 ,
102 header_relative_file in positive ,
103 header_block in positive
104 );
105 --
106 -- Drops a segment currently marked corrupt (without reclaiming space)
107 -- Input arguments:
108 -- tablespace_name - name of tablespace in which segment resides
109 -- header_relative_file - relative file number of segment segment header
110 -- header_block - block number of segment segment header
111 --
112 procedure segment_dump(
113 tablespace_name in varchar2 ,
114 header_relative_file in positive ,
115 header_block in positive ,
116 dump_option in positive DEFAULT SEGMENT_DUMP_EXTENT_MAP
117 );
118
119 --
120 -- Dumps the extent map of a given segment
121 -- Input arguments:
122 -- tablespace_name - name of tablespace in which segment resides
123 -- header_relative_file - relative file number of segment segment header
124 -- header_block - block number of segment segment header
125 -- dump_option - SEGMENT_DUMP_EXTENT_MAP
126 --
127
128 procedure tablespace_verify(
129 tablespace_name in varchar2 ,
130 verify_option in positive DEFAULT TABLESPACE_VERIFY_BITMAP
131 );
132 --
133 -- Verifies that the extent maps and the bitmaps are in sync.
134 -- Input arguments:
135 -- tablespace_name - name of tablespace
136 -- verify_option - TABLESPACE_VERIFY_BITMAP
137 --
138 procedure tablespace_fix_bitmaps(
139 tablespace_name in varchar2 ,
140 dbarange_relative_file in positive ,
141 dbarange_begin_block in positive ,
142 dbarange_end_block in positive ,
143 fix_option in positive
144 );
145 --
146 -- Marks the appropriate dba range (extent) as free/used in bitmap
147 -- Input arguments:
148 -- tablespace_name - name of tablespace
149 -- dbarange_relative_file - relative fileno of dba range (extent)
150 -- dbarange_begin_block - block number of beginning of extent
151 -- dbarange_end_block - block number (inclusive) of end of extent
152 -- fix_option - TABLESPACE_EXTENT_MAKE_FREE or
153 -- TABLESPACE_EXTENT_MAKE_USED
154 --
155 procedure tablespace_rebuild_bitmaps(
156 tablespace_name in varchar2 ,
157 bitmap_relative_file in positive DEFAULT NULL,
158 bitmap_block in positive DEFAULT NULL
159 );
160 --
161 -- Rebuilds the appropriate bitmap(s). If no bitmap block dba specified then
162 -- rebuilds all bitmaps for the given tablespace
163 -- Input arguments:
164 -- tablespace_name - name of tablespace
165 -- bitmap_relative_file - relative file number of bitmap block to rebuild
166 -- bitmap_block - block number of bitmap block to rebuild
167 --
168
169 --
170 procedure tablespace_rebuild_quotas(
171 tablespace_name in varchar2
172 );
173 --
174 -- Rebuilds quotas for given tablespace
175 -- Input arguments:
176 -- tablespace_name - name of tablespace
177 --
178
179 --
180 procedure tablespace_migrate_from_local(
181 tablespace_name in varchar2
182 );
183 --
184 -- Migrates a locally-managed tablespace to dictionary managed
185 -- Input arguments:
186 -- tablespace_name - name of tablespace
187
188 --
189 procedure tablespace_migrate_to_local(
190 tablespace_name in varchar2,
191 unit_size in positive DEFAULT NULL,
192 rfno in positive DEFAULT NULL
193 );
194 --
195 -- Migrates a dictionary-managed tablespace to locally managed
196 -- Input arguments:
197 -- tablespace_name - name of tablespace
198 -- unit_size - bitmap unit size for the tablespace
199 --
200
201 --
202 procedure tablespace_relocate_bitmaps(
203 tablespace_name in varchar2,
204 filno in positive,
205 blkno in positive);
206
207 --
208 -- Tablespace relocate bitmaps to a different location
209 -- Input arguments:
210 -- tablespace_name - name of tablespace
211 --
212
213 --
214 procedure tablespace_fix_segment_states(
215 tablespace_name in varchar2);
216
217 --
218 -- Tablespace fix segment states. During migration of tablespaces
219 -- the segments are put in a transition state. If migration fails,
220 -- the state of the segments can be corrected by calling this
221 -- procedure. This procedure should be called if migration failed
222 -- and the user tries to run in an incompatible mode.
223
224 --
225 procedure tablespace_fix_segment_extblks(
226 tablespace_name in varchar2);
227
228 --
229 -- Tablespace fix segment extents and blocks based on segment
230 -- header entries
231
232 --
233 procedure tablespace_dump_bitmaps(
234 tablespace_name in varchar2);
235
236 --
237 -- Tablespace space header of files
238
239 function segment_number_extents(
240 header_tablespace_id in natural ,
241 header_relative_file in positive ,
242 header_block in positive ,
243 segment_type in positive ,
244 buffer_pool_id in natural ,
245 dictionary_flags in number ,
246 data_object_id in number,
247 dictionary_extents in number
248 ) return pls_integer;
249 pragma RESTRICT_REFERENCES(segment_number_extents,WNDS,WNPS,RNPS);
250 --
251 -- Returns the number of extents which belong to the segment. Will return
252 -- NULL if segment has disappeared. IS NOT to be used for any other
253 -- purposes but by the views which need it and are sure that there info
254 -- is correct. Else internal errors will abound
255 --
256 function segment_number_blocks(
257 header_tablespace_id in natural ,
258 header_relative_file in positive ,
259 header_block in positive ,
260 segment_type in positive ,
261 buffer_pool_id in natural ,
262 dictionary_flags in number ,
263 data_object_id in number,
264 dictionary_blocks in number
265 ) return pls_integer;
266 pragma RESTRICT_REFERENCES(segment_number_blocks,WNDS,WNPS,RNPS);
267 --
268 -- Returns the number of blocks which belong to the segment. Will return
269 -- NULL if segment has disappeared. IS NOT to be used for any other
270 -- purposes but by the views which need it and are sure that there info
271 -- is correct. Else internal errors will abound
272 --
273 procedure segment_moveblocks(
274 segment_owner in varchar2,
275 segment_name in varchar2,
276 partition_name in varchar2,
277 segment_type in varchar2,
278 group_id in natural,
279 minimum_size in positive,
280 move_count in positive,
281 pause_time in natural,
282 iterations in positive
283 );
284 --
285 -- Moves blocks from the segment header to the process freelist
286 -- It only moves blocks if the list is shorter than the minimum
287 -- size for the move_count blocks
288 -- segment_owner - name of the object's owner
289 -- segment_name - name of the object
290 -- partition_name - name of the partition (NULL if not partitioned)
291 -- segment_type - object type (TABLE, INDEX, etc - see DBMS_SPACE)
292 -- group_id - freelist group (0 for the segment header)
293 -- minimum_size - do not move if process free list is longer
294 -- move_count - move up to this number of blocks
295 -- pause_time - pause between loop iterations
296 -- iterations - number of iterations (infinite if NULL)
297 --
298
299
300
301 procedure assm_segment_verify(
302 segment_owner in varchar2,
303 segment_name in varchar2,
304 segment_type in varchar2,
305 partition_name in varchar2,
306 verify_option in positive default SEGMENT_VERIFY_BASIC ,
307 attrib in positive default NULL);
308
309 --
310
311 -- Verifies the consistency of the segment
312 -- Input arguments:
313 -- segment_owner - owner
314 -- segment_name - name of the segment
315 -- segment_type - type of segment
316 -- partition_name - name of partition default NULL
317 -- verify_option - one of SEGMENT_VERIFY_BASIC, DEEP , SPECIFIC
318 -- attrib - used when option SEGMENT_VERIFY_SPECIFIC
319
320
321 procedure nglob_segment_verify(
322 segment_owner in varchar2,
323 segment_name in varchar2,
324 segment_type in varchar2,
325 partition_name in varchar2,
326 verify_option in positive default SEGMENT_VERIFY_BASIC ,
327 attrib in positive default NULL);
328
329 --
330
331 -- Verifies the consistency of the segment
332 -- Input arguments:
333 -- segment_owner - owner
334 -- segment_name - name of the segment
335 -- segment_type - type of segment
336 -- partition_name - name of partition default NULL
337 -- verify_option - one of SEGMENT_VERIFY_BASIC, DEEP , SPECIFIC
338 -- attrib - used when option SEGMENT_VERIFY_SPECIFIC
339
340 procedure assm_tablespace_verify(
341 tablespace_name in varchar2,
342 ts_option in positive,
343 segment_option in positive default NULL);
344
345 --
346 -- Verifies that the tablespace consistency.
347 -- Input arguments:
348 -- tablespace_name - name of tablespace
349 -- ts_option - TS_VERIFY_BITMAPS, TS_VERIFY_DEEP, TS_VERIFY_SEGMENTS
350 -- segment option - used when TS_VERIFY_SEGMENTS, one of SEGMENT_VERIFY_DEEP, SPECIFIC
351 --
352
353
354 function assm_segment_synchwm(
355 segment_owner in varchar2,
356 segment_name in varchar2,
357 segment_type in varchar2,
358 partition_name in varchar2 default NULL,
359 check_only in number default 1
360 ) return pls_integer;
361
362 --
363 -- Synchronize HWMs of the ASSM segment
364 -- Input arguments:
365 -- segment_owner - owner
366 -- segment_name - name of the segment
367 -- segment_type - type of segment
368 -- partition_name - name of partition default NULL
369 -- check_only - whether it is check only default YES
370 -- Output:
371 -- Return TRUE if the segment requires HWM synchronization
372 -- Return FALSE otherwise
373
374 procedure flush_lobsegment_stats;
378
375
376 procedure purge_lobsegment_stats;
377
379 -------------------------------------------------------------------------
380 -- PROCEDURE securefile_segment_repair
381 -------------------------------------------------------------------------
382
383 -- segment repair option: repair metadata blocks
384 SEGMENT_REPAIR_METADATA constant positive := 1;
385
386 procedure segment_repair(
387 segment_owner in varchar2,
388 segment_name in varchar2,
389 segment_type in varchar2,
390 partition_name in varchar2 default NULL,
391 repair_option in number default SEGMENT_REPAIR_METADATA
392 );
393
394 --
395 -- Description:
396 -- Repair SecureFile segment
397 -- Input parameters:
398 -- segment_owner - owner
399 -- segment_name - name of the LOB segment
400 -- segment_type - type of segment
401 -- values: 'LOB', 'LOB PARTITION', 'LOB SUBPARTITION'
402 -- partition_name - name of the LON partition segment
403 -- default: NULL
404 -- repair_option - segment repair option,
405 -- values: see SEGMENT_REPAIR_*** definitions
406 -- default: SEGMENT_REPAIR_METADATA
407 -- Note:
408 -- This function is only used internally and does not require
409 -- documentation.
410
411
412 -------------------------------------------------------------------------
413 -- PROCEDURE segment_extend
414 -------------------------------------------------------------------------
415
416 procedure segment_extend(
417 segment_owner in varchar2,
418 segment_name in varchar2,
419 segment_type in varchar2,
420 partition_name in varchar2 default NULL,
421 target_size in number default 1
422 );
423
424 --
425 -- Description:
426 -- SecureFile segment extend in background
427 -- Input parameters:
428 -- segment_owner - owner
429 -- segment_name - name of the LOB segment
430 -- segment_type - type of segment
431 -- values: 'LOB', 'LOB PARTITION', 'LOB SUBPARTITION'
432 -- partition_name - name of the LOB partition segment
433 -- default: NULL
434 -- target_size - segment target size in GB
435 -- Note:
436 -- This function is only used internally and does not require
437 -- documentation.
438
439
440 -------------------------------------------------------------------------
441 -- PROCEDURE drop_empty_segments
442 -------------------------------------------------------------------------
443
444 procedure drop_empty_segments(
445 schema_name in varchar2 default NULL,
446 table_name in varchar2 default NULL,
447 partition_name in varchar2 default NULL
448 );
449
450 --
451 -- Description:
452 -- Drop segments from empty table(s)/table fragments and dependent
453 -- objects.
454 -- Input parameters:
455 -- schema_name - schema name, default: NULL
456 -- table_name - table name, default: NULL
457 -- partition_name - partition name, default: NULL
458 -- Note:
459 -- Given a schema name, this procedure scans all tables in the schema
460 -- For each table, if the table or any of its fragments are found to be
461 -- empty, and the table satisfies certain criteria [restrictions being
462 -- the same as those imposed by segment creation on demand], the empty
463 -- fragments and associated index segments are dropped. A subsequent
464 -- insert will create segments with the same properties.
465 -- Optionally,
466 -- a. no schema name may be specified in which case we would scan
467 -- tables belonging to all schemas
468 -- b. both schema_name and table_name may be specified to do this
469 -- operation on one particular table
470 -- c. all three arguments may be supplied, in which case we will
471 -- restrict this operation to that partition and its dependent
472 -- objects.
473
474
475 -------------------------------------------------------------------------
476 -- PROCEDURE materialize_deferred_segments
477 -------------------------------------------------------------------------
478
479 procedure materialize_deferred_segments(
480 schema_name in varchar2 default NULL,
481 table_name in varchar2 default NULL,
482 partition_name in varchar2 default NULL
483 );
484
485 --
486 -- Description:
487 -- Materialize segments for tables/table fragments with deferred
488 -- segment creation (and their dependent objects)
489 -- Input parameters:
490 -- schema_name - schema name, default: NULL
491 -- table_name - table name, default: NULL
492 -- partition_name - partition name, default: NULL
493 -- Note:
494 -- Given a schema name, this procedure scans all tables in the schema.
495 -- For each table, if the deferred/delayed segment property is set for
496 -- the table or any of its fragments, a new segment is created for
497 -- those fragments and their dependent objects.
498 -- Optionally,
499 -- a. no schema name may be specified in which case we would scan tables
500 -- belonging to all schemas
501 -- b. both schema_name and table_name may be specified to do this
502 -- operation on one particular table
503 -- c. all three arguments may be supplied, in which case we will
504 -- restrict this operation to that partition and its dependent
505 -- objects
506
507
508 -------------------------------------------------------------------------
509 -- PROCEDURE materialize_deferred_with_opt
510 -------------------------------------------------------------------------
511
512 procedure materialize_deferred_with_opt(
513 schema_name in varchar2 default NULL,
514 table_name in varchar2 default NULL,
515 partition_name in varchar2 default NULL,
516 partitioned_only in boolean default FALSE
517 );
518
519 --
520 -- Description:
521 -- Materialize segments for tables/table fragments with deferred
522 -- segment creation (and their dependent objects), with an additional
523 -- option.
524 -- Input parameters:
525 -- schema_name - schema name, default: NULL
526 -- table_name - table name, default: NULL
527 -- partition_name - partition name, default: NULL
528 -- partitioned_only - apply materialize procedure on partitioned
529 -- tables only, default: FALSE
530 -- Note:
531 -- The materialize_deferred_segments procedure is a wrapper around
532 -- this. This procedure is required for downgrading (from 11.2.0.2)
533 -- to materialize segments for partitioned tables only. The
534 -- partitioned_only argument supports this limited behavior.
535
536
537 -------------------------------------------------------------------------
538 -- PROCEDURE tablespace_fix_affinity
539 -------------------------------------------------------------------------
540 procedure tablespace_fix_affinity(
541 tablespace_name in varchar2,
542 block_num in binary_integer,
543 instance_id in binary_integer DEFAULT NULL
544 );
545
546 --
547 -- Description:
548 -- This procedure sets instance affinity of a File Bitmap Block
549 -- in bigfile tablespace. The block can be either a Level 1 bitmap
550 -- block (FFB) or a Level 2 bitmap block (FSB).
551 -- The report will be generated in the corresponding trace file of
552 -- the session that runs this procedure.
553 --
554 -- Input parameters:
555 -- tablespace_name - name of tablespace
556 -- block_num - block number
557 -- instance_id - id of instance to be affined, by default
558 -- it is the current instance
559 --
560 -- Note:
561 -- (1) If the block is Second-level File Bitmap block (FSB), and if
562 -- there is another FSB that has already been affined to the instance
563 -- specified, this FSB will be freed.
564 -- (2) This function is only used internally and does not require
565 -- documentation.
566
567
568 -- Drop the HEATMAP segment created in sysaux tablespace
569 procedure heat_map_segment_drop;
570
571 -- Set the MAXSIZE storage clause for HEATMAP segment to limit its growth
572 -- Maxsize is specified in tablespace blocks. For unlimited use
573 -- ILMSTAT_MAXSIZE_UNLIMITED.
574 procedure heat_map_segment_setmaxsize(maxsize in binary_integer
575 DEFAULT HEAT_MAP_MAXSIZE_UNLIMITED);
576
577 /* Dummy procedure for creating in memory columnar tablespace extent map */
578 -- Input parameters:
579 -- tablespace_name - name of tablespace
580
581 procedure imc_tablespace_create(
582 tablespace_name in varchar2);
583
584 /* Dummy procedure for drop of in memory columnar tablespace extent map*/
585 -- Input parameters:
586 -- tablespace_name - name of tablespace
587
588 procedure imc_tablespace_drop(
589 tablespace_name in varchar2);
590
591 /* Dummy procedure for creating table/segment in memory columnar */
592 -- Input parameters:
593 -- segment_owner - owner
594 -- segment_name - name of the segment
595 -- segment_type - type of segment
596 -- partition_name - name of the partition segment
597
598 procedure imc_segment_create(
599 segment_owner in varchar2,
600 segment_name in varchar2,
601 segment_type in varchar2,
602 partition_name in varchar2);
603
604 /* Dummy procedure for dropping table/segment in memory columnar */
605 -- Input parameters:
606 -- segment_owner - owner
607 -- segment_name - name of the segment
608 -- segment_type - type of segment
609 -- partition_name - name of the partition segment
610
611 procedure imc_segment_drop(
612 segment_owner in varchar2,
613 segment_name in varchar2,
614 segment_type in varchar2,
615 partition_name in varchar2);
616
617 /* Dummy procedure for loading in memory columnar segment */
618 -- Input parameters:
619 -- segment_owner - owner
620 -- segment_name - name of the segment
621 -- segment_type - type of segment
622 -- partition_name - name of the partition segment
623 -- query - 0 is load, 1 is scan
624
625 procedure imc_segment_load(
626 segment_owner in varchar2,
627 segment_name in varchar2,
628 segment_type in varchar2,
629 partition_name in varchar2,
630 query in binary_integer);
631
632 /* Procedure to dump in memory area */
633 procedure imc_area_dump;
634
635 /* Procedure to dump in memory L1,L2,L3 tree */
636 procedure imc_tree_dump;
637
638
639 end;