Backup Database Link Before Cloning & create them back after clone.

 set pages 0

set linesize 200

set feedback off

spool db_links_backup.sql

select 'create database link ' || db_link || ' connect to ' || username || ' identified by <password> using ''' || host || ''';'

from dba_db_links

where owner = 'PUBLIC';

spool off;

Script to remove archives from Solaris Operating system

 #Author : Abdul Wasiq

CDATE=`date +%d-%m-%Y`

{

echo " Count of archivelog file olderthan 3 days    "

echo " ---------------------------------------------------------- "

/usr/bin/find /u02/oradata/db/archivelog/ -mtime +3 |wc -l

echo "                                                             "

echo " ----------------------------------------------------------- "

echo " List of 3 days old archivelog files "

echo " ----------------------------------------------------------- "

/usr/bin/find /u02/oradata/db/archivelog/ -mtime +3 -exec ls -lrth '{}' \;

echo " ----------------------------------------------------------- "

echo " Removing 3 days old archivelog files  "

echo " ----------------------------------------------------------- "

/usr/bin/find /u02/oradata/db/archivelog/ -mtime +3 -exec rm -f '{}' \;

} > /backup/scripts/logs/arch_remove_$CDATE.log

echo "==================== Completed =================="

Synonyms without a prefix or accessing the object without prefix

 set pages 0 lines 200 trims on verify off feedback off

accept grants_to  prompt 'Enter user to grant privileges: '

accept schema     prompt 'Enter schema on which to grant: '

spool tmpgrants.sql

set pages 0

-- Query for tables (views) in the specified schema

select 'grant select on ' || owner || '.' || table_name || ' to &grants_to;', chr(10),

       'create synonym &grants_to.' || table_name || ' for ' || owner || '.' || table_name || ';', chr(10)

from all_tables  -- Change from user_views to all_tables

where owner = upper('&schema')

union all

select 'grant select on ' || owner || '.' || view_name || ' to &grants_to;', chr(10),

       'create synonym &grants_to.' || view_name || ' for ' || owner || '.' || view_name || ';', chr(10)

from all_views  -- Change from dba_views to all_views

where owner = upper('&schema');

spool off;

Database Features Usage

 set pages 999

set linesize 2000

col c1 heading 'feature'    format a45

col c2 heading 'times|used' format 999,999

col c3 heading 'first|used'

col c4 heading 'used|now'

select name c1,detected_usages  c2,first_usage_date c3,currently_used  c4 from dba_feature_usage_statistics where first_usage_date is not null;

Script to remove archives

#!/bin/bash

#

# Script to Remove archive log files

#

########################################################################## 

CDATE=`date +%d-%m-%Y`

{

echo "##########################################################################"

echo " Removing olderthan 5 days archive log files.... "

/usr/bin/find /proddb/arch/* -mtime +5 -exec ls -lrth '{}' \;

/usr/bin/find /proddb/arch/* -mtime +5 -exec rm -f '{}' \;

echo "=================== END ========================"

} > /backup/logs/arch_remove_$CDATE.log         

Script to move files to another directory

This script is designed and tested on IBM AIX machine.

#!/bin/ksh

export INSTANCE=$(hostname -s)

export MAILFROM=$INSTANCE'@idhar.udhar.com'

SUBJECT="`hostname` Weblogic old files found in u01 and moved to another directory wllogs"

TO=dbs@idhar.udhar.com

echo "File system usage before moving the files to another directory \n \n" >> /home/oracle/scripts/Filesystem_usage.txt

df -gt /u01 /wllogs >> /home/oracle/scripts/Filesystem_usage.txt

export srcdir=/u01/Oracle/Middleware/Oracle_Home/user_projects/domains/base_domain

setTag=`date '+%d%b%y_%H%M%S_weblogic_oldlogs'`

destdir="/wllogs/CRON_WEBLOGIC_LOGS/$setTag"

export destdir

mkdir $destdir

find $srcdir -name "heapdump.*" -mtime +5 -exec mv {} $destdir \;

sleep 10

echo "File system usage after moving the files to wllogs \n \n" >>/home/oracle/scripts/Filesystem_usage.txt

df -gt /u01 /wllogs >> /home/oracle/scripts/Filesystem_usage.txt

echo "===================="   >>/home/oracle/scripts/Filesystem_usage.txt

cd $destdir

echo "Below files are moved to wllogs from u01 \n \n" >> /home/oracle/scripts/Filesystem_usage.txt

ls -ltr >> /home/oracle/scripts/Filesystem_usage.txt

mailx -s "$SUBJECT" -r "$MAILFROM" $TO < /home/oracle/scripts/Filesystem_usage.txt

Gather Stats for schemas

set serveroutput on;

set echo on;

set timing on;

set time on;

EXECUTE dbms_stats.set_global_prefs('DEGREE', DBMS_STATS.AUTO_DEGREE);

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_BANK_DHL', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_PROC_DHL', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_BANK_CBD', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MERCH_AA_PROC', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'MAPS_PG_CBD', estimate_percent => dbms_stats.auto_sample_size, CASCADE=> true, GRANULARITY=> 'ALL');

EXEC DBMS_STATS.gather_dictionary_stats;

--EXEC DBMS_STATS.gather_fixed_objects_stats;

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SYSTEM FLUSH BUFFER_CACHE;

exit;

Call above sql script through shell script and crontab entry is below the reference.

#!/bin/bash

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2

fdate=`date '+%d-%b-%Y-%H_%M'`

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dbadba098#@MERUAT2 @/orabackup/scripts/mer_pos_eee_schema_stats.sql >/orabackup/scripts/logs/mer_pos_eee_schema_stats-${fdate}-execution.log

exit;

Health Check SQL Queries

set lines 999 pages 999;

set echo off;

prompt #########################Tablespace Monitoring:#########################

select a.tablespace_name, round(assigned_space) "ALLOCATED (MB)", round((nvl(free_space,0) + INCRM)) "FREE (MB)", round((assigned_space - nvl(free_space,0) - INCRM)) "USED (MB)",

round( ((assigned_space - nvl(free_space,0) - INCRM) / assigned_space)*100) "% Full"

from(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) - sum(bytes)/(1024*1024) INCRM from dba_data_files group by tablespace_name) A,

(select  tablespace_name, count(*) num2, (sum(bytes)/(1024*1024)) free_space

from dba_free_space a group by tablespace_name) B where b.tablespace_name(+)=a.tablespace_name

union all 

select a.tablespace_name, round(assigned_space) "ALLOCATED (MB)", round((nvl(free_space,0) + INCRM)) "FREE (MB)", round((assigned_space - nvl(free_space,0) - INCRM)) "USED (MB)",

round(((assigned_space - nvl(free_space,0) - INCRM ) / assigned_space)*100) "% Full"

from(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space,

Sum(Decode(Sign(Maxbytes - Bytes), -1, Bytes, Maxbytes))/(1024*1024) - Sum(Bytes)/(1024*1024) Incrm From Dba_Temp_Files Group By Tablespace_Name) A,

(Select  Tablespace_Name, Count(*) Num2, (Sum(free_space)/(1024*1024)) Free_Space

FROM DBA_TEMP_FREE_SPACE GROUP BY TABLESPACE_NAME) B WHERE B.TABLESPACE_NAME(+)=A.TABLESPACE_NAME 

Order By "% Full" Desc;

prompt #########################Process Utilization:#########################

set echo on;

SELECT INST_ID,upper(resource_name) as resource_name,current_utilization,max_utilization,initial_allocation FROM gv$resource_limit WHERE resource_name in ('processes', 'sessions');

set echo off;

prompt #########################Check archive log usage FRA:#########################

col name for a25;

set echo on;

select name,(SPACE_LIMIT/1024/1024/1024), (SPACE_USED/1024/1024/1024), (SPACE_RECLAIMABLE/1024/1024/1024) from v$recovery_file_dest;

set echo off;

prompt #########################Instance Startup :#########################

set echo on;

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

set echo off;

prompt #########################ASM Disk usage:#########################

set echo on;

SELECT NAME,TOTAL_MB,FREE_MB,ROUND((1- (FREE_MB / TOTAL_MB))*100, 2)  "percentage used" FROM v$asm_diskgroup order by 4 desc;

set echo off;

prompt #########################Instance_name:#########################

set echo on;

select instance_name, host_name from gv$instance order by 1;

set echo off;

prompt #########################Open_mode:##########################

set echo on;

select name, open_mode from gv$database  order by 1;

set echo off;

prompt #########################Information About Each Container in a CDB:##########################

COLUMN NAME FORMAT A8

set echo on;

SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

set echo off;

prompt #########################Name and Open Mode of Each PDB:#########################

COLUMN NAME FORMAT A15

COLUMN RESTRICTED FORMAT A10

COLUMN OPEN_TIME FORMAT A40

set echo on;

SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

set echo off;

prompt #########################Locked Object:#########################

set echo on;

select object_name,os_user_name,session_id from all_objects A ,v$locked_object L where A.object_id=L.object_id group by object_name,os_user_name,session_id order by 1,3;

set echo off;

prompt #########################Block corruption:#########################

set echo on;

select count(*) from v$database_block_corruption;

set echo off;

prompt #########################Table_Stats:#########################

set echo on;

select distinct OWNER,TRUNC(LAST_ANALYZED) from DBA_TABLES where owner like '%AA%' group by owner,last_analyzed order by 1;

exit;

Copy all above queries in sql file and call from shell script for each schema in the database...

#!/bin/bash

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2

fdate=`date '+%d-%b-%Y-%H_%M'`

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@MERSIT2 @/orabackup/scripts/DTS_UAT_DB_MERCH_monitor.sql >/orabackup/scripts/logs/DTS_UAT_MERSIT_monitor-${fdate}-execution.log

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@MERUAT2 @/orabackup/scripts/DTS_UAT_DB_MERCH_monitor.sql >/orabackup/scripts/logs/DTS_UAT_MERUAT_monitor-${fdate}-execution.log

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@POSSIT2 @/orabackup/scripts/DTS_UAT_DB_POS_monitor.sql >/orabackup/scripts/logs/DTS_UAT_POSSIT_monitor-${fdate}-execution.log

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@POSUAT2 @/orabackup/scripts/DTS_UAT_DB_POS_monitor.sql >/orabackup/scripts/logs/DTS_UAT_POSUAT_monitor-${fdate}-execution.log

exit;

Script to find temp tablespace free and used space

tempfree.sql

SELECT   A.tablespace_name tablespace, D.mb_total,

         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM     v$sort_segment A,

         (

         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

         FROM     v$tablespace B, v$tempfile C

         WHERE    B.ts#= C.ts#

         GROUP BY B.name, C.block_size

         ) D

WHERE    A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

tempspace.sql

-- GE Restricted

select v.sid, v.serial#, v.status, v.username, s.tablespace, s.segtype

from v$session v, v$sort_usage s

where v.saddr=s.session_addr

/

Script Name : temp_top_sql.sql

description : Run below script to monitor the temp tablespace usage and check what running sqltext.

set linesize 400

column osuser format a20

column sid format 9999

column serial# format 99999

column username format a20

column program format a20

column vp.value format 99999999

        select s.osuser, s.sid, s.serial#,s.username, s.program,

               sum(u.blocks)*vp.value/1024/1024 sort_size_MB

        from   sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp

        where  s.saddr = u.session_addr

          and  vp.name = 'db_block_size'

            group  by s.osuser, s.sid, s.serial#,s.username, s.program, vp.value;

SELECT s.sid, s.status, sq.address,  sq.sql_id, sq.sql_text

FROM v$session s, v$sql sq

WHERE s.status = 'ACTIVE'

and s.sql_hash_value = sq.hash_value

and s.sql_address = sq.address

and s.sid IN (

select s.sid

     from   sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp

where  s.saddr = u.session_addr

            and  vp.name = 'db_block_size'

         group  by s.osuser, s.sid, s.serial#,s.username, s.program, vp.value);

Script to check all tablespace details in the database

set linesize 300

set pagesize 300

select a.tablespace_name, b.total,

       c.available, d.large_extent,

       (b.total - c.available) used,

       round((((b.total - c.available)*100)/b.total),2) pctused

  from (select tablespace_name, (sum(bytes)/1024)/1024 total

          from dba_data_files group by tablespace_name)  b,

       (select tablespace_name,  nvl(round(((sum(bytes)/1024)/1024),2),0) available

          from dba_free_space group by tablespace_name)  c,

       (select tablespace_name, (max(bytes)/1024)/1024 large_extent

          from dba_free_space group by tablespace_name)  d,

       dba_tablespaces  a

 where a.tablespace_name = b.tablespace_name(+)

   and a.tablespace_name = c.tablespace_name(+)

   and a.tablespace_name = d.tablespace_name(+)

order by pctused

/

Script to find top10active jobs in database.

 /* top10active.sql
    shows the top 10 longest-active user sessions
*/
col osuser format a10 trunc
col LastCallET     format 99,999
col username format a10 trunc
col uprogram format a25 trunc
col machine format a10 trunc
set linesize 132 pages 20
set verify off
accept trgtuser char default ALL prompt 'Limit to what userid <ALL> : '
select * from (
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
  s.sid, s.status,
  floor(last_call_et/60) "LastCallET",
 s.username, s.osuser, 
 p.spid, s.module || ' - ' || s.program uprogram, 
s.machine, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
  and s.type = 'USER'
  and s.username is not null
  and s.status = 'ACTIVE'
  and (s.username = upper('&trgtuser') or upper('&trgtuser') = 'ALL')
order by 4 desc)
where rownum < 31;

Script to check tablespace details.

 

column file_name format a55

set linesize 132

set pages 200

column tablespace_name format a11

set verify off

accept tbs prompt 'What is the Tablespace Name: '

SELECT  dts.tablespace_name, 

NVL(ddf.bytes / 1024 / 1024, 0) avail,

NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,

NVL(dfs.bytes / 1024 / 1024, 0) free,

TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00')

"Used %" ,

TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes

* 100, 0), '990.00') free_pct,

decode(sign(

(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 /

1024, 0)),-1,0,(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 / 1024, 0))  "Required MB"

FROM 

sys.dba_tablespaces dts, 

(select tablespace_name, sum(bytes) bytes 

from dba_data_files group by tablespace_name) ddf, 

(select tablespace_name, sum(bytes) bytes 

from dba_free_space group by tablespace_name) dfs 

WHERE 

dts.tablespace_name = ddf.tablespace_name(+) 

AND dts.tablespace_name = dfs.tablespace_name(+) 

and dts.tablespace_name = '&tbs';

select file_name,bytes/1024/1024 from dba_data_files

where tablespace_name = '&tbs';

To find information about locks in the database

To check locks in the database:

col schema format a8

col object format a20

select substr(a.os_user_name,1,9)    "OS User" 

,      substr(a.oracle_username,1,8) "DB User" 

,      substr(b.owner,1,8)           "Schema" 

,      e.sid      "Sid"

,      substr(b.object_name,1,20)    "Object" 

,      substr(b.object_type,1,10)    "Type" 

,      substr(c.segment_name,1,5)    "RBS" 

,      substr(d.used_urec,1,12)      "# of Records" 

from v$locked_object a 

,    dba_objects b 

,    dba_rollback_segs c 

,    v$transaction d 

,    v$session e 

where a.object_id =  b.object_id 

  and a.xidusn    =  c.segment_id 

  and a.xidusn    =  d.xidusn 

  and a.xidslot   =  d.xidslot 

  and d.addr      =  e.taddr;

Locksid

====

select substr(a.os_user_name,1,9)    "OS User"

,      substr(a.oracle_username,1,8) "DB User"

,      substr(b.owner,1,8)           "Schema"

,      substr(b.object_name,1,20)    "Object"

,      substr(b.object_type,1,10)    "Type"

,      substr(c.segment_name,1,5)    "RBS"

,      substr(d.used_urec,1,12)      "# of Records"

, e.sid

from v$locked_object a

,    dba_objects b

,    dba_rollback_segs c

,    v$transaction d

,    v$session e

where a.object_id =  b.object_id

  and a.xidusn    =  c.segment_id

  and a.xidusn    =  d.xidusn

  and a.xidslot   =  d.xidslot

  and d.addr      =  e.taddr

/

comment : usefull to check sql for sid or locksql

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

Check the size of different pools in database using SQL command and database parameter file.

set linesize 500
set pages 200
col name for a35
col value for a35

select name, value from v$parameter2 where name in ('sga_target','java_pool_size','large_pool_size','shadow_core_dump','db_cache_size','sga_max_size','sga_target','shared_pool_reserved_size','shared_pool_size');

Checking at OS Level.

egrep -i 'shared_pool_size|shared_pool_reserved_size|db_cache_size|sga_max_size|sga_target' initTEST.ora

*.db_cache_size=2G
*.sga_max_size=0
*.sga_target=0
*.shared_pool_reserved_size=320M
*.shared_pool_size=3500M
*pga_aggregate

Solaris tar and untar commands

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

solaris tar commands

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

nohup tar -cvf - appl |gzip -c > /backup/rman_1060/bhaogpp1/bin_25feb13/appl.tar.gz &

nohup tar -cvf - 10.1.2 |gzip -c > /backup/rman_1060/bhaogpp1/bin_25feb13/1012.tar.gz &

 nohup tar -cvf - comn |gzip -c > /backup/rman_1060/bhaogpp1/bin_25feb13/comn.tar.gz &

 nohup tar -cvf - 10.1.3 |gzip -c > /backup/rman_1060/bhaogpp1/bin_25feb13/1013.tar.gz &

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

solaris untar commands

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

 nohup gunzip -c /backup/rman_0008/bhaerp99/BIN_18oct12/comn.tar.gz | tar -xvf - &

 nohup gunzip -c /backup/rman_0008/bhaerp99/BIN_18oct12/1012.tar.gz | tar -xvf - &

 nohup gunzip -c /backup/rman_0008/bhaerp99/BIN_18oct12/appl.tar.gz | tar -xvf - &

 nohup gunzip -c /backup/rman_0008/bhaerp99/BIN_18oct12/1013.tar.gz | tar -xvf - &

 nohup  gunzip -c /backup/rman_0008/bhaerp99/BIN_18oct12/1120.tar.gz | tar -xvf - &

Change database global name

  Global database name

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

alter database rename global_name to abcgapn1.bh.com;

Linux tar and untar command

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

Linux Tar Command:

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

cd /u01/oracle/

nohup tar -czvf /bhapgag1/staging/bin_gpap1_01Sep2013/PGAP1_1120.tar.gz 11.2.0 > /bhapgag1/staging/bin_gpap1_01Oct2013/1120.log &

nohup tar -czvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_appl.tar.gz appl > /bhapgag1/staging/bin_gpap1_01Oct2013/appl.log &

nohup tar -czvf /bhapgag1/staging/bin_gpap1_01Oct2013/GAP1_comn.tar.gz comn > /bhapgag1/staging/bin_gpap1_01Oct2013/comn.log &

nohup tar -czvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_1012.tar.gz 10.1.2 > /bhapgag1/staging/bin_gpap1_01Oct2013/10.1.2.log &

nohup tar -czvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_1013.tar.gz 10.1.3 > /bhapgag1/staging/bin_gpap1_01Oct2013/10.1.3.log &

Linux Untar Command:

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

cd /u01

nohup tar -xzvf /bhapgag1/staging/bin_gpap1_01Sep2013/PGAP1_1120.tar.gz > /bhapgag1/staging/bin_gpap1_01Oct2013/1120_untar.log &

nohup tar -xzvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_appl.tar.gz > /bhapgag1/staging/bin_gpap1_01Oct2013/appl_untar.log &

nohup tar -xzvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_comn.tar.gz > /bhapgag1/staging/bin_gpap1_01Oct2013/comn_untar.log &

nohup tar -xzvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_1012.tar.gz > /bhapgag1/staging/bin_gpap1_01Oct2013/10.1.2_untar.log &

nohup tar -xzvf /bhapgag1/staging/bin_gpap1_01Oct2013/PGAP1_1013.tar.gz > /bhapgag1/staging/bin_gpap1_01Oct2013/10.1.3_untar.log & 

setup ssh

 setup ssh

++++++++++

on Node1:

---------

mkdir $HOME/.ssh

ssh-keygen -t rsa

copy content of id_rsa.pub to authorized_keys on other (node2) server

on Node2:

---------

and do the same on node2

mkdir $HOME/.ssh

ssh-keygen -t rsa

copy content of id_rsa.pub to authorized_keys on other (node1) server 

How to to find out toad session

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

To find out toad session

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

select p.spid,s.sid,s.serial#,s.username,s.status,s.last_call_et,p.program,p.terminal,logon_time,module,s.osuser

from V$process p,V$session s where s.paddr = p.addr and s.status = 'ACTIVE' and s.module like '%TOAD%';

How to get encrypted password of a user.

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

To get encrypted password of a user.

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

select 'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password from  dba_users where username = 'GLOGOWNER';

How to remove archives for a specific date?

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

How to remove archives for a specific date?

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

$cd /u01/archive

$rm `ls -l|grep "Apr 13" |awk '{print $9}'`

How to create the .bat file for easy access of Linux, Red hat & Unix machines without entering the Password through Putty

open a new notepad file.
start putty ctssd –l root -pw root123
save as in .bat format file (dev.bat)

Note: Make sure your putty software and the created .bat files (env files) should in the same location.

Just double click on that saved .bat file it will Directly connect to respected server.

You Cannot Analyze a Table Through a Synonym

This was tested on an Oracle 12 database. First I created a table and analyzed it: 

$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Wed May 22 08:56:23 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table wasiq11 (one_col number);

Table created.

SQL> analyze table wasiq11 compute statistics;

Table analyzed.

Then I created a synonym for the table

SQL> create synonym wasiq22 for wasiq11;

Synonym created.

I found that I was able to describe the table via the synonym

SQL> desc wasiq22
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ONE_COL                                            NUMBER

but I could not able to analyze the table through a synonym

SQL> analyze table wasiq22 compute statistics;
analyze table wasiq22 compute statistics
              *
ERROR at line 1:
ORA-00942: table or view does not exist

sqlplus /nolog

This allows you to start a SQL*Plus session without connecting to a database:

-bash-3.2$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Wed May 22 08:27:44 2019

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

SQL> select sysdate from dual;
SP2-0640: Not connected
SQL> show user
USER is ""

SQL>

Oracle: Show installed components/options and feature-usage

To check which components are installed in a database:

SELECT * FROM DBA_REGISTRY;
This view also shows the coresponding schema to a component – and also it’s related schemas.

To check which options are enabled:

SELECT * FROM V$OPTION;
To check which features are used:

SELECT * FROM DBA_FEATURE_USAGE_STATISTICS;