Sql Commads

Find the objects that are currently being accessed.

select distinct sp. object_name from gv$sql_plan sp,gv$session gs
 where sp.sql_id =gs.sql_id
 and gs.status='ACTIVE'
 and sp.object_name is not null
 and gs.username not in ('SYS','SYSTEM')
 and sp.object_name not like '%:%'

Find the SQL and its duration that is currently running 

select distinct a.sid,a.serial#,a.logon_time,a.username,a.sql_id,a.sql_exec_start,sysdate,a.status,
substr(to_char(NUMTODSINTERVAL(a.sql_exec_start-sysdate, 'day')),5,15) duration ,a.event,a.program,a.module,b.sql_text
from gv$session a, gv$sql b
where a.status='ACTIVE'
and a.username not in ('SYS','SYSTEM')
and b.sql_id=a.sql_id
and b.parsing_schema_name=a.username
and a.program not like 'orac%'
and b.parsing_schema_id=a.user#
order by a.sql_exec_start;
 

Find the Top SQLs order by executing duration for a particular Period.

with qduration as ( select ash.sql_id,ash.user_id,ash.sql_exec_start,min(ash.sample_time) qmin ,max(ash.sample_time) qmax  ,
EXTRACT (hour FROM (max(ash.sample_time)-min(ash.sql_exec_start))) HH,
EXTRACT (MINUTE FROM (max(ash.sample_time)-min(ash.sql_exec_start)))  MI ,
EXTRACT (SECOND FROM (max(ash.sample_time)-min(ash.sql_exec_start))) SS
from dba_hist_active_sess_history ash
where ash.sample_time  BETWEEN    to_date('07-DEC-2017 10:00:00','DD-MON-YYYY HH24:MI:SS')
and to_date('07-DEC-2017 13:23:00','DD-MON-YYYY HH24:MI:SS')
and sql_id is not null
and sql_exec_start is not null
 --and ash.user_id=71
group  by ash.sql_exec_start,ash.sql_id,ash.user_id)
select qd.sql_id,qd.user_id,qd.sql_exec_start,qd.qmax,qd.qmin,qd.HH,qd.mi,qd.ss,dbms_lob.substr(dh.sql_text,4000,1)  from qduration qd,dba_hist_sqltext dh
where qd.sql_id=dh.sql_id
order by 6 desc , 7  desc,8 desc;

SQL to find the tables are analyzed.

select owner,table_name,num_rows,last_analyzed from dba_tables where table_name=&tablename and owner=&owner;

From delveloper perspective, use 

select table_name,num_rows,last_analyzed from dba_tables where table_name=&tablename ;

Find temp usage 

 select s.username,s.osuser,u.tablespace,s.sql_id,substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
 sum(round(((u.blocks*p.value)/1024/1024),2)) size_mb
 from v$sort_usage u, v$session s, v$sqlarea a,v$parameter p
 where s.saddr = u.session_addr
 and a.address (+) = s.sql_address
 and a.hash_value (+) = s.sql_hash_value
 and p.name = 'db_block_size'
 group by  s.username,s.osuser,u.tablespace,s.sql_id,
 substr(a.sql_text, 1, (instr (a.sql_text, ' ')-1)) 

STEP BY STEP RMAN RECOVERY CATALOG CREATION AND CONFIGURATION. (WINDOWS)

 RMAN Recovery Catalog Creation and Configuration. (Windows)

The RMAN Recovery Catalog is a database schema that holds the metadata detailing the RMAN backup operations performed on the target database. The metadata include the following information from about the target database: database structure, RMAN configuration, data file and archive log backups (backup sets, pieces and copies), archived redo logs and their copies. In addition to the metadata the recovery catalog can also store scripts that can be used by all registered target databases.

This document will detail the steps to create an RMAN recovery catalog using Oracle Database 11gR2 on Windows OS. The process is the same for other versions of UNIX, Solaris and AIX.

It is recommended that the recovery catalog be in a database separate from any of the target databases. Many organizations store the recovery catalog in the same database as their Enterprise Manager Grid Control repository. It is also recommended that the database that houses the recovery catalog to be in ARCHIVELOG mode. In this demo I am using catalog database with the name of CATDB.

First create a table space to house the recovery catalog schema.

C:\Users\rajjha>set oracle_sid=catdb

C:\Users\rajjha>sqlplus

SQL*Plus: Release 11.2.0.1.0

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace rec_catalog
     datafile ‘D:\app\rajjha\oradata\CATDB\rec_catalog.dbf’
     size 10M autoextend on
     extent management local uniform size 1M;

Next we create the recovery catalog owner. The user SYS cannot be the owner of the recovery catalog.

SQL> create user rcat identified by password
    default tablespace rec_catalog
    quota unlimited on rec_catalog;

User created.

SQL> grant recovery_catalog_owner to rcat;

Grant succeeded.

The role RECOVERY_CATALOG_OWNER has all of the privileges need to query and maintain the recovery catalog.

