tag:blogger.com,1999:blog-73918222023-11-16T20:33:03.660+07:00budsus's notesThis page is about my daily activities, especially talk about what I have done with any interesting things, specially in information technology. I hope we can share our knowledge and skill. Thanks :DBudi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.comBlogger72125tag:blogger.com,1999:blog-7391822.post-27555069232148658022008-04-23T21:03:00.009+07:002008-11-13T12:39:27.762+07:00Oracle for Beginner: ORDB with Oracle (III)In this third part of my articles about ORDB with Oracle, I just want to share about a simple example of implementation ORDB in Oracle based on class diagram UML. I want to show you about how to implement aggregation, association and inheritance in Oracle. Here is the class diagram:<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0ZFEQro_am4u_KNFwITOcBJSLOrWVtG48v1MfRDN9YPeaAvt7237DPu6M5Jsn4hQ7c20DgxWmpJb8UScpBWsZZ7Ar-yULrxFmcQ01LBk8EuIEktNICCEd1mGVTGeghX9E-8p44A/s1600-h/UMLClass.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0ZFEQro_am4u_KNFwITOcBJSLOrWVtG48v1MfRDN9YPeaAvt7237DPu6M5Jsn4hQ7c20DgxWmpJb8UScpBWsZZ7Ar-yULrxFmcQ01LBk8EuIEktNICCEd1mGVTGeghX9E-8p44A/s400/UMLClass.png" alt="" id="BLOGGER_PHOTO_ID_5192443131232588130" border="0" /></a><br />Segitiga and Lingkaran class are child class from Bidang class (as parent class). Bidang class has association relationship with Warna class. This association is one direction that means Bidang class has a Warna's type attribute. First, we have to create Warna class:<br /><br /><code>create type warna as object(<br />r number(3),<br />b number(3),<br />g number(3),<br />member function getWarna return varchar<br />);<br />/<br /><br />create type body warna as<br />member function getWarna return varchar is<br />begin<br />return 'Red=' || to_char(self.r) || ', Blue=' || to_char(self.b) || ', Green=' || to_char(self.g);<br />end;<br />end;<br />/</code><br /><br />Warna class has 3 attributes, i.e. r for Red, b for Blue and g for Green, and 1 operation that will return a varchar for the color. Now, we can create Bidang that will have an attribute as an object for Warna class.<br /><br /><code>create type Bidang as object(<br />x number(3),<br />y number(3),<br />color warna,<br />member function getLuas return number,<br />member function getKeliling return number<br />) not final;<br />/<br /><br />create type body Bidang as<br />member function getLuas return number is<br />begin<br />return 0;<br />end;<br />member function getKeliling return number is<br />begin<br />return 0;<br />end;<br />end;<br />/</code><br /><br /><a name='more'></a><br />Bidang class has a coordinate attribute that is presented as x and y attribute. Because of Bidang class will become as parent class for Segitiga and Lingkaran, then in Bidang class must provide function getLuas and getKeliling. Why? We have to do this because in Bidang class could save either Bidang, Segitiga, or Lingkaran objects. So, we have to prepare those method in Bidang class. Next, we will create Segitiga class and Lingkaran class:<br /><br /><code>create or replace type Segitiga under Bidang (<br />alas number(10,3),<br />tinggi number(10,2),<br />overriding member function getLuas return number,<br />overriding member function getKeliling return number<br />);<br />/<br /><br />create or replace type body Segitiga as<br />overriding member function getLuas return number is<br />begin<br />return 0.5 * self.alas * self.tinggi;<br />end;<br />overriding member function getKeliling return number is<br />begin<br />return 3 * self.alas;<br />end;<br />end;<br />/<br /><br />create or replace type Lingkaran under Bidang (<br />r number(10,3),<br />overriding member function getLuas return number,<br />overriding member function getKeliling return number<br />);<br />/<br /><br />create or replace type body Lingkaran as<br />overriding member function getLuas return number is<br />begin<br />return 3.14 * self.r * self.r;<br />end;<br />overriding member function getKeliling return number is<br />begin<br />return 2 * 3.14 * self.r;<br />end;<br />end;<br />/</code><br /><br />As we can look for that command, the methods declaration in Segitiga and Lingkaran class must be declare as OVERRIDING MEMBER FUNCTION, because this method will override parent's methods. To implement 1..* composition relationship between Bidang class and Bangun class, we can create a new type as table of Bidang, and we will declare a new attribut in Bangun class that has type of table of Bidang:<br /><br /><code>create or replace type bidang_tab as table of bidang;<br />/<br /><br />create or replace type Bangun as object(<br />nama varchar(10),<br />mybidang bidang_tab)<br />/</code><br /><br />The final command is to create a nested table of Bangun. With this table, we can make CRUD (Create, Read, Update and Delete) for Bidang and Bangun data.<br /><br /><code>create table bangun_tab of bangun (<br />primary key (nama)<br />) nested table mybidang store as mybangun_tab;</code><br /><br />Yes, now we can try to insert some records to our BANGUN_TAB nested table with simple INSERT command:<br /><br /><code>insert into bangun_tab values ('test1', bidang_tab(segitiga(0, 0, warna(0, 0, 0), 10.0, 20.0), segitiga(100, 100, warna(65, 0, 0), 20.0, 5.0), bidang(20.0, 20.0, warna(0, 0, 0))));<br /><br />insert into bangun_tab values ('test2', bidang_tab(lingkaran(0, 0, warna(255, 255, 255), 20.0), segitiga(200, 200, warna(65, 0, 0), 20.0, 5.0)));</code><br /><br />And of course we can query our above data:<br /><br /><code>SQL> col warna format a25<br />SQL> select nama, b.color.getWarna() "warna", b.getLuas() "Luas", b.getKeliling() "Keliling"<br />2 from bangun_tab, table(bangun_tab.mybidang) b;<br /><br />NAMA warna Luas Keliling<br />---------- ------------------------- ---------- ----------<br />test1 Red=0, Blue=0, Green=0 100 30<br />test1 Red=65, Blue=0, Green=0 50 60<br />test1 Red=0, Blue=0, Green=0 0 0</code><br /><br />I hope this sharing can help all of you to learn about how to implement ORDB in Oracle. It's looked like a simple commands, is it?Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-65175528484065663202008-04-01T20:19:00.001+07:002008-04-02T16:18:16.043+07:00Oracle for Beginner: ORDB with Oracle (II)Continuing my last note, here we will try to use VARRAY() to make multiple value for a attribute of table. One of disadvantage of varray is we have to specify the maximum of element array that we want to save in it.<br /><br />Here an example of how to create a varray type:<br /><br />SQL> create type v_pengajar_t as varray(3) of pengajar_t;<br /><br />Like previous example, we can create table which have one attribute with varray() type.<br /><br />SQL> create table vkursus(<br /> 2 no_kursus char(5) primary key,<br /> 3 nama varchar(30),<br /> 4 pengajar v_pengajar_t<br /> 5 );<br /><br />To insert new data, we can use statement below:<br /><br />SQL> insert into vkursus values ('X01', 'Oracle', v_pengajar_t(pengajar_t('P01', 'Budi'), pengajar_t('P02', 'Wati')));<br /><br />1 row created.<br /><br />SQL> insert into vkursus values ('X02', 'ORDB with Oracle', v_pengajar_t(pengajar_t('P01', 'Budi'), pengajar_t('P03', 'Othie'), pengajar_t('P04','Indah')));<br /><br />1 row created.<br /><br />SQL> commit;<br /><br /><br />To make a query for varray, we can use table() function to create a virtual table from varray field. Here is an example:<br /><br />SQL> select no_kursus, v.nama, p.nama<br /> 2 from vkursus v, table(v.pengajar) p;<br /><br />NO_KU NAMA NAMA<br />----- ------------------------------ ------------------------------<br />X01 Oracle Budi<br />X01 Oracle Wati<br />X02 ORDB with Oracle Budi<br />X02 ORDB with Oracle Othie<br />X02 ORDB with Oracle IndahBudi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-34417467943646312802008-03-27T13:25:00.003+07:002008-03-27T14:16:06.460+07:00Oracle for Beginner: ORDB with Oracle (I)If you want to learn about object relational database in Oracle, the first thing that you have to understand is object oriented concept itself. By the way, in what level of your understanding, let we learn about how to implement a simple Oracle's ORBMS.<br />In Oracle, there are object schema, .i.e., User Define Type (UDT). With this object, we can create any customize type, specially object type. Here is a example:<br /><br />create or replace type pengajar_t as object (<br /> pengajar_id char(5),<br /> nama varchar(30)<br />)<br />/<br /><br />With customize object type, we can use that as a attribute type for a table.<br /><br />create table kursus (<br /> kursus_id char(5) primary key,<br /> nama_kursus varchar(50),<br /> pengajar pengajar_t<br />);<br /><br />If you want to insert or update for that table, here are an example for how to do that:<br /><br />insert into kursus values ('K01', 'Air Blender', pengajar_t('X-01','Avatar'));<br />insert into kursus values ('K02', 'Kamehameha', pengajar_t('X-02','Goku'));<br />update kursus k set k.pengajar.nama = 'Gohan' where k.pengajar.pengajar_id ='X-02';Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-15823007926817398042008-03-23T18:18:00.004+07:002008-03-23T18:48:11.674+07:00OpenSource style for marketingAs you know, Open Source movement have changed the world to make their policies, either for organization or ourself. With this movement, everyone can became a programmer, a documentation maintainer, a tester, or may be only as a end user. I interested about how to make community, like Open Source, to make a marketing more powerful. With involving our customers, I believe that they will give their ideas (like wish list) to make our organization can give services better. I talk this with my client that one important thing to make their business can get the trust from their customers or partners is to make a good relationship with them. This is why CRM or PRM (<span style="font-style: italic;">Partner Relationship Management</span>) concept arise on the surface.<br />How to build a customer community for our organization? Of course the most important thing to realize that is we have to give the best of our service to our partners. After that, we can use the Internet service to capture our customer requirements or needs. Indirectly, if our partner community is strong, they will promote our organization to others. This is a good strategy. And I believe that almost marketing manager had realize this way. But in Indonesia, specially for small-medium enterprise, still don't figure out about this. The big problem is the Internet penetration still low. Based on the <a href="http://www.apjii.or.id/dokumentasi/statistik.php?lang=eng">APJII</a> data, the Internet users in Indonesia at the end of 2005 was estimated around 1.6 million (I thing from 200 million population :D). I thing, we have to build and maintain many communities to develop their local small-business enterprise area to realize the Internet. Many names have arisen , like <span style="font-style: italic;">Internet goes to Village</span>. I still figure out about the cheaper Internet access from villages. May be we can use mess wireless protocol to build local wireless communication with WiFi. Who knows...Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com1tag:blogger.com,1999:blog-7391822.post-53657842401285259342008-02-12T10:52:00.000+07:002008-11-13T12:39:27.985+07:00Make My Desktop more fancyI use fedora core 7 with Gnome and Beryl as the desktop management. To make my Beryl Desktop more fancy, I installed <a href="http://sourceforge.net/projects/mac4lin">Mac4Lin Emerald Theme</a>. This theme really make me more exciting to use Linux Desktop as my operating system in my notebook. To make my Linux Desktop to become most like a Tiger Mac X OS, I installed <a href="https://launchpad.net/awn">Avant Window Navigator</a> too. With Avant, I have a fancy launch pad like Mac X OS in my Desktop. Very impressive.<br /><br />In another thing, in my Linux box, I have installed VMWare Player that playing M$ Windows XP Home Edition (provided by compaq presario V1000 bundled) with SP2 for me. (I have tried VirtualBox to play Fedora Core 8). For my virtual M$ Windows, I want to make the desktop more like Fedora too, so I installed <a href="http://labeb.net.tc/">Fedora Transformation Packet</a>. I think this theme is very impressive for my windows.<br /><br />Here is the example of my desktop with Mac4Lin and Avant :<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ_KVtk6ca52mU4qe1sIcJP2zGu4zSeA5-uSEdDapeEGgUrNBL1FxyYl3hIGJkL8tD5vgWQcDWXnywsFzotZ4XxiqBCDMAIpJxBFFDYy5HiFNsiPHMq_Tz0sKkNH-hah2sEHH6mg/s1600-h/MyLastDesktop.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ_KVtk6ca52mU4qe1sIcJP2zGu4zSeA5-uSEdDapeEGgUrNBL1FxyYl3hIGJkL8tD5vgWQcDWXnywsFzotZ4XxiqBCDMAIpJxBFFDYy5HiFNsiPHMq_Tz0sKkNH-hah2sEHH6mg/s320/MyLastDesktop.png" alt="" id="BLOGGER_PHOTO_ID_5165951081138724178" border="0" /></a>Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com5tag:blogger.com,1999:blog-7391822.post-62251955238434256772008-02-11T20:25:00.003+07:002008-03-23T17:58:55.017+07:00My Favorite mobile IMAt the first time when I changed my phone cell to N73, I have tried to fine a better IM application. First of all, I use mig33 (<a href="http://www.mig33.com/">http://www.mig33.com</a>) . But after a week, I didn't convenient with that. So, I tried to use shMessenger (<a href="http://www.shmessenger.ro/lang_en/index.jsp">http://www.shmessenger.ro/lang_en/index.jsp</a>). This is a good mobile IM application, but unfortunately that application only support Yahoo! IM. I want to use an application with multiple IM protocol. Then, I found fring (<a href="http://www.fring.com/">http://www.fring.com</a>). With early fring, I can use GTalk protocol, SIP, Fring, ICQ, but there were no Y! IM. But with the newest fring version (3.30), this application make me happy because they already support Y! IM protocol. So.. fring is the best mobile IM application for me. Right now. :D<br /><br />Today (the day after I wrote this article), my friend has told me about another better Mobile IM application, i.e. <a href="http://www.nimbuzz.com/">Nimbuzz</a>. Nimbuzz is j2me based application that support multi IM protocol and facebook. I have installed too and want to try it, may be this application can change my favorite, i.e. fring. Let me see...<br /><br />After almost 2 months, I have decided to use Nimbuzz as my mobile messenger applications. :D Sorry fring.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com2tag:blogger.com,1999:blog-7391822.post-54229562881946136242007-11-21T08:31:00.000+07:002007-11-21T08:37:50.152+07:00Some Java OpenSource ProjectsEverytime I need one or more Java packages or components, I always use sourceforge.net or freshmeat.net. Everyone know about this. This site is very useful for me either as system analyst or Java programmer.<br />Today, I have found a good site, i.e. <a href="http://www.java2s.com/Open-Source/Java/CatalogJava.htm">Java Open Source</a>, which provide a list of Java open source projects. May be you already know about this site, but for me I want to note at my blog to share with other.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-74616119890029908352007-10-09T13:18:00.000+07:002008-11-13T12:39:28.068+07:00SQL Tuning AdvisorThis practice is based on http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php#sql_tuning_advisor.<br /><br />SQL> conn / as sysdba<br />SQL> DECLARE<br />2 l_sql VARCHAR2(500);<br />3 l_sql_tune_task_id VARCHAR2(100);<br />4 BEGIN<br />5 l_sql := 'SELECT e.*, d.* ' ||<br />6 'FROM hr.employees e JOIN hr.departments d ON e.department_id = d.department_id ' ||<br />7 'WHERE NVL(employee_id, ''0'') = :empno';<br />8 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (<br />9 sql_text => l_sql,<br />10 bind_list => sql_binds(anydata.ConvertNumber(100)),<br />11 scope => DBMS_SQLTUNE.scope_comprehensive,<br />12 time_limit => 60,<br />13 task_name => 'emp_dept_tuning_task',<br />14 description => 'Tuning task for an EMP to DEPT join query.');<br />15 DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);<br />16 END;<br />17 /<br /><br />SQL> EXEC DBMS_STATS.delete_table_stats('HR', 'EMPLOYEES');<br /><br />SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');<br /><br />In Enterprise Manager, click Advisor Central, you will see your sql tuning task completed. Click that name. In SQL Tuning result page, you can click View Recommendations button to view all of recommendations based on the query.<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixYN_4F-WPVUYtp_GJu4sn-EXi6zaGxXgYmTPrlK5XvGxbY7hp1Ek2nZMBM4EsZtGAOC2iWdTLOa4ld5nR8LeHeIaTnFAjVI_JfnQsjST1eDY-GigcN4SbvlzmKRiPdAryAZ45yA/s1600-h/Screenshot-Oracle+Enterprise+Manager+%28SYS%29+-+Advisor+Central+-+Mozilla+Firefox.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEixYN_4F-WPVUYtp_GJu4sn-EXi6zaGxXgYmTPrlK5XvGxbY7hp1Ek2nZMBM4EsZtGAOC2iWdTLOa4ld5nR8LeHeIaTnFAjVI_JfnQsjST1eDY-GigcN4SbvlzmKRiPdAryAZ45yA/s320/Screenshot-Oracle+Enterprise+Manager+%28SYS%29+-+Advisor+Central+-+Mozilla+Firefox.png" alt="" id="BLOGGER_PHOTO_ID_5119219393084834658" border="0" /></a><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEib48FY_HylVXVZXvLI-8E3swAtSDz_OseicIFXtjTtKDL9e8BiPUkAdhe_6Kj1yHcQPZsVy82xS6n9wgxfAiQ7DZ1C5_5bqGugswldIMOjDBjN-tm2QEQPWXJMOCQF8ac7h_eL5Q/s1600-h/Screenshot-Oracle+Enterprise+Manager+%28SYS%29+-+SQL+Tuning+Results:emp_dept_tuning_task+-+Mozilla+Firefox.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEib48FY_HylVXVZXvLI-8E3swAtSDz_OseicIFXtjTtKDL9e8BiPUkAdhe_6Kj1yHcQPZsVy82xS6n9wgxfAiQ7DZ1C5_5bqGugswldIMOjDBjN-tm2QEQPWXJMOCQF8ac7h_eL5Q/s320/Screenshot-Oracle+Enterprise+Manager+%28SYS%29+-+SQL+Tuning+Results:emp_dept_tuning_task+-+Mozilla+Firefox.png" alt="" id="BLOGGER_PHOTO_ID_5119220075984634738" border="0" /></a><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgZtIvwiNCnoGmVf4Z4LxRPDWmAvtjYKo_ODO7Tgm1J4BcgPWWzsmoCzjjJ6X_wvSdqAjg7tScm6kbuUtJ5u7C6u28M7PuSSFYQHxnk5oY-ilKuRXp1hyUORKs1NRMMRc5x4iFqA/s1600-h/Screenshot-Oracle+Enterprise+Manager+%28SYS%29+-+Recommendations+for+SQL+ID:0ft45ywywcm1q+-+Mozilla+Firefox.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgZtIvwiNCnoGmVf4Z4LxRPDWmAvtjYKo_ODO7Tgm1J4BcgPWWzsmoCzjjJ6X_wvSdqAjg7tScm6kbuUtJ5u7C6u28M7PuSSFYQHxnk5oY-ilKuRXp1hyUORKs1NRMMRc5x4iFqA/s320/Screenshot-Oracle+Enterprise+Manager+%28SYS%29+-+Recommendations+for+SQL+ID:0ft45ywywcm1q+-+Mozilla+Firefox.png" alt="" id="BLOGGER_PHOTO_ID_5119221342999987090" border="0" /></a>Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-57757023256423703032007-10-09T12:12:00.000+07:002007-10-09T12:45:23.396+07:00SQLAccess AdvisorSQL> conn / as sysdba<br />SQL> BEGIN<br /> 2 DBMS_ADVISOR.quick_tune(<br /> 3 advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,<br /> 4 task_name => 'test_quick_tune',<br /> 5 attr1 => 'SELECT * FROM hr.employees WHERE upper(last_name) = ''SMITH''');<br /> 6 END;<br /> 7 /<br /><br />From Enterprise Manager, click Advisor Central. From the page, you can see test_quick_tune. Click that task name. In the page Result for Task, click the Recommendation tab. You will see a recommendation for tuning your index based on your query.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-16563432742595342872007-10-09T09:10:00.000+07:002008-11-13T12:39:28.463+07:00Using Segment AdvisorFor each segment, Oracle will put a boundary (i.e. Highwater Mark) that will separate between used blocks and free blocks. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. In normal DB operations, the high water mark only moves upwards, not downwards. If there is a lot of free space below the high water mark, one might consider to use alter table move statements. In another words, we have to shrink or compact the segment to move HWM downward. This condition will make full scan segment more faster.<br />We will try to use segment advisor to make our segment can be optimized based on the statistical data of our segment.<br /><br />SQL> create table uji2 (id number(4), nilai varchar(1000));<br /><br />Table created.<br /><br />SQL> begin<br />2 for i in 1..10000 loop<br />3 insert into uji2 values (i, lpad('*', 1000, '*'));<br />4 end loop;<br />5 end;<br />6 /<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> update uji2 set data = null where mod(id, 5) = 0;<br /><br />200 rows updated.<br /><br />SQL> commit;<br /><br />Commit complete.<br /><br />SQL> set autotrace traceonly<br />(if you have any problems with this command, please refer to <a href="http://www.samoratech.com/tips/swenableautotrace.htm">http://www.samoratech.com/tips/swenableautotrace.htm</a>)<br /><br />SQL> select count(*) from uji2;<span style="font-family:courier new;"></span><br />(Summary)<br /><br />SQL> alter table uji2 enable row movement;<br /><br />Table altered.<br /><br />SQL> alter table uji2 shrink space;<br /><br />Table altered.<br /><br />SQL> select count(*) from uji2;<br />(Summary)<br /><span style="font-family:courier new;"></span><br />Based on our practice, you can see that after shrink space, the amount of blocks that was reading have decreased, because the HWM was downwarded.<br /><br />Now, we will use Segment Advisor from Enterprise Manager to get an advice about our segment.<br /><br />SQL> create table uji2 (id int, data char(1000));<br /><br />Table created.<br /><br />SQL> insert into uji2 select object_id, lpad('*', 1000, '*')<br />2 from all_objects;<br /><br />SQL> update uji2 set data = null where mod(id, 5) = 0;<br /><br />Go to Enterprise Manager, and click Performance tab. In bottom section, click Advisor Central, and then click Segment Advisor. In Segment Advisor page, click <span class="xh">Schema Objects, and click Next. Click Add button to add our table, i.e. uji2.<br /><br /></span><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrmCNqq29OnXkBE5dMlaNnn0orULXMEpNkGR6cqEKmdx7k1mF5FLHKYwAYeMwLrjVC6DKy0BP3dkBbze_lCxO5rz0H8BMWdKepzqxlts99KhxfydtTEpxa89qdYMXxS7Ga8bKEpg/s1600-h/SegmentAdvisor.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrmCNqq29OnXkBE5dMlaNnn0orULXMEpNkGR6cqEKmdx7k1mF5FLHKYwAYeMwLrjVC6DKy0BP3dkBbze_lCxO5rz0H8BMWdKepzqxlts99KhxfydtTEpxa89qdYMXxS7Ga8bKEpg/s400/SegmentAdvisor.png" alt="" id="BLOGGER_PHOTO_ID_5119176134174229266" border="0" /></a><span class="xh">Click Next, and you can click Submit button directly. Here is example of the result :<br /></span><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfQazAxejlZHfrUsAHAmdSsV7gxfa0V19eVXPzpOoaXEr6Wym8CsPttMsEYjD1P8m1Ns6g_xr_7bzudwObdBCF5O_G7H4HPLdNRujUpPyJtWSN67UGl1CRGNFevbqWiuE6ESVt_A/s1600-h/SegmentAdvisor2.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfQazAxejlZHfrUsAHAmdSsV7gxfa0V19eVXPzpOoaXEr6Wym8CsPttMsEYjD1P8m1Ns6g_xr_7bzudwObdBCF5O_G7H4HPLdNRujUpPyJtWSN67UGl1CRGNFevbqWiuE6ESVt_A/s320/SegmentAdvisor2.png" alt="" id="BLOGGER_PHOTO_ID_5119177985305133874" border="0" /></a>Click the name of latest Segment Advisor task. In the page of Segment Advisor task, click Recomendataion Details.<br /><br /><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjROhMNRsv67_Yip8fH42me7nRt4_Nw2abkmD_qD0b814Z5PmOy9E080QyIJiRkvm8L9BgzOpCCTIjDLXj8JiU7L5CkMh0r9psM_j0ipVCEBVJGBo0e_LXvhHYjg5W60YhYwKc-Qw/s1600-h/SegmentAdvisor3.png"><img style="margin: 0px auto 10px; display: block; text-align: center; cursor: pointer;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjROhMNRsv67_Yip8fH42me7nRt4_Nw2abkmD_qD0b814Z5PmOy9E080QyIJiRkvm8L9BgzOpCCTIjDLXj8JiU7L5CkMh0r9psM_j0ipVCEBVJGBo0e_LXvhHYjg5W60YhYwKc-Qw/s320/SegmentAdvisor3.png" alt="" id="BLOGGER_PHOTO_ID_5119179926630351698" border="0" /></a><br />Now, you can click Shrink button to make segment compact.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-39325272251167262282007-09-26T08:20:00.002+07:002007-09-26T08:46:15.898+07:00Default Temporary TablespaceWhen I was teaching about temporary tablespace, I forgot about how to know where is the default temporary tablespace that is used in current Oracle database? After surfed arround the digital world, I have found some articles that give me a new knowledge. Here are the practice :<br /><br />prompt> sqlplus /nolog<br />SQL> conn / as sysdba<br />SQL> col property_value format a20<br />SQL> select property_name, property_value from database_properties;<br /><br />From above query, we can get all of database properties include default_temp_tablespace. If you want to change the default temporary tablespace, you can take a little steps below (my assumption is your database use OMF):<br /><br />SQL> conn / as sysdba<br />SQL> create temporary tablespace temp1 datafile 20M;<br />SQL> alter database default temporary tablespace temp1;<br />SQL> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';<br /><br />If you want to know about the temporary status and space information, you can query with this:<br />select * from v$tempfile;Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-6423880007145999312007-09-26T08:03:00.000+07:002007-09-26T08:16:42.906+07:00Another FGAWhen I tried to find more new thing about FGA (Fine Grained Audit), I found a simple practice to define a simple auditing based on a condition for a table. Here are the steps:<br /><br />prompt> sqlplus /nolog<br />SQL> conn / as sysdba<br />SQL> execute dbms_fga.add_policy('HR', 'EMPLOYEES', 'EMPDEPT_POLICY', 'department_id > 90');<br />SQL> analyze table hr.employees compute statistics;<br />SQL> conn hr/hr<br />SQL> select first_name from employees where department_id = 20;<br />SQL> select first_name from employees where department_id = 100;<br />SQL> select distinct deparment_id from employees;<br />SQL> conn / as sysdba<br />SQL> select timestamp#, obj$name, policyname, scn, lsqltext from fga_log$;<br />SQL> execute dbms_fga.drop_policy('HR', 'EMPLOYEES', 'EMPDEPT_POLICY');Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-23411548400435013202007-08-22T13:31:00.000+07:002007-08-22T14:20:29.221+07:00Oracle for Beginner: A Simple Audit$ sqlplus /nolog<br />SQL> conn / as sysdba<br />SQL> alter system set audit_trail=db scope=spfile;<br />SQL> shutdown immediate;<br />SQL> startup<br />SQL> conn / as sysdba<br />SQL> select name, value from v$parameter where name like 'audit%';<br />SQL> @$ORACLE_HOME/rdbms/admin/cataudit.sql<br />SQL> audit connect by budsus whenever not successful;<br />SQL> conn budsus/sdasdasd<br />SQL> conn budsus/xhsjd<br />SQL> conn / as sysdba<br />SQL> desc aud$;<br />SQL> select * from aud$;<br /><br />SQL> grant select, insert, update on hr.departments to budsus;<br />SQL> Audit Update, Delete, Insert On hr.departments By Access Whenever Successful;<br />SQL> conn budsus/xxx<br />SQL> insert into hr.departments values (... <please> ... );<br />SQL> conn / as sysdba<br />SQL> col comment$text format a20<br />SQL> select statement, timestamp#, userid, comment$text from aud$ where userid='BUDSUS'<br />SQL> select * from select * from dba_common_audit_trail;<br />SQL> noaudit update, delete, insert on hr.departments;</please>Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com1tag:blogger.com,1999:blog-7391822.post-46956930194519138842007-08-18T16:10:00.000+07:002007-08-29T18:50:00.730+07:00Oracle for Beginner: Knowing your Linux Kernel Parameter for OracleIf you want to know about how to install Oracle 10g on your Linux, there are some interesting articles that may be can help you to inform step by step installation. Here are some of those:<br /><ul><li><a href="http://www.oracle-base.com/articles/10g/Articles10g.php">Oracle-Base</a> (this site is a famous one)</li><li><a href="http://www.dizwell.com/prod/node/52?page=0%2C0">Dizwell Informatics</a> (installing Oracle10g on Ubuntu)<br /></li><li><a href="http://www.oracle.com/technology/tech/linux/install/index.html">OTN Oracle-on-Linux</a>.</li></ul>From those guides, before installation is started, there are some kernel parameters that have to be setup in /etc/sysctl.conf files. Here are from Oracle-Base:<br /><pre>kernel.shmall = 2097152<br />kernel.shmmax = 2147483648<br />kernel.shmmni = 4096<br /># semaphores: semmsl, semmns, semopm, semmni<br />kernel.sem = 250 32000 100 128<br />fs.file-max = 65536</pre> kernel.shm* are parameters that is used to configure shared memory in Linux. Shared memory is a system resource that is used for interprocess communication. With this technique, two or more processes share a single chunk of memory to communicate between them. Shared memory is allocated in segments.<br /><ul><li><span style="font-family:courier new;">shmall</span> : define the maximum amount of shared memory that may be in use at any time on the system.</li><li><span style="font-family:courier new;">shmmax</span> : define the maximum size of each shared memory segment (max. value is 4GB).</li><li><span style="font-family:courier new;">shmmni</span> : define the maximum number of shared memory segments on the system.<br /></li></ul>Semaphore is an important mechanism for controlling access to resources. Oracle instance use semaphore to control access to shared memory. Here is the kernel parameter which is used to configure the semaphore:<br /><pre># semaphores: semmsl, semmns, semopm, semmni<br />kernel.sem = 250 32000 100 128</pre><ul><li><span style="font-family:courier new;">semmsl</span>: maximum number of semaphores per set.</li><li><span style="font-family:courier new;">semmns</span> : total number of semaphores in the system.</li><li><span style="font-family:courier new;">semopm</span> : maximum number of operations per semop call.</li><li><span style="font-family:courier new;">semmni</span> : maximum number of semaphore sets.<br /></li></ul>fs.file-max is a file handle parameter that determines the maximum number of files that each process can open. Default value is 8192 and the maximum value is 65536.<br /><br />For more information, please refer to <a href="http://www.puschitz.com/TuningLinuxForOracle.shtml">puschtz</a> and <a href="http://www.dba-oracle.com/t_linux_unix_memory_kernel_parameters.htm">dba-oracle.com</a> .Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com1tag:blogger.com,1999:blog-7391822.post-60816644357088690022007-08-06T16:20:00.001+07:002007-08-06T16:22:48.442+07:00System 7 SimulationIf you want to look and try an old Mac Operating System, i.e. System 7, you can visit <a href="http://myoldmac.net/webse-e-flash.htm">WebSE</a>. This simulation is very good. They create it using Flash. Very Interactive. You have to try it.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com3tag:blogger.com,1999:blog-7391822.post-39945995335748734852007-08-06T09:42:00.000+07:002008-11-13T12:39:28.728+07:00My New Book<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggkhS4jtxYH_wRm0hpy002LKHuVThloiPaf66952gHFyyk6KUMWpk8ZTJ_T6Ib7JbvxYiTSRKoVf6uOFC-TCq4dheTSdve9qTsTNcNWZYJbPAOpr7_fX_7hTBXFaY1d7PJRmPZhw/s1600-h/bukuOracle.JPG"><img style="margin: 0pt 10px 10px 0pt; float: left; cursor: pointer; width: 87px; height: 129px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggkhS4jtxYH_wRm0hpy002LKHuVThloiPaf66952gHFyyk6KUMWpk8ZTJ_T6Ib7JbvxYiTSRKoVf6uOFC-TCq4dheTSdve9qTsTNcNWZYJbPAOpr7_fX_7hTBXFaY1d7PJRmPZhw/s200/bukuOracle.JPG" alt="" id="BLOGGER_PHOTO_ID_5095454147783812706" border="0" /></a><br />This month, my newest book has been released (in Bahasa Indonesia). In this book, I wrote about Oracle10g XE Database basic administration and talk about how to build an application to access Oracle10g XE Database in four computer languages, i.e. Java, PHP, Ruby on Rails and Oracle Application Express. In this book, I explained about how to build a program that access a BLOB data type in OracleXE database. For more detail, please visit <a href="http://budsus.wordpress.com/">beranda budsus</a>.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-62552136708728230982007-08-05T13:43:00.000+07:002007-08-05T14:11:03.172+07:00Oracle for Beginner: Create External TableIn Oracle 10g, there is a data pump module that handle almost moving data from database Oracle to another database or external storage. Except data pump, there is a oracle loader that can be used to create and access an external data file (csv) as a regular table. In this article we will try to create an external table with data pump and oracle loader. This is a very simple thing that you can do easily in Oracle 10g.<br /><br />First, we will try to create external table with Oracle data pump module :<br /><br />$ sqlplus /nolog<br />SQL> conn / as sysdba<br />SQL> create directory mylab as 'your/data/path';<br /><pre>SQL> create table dept(department_name)<br />2 organization external<br />3 (<br />4 type ORACLE_DATAPUMP<br />5 DEFAULT DIRECTORY mylab<br />6 LOCATION ('ware.exp')<br />7 )<br />8 AS select distinct department_name from hr.departments;<br /></pre><br />SQL> select * from dept;<br /><br />Next, in same manner, we can access an external csv as a regular table in Oracle database using Oracle Loader:<br /><ul><li>Create a csv file. For example we will create <span style="font-style: italic;">kota.dat</span> with the following values:</li><pre>Jakarta,DKI<br />Surabaya,Jawa Timur<br />Semarang,Jawa Tengah<br />Yogyakarta,DIY<br />Malang,Jawa Timur<br />Bandung,Jawa Barat</pre><br /><li>login to your database as sysdba, create a directory object as a map for your data directory, and create an external table:<br /></li></ul>$ sqlplus /nolog<br />SQL> conn / as sysdba<br />SQL> create directory mydata as 'your/data/path';<br /><pre>SQL> create table kota(kota varchar(50), propinsi varchar(50))<br /> 2 organization external<br /> 3 (type oracle_loader default directory mydata<br /> 4 access parameters<br /> 5 ( records delimited by newline<br /> 6 badfile mydata:'kota%a_%p.bad'<br /> 7 logfile mydata:'kota%a_%p.log'<br /> 8 fields terminated by ','<br /> 9 missing field values are null<br />10 (kota, propinsi)<br />11 )<br />12 location ('kota.dat') );</pre><br />SQL> select * from kota;<br /><br />I hope you can find a new experience with Oracle Database.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-2240591698428483382007-08-05T13:31:00.000+07:002007-08-05T14:21:10.165+07:00Fixing vmware-config on FC7I just want to share about vmware-config (vmware player) error fixing when I tried to run it on my FC7 with kernel 2.6.22.1-41. This <a href="http://www.vmware.com/community/thread.jspa?messageID=696176">discussion</a> is very helpful for me. I just want to rewrite again in my blog, so if I forget about this, I can just visit this my note.<br /><ul><li>download the patch from <a href="http://www.demorecorder.com/download/Vmware-patch-for-2.6.22.1/patch-vmnet-for-linux-2.6.22.1.gz">here</a>.<br /></li><li>follow these instructions :</li><ul><li>cd /usr/lib/vmware/modules/source<br /></li><li>cp vmnet.tar vmnet.tar.orig<br /></li><li>tar xvpf vmnet.tar<br /></li><li>zcat <span style="font-style: italic;">yourdownloaddir</span>/patch-vmnet-for-linux-2.6.22.1.gz | patch -p4<br /></li><li>rm vmnet.tar; tar cvf vmnet.tar vmnet-only<br /></li></ul><li>run /usr/bin/vmware-config.pl again. Now, I can configure my vmware network modul on vmware player clearly.<br /></li></ul>It's good thing that vmware-player can run very well on my FC7. Thanks.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com1tag:blogger.com,1999:blog-7391822.post-32823359095765069742007-07-29T13:25:00.000+07:002007-08-22T13:29:04.819+07:00Oracle for Beginner: Fine Grained Access (FGA)SQL> conn / as sydba<br />SQL> alter system set Event="28131 trace name context forever" scope=spfile;<br />SQL> shutdown immediate;<br />SQL> startup<br />SQL> conn / as sysdba<br />SQL> show parameter event;<br />SQL> create user test identified by oracle;<br />SQL> grant connect, resource, create any context, administer database trigger to test;<br />SQL> grant execute on dbms_rls to test;<br />SQL> conn test/oracle<br />SQL> create table manager(dep_id number(3) primary key, nama_manajer varchar(30));<br />SQL> create table karyawan(dep_id number(3), nama_kary varchar(30), primary key(dep_id, nama_kary) );<br />SQL> insert into manager values (10, 'BUDSUS');<br />SQL> insert into manager values (20, 'HR');<br />SQL> commit;<br />SQL> insert into karyawan values (10, 'ANI');<br />SQL> insert into karyawan values (10, 'WATI');<br />SQL> insert into karyawan values (20, 'ANTOK');<br />SQL> commit;<br />SQL> create table orders(id number(4) primary key, dep_id number(3), tgl date, karyawan varchar(30) default user);<br />SQL> insert into orders values (1, 10, '01-AUG-07', 'ANI');<br />SQL> insert into orders values (2, 10, '02-AUG-07', 'ANI');<br />SQL> insert into orders values (3, 10, '02-AUG-07', 'WATI');<br />SQL> insert into orders values (4, 20, '02-AUG-07', 'ANTOK');<br />SQL><br /><br />Here is the procedure that will be called when a user login to the database:<br /><br /><span style="color: rgb(0, 0, 153);">create or replace procedure set_testapp_role(</span><br /><span style="color: rgb(0, 0, 153);"> p_user varchar2 default sys_context('userenv', 'session_user')) is</span><br /><br /><span style="color: rgb(0, 0, 153);">--parameter berisi username,</span><br /><span style="color: rgb(0, 0, 153);">--jika tidak diberikan, username akan diambil dari sys_context()</span><br /><span style="color: rgb(0, 0, 153);">--yang mengembalikan user session saat ini</span><br /><br /><span style="color: rgb(0, 0, 153);">--v_ctx menyimpan nama application context.</span><br /><span style="color: rgb(0, 0, 153);">v_ctx varchar2(16) := 'testapp_ctx';</span><br /><br /><span style="color: rgb(0, 0, 153);">v_is_manajer number;</span><br /><span style="color: rgb(0, 0, 153);">v_dep_id number;</span><br /><span style="color: rgb(0, 0, 153);">v_is_kary number;</span><br /><br /><span style="color: rgb(0, 0, 153);">begin</span><br /><span style="color: rgb(0, 0, 153);"> --set variabel application context "username" dengan user yang diberikan</span><br /><span style="color: rgb(0, 0, 153);"> dbms_session.set_context(v_ctx, 'username', p_user);</span><br /> <br /><span style="color: rgb(0, 0, 153);"> select count(*) into v_is_manajer from test.manajer where nama_manajer=p_user;</span><br /><span style="color: rgb(0, 0, 153);"> select count(*) into v_is_kary from test.karyawan where nama_kary=p_user;</span><br /> <br /><span style="color: rgb(0, 0, 153);"> --jika user adalah TEST,</span><br /><span style="color: rgb(0, 0, 153);"> -- maka beri role APP_OWNER.</span><br /> <br /><span style="color: rgb(0, 0, 153);"> if (p_user=sys_context('userenv','current_schema')) then</span><br /><span style="color: rgb(0, 0, 153);"> dbms_session.set_context(v_ctx,'rolename','APP_OWNER');</span><br /><span style="color: rgb(0, 0, 153);"> elsif (v_is_manajer=1) then</span><br /><span style="color: rgb(0, 0, 153);"> --jika user manajer, beri role APP_ADMIN dan ambil</span><br /><span style="color: rgb(0, 0, 153);"> --dep_id</span><br /> <br /><span style="color: rgb(0, 0, 153);"> select dep_id into v_dep_id from manajer where nama_manajer=p_user;</span><br /> <br /><span style="color: rgb(0, 0, 153);"> dbms_session.set_context(v_ctx,'rolename','APP_ADMIN');</span><br /><span style="color: rgb(0, 0, 153);"> dbms_session.set_context(v_ctx,'depid',v_dep_id);</span><br /> <br /><span style="color: rgb(0, 0, 153);"> elsif (v_is_kary=1) then</span><br /><span style="color: rgb(0, 0, 153);"> --jika user karyawan, beri role APP_USER</span><br /><span style="color: rgb(0, 0, 153);"> dbms_session.set_context(v_ctx,'rolename','APP_USER');</span><br /><span style="color: rgb(0, 0, 153);"> else</span><br /><span style="color: rgb(0, 0, 153);"> --jika user tidak berwewenang</span><br /><span style="color: rgb(0, 0, 153);"> dbms_session.set_context(v_ctx,'rolename','NOT_AUTHORIZED');</span><br /><span style="color: rgb(0, 0, 153);"> end if;</span><br /><span style="color: rgb(0, 0, 153);">end;</span><br /><span style="color: rgb(0, 0, 153);">/</span><br /><br />We have to create a context to save some global variables :<br /><br /><span style="color: rgb(0, 0, 153);">create or replace context testapp_ctx using set_testapp_role;</span><br /><span style="color: rgb(0, 0, 153);">/</span><br /><br />We will call set_testapp_role procedure for each time a user login to database. To make that thing can run, we have to create a trigger that will be run for each time a login happened:<br /><br /><span style="color: rgb(0, 0, 153);">create or replace trigger test_logon_trigger after logon on database</span><br /><span style="color: rgb(0, 0, 153);">begin</span><br /><span style="color: rgb(0, 0, 153);"> set_testapp_role;</span><br /><span style="color: rgb(0, 0, 153);">end;</span><br /><span style="color: rgb(0, 0, 153);">/</span><br /><br />Here is a function that will return a condition string for a user session. It's mean, when a user is login to database and want to access ORDERS table, the system will make a restriction for each query from the active user:<br /><br /><span style="color: rgb(0, 0, 153);">create or replace function testapp_security_function (</span><br /><span style="color: rgb(0, 0, 153);"> p_schema varchar2, p_object varchar2) return varchar2 is</span><br /><span style="color: rgb(0, 0, 153);">begin</span><br /><span style="color: rgb(0, 0, 153);"> if (sys_context('testapp_ctx','rolename')='APP_OWNER') then</span><br /><span style="color: rgb(0, 0, 153);"> --tidak ada kondisi yang diberkan</span><br /><span style="color: rgb(0, 0, 153);"> return '';</span><br /><br /><span style="color: rgb(0, 0, 153);"> elsif (sys_context('testapp_ctx','rolename')='APP_ADMIN') then</span><br /><span style="color: rgb(0, 0, 153);"> --kondisi query dibatasi untuk data-data departemen dimana</span><br /><span style="color: rgb(0, 0, 153);"> --user sekarang sebagai manajernya (APP_ADMIN)</span><br /><span style="color: rgb(0, 0, 153);"> return 'dep_id=sys_context(''testapp_ctx'',''depid'')';</span><br /><br /><span style="color: rgb(0, 0, 153);"> elsif ( sys_context('testapp_ctx','rolename')='APP_USER') then</span><br /><span style="color: rgb(0, 0, 153);"> --untuk role APP_USER</span><br /><span style="color: rgb(0, 0, 153);"> return 'karyawan=sys_context(''testapp_ctx'',''username'') and '||</span><br /><span style="color: rgb(0, 0, 153);"> 'dep_id = (select dep_id from test.karyawan ' ||</span><br /><span style="color: rgb(0, 0, 153);"> 'where nama_kary=sys_context(''testapp_ctx'',''username''))';</span><br /><br /><span style="color: rgb(0, 0, 153);"> else</span><br /><span style="color: rgb(0, 0, 153);"> --untuk NOT_AUTHORIZED</span><br /><span style="color: rgb(0, 0, 153);"> return '1=2';</span><br /><span style="color: rgb(0, 0, 153);"> end if;</span><br /><span style="color: rgb(0, 0, 153);">end;</span><br /><span style="color: rgb(0, 0, 153);">/</span><br /><br />Then, we add a new security policy to implement FGA :<br /><br /><span style="color: rgb(0, 0, 153);">declare</span><br /><span style="color: rgb(0, 0, 153);">begin</span><br /><span style="color: rgb(0, 0, 153);">DBMS_RLS.ADD_POLICY (</span><br /><span style="color: rgb(0, 0, 153);">object_schema => 'TEST',</span><br /><span style="color: rgb(0, 0, 153);">object_name => 'ORDERS', policy_name => 'TESTAPP_POLICY',</span><br /><span style="color: rgb(0, 0, 153);">function_schema => 'TEST',</span><br /><span style="color: rgb(0, 0, 153);">policy_function => 'TESTAPP_SECURITY_FUNCTION',</span><br /><span style="color: rgb(0, 0, 153);">statement_types => 'SELECT,UPDATE,INSERT,DELETE',</span><br /><span style="color: rgb(0, 0, 153);">update_check => TRUE,</span><br /><span style="color: rgb(0, 0, 153);">enable => TRUE,</span><br /><span style="color: rgb(0, 0, 153);">static_policy => FALSE);</span><br /><span style="color: rgb(0, 0, 153);">end;</span><br /><span style="color: rgb(0, 0, 153);">/</span><br /><br />Let's try our FGA example:<br /><br />SQL> conn / as sysdba<br />SQL> create user budsus identified by oracle;<br />SQL> grant connect to budsus;<br />SQL> create user ani identified by oracle;<br />SQL> grant connect to ani;<br />SQL> conn test/oracle<br />SQL> grant select, update, delete, insert on orders to budsus;<br />SQL> grant select, update, delete, insert on orders to ani;<br /><br />SQL> conn ani/oracle<br />SQL> select * from test.orders;<br /><br /> ID DEP_ID TGL KARYAWAN<br />---------- ---------- --------- ------------------------------<br /> 1 10 01-AUG-07 ANI<br /> 2 10 02-AUG-07 ANI<br /><br />SQL> insert into test.orders values (10,20,'03-AUG-07','ANI');<br />insert into test.orders values (10,20,'03-AUG-07','ANI')<br /> *<br />ERROR at line 1:<br />ORA-28115: policy with check option violation<br />SQL> insert into test.orders values (10,10,'03-AUG-07','ANI');<br /><br />1 row created.<br /><br />SQL> conn budsus/oracle<br />Connected.<br />SQL> select * from test.orders;<br /><br /> ID DEP_ID TGL KARYAWAN<br />---------- ---------- --------- ------------------------------<br /> 1 10 01-AUG-07 ANI<br /> 2 10 02-AUG-07 ANI<br /> 3 10 02-AUG-07 WATI<br /> 10 10 03-AUG-07 ANIBudi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com1tag:blogger.com,1999:blog-7391822.post-26204634548990935592007-07-26T00:46:00.000+07:002007-07-31T20:34:12.915+07:00Oracle for Beginner: 'Snapshot too old' a simple practiceIn Oracle, there is a undo tablespace (is controlled by undo_tablespace parameter) that is used by oracle to store old image of updated rows. With this tablespace, you can rollback to cancel the transaction. In a undo tablespace, there are some undo segments that is controlled by oracle automatically (it is recommended that undo_management init parameter has value AUTO). For each old row images will be retained at undo segment for 900 seconds (undo_retention parameter), after that the block for old images can be overwritten by another old row images. If the undo tablespace is too small to retain old row images, sometime you will get an error that will tell you about there is no enough space for undo data. Here is an example of that error:<br /><br />ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS_2'<br /><br />The "snapshot too old" error can be happen if some old row images, that is used by another query for read consistency, have been overwritten. In this article, from another resources too, I would like to share about a simple practice to help us to understand about this error.<br /><br />$ sqlplus /nolog<br /><span style="color: rgb(204, 0, 0);">SQL> conn / as sysdba</span><br /><span style="color: rgb(204, 0, 0);">SQL> create undo tablespace undotbs_2 datafile size 1M;</span><br /><br />Tablespace created.<br /><br /><span style="color: rgb(204, 0, 0);">SQL> alter system set undo_tablespace='UNDOTBS_2';</span><br /><br />System altered.<br /><br /><span style="color: rgb(204, 0, 0);">SQL> create user budsus identified by oracle quota 10M on users;</span><br /><span style="color: rgb(204, 0, 0);">SQL> grant connect, create table, create any index to budsus;</span><br /><br />Grant succeeded.<br /><span style="color: rgb(204, 0, 0);">SQL> conn budsus/oracle</span><br />Connected.<br /><span style="color: rgb(204, 0, 0);">SQL> create table siswa (id number(5), nama varchar(100));</span><br /><br /><span style="color: rgb(204, 0, 0);">SQL> create table dump(tmp varchar(100));</span><br /><code><br />SQL> begin<br />2 for i in 1..5000 loop<br />3 insert into siswa values (i, 'siswa terbaru ' || i);<br />4 if mod(i, 100) = 0 then<br />5 insert into dump values ('teeeeeeeemmmmmmmmmppppppppp');<br />6 commit;<br />7 end if;<br />8 end loop;<br />9 commit;<br />10 end;<br /><br />SQL> declare<br />2 cursor crs_siswa is select rowid, siswa.* from siswa where id < 250; <br />3 begin <br />4 for v_siswa in crs_siswa loop <br />5 update dump set tmp = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaa'; <br />6 update dump set tmp = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbb'; <br />7 update dump set tmp = 'ccccccccccccccccccccccccccccc'; <br />8 update siswa set nama = 'siswa terupdate ' || v_siswa.id where v_siswa.rowid = rowid; <br />9 commit;<br />10 end loop;<br />11 end;<br />12 /<br />...<br />ORA-01555: snapshot too old: rollback segment number 15 with name "_SYSSMU15$" too small <br /></code><br /><span style="color: rgb(204, 0, 0);">SQL> conn / as sysdba;</span><br /><span style="color: rgb(204, 0, 0);">SQL> alter system set undo_tablespace='UNDOTBS1';</span><br /><span style="color: rgb(204, 0, 0);">SQL> drop tablespace undotbs_2;</span><br /><span style="color: rgb(204, 0, 0);">SQL> drop table siswa;</span><br /><span style="color: rgb(204, 0, 0);">SQL> drop table dump;</span><br /><span style="color: rgb(204, 0, 0);">SQL> quit</span><br /><br />Thanks.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com3tag:blogger.com,1999:blog-7391822.post-33800451722874560702007-07-25T09:49:00.000+07:002007-07-26T00:49:40.644+07:00Language Translator ApplicationAltough this application is the old one (now with a good service), but this very helpful for me. I use gnome-translate that is created by Jean-Yves Lefort. Actually I use this application for make translation for a site from one language to other language. Sometime I use this to translate from English-Indonesia vice versa. Unfortunately, to use this application, we should have a Internet connection directly without proxy (based on my experience). Please visit <a href="http://www.nongnu.org/libtranslate/gnome-translate/">GNOME Translate</a> web site to get more information and more application from the Author.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-29831989283562578692007-07-23T15:56:00.000+07:002007-07-23T16:45:16.364+07:00Oracle for Beginner: Deferrable ConstraintIn this article I describe a simple example of deferrable constraint. With this feature, we can make the validation of the constraint process deferred until a <i>commit</i> is done. Sometime we need this condition when we try to load data from external file to our table.<br /><br />SQL> create table testing(nilai number(5) check ( nilai in (10, 20, 30) ) deferrable initially deferred );<br />SQL> alter session set constraint = deferred;<br />SQL> insert into testing values(100);<br /><span style="color: rgb(51, 51, 255);">(there are no errors! why?)</span><br />SQL> insert into testing values(20);<br />SQL> commit;<br /><span style="color: rgb(51, 51, 255);">(there is an error, because there is data 100 that is not invalid for our check constraint)</span><br />SQL> select * from testing;<br />SQL> alter session set constraint = immediate;<br />SQL> insert into testing values (100);<br /><span style="color: rgb(51, 51, 255);">(immediately, oracle will check the new data with check constraint)</span><br />SQL> insert into testing values (10);<br />SQL> commit;<br /><br />There is another ways to manage a constraint. We can change the status of constraint. There are for kind of status: enable (validate), enable novalidate, disable validate, disable (novalidate). In this moment, we will try to change status of our constraint in table TESTING:<br /><br />SQL> desc user_constraints;<br />SQL> select constraint_name from user_constraints where table_name = 'TESTING';<br /><span style="color: rgb(51, 51, 255);">(with this query, you can obtain the constraint name for TESTING table. Please note that constraint_name)</span><br />SQL> alter table testing modify constraint <i>const_name</i> disable;<br />SQL> insert into testing values(50);<br /><span style="color: rgb(51, 51, 255);">(there are no problems, because constraint is disabled)</span><br />SQL> insert into testing values(30);<br />SQL> commit;<br />SQL> alter table testing modify constraint <span style="font-style: italic;">const_name</span> enable novalidate;<br /><span style="color: rgb(51, 51, 255);">(what happen? why? )</span><br />SQL> insert into testing values (50);<br /><span style="color: rgb(51, 51, 255);">(why oracle give you an error?)</span><br />SQL> insert into testing values(20);<br />SQL> commit;<br /><br />That's all for today. Thanks.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-30467405988399545932007-07-22T11:17:00.000+07:002007-07-22T14:32:50.595+07:00Oracle for Beginner: Password Resource ManagementEverytime a user account was created, Oracle will assign a profile for that user. The default profile is 'DEFAULT' if for that user was not specified the profile when was created. There are two kind of resouces that can be managed in a profile: Kernel Resouce and Password resource. Lets we try to do a simple exercise to manage password resource:<br /><br />oracle$ sqlplus /nolog<br /><span style="color: rgb(204, 0, 0);">SQL> conn / as sysdba</span><br /><span style="color: rgb(204, 0, 0);">SQL> select profile from dba_profiles;</span><br />(you will see all of profile objects that have been created. There are two default profile objects, i.e. DEFAULT and MONITORING_PROFILE).<br /><span style="color: rgb(204, 0, 0);">SQL> select * from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';</span><br /><pre><br />PROFILE RESOURCE_NAME RESOURCE LIMIT<br />-------------------- ------------------------- -------- --------------------<br />DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10<br />DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED<br />DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED<br />DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED<br />DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL<br />DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED<br />DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED<br /></pre><br />(with this query, we can obtain all of password resource parameter for profile DEFAULT)<br /><span style="color: rgb(204, 0, 0);">SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql</span><br />(this is a build-in script that will change password resource parameters in DEFAULT profile to make some tight restriction for password management. utlpwdmg.sql script will create a new stored function which is called verify_function, and then alter the DEFAULT profile)<br /><span style="color: rgb(204, 0, 0);">SQL> select * from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';</span><br /><pre><br />PROFILE RESOURCE_NAME RESOURCE LIMIT<br />-------------------- ------------------------- -------- --------------------<br />DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3<br />DEFAULT PASSWORD_LIFE_TIME PASSWORD 60<br />DEFAULT PASSWORD_REUSE_TIME PASSWORD 1800<br />DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED<br />DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION<br />DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006<br />DEFAULT PASSWORD_GRACE_TIME PASSWORD 10<br /></pre><br /><br />Now, the DEFAULT profile was changed. Try some practice below:<br />1. try to create a new user account.<br /><br /><span style="color: rgb(204, 0, 0);">SQL> conn / as sysdba</span><br /><span style="color: rgb(204, 0, 0);">SQL> create user myuser identified by oracle;</span><br />(because the password_verify_function has been changed with verify_function, evertime new password want to save to oracle should consider some rules that already have defined by verify_function.)<br />SQL> create user myuser identified by oracle_2007;<br />(done!)<br /><span style="color: rgb(204, 0, 0);">SQL> grant connect to myuser;</span><br />SQL><br /><br />2. try to login with invalid password for 3 times, and then login with valid password for last time:<br /><br /><span style="color: rgb(204, 0, 0);">SQL> conn myuser/gggg</span><br /><span style="color: rgb(204, 0, 0);">SQL> conn myuser/rrrr</span><br /><span style="color: rgb(204, 0, 0);">SQL> conn myuser/ttttt</span><br /><span style="color: rgb(204, 0, 0);">SQL> conn myuser/oracle_2007</span><br /><pre>ERROR:<br />ORA-28000: the account is locked</pre><br />(what happen? yes, your account is locked, because we have failed to login for 3 times (FAILED_LOGIN_ATTEMPTS). Now, we should wait for about 1 minute (1/1440) which is 1440 is number of minutes in a day, see PASSWORD_LOCK_TIME).<br />(after 1 minute, try to login again with correct password)<br /><span style="color: rgb(204, 0, 0);">SQL> conn myuser/oracle_2007</span><br /><pre>Connected.</pre><br /><br />3. The parameter PASSWORD_LIFE_TIME is to determine the age of your password (in days). After the (PASSWORD_LIFE_TIME + 1)th day, you have PASSWORD_GRACE_TIME days to retain your old password. Now, we will try to change both parameters for short time, so you can try it immediately.<br /><span style="color: rgb(204, 0, 0);">SQL> conn / as sysdba</span><br /><span style="color: rgb(204, 0, 0);">SQL> alter profile default limit</span><br /><span style="color: rgb(204, 0, 0);">PASSWORD_LIFE_TIME 5/1440</span><br /><span style="color: rgb(204, 0, 0);">PASSWORD_GRACE_TIME 5/1440;</span><br /><span style="color: rgb(204, 0, 0);">SQL> conn myuser/oracle_2007</span><br />(please wait for about 5 minutes)<br /><span style="color: rgb(204, 0, 0);">SQL> conn myuser/oracle_2007</span><br /><pre>ERROR:<br />ORA-28002: the password will expire within 0 days</pre><br />(please wait for about 5 minutes again)<br /><span style="color: rgb(204, 0, 0);">SQL> conn myuser/oracle_2007</span><br /><pre>ERROR:<br />ORA-28001: the password has expired<br /><br />Changing password for testing<br />New password: </pre><br />(please give your new password with minimum 3 new character that different from the old one)<br /><span style="color: rgb(204, 0, 0);">SQL> conn / as sysdba</span><br /><span style="color: rgb(204, 0, 0);">SQL> alter profile default limit</span><br /><span style="color: rgb(204, 0, 0);">PASSWORD_LIFE_TIME 60</span><br /><span style="color: rgb(204, 0, 0);">PASSWORD_GRACE_TIME 10;</span><br /><br /><br />4. Between parameter PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX should be choosen only one. It's mean, if we mention a value for one parameter, the second parameter should have UNLIMITED value. Both parameters are used to determine how many times old passwords can be reuse again.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com2tag:blogger.com,1999:blog-7391822.post-50935942592596115192007-07-19T19:22:00.000+07:002007-07-22T11:37:05.293+07:00Oracle for Beginner: Creating User AccountIn Oracle, we can create an user account easily. Here are some examples that will show you how to create a new user account and grant some privileges to that user (as dba user):<br /><br /><span style="color: rgb(102, 51, 102);">$ sqlplus /nolog</span><br /><span style="color: rgb(102, 51, 102);">SQL> conn / as sysdba</span><br /><span style="color: rgb(102, 51, 102);">SQL> create user xxx identified by password_xxx;</span><br /><span style="color: rgb(102, 51, 102);">SQL> grant connect to xxx ;</span><br /><span style="color: rgb(102, 51, 102);">SQL> quit</span><br /><br /><span style="font-style: italic; color: rgb(51, 51, 255);">(now, you can try to login as user xxx)</span><br /><span style="color: rgb(102, 51, 102);">$ sqlplus xxx/password_xxx</span><br /><span style="font-style: italic; color: rgb(51, 51, 255);">(done user xxx login in! but you can't do anything except querying some dictionary view that already grant to you, like v$version, user_tables, user_indexes, etc.)</span><br /><span style="color: rgb(102, 51, 102);">SQL> select * from v$version;</span><br /><span style="color: rgb(102, 51, 102);">SQL> select table_name from user_tables;</span><br /><span style="font-style: italic; color: rgb(51, 51, 255);">(you also can change your password)</span><br /><span style="color: rgb(102, 51, 102);">SQL> alter user xxx identified by oracle;</span><br /><span style="font-style: italic; color: rgb(51, 51, 255);">(but you can't do DDL or DML operation)</span><br /><span style="color: rgb(102, 51, 102);">SQL> create table test(a number);</span><br /><span style="font-style: italic; color: rgb(51, 51, 255);">(error!)</span><br /><span style="color: rgb(102, 51, 102);"> SQL> exit</span><br /><br />We will do a simple practice to make our user xxx can do DDL or DML operation on tablespace "operations" with space limitation . To do this, you should login again as DBA:<br /><br /><span style="color: rgb(102, 51, 102);">$ sqlplus /nolog</span><br /><span style="color: rgb(102, 51, 102);">SQL> conn / as sysdba</span><br /><span style="color: rgb(102, 51, 102);">SQL> create tablespace operations datafile size 5M;</span><br /><span style="color: rgb(102, 51, 102);">SQL> alter user xxx default tablespace operations quota 2M on operations password expire;<br /></span><span style="color: rgb(51, 51, 255); font-style: italic;">(we want to change default tablespace from "users" to "operations" and make a space limitation for user xxx only for 2M. Option "password expire" is an option to force user xxx to change the password. Try to login as user xxx again)</span><br /><span style="color: rgb(102, 51, 102);">SQL> grant create table, create any index, create sequence to xxx;<br /></span><span style="color: rgb(51, 51, 255); font-style: italic;">(as a dba, we grant some privileges for user xxx, so he/she can DDL and DML toward their own objects)</span><br /><span style="color: rgb(102, 51, 102);">SQL> conn xxx/oracle</span><br /><span style="font-size:85%;"><span style="color: rgb(153, 0, 0);">ERROR:</span><br /><span style="color: rgb(153, 0, 0);">ORA-28001: the password has expired</span><br /><br /><span style="color: rgb(153, 0, 0);">Changing password for xxx</span><br /><span style="color: rgb(153, 0, 0);">New password: </span><br /><span style="color: rgb(153, 0, 0);">Retype new password: </span><br /><span style="color: rgb(153, 0, 0);">Password changed</span><br /><span style="color: rgb(153, 0, 0);">Connected.</span></span><br /><span style="color: rgb(102, 51, 102);">SQL> create sequence seq_test;</span><br /><span style="color: rgb(102, 51, 102);">SQL> create table test(id number(4) primary key, data varchar(40));</span><br /><span style="color: rgb(102, 51, 102);">SQL> insert into test values(seq_test.next, 'test one');</span><br /><span style="color: rgb(102, 51, 102);">SQL> commit;</span><br /><span style="color: rgb(102, 51, 102);">SQL> select * from test;</span><br /><span style="color: rgb(102, 51, 102);">SQL> drop table test purge;</span><br /><span style="color: rgb(102, 51, 102);">SQL> exit</span><br /><br />In unix/linux environment, if you want unix/linux user account can login to oracle which is the instance oracle is also running on the same machine, you can try with these a simple practice:<br /><br /><span style="color: rgb(153, 0, 0);">$ su -</span><br /><span style="color: rgb(153, 0, 0);">password: </span><br /><span style="color: rgb(153, 0, 0);"># adduser testing</span><br /><span style="color: rgb(153, 0, 0);"># passwd testing</span><br /><span style="color: rgb(153, 0, 0);"># exit</span><br /><br /><span style="color: rgb(102, 51, 102);">$ sqlplus /nolog</span><br /><span style="color: rgb(102, 51, 102);">SQL> conn / as sysdba</span><br /><span style="color: rgb(102, 51, 102);">SQL> show parameter os_authent_prefix;</span><br /><span style="font-size:85%;"><pre>NAME TYPE VALUE<br />------------------------------------ ----------- ------------------------------<br /><span style="font-family:courier new;">os_authent_prefix string ops$</span></pre></span><br /><span style="color: rgb(102, 51, 102);">SQL> create user ops$testing identified by externally;</span><br /><span style="color: rgb(102, 51, 102);">SQL> grant connect, resource to ops$testing;</span><br /><span style="color: rgb(102, 51, 102);">SQL> quit</span><br /><span style="color: rgb(102, 0, 0);">$ su - testing</span><br /><span style="color: rgb(102, 0, 0);">password:</span><br /><span style="color: rgb(102, 0, 0);">$ sqlplus / </span><br /><span style="color: rgb(102, 51, 102);">SQL> show user;</span><br /><span style="color: rgb(102, 51, 102);">ops$testing</span><br /><span style="color: rgb(51, 51, 255); font-style: italic;">(now you can login to your oracle with operating system user account)</span>Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0tag:blogger.com,1999:blog-7391822.post-24335692882720102872007-07-17T16:15:00.000+07:002007-07-17T16:34:00.284+07:00Oracle for Beginner:
Securing RolesFrom this article, I want to share some commands that I have knew about Oracle10g. At this point, we will learn about how to make secure our roles, so every user that want to activate that roles should identifying with the password for each role. Here is an example to realize that:<br /><br /><span style="color: rgb(153, 0, 0);">$ sqlplus / as sysdba</span><br /><span style="color: rgb(153, 0, 0);">SQL> create user test1 identified by oracle;</span><br /><span style="color: rgb(153, 0, 0);">SQL> grant connect, create table to test1;</span><br /><span style="color: rgb(153, 0, 0);">SQL> conn test1/oracle</span><br /><span style="color: rgb(153, 0, 0);">SQL> create table test(name varchar(20));</span><br /><span style="color: rgb(153, 0, 0);">SQL> conn / as sysdba</span><br /><span style="color: rgb(153, 0, 0);">SQL> create role test_mgr identified by oracle;</span><br /><span style="color: rgb(153, 0, 0);">SQL> grant insert, update, delete on test1.test to test_mgr;</span><br /><span style="color: rgb(153, 0, 0);">SQL> create role test_qry;</span><br /><span style="color: rgb(153, 0, 0);">SQL> grant select on test1.test to test_qry;</span><br /><span style="color: rgb(153, 0, 0);">SQL> grant test_qry, test_mgr to budsus;</span><br /><span style="color: rgb(153, 0, 0);">SQL> alter user budsus default role all except test_mgr;</span><br /><span style="color: rgb(153, 0, 0);">SQL> conn budsus/oracle</span><br /><span style="color: rgb(153, 0, 0);">SQL> select * from test1.test;</span><br /><span style="font-style: italic; color: rgb(0, 204, 204);">(no problem with this command, because you have test_qry role)</span><br /><span style="color: rgb(153, 0, 0);">SQL> insert into test1.test values ('Testing 1');</span><br /><span style="font-style: italic; color: rgb(0, 204, 204);">(this command will generate an error, because your test_mgr is not default for your user account. In this context, you have to activate it first)</span><br /><span style="color: rgb(153, 0, 0);">SQL> set role test_mgr identified by oracle;</span><br /><span style="font-style: italic; color: rgb(0, 204, 204);">(done! now you have object privileges for test1.test)</span><br /><span style="color: rgb(153, 0, 0);">SQL> insert into test1.test values ('Testing 1');</span><br /><span style="font-style: italic; color: rgb(0, 204, 204);">(no problem)</span><br /><span style="color: rgb(153, 0, 0);">SQL> commit;</span><br /><span style="color: rgb(153, 0, 0);">SQL> select * from test1.test;</span><br /><span style="color: rgb(153, 0, 0);">SQL> quit</span><br /><br />Now, you can make secure for any roles that you want.Budi Susantohttp://www.blogger.com/profile/03600457033968705451noreply@blogger.com0