Gather statistics for table,index and schema

Statistics Types:-

1) System statistics :- 

exec dbms_stats.gather_system_stats('Start');

select * from sys.aux_stats$;

2) Optimizer statistics 

Below are the Optimizer Statistics ( tables, indexes and Schema):-

 -- Connect to your Oracle database using a SQL client or SQL*Plus

-- Execute the following command to gather statistics for a specific table

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname     => 'schema_name',
  tabname     => 'table_name',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  cascade     => TRUE
);

-- Execute the following query to view statistics for a specific table

SELECT *

FROM DBA_TABLES

WHERE OWNER = upper('&schema_name')

AND TABLE_NAME = upper('&table_name');

------------------- Stats for Index -----------------------

-- Connect to your Oracle database using a SQL client or SQL*Plus

-- Execute the following command to gather statistics for a specific index

EXEC DBMS_STATS.GATHER_INDEX_STATS(

  ownname     => 'schema_name',

  indname     => 'index_name',

  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE

);

-- Execute the following query to view statistics for a specific index

SELECT *

FROM DBA_INDEXES

WHERE OWNER = upper('&schema_name')

AND INDEX_NAME = upper('&index_name');

-------------------------------------  Stats for SCHEMA ---------------------------

-- Connect to your Oracle database using a SQL client or SQL*Plus

-- Execute the following command to gather statistics for all objects in a schema

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(

  ownname     => 'schema_name',

  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

  cascade     => TRUE

);

-- Execute the following query to view statistics for all objects in a schema

SELECT *

FROM DBA_TAB_STATISTICS

WHERE OWNER = upper('&schema_name');

----- Optimizer Statistics ----------

exec dbms_stats.gather_database_stats;

exec dbms_stats.gather.dictionary_stats;

***************************************************

select table_name from dba_tables where table_name in ('TABLE1','TABLE2');

select owner,segment_name  from dba_segments where segment_name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5');

select * from dba_dependencies where name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5'); --- TABLE1,TABLE2,TABLE3,TABLE4,TABLE5

select (select host_name from v$instance),(select instance_name from v$instance),table_name, count(index_name) "indexes_count" from dba_indexes where table_name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5') group by table_name;

--- READY MADE QUERYIES 

set serverout on;

begin 