SQL> select privilege from dba_sys_privs where grantee = ‘RECOVERY_CATALOG_OWNER’;

PRIVILEGE
—————————————-
CREATE SYNONYM
CREATE CLUSTER
ALTER SESSION
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE SESSION
CREATE TYPE
CREATE VIEW
CREATE TRIGGER

11 rows selected.

SQL>

The actual creation of the recovery catalog is performed in RMAN while connected to the database in which the catalog is to be created. The command CREATE CATALOG will create the recovery catalog in the default table space of the user. If you need to store the catalog in a table space other than the users default table space you can provide the table space name using TABLESPACE.

C:\Users\rajjha>rman target / catalog rcat@CATDB

Recovery Manager: Release 11.2.0.1.0 –

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CATDB (DBID=2365959513)
recovery catalog database Password:
connected to recovery catalog database

RMAN> create catalog tablespace rec_catalog;

recovery catalog created

To the new database to be backed up, you have to register it on the rman catalog you just created: (Fon any new database you can use this catalog after register database in catalog)

RMAN>  register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Configure the persistent parameters.

RMAN> configure retention policy to recovery window of 2 days;

starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
RMAN> configure default device type to disk;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN>
RMAN> configure channel device type disk format ‘D:\oraclebackup\Backup%d_DB_%U_%S_%P’;

starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   ‘D:\oraclebackup\Backup%d_DB_%U_%S_%P’;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Take database full backup. The full database backup should be taken first time. Afterwards you can use Incremental, Full or archivelog backup depend on your backup policy as well as space available.

C:\Users\rajjha>rman target / catalog rcat/password@CATDB
RMAN> run{
backup database plus archivelog;
delete noprompt obsolete;
}

RMAN> exit
Now the RMAN setup is completed successfully. Here is the info about RMAN.
Primary DB = MYDB
Catalog DB = CATDB
RMAN Backup location = D:\oraclebackup.
Now take the full backup. Every day run the below script that backup the new archive log files. You may run through scheduler

C:\Users\rajjha>rman target / catalog rcat@CATDB
RMAN> run{
delete noprompt obsolete;
backup archivelog all;
}

RMAN> exit

Oracle Day to Day queries

Database start time:
++++++++++++++++

SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance

