ORACLE DBA

To check tables and indexes last analyzed date To check tables and indexes last analyzed date and Database stats set pages 200 col index_owner form a10 col table_owner form a10 col owner form a10 spool checkstat.lst PROMPT Regular Tables select owner,table_name,last_analyzed, global_stats from dba_tables where owner not in (‘SYS’,’SYSTEM’) order by owner,table_name / PROMPT Partitioned Tables select table_owner, table_name, partition_name, last_analyzed, global_stats from dba_tab_partitions where table_owner not in (‘SYS’,’SYSTEM’) order by table_owner,table_name, partition_name / PROMPT Regular Indexes select owner, index_name, last_analyzed, global_stats from dba_indexes where owner not in (‘SYS’,’SYSTEM’) order by owner, index_name / PROMPT Partitioned Indexes select index_owner, index_name, partition_name, last_analyzed, global_stats from dba_ind_partitions where index_owner not in (‘SYS’,’SYSTEM’) order b...

Today I faced some issue regarding listener startup and want to share this info with you folks… I got an email from users saying they are unable to connect to one of the production server. They are getting “NO LISTENER” message. So, its clear from this that listener could have been shutdown. I logged in and checked the listener status using both “lsnrctl status” command and “ps -ef | grep tns” command. Both of the commands didn’t given any posivitive result. So I started the listener with the below command and got error as this… oracle@YesB.in:/home/oracle [PROD] >lsnrctl LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-FEB-2016 12:46:17 Copyright (c) 1991, 2011, Oracle.  All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> start Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... TNS-12537: TNS:connection closed  TNS-12560: TNS:protocol adapter error   TNS-00507: Co...

Single Process (with out HINTS) SELECT * FROM sh.customers ORDER BY cust_first_name, cust_last_name, cust_year_of_birth 55500 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2792773903 ---------------------------------------------------------------------------------------- | Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |           | 55500 |  9810K|       |   2612   (1)| 00:00:32 | |   1 |  SORT ORDER BY     |           | 55500 |  9810K|    12M|  2612   (1)| 00:00:32 | |   2 |   TABLE ACCESS FULL| CUSTOMERS | 55500 |  9810K|       |   406  ...

How to determine size of Schema or Index or Table in Oracle Database. Size of a User or Schema select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' group by owner; Size of INDEX select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_name='INDEX_NAME' group by segment_name; OR select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_name='INDEX_NAME' group by owner,segment_name; List of Size of all INDEXES of a USER select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_type='INDEX' group by segment_name order by "SIZE in GB" desc;  OR select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type=...