for x in (select owner,segment_name  from dba_segments where segment_name in ('TABLE1','TABLE2','TABLE4','TABLE3','TABLE5''))

loop

dbms_output.put_line('EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> '''||x.owner||''''||','||' tabname=>'||''''||x.segment_name||''''||','||'estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);');

end loop;

end ;

/

--EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE1',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE2',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE3',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SCHEMA', tabname=>'TABLE4    ',estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=> TRUE);

Rebuild/Move tables and Rebuild indexes and Shrink tables

 1) Rebuild tables with no long columns and LOB columns (below example for

sequential move)

SQL> alter table <owner>.<table_name> move tablespace <tablespace_name>;

SQL> alter table &owner.&table_name move tablespace &tablespace_name;

2) If time constraint and having multiple cpu's then use below

SQL> alter table <owner>.<table_name> move tablespace <tablespace name>

parallel < no_of_cpu>;

SQL> alter table &owner.&table_name move tablespace &tablespace_name parallel &no_of_cpu;

3) If we are using parallel, then the above statement permanent

changes to the table  for parallelism, so better to reset after

the table rebuild is done

SQL> alter table table_name parallel 1;

SQL> alter table &table_name parallel &no_of_cpu;

4) We need to rebuild  all the table indexes after move them to new tablespaces

SQL>

select 'alter index '|| owner || '.' || index_name || ' rebuild ;'

from dba_indexes

where table_owner = '&owner'

and table_name = '&table_name'

and partitioned = 'NO'

and index_type != 'LOB'

order by owner, index_name;

5) check index status

SQL> select index_name, status from dba_indexes where table_name='&table' ;

SQL> ALTER TABLE pune.city ENABLE ROW MOVEMENT;

SQL> ALTER TABLE pune.city SHRINK SPACE CASCADE;

SQL> alter tablespace users coalesce;

-- Use Segment Advisor

-- Analyze table

SQL> ANALYZE TABLE pune.city COMPUTE STATISTICS;

SQL> ALTER TABLE pune.city COMPRESS FOR ALL OPERATIONS;

HOW TO CHECK IF INDEX NEEDS TO BE REBUILD

1.  SQL>  analyze index &index_name validate structure;

1.1 SQL> select name,height,lf_rows,lf_blks,del_lf_rows from index_stats;

NAME                HEIGHT    LF_ROWS    LF_BLKS    DEL_LF_ROWS

ASIA_INDEX                1                    3                    1                            0

Hint:- if  HEIGHT > 4 -----> REBUILD THE INDEX

           DEL_LF_ROWS < 20%

2. SQL>  analyze index &index_name monitoring usage;   ----> query v$object_usage;

3.  SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SYS', 'ASIA_INDEX');

To shrink tables in oracle database:

--Enable Row Movement

SQL> alter table employees enable row movement;

--Shrink the table  and compact data and release space back to tablespace

SQL> alter table employees shrink space;

--Rebuild indexes

SQL> alter index emp_idx rebuild;

--Disable row movement

SQL> alter table employees disable row movement;

different ways to connect to sqlplus console from remote server

 sqlplus /  as sysdba  ---------------------> from the host server

sqlplus user/passwd@service_or_sid ---------------------> from remote server

sqlplus user/passwd@host:1521/service_or_sid ---------------------> remote server 

to check database status:- 

sudo -iu oracle----> tnsping servicename ----> thats it as per tnsnames.ora entries 

to check server status :-

ping server as per /etc/hosts entries

from shell script 

export OH

export OS

export PATH

export LD_LIBRARY_PATH

sqlplus \/nolog <<< !! > output.txt

conn $constring;

$query;

exit;

!!

Automatically export ORACLE environment variables in Linux

 

cat /etc/oratab | grep -e "^[\a-Az-Z0-9\]\{0,8\}\:*/[[:graph:]]*" | awk -F: '{print $1,$2}' | read a b

$ echo $a

target

 $ echo $b

/usr/local/opt/oracle/app/oracle/product/11.2.0.4

export ORACLE_HOME=$b

export ORACLE_SID=$a

export PATH=$ORACLE_HOME/bin:$PATH

Experience As a Fresher In Oracle Database

1. You'll get almost no help from colleagues to learn and understand the system.

2. You'll be fully rely on internet and various websites to help you resolve and learn the system.

3. You'll be ask to do difficult task even if you know nothing and if you ask for help you'll be taunt for it.

4. You'll get no respect neither from your colleagues or clients if they know you're fresher. 

5. If anything happens bad you'll be the one who will blame for if you were there. 

6. keep your seat belt pack when performing database operation after you grasp some hold on some experience. 

7. crosscheck your steps what you're doing if you are careless then your job in danger.

8. you'll listen some hoax and stories about peoples moving from this team to another , this company to another don't give your attention and just focus on work.

9. don't make friends and enemy at work.

10. Keep upgrading your knowledge also dive into another technologies like development, testing and have keep good company if found any. 

11. Always strive for betterment because everything needs some pay offs whether its relationship, jobs, needs, desires see you'll experience the burden of expectations of your family, relative and  friends from you. Make sure not to be a step for some body. 

12. Always keep your experience notes, gather them keep steps in notepad or  notepad++.

13. Once you get sufficient material to support your actions and experience you'll see things getting simple and smoother. 

14. Always tell less than necessary nobody in meeting wants to understand just they want the issue get resolved. 

15. Your colleague will before you to put there blames on you keep alertness. 

16. Keep checking your mailbox 

17. Always make folders of your team members for their mails. 

18. The daily task is like checking mails resolving them, answering meeting, give your work status for the day or week, requesting and engaging other teams to resolve complex issue. 

19. patching, upgrading, change request (CR), trouble ticket (tt). 

20. Finding contacts and mail ids of SDBA, PAS, CI/CD, SCM team and mail them. 

21. Finding decommission database servers and telling info to other teams.  

22. Oracle provides zero level of improvement if you wish to become expert since its extremely complex in the perspective of its older age and how it works. 

23. Sometime i feel like i am learning 10 centuries at a time we can take an example of oracle versions 10g, 11g, 12c and 19c. 

24. You'll need to frequently raise SR (support request/service request) to oracle support to get help for issue happens on production database and this is not a quickly resolve you need to wait and keep checking updates on oracle support website often needs to have meeting with oracle support guy. Again if the idea from oracle support guy not worked or your team lead/client rejected it you need to ask oracle support guy to review or give another idea/way this will keep going until the final best idea/way can meet. 

Standby DB Parameters

# Changing Protection Mode:-  

alter database set standby database to maximum {availability|performance|protection};

# Status of protection mode on standby database:-

select protection_mode from v$database;

Availability	AFFIRM/NOAFFIRM	SYNC	DB_UNIQUE_NAME
Performance (default)	NOAFFIRM	ASYNC	DB_UNIQUE_NAME
Protection	AFFIRM	SYNC	DB_UNIQUE_NAME

1. With LGWR, default is sync used.
2. With ARCH , only SYNC is valid.
3. SYNC attribute is used for no data loss. It ensure that redo is transmitted successfully at destination before continue.

compatible=release_number

control_file_record_keep_time=no of days (0-365) 

control_files='/path/to/control_file.ctl','/path/to/control_file.ctl';

db_file_name_convert='location_primary_datafile','location_standby_datafile';

db_unique_name=unique_name_for_standby

fal_client=server_self_name  ( no longer recommend can skip)

fal_server=server_source_name (no longer recommend can skip)

instance_name=standby_db_unique_name

# for standby 

log_archive_config='DG_CONFIG=(db_unqiue_name_standby,db_unique_name_primary)'   (highly recommended)

# for primary 

log_archive_config='DG_CONFIG=(db_unqiue_name_primary,db_unique_name_standby)'   (highly recommended)

log_archive_dest_n={LOCATION=path_name | SERVICE=service_name, attribute,attribute,...} ...req

log_archive_dest_state_n={ENABLE|DEFER|ALTERNATE} ....required

log_archive_format=log_%d_%t_%s_%r.arc ----req

log_archive_max_processes=integer      .-----max arcn process (1-30) default 4 

log_file_name_convert='location_redo_logs_primary','location_redo_log_standby'; --------------req

standby_file_management={auto|manual}  ----req

To create standby db:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u02/app/oracle/dbs/stbycf.ctl';

Copy the standby control file.
Copy the backup datafiles.
Copy all available archived redo logs to the standby site.
Copy the online redo logs. This is recommended for switchover and failover operations.

Resize Redo logs in Oracle Database

In RAC, each instance has a redo thread; when you start the instance, it mounts one and only one thread. Therefore if you want to add logs or increase the size, you must do it for each thread. The goal is normally to have the same number and size of logs for each thread.

First, determine what log members each thread has

Here is a sample of a script to show what log members you currently have and their sizes:
-- Script begins here -
spool log.lst
set echo off
set feedback off
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
spool off
-- End of script --
Sample output:
GROUP# THREAD# MEMBER                             ARCHIVED STATUS    MB
------ ------- ---------------------------------- -------- --------- ---
     3       2 /u02/oradata/redologs/redo03a.log  NO       INACTIVE   10
     3       2 /u02/oradata/redologs/redo03b.log  NO       INACTIVE   10
     4       2 /u02/oradata/redologs/redo04a.log  NO       CURRENT    10
     4       2 /u02/oradata/redologs/redo04b.log  NO       CURRENT    10
     5       1 /u02/oradata/redologs/redo05a.log  NO       CURRENT   50
     5       1 /u02/oradata/redologs/redo05b.log  NO       CURRENT   50
     6       1 /u02/oradata/redologs/redo06a.log  NO       INACTIVE  50
     6       1 /u02/oradata/redologs/redo06b.log  NO       INACTIVE  50
     7       1 /u02/oradata/redologs/redo07a.log  NO       INACTIVE  50
     7       1 /u02/oradata/redologs/redo07b.log  NO       INACTIVE  50
     8       1 /u02/oradata/redologs/redo08a.log  NO       INACTIVE  50
     8       1 /u02/oradata/redologs/redo08b.log  NO       INACTIVE  50

EXAMPLE:

Consider the above sample output.  For Thread 2, you have 2 redo log groups with 2 10MB member each. You want to create 4 50MB logs for thread 2.

1. Add 4 new redo log groups to Thread 2, with two member each, the member 50MB in size :

alter database add logfile
thread 2 group 9
('/u02/oradata/redologs/redo09a.log','/u02/oradata/redologs/redo09b.log') size 50M;

alter database add logfile
thread 2 group 10
('/u02/oradata/redologs/redo10a.log','/u02/oradata/redologs/redo10b.log') size 50M;

alter database add logfile
thread 2 group 11
('/u02/oradata/redologs/redo11a.log','/u02/oradata/redologs/redo11b.log') size 50M;

alter database add logfile
thread 2 group 12
('/u02/oradata/redologs/redo12a.log','/u02/oradata/redologs/redo12b.log') size 50M;

2. Once you have added them, rotate the logs ("alter system switch logfile") on instance 2 so that
the active log is 50MB and both of the 10MB logs are inactive.


Rotate the logs:




alter system switch logfile;


select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) fsize
from v$log l, v$logfile f where f.group# = l.group#
and l.thread#=2 ;



repeat until the active log is 50MB and both of the 10MB logs are inactive.


3. Once both of the 10MB logs are inactive, you can drop the redo log groups with the 10mb members.




alter database drop logfile group 3;
alter database drop logfile group 4;


Result:

GROUP# THREAD# MEMBER                            ARCHIVED STATUS     MB
------ ------- --------------------------------- -------- ---------- ---
     5       1 /u02/oradata/redologs/redo05a.log  NO       CURRENT    50
     5       1 /u02/oradata/redologs/redo05b.log  NO       CURRENT    50
     6       1 /u02/oradata/redologs/redo06a.log  NO       INACTIVE   50
     6       1 /u02/oradata/redologs/redo06b.log  NO       INACTIVE   50
     7       1 /u02/oradata/redologs/redo07a.log  NO       INACTIVE   50
     7       1 /u02/oradata/redologs/redo07b.log  NO       INACTIVE   50
     8       1 /u02/oradata/redologs/redo08a.log  NO       INACTIVE   50
     8       1 /u02/oradata/redologs/redo08b.log  NO       INACTIVE   50
     9       2 /u02/oradata/redologs/redo09a.log  NO       CURRENT    50
     9       2 /u02/oradata/redologs/redo09b.log  NO       CURRENT    50
    10       2 /u02/oradata/redologs/redo10a.log  NO       INACTIVE   50
    10       2 /u02/oradata/redologs/redo10b.log  NO       INACTIVE   50
    11       2 /u02/oradata/redologs/redo11a.log  NO       INACTIVE   50
    11       2 /u02/oradata/redologs/redo11b.log  NO       INACTIVE   50
    12       2 /u02/oradata/redologs/redo12a.log  NO       INACTIVE   50
    12       2 /u02/oradata/redologs/redo12b.log  NO       INACTIVE   50 

Trace files and alert log view Quickly

 tail -10f   $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log

tail -10f $ADR_BASE/diag/rdbms/$DB_UNIQUE_NAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log 

~/.bashrc Importance in shell script

 You can make changes permanent on current shell using bashrc script like :-

~/.bashrc:-

1. make alias command='command'

2. functions and packages 

3. shopt -s cdspell (set), shopt -u cdspell (unset), shopt -s autocd (set), shopt -u autocd (unset)

shopt command magic

 To auto correct directory names on cd :- shopt -s cdspell

To use "directory name" as "cd directory_name" :- shopt -s autocd 

To unset shopt commands :- shopt -u cdspell   and shopt -u autocd 

To make shopt changes permanent :- add shopt -s cdspell and shopt -s autocd in ~/.bashrc script 

To show all set parameters of shopt command:-

Important options:-  

shopt -s cdspell (-s set) 

shopt -u cdspell (-u unset) 

and shopt -p (print all option)

Oracle Database RESETLOGS AND NORESETLOGS Options

RESETLOGS:-

alter database open resetlogs;

this option will reset redo logs and start generating redo logs newly so all old redo logs will be discarded. 

NORESETLOGS:-

alter database open noresetlogs;

this option will used pre-existing redo logs and don't generate redo logs newly.