check the tablespace size
--------------------------------------
set linesize 200
set pagesize 300
select t.tablespace,  t.totalspace as " Totalspace(MB)",round((t.totalspace-fs.freespace),2) as "Used Space(MB)",
fs.freespace as "Freespace(MB)",round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used",
round((fs.freespace/t.totalspace)*100,2) as "% Free" from
(select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace
from dba_data_files d group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f
group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;


select min(creation_time),df.ts#,ts.name,sum(df.bytes)/1024/1024 curr_size_mb,
(sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) growth_per_day,
(sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 120 growth_120_days,
(sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time)) * 240 growth_240_days,
(sum(bytes)/1024/1024)+((sum(df.bytes)/1024/1024)/round(sysdate-min(creation_time))*365) projection_365_days_mb
from v$datafile df ,v$tablespace ts where df.ts#=ts.ts#  group by df.ts#,ts.name order by df.ts#;


check the tablespace size in percentages
-----------------------------------------------------------
select a.tablespace_name,  a.alloc_megs,  b.total_megs, ( ( b.total_megs - a.alloc_megs ) / b.total_megs) * 100 as "Percentage Used" from alloc_space a,avail_space b where a.tablespace_name = b.tablespace_name;

check the database size
-------------------------------------
 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size",
round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space",
round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used,
(select sum(bytes) as p
from dba_free_space) free
group by free.p;

SQL>select 'Complete Database Size is', sum(bytes/1024/1024/1024) tt, 'Gb.' from sys.dba_data_files;

find the datafiles details of tablespaces
--------------------------------------------------------
 select file_name,tablespace_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024,maxblocks,increment_by from dba_data_files where tablespace_name='SYSTEM';
other activities in database
--------------------------------------

select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

select profile,resource_name,limit from dba_profiles order by profile;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

select table_name,constraint_name,status from dba_constraints where constraint_name='SYS_C008302';

select count(*),OBJECT_TYPE,owner from dba_objects where owner NOT IN ('SYSTEM','SYS','QUEST') group by OBJECT_TYPE,owner;

select owner,last_analyzed,num_rows from dba_tables where table_name=''

select bytes/1024/1024,segment_name from dba_segments where segment_type='INDEX' and owner='QUEST' order by 1;

select TABLESPACE_NAME,SEGMENT_NAME,EXTENTS from dba_segments where segment_name='AUD$';

select 'alter system kill session '||''''||sid||','||serial# ||''''||' immediate ;' from v$session  where trunc(logon_time)<sysdate;

select sid||','||username||','||osuser||','||to_char(logon_time,'DD-MON-YY HH24:MI') from v$session where program like '%TOAD%' and logon_time < sysdate-4  order by logon_time;

select sid,serial#,logon_time from v$session where upper(program) like '%TOAD%';

select holding_session from dba_blockers a where not exists (select  'x'  from dba_waiters b where b.waiting_session = a.holding_session)

select default_tablespace from dba_users ;

select username,account_status from dba_users where username like ''

 select PRIVILEGE from  dba_sys_privs where GRANTEE=MULLIGAN_N';

select GRANTED_ROLE from DBA_ROLE_PRIVS  where GRANTEE='HSERVER';

select PRIVILEGE from  dba_sys_privs where GRANTEE='HSERVER';

select sum(bytes/1024/1024) from dba_segments where owner='O2OMDML';

select * from v$access where object=''

select tablespace_name, username, bytes, max_bytes  from dba_ts_quotas where username='MKALDAS';


select count(*),trunc(first_time) from v$log_history group by trunc(first_time);

select sum(bytes/1024/1024/1024) from dba_segments;

select sum(bytes/1024/1024) from dba_segments where segment_name='AUD$';

select sum(bytes/1024/1024) from dba_segments where owner='EPIMM';

select tablespace_name,bytes/1024/1024/1024 from sm$ts_avail order by 1;

select tablespace_name,bytes/1024/1024/1024 from sm$ts_used order by 1;

select tablespace_name,bytes/1024/1024/1024 from sm$ts_free order by 1;

select bytes/1024/1024 as mb,tablespace_name from sm$ts_free where tablespace_name='';

select bytes/1024/1024,file_name from dba_data_files where tablespace_name='UNDOTBS1'

select bytes/1024/1024 as mb,tablespace_name from sm$ts_avail;

select bytes/1024/1024 as mb,tablespace_name from sm$ts_used;

select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

select sid,serial#,status,logon_time from v$session where program like 'rman%';

select count(*) from v$session where type='USER';

select count(*),trunc(logon_time)  from v$session where type='USER' group by trunc(logon_time);

select fuzzy,count(*) from v$datafile_header group by fuzzy;

select sid,failures,last_date,this_date from dba_jobs_running where job in (' ');

select sid,serial#,username from v$session where lockwait is not null;

select * from gv$session where lockwait is not null;

select * from v$session where lockwait is not null;

s

select HOLDING_SESSION from dba_blockers where HOLDING_SESSION not in (select WAITING_SESSION from dba_waiters);

select inst_id from gv$instance;

select inst_id,username,count(*) from gv$session group by inst_id,username;

select hash_value from v$sql where sql_text like ' ';

select hash_value,sql_text from v$sqltext where hash_value = '&1' order by piece;

select * from gv$lock where block <1;

 select  TABLESPACE_NAME,
  2  ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='amo_msoffline_std';

select file_name from dba_temp_files;

select memeber from v$logfile;

select name from v$controlfile;

select open_mode from v$database;

select instance_name from v$instance;

select name from v$tablespace;

select name,status from v$datafile;

select temporary_tablespace from dba_users;

select * from dba_synonyms where owner='';

select username,owner,action_time,tiemstamp from dba_audit_objects where object_name='';

select max(sequence#) from v$log_history;

select host_name from v$instance;

select host from dba_db_links;

select dbid from v$database;

select * from registry$history;

select * from dba_registry;

select * from v$backup;

select * from v$recoverfile;

select distinct status from v$datafile;

select TEXT from dba_source where name='VERIFY_FUNCTION';

select * from V$PWFILE_USERS;

select * from ejbtimer;
===========================================================================================================================

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

alter user username identified by

alter database datafile '' offline drop;

alter database datafile 'PATH'  resize ;

alter database open;

alter database default temporary tablespace '';

alter user QUEST default tablespace QUEST_INDEX;

alter tablespace LARGE_FACT_X add datafile '/u20/oradata/GDWQ/LARGE_FACT_x02.dbf' size 2850m;

alter database backup controlfile to trace;

alter profile O2_USER_PROFILE limit PASSWORD_VERIFY_FUNCTION NULL

alter user SRIRAM_S PASSWORD EXPIRE;

ALTER USER C_NALABOTHU_R QUOTA 10M ON USR;

alter view STAGING.TD_D_SCORE_V compile;

CREATE INDEX "IX_sms_sentstate_3" ON "sms_sentstate"(CustomerID) TABLESPACE "O2OM_DATA_INDEX_01";

===========================================================================================================================
drop tablespace tablespace_name including contents and datafiles;

drop temporary tablespace tablespace_name including contents and datafiles;

drop user username;

drop user username cascade;

drop table tablename purge;

drop table tablename cascade constraints;

drop table tablename;
====================================================================================================

find tablespaces is online or offiline :

select  tablespace_name, online_status from dba_data_files where tablespace_name='tablespace_name';

find tablespaces is autoexten, tbalespace name :

select file_name , tablespace_name, autoextensible from dba_data_files;


find the privilege in database;



select  privilege||' '||table_name  "Privileges for " from  dba_tab_privs where  grantee='&data'
union
select  privilege from  dba_sys_privs where grantee='&data'
union
select granted_role from dba_role_privs where  grantee='&data';
10:33:13 AM: Lakshmidevi V: this is to check all the privs for a user





SQL> alter user ISMDBA11 account unlock;

User altered.

SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,PROFILE from dba_users;


SELECT s.sid, s.serial#, s.username, s.program,
          i.block_changes
          FROM v$session s, v$sess_io i
          WHERE s.sid = i.sid
          ORDER BY 5 desc;


SELECT s.sid, s.serial#, s.username, s.program,
         t.used_ublk, t.used_urec
          FROM v$session s, v$transaction t
          WHERE s.taddr = t.addr
          ORDER BY 5 desc, 6 desc;


Use this query to find the session information from OS Process id
----------------------------------------------------------------------------------------------------------------------

select sid,serial#,status,machine,program,sql_hash_value,to_char(sysdate,'DD/MM/YYYY HH24:MI')  from v$session
where paddr in (select addr from v$process where spid=&spid)
/

select spid from v$process where addr =(select paddr from v$session
where sid=&sid)
/

====================================================================================================
find . -name "030609_GSM.txt" -print (for searching a file from the filesystem)
find . -print|xargs grep -i v\$session
find . -mtime -1 -print (To find Recently created files)
find . -size +10000 -print (find largest files on the server)
find . -mtime +7 (Delete files in bulk)
find . -size +100000 -exec ls -alrt {} \;
find . -size +10000 -exec ls -alrt {} \;
find / -name Chapter1 -type f -print
find /usr -name Chapter1 -type f -print
find /usr -name "Chapter*" -type f -print
find /usr/local -name "*.html" -type f -print
find /usr/local -name "*.html" -type f -exec chmod 644 {} \;
find . -type f -exec ls -l {} \;
find . -type d -exec ls -ld {} \;
find . -type f -name "Poop*" -exec rm {} \;
find . -type d -name CVS -exec rm -r {} \;
find . -type f -name -exec chmod 644 {} \;
find . -type d -name -exec chmod 755 {} \;
locate lost-file
====================================================================================================
Memory Check

$prtconf   |  grep  "Mem" (Solaris)
$prtconf|grep -i mem (Solaris)
$lsdev -C|grep mem (AIX)
$ipcs -pmb (Allocated memory Segments)
$lsdev -C|grep Process|wc -l (Dispays the number of CPU's)
$psrinfo -v|grep "Status of processor"|wc -l (Dispays the number of CPU's in Solaris)
$kmtune|grep sem (Displays values for semaphores)
$grep error /var/adm/syslog/syslog.log|more (Show server log in HP-UX)
$errpt -a|more (Show server log in AIX)
$lsps -a (Displays swap usage in AIX)
$swapinfo -tam (Displays swap usage in HP-UX)
====================================================================================================
Script to create DB link

select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by d3vf0rsys# using '''|| HOST||''';' from dba_db_links
where DB_LINK IN (SELECT SID||'_'||SERVER FROM database_list where USAGE='PROD');


select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by ' ||
 substr(a.SERVER,0,1)||substr(a.server,(length(a.SERVER)),1)||'br0adc#st using '''|| HOST||''';' from dba_db_links,ops$oracle.database_list a
 where DB_LINK IN (SELECT SID||'_'||SERVER FROM ops$oracle.database_list where USAGE='PROD')
and  substr(DB_LINK,1,instr(DB_LINK,'_')-1)=a.sid


Script to Drop DB link

select 'drop database link '||DB_LINK||';' from dba_db_links
where DB_LINK IN (SELECT SID||'_'||SERVER FROM database_list where USAGE='DEV');


select 'create database link '||DB_LINK||' connect to '||USERNAME||' identified by ' ||
 substr(a.SERVER,0,1)||substr(a.server,(length(a.SERVER)),1)||'d3cis1#n using '''|| HOST||''';' from dba_db_links,ops$oracle.database_list a
 where DB_LINK IN (SELECT SID||'_'||SERVER FROM ops$oracle.database_list where USAGE='DEV')
and  substr(DB_LINK,1,instr(DB_LINK,'_')-1)=a.sid
===================================================================================================
SNAPSHOT_FEATURE PROCEDURE (REBUILD)

select OWNER,NAME,MASTER_OWNER,MASTER,to_char(LAST_REFRESH,'dd/mm/yyyyhh24:mi') from DBA_SNAPSHOT_REFRESH_TIMES where name like '%SERVICE_FEATURE%'

select host,OWNER from dba_db_links where db_link='CUSTDB.WORLD';

 select OWNER,NAME,RNAME,REFGROUP,JOB,NEXT_DATE from DBA_REFRESH_CHILDREN;

nohup ./run.sh @snapshot_rebuild.sql &

BEGIN
         DBMS_REFRESH.ADD(
           name => '"DBLUSGO"."MPSGUIDE_SNAPS"',
           list => '"DBLUSGO"."SERVICE_FEATURE_SNP"',
           lax => TRUE);
      END;

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DBLUSGO', tabname => 'SERVICE_FEATURE_SNP', cascade => TRUE, estimate_percent => 5, method_opt => 'FOR ALL COLUMNS SIZE 1');
====================================================================================================
This script provides the backup status of the datafiles in the database.
This is a good check to perform on an oracle database before the start of every business day to make sure that none of the oracle data files are left in a backup mode.

set lines 132
col name format a50
select * from (
select f.name, to_char(b.time,'mm/dd hh24:mi'), b.status
from v$backup b, v$datafile f
where b.file# = f.file#
order by b.time desc)
where rownum < 11
/
====================================================================================================
set pages 300
set linesize 3000
select rpad(a.tablespace_name,20,'.')tablespace_name,
   sum(a.bytes)/(1024*1024) Allocated,
   sum(a.bytes)/(1024*1024) - max(nvl(b.space,0)) Used,
   max(nvl(b.space,0)) Free,
   max(nvl(b.cont,0))/(1024*1024)  Contiguous
from dba_data_files a,
   (select tablespace_name,sum(bytes)/(1024*1024) space,max(bytes) cont
    from dba_free_space
    group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
group by a.tablespace_name order by TABLESPACE_NAME
/
====================================================================================================
set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB",
decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB",
decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || '  **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by tablespace_name
/
====================================================================================================
select index_name ,column_position colp,rtrim( column_name) colu
from user_ind_columns
where index_name in (
select index_name from user_indexes
where table_name = '&tname')
order by  index_name,column_position
/

select index_type from user_indexes where index_name in ('IX_sms_content_1','IX_sms_content_2');

drop index "IX_sms_content_1";

create index "IX_sms_content_1" on "sms_content" (CUSTOMERID,FOLDERID,MESSAGECONTENTID) online compute statistics tablespace O2OM_DATA_INDEX_01;
====================================================================================================
execute dbms_stats.gather_table_stats (ownname => 'TAP_USER',tabname=>'TAPOUT',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8,cascade => TRUE);

exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DBLUSGO', tabname => 'SERVICE_FEATURE_SNP', cascade => TRUE, estimate_percent => 5, method_opt => 'FOR ALL COLUMNS SIZE 1');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'EPIMM',METHOD_OPT=>'FOR ALL INDEXED COLUMNS ',DEGREE=>8,CASCADE=>TRUE);

exec DBMS_JOB.BROKEN( job=>192, broken=>FALSE );

exec dbms_system.SET_SQL_TRACE_IN_SESSION('&SID','&SR',&TF);

exec statspack.snap (i_snap_level=>7)

exec DBMS_JOB.remove (212);

exec DBMS.JOB.RUN (214);
====================================================================================================
===========================================================================================================================
select table_name from dict where table_name like '%CONS%';
===========================================================================================================================
select 'GRANT EXECUTE ON '||OWNER||'.'||OBJECT_NAME||' TO CHS_READ;' FROM DBA_OBJECTS WHERE OWNER='DSSPRIVATE' AND OBJECT_TYPE ='PROCEDURE';

select 'GRANT SELECT,INSERT,UPDATE,DELETE ON '||OWNER||'."'||OBJECT_NAME||'" TO ROK_S;' FROM DBA_OBJECTS WHERE OWNER='O2OMDDL' AND OBJECT_TYPE IN ('TABLE');

select 'GRANT SELECT ON '||OWNER||'.'||OBJECT_NAME||' TO MCIVER_F;' FROM DBA_OBJECTS WHERE OWNER='OADBA' AND OBJECT_TYPE IN ('TABLE');

select distinct owner from dba_segments where segment_name in ('MPPI_USERWALLET','MPPI_INSTRUMENT','MPPI_INSTRUMENT_TYPE','WALLET_PAYMENT_INSTRUMENT','MPPI_TRANSACTION','TOPUP_TRANSACTION','MPPI_AUDIT_USERGENERAL');

select 'GRANT SELECT ON '||OWNER||'.'||SEGMENT_NAME||' TO BI_DSTAGE_EXT;'  FROM dba_segments where segment_name in ('MPPI_USERWALLET','MPPI_INSTRUMENT','MPPI_INSTRUMENT_TYPE','WALLET_PAYMENT_INSTRUMENT','MPPI_TRANSACTION','TOPUP_TRANSACTION','MPPI_AUDIT_USERGENERAL');

select  FREE_SPACE,MAX_FREE_SIZE,USED_SPACE,AVG_USED_SIZE from v$shared_pool_reserved;
select 'exec DBMS_JOB.BROKEN (job==> '||JOB|| ',broken==>TRUE);'  from dba_jobs where LOG_USER!='SYS;

select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||' ;' from dba_role_privs where GRANTEE='C_NALABOTHU_R';

select 'execute dbms_stats.gather_table_stats (ownname =>'||''''||OWNER||''''||',tabname=> '||''''||TABLE_NAME||''''||',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,degree => 8,cascade => TRUE);' from dba_tables where OWNER='STAGING' and LAST_ANALYZED IS NULL;

select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

select profile,resource_name,limit from dba_profiles order by profile;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

select table_name,constraint_name,status from dba_constraints where constraint_name='SYS_C008302';

select count(*),OBJECT_TYPE,owner from dba_objects where owner NOT IN ('SYSTEM','SYS','QUEST') group by OBJECT_TYPE,owner;

select owner,last_analyzed,num_rows from dba_tables where table_name=''

select bytes/1024/1024,segment_name from dba_segments where segment_type='INDEX' and owner='QUEST' order by 1;

select TABLESPACE_NAME,SEGMENT_NAME,EXTENTS from dba_segments where segment_name='AUD$';

select 'alter system kill session '||''''||sid||','||serial# ||''''||' immediate ;' from v$session  where trunc(logon_time)<sysdate;

select sid||','||username||','||osuser||','||to_char(logon_time,'DD-MON-YY HH24:MI') from v$session where program like '%TOAD%' and logon_time < sysdate-4  order by logon_time;

select sid,serial#,logon_time from v$session where upper(program) like '%TOAD%';

select holding_session from dba_blockers a where not exists (select  'x'  from dba_waiters b where b.waiting_session = a.holding_session)

select default_tablespace from dba_users ;

select username,account_status from dba_users where username like ''

 select PRIVILEGE from  dba_sys_privs where GRANTEE=MULLIGAN_N';

select GRANTED_ROLE from DBA_ROLE_PRIVS  where GRANTEE='HSERVER';

select PRIVILEGE from  dba_sys_privs where GRANTEE='HSERVER';

select sum(bytes/1024/1024) from dba_segments where owner='O2OMDML';

select * from v$access where object=''

select tablespace_name, username, bytes, max_bytes  from dba_ts_quotas where username='MKALDAS';


select count(*),trunc(first_time) from v$log_history group by trunc(first_time);

select sum(bytes/1024/1024/1024) from dba_segments;

select sum(bytes/1024/1024) from dba_segments where segment_name='AUD$';

select sum(bytes/1024/1024) from dba_segments where owner='EPIMM';

select tablespace_name,bytes/1024/1024/1024 from sm$ts_avail order by 1;

select tablespace_name,bytes/1024/1024/1024 from sm$ts_used order by 1;

select tablespace_name,bytes/1024/1024/1024 from sm$ts_free order by 1;

select bytes/1024/1024 as mb,tablespace_name from sm$ts_free where tablespace_name='SYSTEM';

select bytes/1024/1024,file_name from dba_data_files where tablespace_name='UNDOTBS1'

select bytes/1024/1024 as mb,tablespace_name from sm$ts_avail;

select bytes/1024/1024 as mb,tablespace_name from sm$ts_used;

select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup;

select sid,serial#,status,logon_time from v$session where program like 'rman%';

select count(*) from v$session where type='USER';

select count(*),trunc(logon_time)  from v$session where type='USER' group by trunc(logon_time);

select fuzzy,count(*) from v$datafile_header group by fuzzy;

select sid,failures,last_date,this_date from dba_jobs_running where job in (' ');

select sid,serial#,username from v$session where lockwait is not null;

select * from gv$session where lockwait is not null;

select * from v$session where lockwait is not null;

s

select HOLDING_SESSION from dba_blockers where HOLDING_SESSION not in (select WAITING_SESSION from dba_waiters);

select inst_id from gv$instance;

select inst_id,username,count(*) from gv$session group by inst_id,username;

select hash_value from v$sql where sql_text like ' ';

select hash_value,sql_text from v$sqltext where hash_value = '&1' order by piece;

select * from gv$lock where block <1;

 select  TABLESPACE_NAME,
  2  ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='amo_msoffline_std';

select file_name from dba_temp_files;

select memeber from v$logfile;

select name from v$controlfile;

select open_mode from v$database;

select instance_name from v$instance;

select name from v$tablespace;

select name,status from v$datafile;

select temporary_tablespace from dba_users;

select * from dba_synonyms where owner='';

select username,owner,action_time,tiemstamp from dba_audit_objects where object_name='';

select max(sequence#) from v$log_history;

select host_name from v$instance;

select host from dba_db_links;

select dbid from v$database;

select * from registry$history;

select * from dba_registry;

select * from v$backup;

select * from v$recoverfile;

select distinct status from v$datafile;

select TEXT from dba_source where name='VERIFY_FUNCTION';

select * from V$PWFILE_USERS;

select * from ejbtimer;
===========================================================================================================================

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

alter user username identified by

alter database datafile '' offline drop;

alter database datafile 'PATH'  resize ;

alter database open;

alter database default temporary tablespace '';

alter user QUEST default tablespace QUEST_INDEX;

alter tablespace UNDOTBS1 add datafile '/crmc1p_db17/oradata/undotbs05.dbf' size 2000m;

alter database backup controlfile to trace;

alter profile O2_USER_PROFILE limit PASSWORD_VERIFY_FUNCTION NULL

alter user SRIRAM_S PASSWORD EXPIRE;

ALTER USER C_NALABOTHU_R QUOTA 10M ON USR;

alter view STAGING.TD_D_SCORE_V compile;

CREATE INDEX "IX_sms_sentstate_3" ON "sms_sentstate"(CustomerID) TABLESPACE "O2OM_DATA_INDEX_01";

===========================================================================================================================
drop tablespace tablespace_name including contents and datafiles;

drop temporary tablespace tablespace_name including contents and datafiles;

drop user username;

drop user username cascade;

drop table tablename purge;

drop table tablename cascade constraints;

drop table tablename;
====================================================================================================

find tablespaces is online or offiline :

select  tablespace_name, online_status from dba_data_files where tablespace_name='tablespace_name';

find tablespaces is autoexten, tbalespace name :

select file_name , tablespace_name, autoextensible from dba_data_files;


find the privilege in database;



select  privilege||' '||table_name  "Privileges for " from  dba_tab_privs where  grantee='&data'
union
select  privilege from  dba_sys_privs where grantee='&data'
union
select granted_role from dba_role_privs where  grantee='&data';



continues ....

Archive Generation is more in oracle

chceck tbs in begin backup mode:
================================

select a.tablespace_name
from sys.dba_data_files a, sys.v_$backup b
where b.status = 'ACTIVE'
and b.file# = a.file_id
group by a.tablespace_name;


Query to find the session that is generating more Archives
This Query is to find the session that is generating more Archives.
====================================================================

col program for a10
col username for a10
select to_char(sysdate,'hh24:mi'), username, program , a.sid, a.serial#, b.name, c.value
from v$session a, v$statname b, v$sesstat c
where b.STATISTIC# =c.STATISTIC#
and c.sid=a.sid and b.name like 'redo%'
order by value;





Check which session is generating more redo.
============================================
set pages 1000
set lines 140
SELECT s.sid, s.serial#, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc, 1, 2, 3, 4;


SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec FROM v$session s, v$transaction t
WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;


The o/p of the query gives the sid , Number of undo blocks used and Number of undo records used that were happening during that time.
From the sid we can get the request id and the request name.



select sql_text from v$sqltext where hash_value=( select prev_hash_value from v$session where sid='&sid');

select sid||' - '||serial#||' - '||osuser||' - '||username||' - '||machine||' - '||status||' - '||logon_time
from v$session where sid=&123;

Tablespace used and free space

set linesize 400

set pagesize 20

set heading on

select c.tablespace_name as "TABLESPACE",

ROUND((b.total_space_available/1048576),2) AS "SIZE MB",

ROUND((b.total_space_available/1048576)-(a.free_space/1048576),2) as "USED MB",

ROUND((a.free_space/1048576),2) as "FREE MB",

ROUND(100 - (((a.free_space/1048576)/(b.total_space_available/1048576))*100),1)||' %'

as "% USED",ROUND((((a.free_space/1048576)/(b.total_space_available/1048576))*100),1)||' %' as "% FREE"

from (select sum(bytes) as free_space,tablespace_name

from dba_free_space -- Free space in permanent tablespace

group by tablespace_name UNION ALL

select sum(bytes_free) as free_space,tablespace_name

from v$temp_space_header -- Free space in temporary tablespaces

group by tablespace_name) a, -- Calculate the free space that each tablespace has

(select sum(user_bytes) as total_space_available,tablespace_name

from dba_data_files -- Total space in permanent tablespaces

group by tablespace_name

UNION ALL select sum(user_bytes) as total_space_available,tablespace_name

from dba_temp_files -- Total space in temporary tablespaces

group by tablespace_name) b, -- Calculate the total size of each TS

dba_tablespaces c

where a.tablespace_name (+) = b.tablespace_name and -- show TS that are full so have no entries in dba_free_space

b.tablespace_name = c.tablespace_name 

order by ROUND(100 - (((a.free_space/1048576)/(b.total_space_available/1048576))*100),1) DESC;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Check the datafile size using the tablespace name.

set linesize 150

column file_name format a50

column tablespace_name format a10

select file_name,bytes/1024/1024 ,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&tablespace_name';

For Temp size

set linesize 150

column file_name format a50

column tablespace_name format a10

select file_name,bytes/1024/1024 ,AUTOEXTENSIBLE from dba_temp_files where tablespace_name='&tablespace_name';

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ASM Tablespace Utilization Script

----------------------------------

SET LINESIZE 300

SET PAGESIZE 9999

SET VERIFY off

COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'

COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'

COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'

COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'

COLUMN state                  FORMAT a11           HEAD 'State'

COLUMN type                   FORMAT a6            HEAD 'Type'

COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (GB)'

COLUMN free_mb                FORMAT 999,999,999   HEAD 'Free Size (GB)'

COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (GB)'

COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'

SELECT

    distinct name                            group_name

  , sector_size                              sector_size

  , block_size                               block_size

  , allocation_unit_size                     allocation_unit_size

  , state                                    state

  , type                                     type

  , round(total_mb/1024)                     "total_gb"

  , round(free_mb/1024)                      "free_gb"

  , round((total_mb - free_mb) / 1024)       "used_gb"

  , round((1- (free_mb / total_mb))*100, 2)  "pct_used"

from v$asm_diskgroup ORDER BY name

/

ASM Disk Space Usage Script (In Detail)

---------------------------------------

SET LINESIZE 150 

SET PAGESIZE 9999 

SET VERIFY off 

COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name' 

COLUMN disk_file_path FORMAT a17 HEAD 'Path' 

COLUMN disk_file_name FORMAT a20 HEAD 'File Name' 

COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group' 

COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)' 

COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)' 

COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used' 

break on report on disk_group_name skip 1 

compute sum label 'Disk Group' of total_mb used_mb on disk_group_name 

compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report 

SELECT 

 NVL(a.name, '[CANDIDATE]') disk_group_name 

 , b.path disk_file_path 

 , b.name disk_file_name 

 , b.failgroup disk_file_fail_group 

 , b.total_mb total_mb 

 , (b.total_mb - b.free_mb) used_mb 

 , ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used 

FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER' 

ORDER BY a.name 

/

Datafiles of a particular TableSpace

-------------------------------------

set pages 9999 lines 300

col tablespace_name for a30

col file_name for a80

select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB 

from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;

ASM Disk Database Files Script

------------------------------

set pages 9999 lines 300

col full_alias_path for a70

col file_type for a15

select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, 

        system_created, alias_directory, file_type

 from ( select b.name gname, a.parent_index pindex, a.name aname, 

               a.reference_index rindex , a.system_created, a.alias_directory,

               c.type file_type

        from v$asm_alias a, v$asm_diskgroup b, v$asm_file c

        where a.group_number = b.group_number

              and a.group_number = c.group_number(+)

              and a.file_number = c.file_number(+)

              and a.file_incarnation = c.incarnation(+)

      )

 start with (mod(pindex, power(2, 24))) = 0

             and rindex in 

                 ( select a.reference_index

                   from v$asm_alias a, v$asm_diskgroup b

                   where a.group_number = b.group_number

                         and (mod(a.parent_index, power(2, 24))) = 0

                         and a.name = '&DATABASE_NAME'

                 )

 connect by prior rindex = pindex;

TABLESPACE DDL

--------------

set pagesize 0

SET LONG 9999999

select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;

To resize a datafile (ASM)

--------------------------

alter database datafile '&datafile_name' resize 4096M;

If AUTOEXTEND ON

----------------

alter database datafile '&datafile_name' autoextend on maxsize 8G;

To add a new datafile in a tablespace (ASM)

-------------------------------------------

BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile

select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';

ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;

If AUTOEXTEND ON

----------------

ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;

To Create a new tablespace  (ASM)

---------------------------------

CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;

If AUTOEXTEND ON

----------------

CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;

Schemas in a tablespace

-----------------------

set pages 9999 lines 300

col "size MB" format 999,999,999

col "Objects" format 999,999,999

select    obj.owner "Owner"

,    obj_cnt "Objects"

,    decode(seg_size, NULL, 0, seg_size) "size MB"

from     (select owner, count(*) obj_cnt from dba_objects group by owner) obj

,    (select owner, ceil(sum(bytes)/1024/1024) seg_size

    from dba_segments group by owner) seg

where     obj.owner  = seg.owner(+)

order    by 3 desc ,2 desc, 1

/

All schema object details in a tablespace

-----------------------------------------

set pages 9999 lines 300

col tablespace_name format a15

col segment_name format a40

col segment_type format a20

col PARTITION_NAME format a20

col mb format 999,999,999

select    owner

,       tablespace_name

,       segment_name

,       segment_type

,       PARTITION_NAME

,    ceil(sum(bytes) / 1024 / 1024) "Size in MB"

from    dba_segments

where    tablespace_name like '&tablespace_name'

group    by segment_name

order   by ceil(sum(bytes) / 1024 / 1024) desc

/

SELECT SUBSTR(data_files.tablespace_name,1,20) "Tablespace Name",

       tot_create_blk /1024/1024 created_size,

       ROUND(max_free_blk / 1024/1024,2) max_free,

       ROUND(tot_free_blk / 1024/1024,2) tot_free,

       ROUND((tot_free_blk/tot_create_blk*100),3) pct_free

FROM   ( SELECT tablespace_name,

                SUM(bytes) tot_create_blk

         FROM sys.dba_data_files

         GROUP BY tablespace_name ) data_files,

       ( SELECT tablespace_name,

                MAX(bytes) max_free_blk,

                SUM(bytes) tot_free_blk

         FROM sys.dba_free_space

         GROUP BY tablespace_name ) free_space

       WHERE data_files.tablespace_name = free_space.tablespace_name

       and data_files.tablespace_name = '&tablespace'

ORDER BY data_files.tablespace_name;

============================================================================================================