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=...

Oracle Data Guard Interview Questions & Answers What are the types of Oracle Data Guard? Oracle Data Guard classified in to two types based on way of creation and method used for Redo Apply. They are as follows. Physical standby (Redo Apply technology) Logical standby (SQL Apply Technology) What are the advantages in using Oracle Data Guard? Following are the different benefits in using Oracle Data Guard feature in your environment. High Availability. Data Protection. Off loading Backup operation to standby database. Automatic Gap detection and Resolution in standby database. Automatic Role Transition using Data Guard Broker. What are the different services available in Oracle Data Guard? Following are the different Services available in Oracle Data Guard of Oracle database. Redo Transport Services. Log Apply Services. Role Transitions. What are the different Protection modes available in Oracle Data Guard? Following are the different protect...

1. Which types of backups you can take in Oracle?    3 types  of backup  Hot,  Cold, partial and incremental 2. A database is running in NOARCHIVELOG mode then which type of backups you can take? Cold backup ( shutdown immediate , startup mount  and backup database and after startup  a database) 3. Can you take partial backups if the Database is running in NOARCHIVELOG mode? No, database must be offline. 4. Can you take Online Backups if the the database is running in NOARCHIVELOG mode? No, only with archivelog 5. How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode? SQL>shutdown immediate;  SQL>startup mount;    SQL> alter database archivelog; SQL> alter database open; 6. You cannot shutdown the database for even some minutes, then in which mode you should run the database? RMAN>startup  explicite; 7. Where should you place Archive logfiles, in the same disk wher...