PROCEDURE: SYS.DBMS_FEATURE_SEG_MAIN_ONL_COMP

Source

1 PROCEDURE DBMS_FEATURE_SEG_MAIN_ONL_COMP
2   (isAnyFragCompressed OUT  NUMBER,
3    numFragsCompressed  OUT  NUMBER,
4    fragObjNumList      OUT  CLOB)
5 AS
6   partnFragsCompressed     NUMBER;
7   subpartnFragsCompressed  NUMBER;
8   fragObjNumListStr        VARCHAR2(1000);
9 
10   -- select from tabpart$
11   cursor tp_cursor is      select tp.obj# from sys.tabpart$ tp
12                            where bitand(tp.flags, 33554432) = 33554432;
13 
14   -- select from tabsubpart$
15   cursor tsp_cursor is     select tsp.obj# from sys.tabsubpart$ tsp
16                            where bitand(tsp.flags, 33554432) = 33554432;
17 BEGIN
18   -- initialize
19   isAnyFragCompressed := 0;
20   numFragsCompressed  := 0;
21   fragObjNumListStr   := NULL;
22 
23   -- count partitions compressed through an online PMOP
24   select count(*) into partnFragsCompressed
25   from sys.tabpart$ where bitand(flags, 33554432) = 33554432;
26 
27   -- count subpartitions compressed through an online PMOP
28   select count(*) into subpartnFragsCompressed
29   from sys.tabsubpart$ where bitand(flags, 33554432) = 33554432;
30 
31   -- loop through tabpart$
32   if (partnFragsCompressed > 0) then
33     isAnyFragCompressed := 1;
34     fragObjNumListStr   := fragObjNumListStr || 'Partition Obj# list: ';
35     for ri in tp_cursor
36     loop
37       fragObjNumListStr := fragObjNumListStr || ri.obj# || ':';
38     end loop;
39     fragObjNumListStr := fragObjNumListStr || chr(10);
40   end if;
41 
42   -- loop through subpart$
43   if (subpartnFragsCompressed > 0) then
44     isAnyFragCompressed := 1;
45     fragObjNumListStr   := fragObjNumListStr || 'Subpartition Obj# list: ';
46     for ri in tsp_cursor
47     loop
48       fragObjNumListStr := fragObjNumListStr || ri.obj# || ':';
49     end loop;
50   end if;
51 
52   -- populate the variables to be returned
53   if (partnFragsCompressed + subpartnFragsCompressed > 0) then
54     isAnyFragCompressed := 1;
55     numFragsCompressed  := partnFragsCompressed + subpartnFragsCompressed;
56     fragObjNumList      := to_clob(fragObjNumListStr);
57   end if;
58 
59 END DBMS_FEATURE_SEG_MAIN_ONL_COMP;