As we know, the private db_links can be dropped only by the owner of the database_link.  But you don’t have the password that db_link owner, then there is a workaround for that. SQL>select * from dba_db_links where OWNER=’SCOTT’; OWNER DB_LINK USERNAME HOST CREATED —————————— ——————– —————————— ——————– ——— SCOTT LINK1 SCOTT testdb 04-NOV-11 SQL>drop database […]

            Suppose you want to create a database_link, but you don’t have the privilege to modify the tnsname entry. In that case you can create the database_link directly using tns description. sql>create public database link IMFP connect to iwf identified by thr3iwf USING ‘(DESCRIPTION=(ADDRESS_LIST=( ADDRESS=(PROTOCOL=TCP)(HOST=testoracle.com)(PORT=1522))) (CONNECT_DATA=(SERVICE_NAME=IMFP)))’ / database link created. […]

An oracle database can be created either using Manually or using DBCA( GUI Method).  But few organizations recommend to use manual method for creating database. Here I have provided steps for creating database Manually with installation of differnt optional components. Before creating an oracle database, Make sure oracle database software is installed. REFER –  Steps […]

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 […]