Creating a database link from oracle to oracle database is easy. But setting up db_link from oracle to mssql server isn’t straight forward. We need to do odbc setup. Its knows as heterogeneous db_link.  We can achive this either by using third party odbc drivers or using oracle provided gateways utility. Here in this article […]

Suppose  you want to do some activity under another user, But you don’t know the password of that user. Then how you will do it? There is a way you can do it.  See the below demo. Suppose a user TEST1 wants to connect to TEST2 user and create a table and we don’t know […]

Follow below steps for deinstalling XDB component:   — The catnoqm.sql script drops XDB. >SQL> spool xdb_removal.log SQL> set echo on; SQL> connect / as sysdba SQL> shutdown immediate; SQL> startup SQL> @?/rdbms/admin/catnoqm.sql SQL> spool off; FOR INSTALLING XDB COMPONENT:   —-The catqm.sql script requires few parameters be passed to it when run: SQL> spool […]

 Usually we create synonym for table. But can we really create synonym for a schema?   Yes, there is an undocumented parameter  _enable_schema_synonyms oracle which enables this feature. Check the parameter status and make it true.   SQL>select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and ksppinm like ‘%schema%synonym%’ KSPPINM KSPPSTVL —————————- ———— […]

   There are 2 types of logging mode in oracle database.   1. Archivelog mode In this mode, after the online redo logs are filled , it will move to archive location 2. Noarchivelog mode In this mode, filled online redo logs wont be archives, instead they will be overwritten.   Enable archive log mode: […]

There are different methods to partition an existing table. Here we will create a non partitioned table(PRODUCT) and convert it to a partitioned table using EXCHANGE option. EXAMPLE: Create a normal table with index and constraints and insert some data:   conn oranet/oranet Connected. CREATE TABLE PRODUCT ( id NUMBER, code VARCHAR2(10), description VARCHAR2(50), created_date […]