Do a “create table” as command without selecting any rows create table abc_new as select * from abc where 1=0;
Category SQL
Working on creating a muti-master replication for a production system. Each database needs to be active and replicate changes back…
This error occurs when you have a public synonym defined and the object referencing or owning the synonym has been…
CAUTION !!!! DO NOT DO THIS ON YOUR PRIMARY. IF YOU DO THEN YOU WOULD REQUIRE MEDIA RECOVERY ON THE…
SELECT /*+ RULE */ GRANTEE FROM SYS.DBA_ROLE_PRIVS A WHERE GRANTED_ROLE=’DBA’ AND EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME =A.GRANTEE);
On Primary:- select thread#, max(sequence#) “Last Primary Seq Generated” from v$archived_log where first_time between (sysdate-1) and (sysdate+1) group by thread#…
Even when sessions are inactive they can be executing SQL’s in the background. You have to identify the SQL’s which…
Source –> http://oraexplorer.com/2007/11/oracle-apex-in-11g-installation/ Today I installed Oracle 11g (11.1.0.6) on my machine. I did not realize that Oracle APEX is…
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 (‘+DATA/swx/onlinelog/redo-05-01.log’,’+DATA/swx/onlinelog/redo05-02.log’) SIZE 100m, GROUP 6 (‘+DATA/swx/onlinelog/redo-06-01.log’,’+DATA/swx/onlinelog/redo-06-02.log’) SIZE 100m; ALTER DATABASE ADD LOGFILE…
While creating a’ Report and Form’ Application in Oracle APEX if your using a table which has no primary key…
Below is the Solaris user profiles for “grid” and “oracle” user. If you have followed Oracles official documentation for installation…
with t as ( select to_date(‘201111′,’yyyymm’) ym from dual) SELECT * FROM ( SELECT MIN (DECODE (TO_CHAR (YM + LEVEL…
Query to check if there is any logon triggers defined for your database. Logon trigger is a trigger which fires…
Sometimes it becomes necessary to add a dummy value next to a dummy column in a report. It can be…
ORACLE TRANSPARENT DATABASE ENCRYPTION 11gR2 : eNCRYPTING eNTIRE tABLESPACE Create oracle encryption wallet: create a directory on the oracle server.…
In a heterogeneous environment it sometimes becomes necessary to access information across different database. In this exercise I will demonstrate…
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…
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…
Purpose of this parameter found in sqlnet.ora file: Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If…
—– Procedure to import table from production system to test system —— 1. Copy the backup(dump) of schema onto /backup_restor_only…
The size of the database is the total size of the datafiles that make up the tablespaces of the database.…