In a heterogeneous environment it sometimes becomes necessary to access information across different database. In this exercise I will demonstrate…
Tag SQL
From time to time a DBA might need move an Oracle database datafile from one location to another. Reasons for…
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl,ksppstdf, decode(bitand(ksppiflg/256,1),1,’TRUE’,’FALSE’), decode(bitand(ksppiflg/65536,3),1,’IMMEDIATE’,2,’DEFERRED’,3,’IMMEDIATE’,’FALSE’), decode(bitand(ksppstvf,7),1,’MODIFIED’,4,’SYSTEM_MOD’,’FALSE’), decode(bitand(ksppstvf,2),2,’TRUE’,’FALSE’), ksppdesc from x$ksppi x, x$ksppcv y where (x.indx = y.indx) /*and (translate(ksppinm,’_’,’#’) not like…
Here are some scripts related to Tables/Indexes . Tabs w/ Questionable Inds TABLES WITH QUESTIONABLE INDEX(ES) NOTES: Owner – Owner…
PL/SQL Procedure to Automatically Backup ControlFile to trace whenever you are shutting down or starting up the database. ‘ALTER DATABASE…
Dedicated to Gwen Shapira It’s a tale of woe and malice, a tale of a DBA That the users learned…
*** I would like to update this post since this is one of the most frequently accessed pages on my blog.…
How to drop all indexes in a SCHEMA –Login as the schema user whose indexes you want to drop SQL>set…
The final answer to the age old debate of count(*) vs count(1).Absolute proof that count(*) is faster than count(1). sql->alter…
1. Start session trace To start a SQL trace for the current session, execute: ALTER SESSION SET sql_trace = true;…
A database link creates a connection between a local database and a remote database. You might want to create a…
Purpose of this parameter found in sqlnet.ora file: Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If…
The size of the database is the total size of the datafiles that make up the tablespaces of the database.…
Copy Table to a new table with some condition ______________________________________________ SQL> CREATE TABLE newTable AS (SELECT * FROM emp WHERE…