tag:blogger.com,1999:blog-31216575.post4332247020197460728..comments2024-01-27T07:32:53.464+01:00Comments on On The Peoplesoft Road: Peoplesoft on 11gR2 64-bitsNicolas Gasparottohttp://www.blogger.com/profile/06634519580752248909noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-31216575.post-79888044642800344762009-09-08T20:54:48.228+02:002009-09-08T20:54:48.228+02:00That would be really nice, here the results of spa...That would be really nice, here the results of space of a demo FSCM9 :<br />SQL> select segment_type, count(*) "NbSegments", trunc(sum(bytes)/1024/1024) "SizeMb"<br /> 2 from user_segments<br /> 3 where segment_type in ('TABLE','INDEX')<br /> 4 and segment_name not like 'BIN%'<br /> 5 group by segment_type;<br /><br />TABLE 54010 15437<br />INDEX 62230 63497<br /><br />SQL> set serveroutput on<br />SQL> declare<br /> 2 v_count number;<br /> 3 v_nb_empty_tbl number:=0;<br /> 4 v_size_empty_tbl number:=0;<br /> 5 v_nb_empty_idx number:=0;<br /> 6 v_size_empty_idx number:=0;<br /> 7 v_bytes number;<br /> 8 begin<br /> 9 for tbl in (select table_name from user_tables) loop<br /> 10 begin<br /> 11 execute immediate 'select count(*) from '||tbl.table_name||' where rownum=1' into v_count;<br /> 12 if v_count=0 then<br /> 13 begin<br /> 14 select bytes<br /> 15 into v_bytes<br /> 16 from user_segments<br /> 17 where segment_type='TABLE'<br /> 18 and segment_name =tbl.table_name;<br /> 19 exception when no_data_found then v_bytes:=0;<br /> 20 end;<br /> 21 v_nb_empty_tbl:=v_nb_empty_tbl+1;<br /> 22 v_size_empty_tbl:=v_size_empty_tbl+v_bytes;<br /> 23 for idx in (select index_name from user_indexes where table_name=tbl.table_name) loop<br /> 24 begin<br /> 25 select bytes<br /> 26 into v_bytes<br /> 27 from user_segments<br /> 28 where segment_name=idx.index_name<br /> 29 and segment_type='INDEX';<br /> 30 exception when no_data_found then v_bytes:=0;<br /> 31 end;<br /> 32 v_nb_empty_idx:=v_nb_empty_idx+1;<br /> 33 v_size_empty_idx:=v_size_empty_idx+v_bytes;<br /> 34 end loop;<br /> 35 end if;<br /> 36 exception when others then dbms_output.put_line('ERROR : '||tbl.table_name||' --> ORA'||sqlcode||': '||sqlerrm);<br /> 37 end;<br /> 38 end loop;<br /> 39 dbms_output.put_line('Nb empty tables => '||to_char(v_nb_empty_tbl)||' -- Size (Mb) => '||to_char(trunc(v_size_empty_tbl/1024/1024)));<br /> 40 dbms_output.put_line('Nb empty indexes => '||to_char(v_nb_empty_idx)||' -- Size (Mb) => '||to_char(trunc(v_size_empty_idx/1024/1024)));<br /> 41 end;<br /> 42 /<br />Nb empty tables => 47405 -- Size (Mb) => 12360<br />Nb empty indexes => 54788 -- Size (Mb) => 53508<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL><br /><br />Conclusion : we could save 65.8Gb over a database of 78.8Gb, wouldn't it be nice ?Nicolas Gasparottohttps://www.blogger.com/profile/06634519580752248909noreply@blogger.comtag:blogger.com,1999:blog-31216575.post-43393493746391846982009-09-08T20:39:30.189+02:002009-09-08T20:39:30.189+02:00Hi Nuno,
Yes, that was my plan to test it out.
How...Hi Nuno,<br />Yes, that was my plan to test it out.<br />However, I have some issue with this clause "SEGMENT CREATION DEFERRED" especially combined with ALTER TABLE command.<br />See here below :<br />SQL> create table mytable (id number);<br /><br />Table created.<br /><br />SQL> select bytes from user_segments where segment_name='MYTABLE';<br /><br />no rows selected<br /><br />Ok, fine, that is expected, now I MOVE the table :<br />SQL> alter table mytable move;<br /><br />Table altered.<br /><br />SQL> select bytes from user_segments where segment_name='MYTABLE';<br /><br /> BYTES<br />----------<br /> 65536<br /><br />Hey, wait, that should not create the segment according to my table creation statement, right ?<br /><br />Then here we enter into dark side, cannot figure out how put this propery back and remove the created segment (which is what we all want for the thousands of empty table on a Peoplesoft db) :<br />SQL> alter table mytable segment creation deferred;<br />alter table mytable segment creation deferred<br /> *<br />ERROR at line 1:<br />ORA-01735: invalid ALTER TABLE option<br /><br />SQL> alter table mytable move segment creation deferred;<br />alter table mytable move segment creation deferred<br /> *<br />ERROR at line 1:<br />ORA-14133: ALTER TABLE MOVE cannot be combined with other operations<br /><br />If someone got it, I would be happy.<br />Thanks,<br />Nicolas.Nicolas Gasparottohttps://www.blogger.com/profile/06634519580752248909noreply@blogger.comtag:blogger.com,1999:blog-31216575.post-5292341119040095322009-09-08T13:58:19.748+02:002009-09-08T13:58:19.748+02:00Hey Nicolas, I was wondering if you could do some ...Hey Nicolas, I was wondering if you could do some testing on the deferred allocation? <br /><br />I don't have access to Linux at the moment to load 11gr2 on.<br /><br />It's this: <br />- given the deferred segment allocation, I'm thinking: instead of a huge export/import to bring a large schema into it, what if we can change the table segment allocation via a simple ALTER TABLE MOVE?<br /><br />Getting a PL/SQL procedure to do it in the background would be child's play.<br /><br />The manual seems to imply we can:<br /><br />"When you issue an ALTER TABLE ... MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement."<br /><br />And the syntax for ALTER TABLE also allows for the SEGMENT clause to be specified.<br /><br />So, rather than exporting/importing an entire database to get rid of the initial segment(s), we may be able to do it via MOVE with/without ALTER TABLE?<br /><br />Needs to be tested and I don't have access to the required gear. <br /><br />Are you game to do it and publish the results? I'm sure other Peoplesoft folks would love to know the results.Noonshttps://www.blogger.com/profile/07694829378563989648noreply@blogger.com