Blog for oracle basic 9i,10g,11g,12c

Table size   Select segment_name, segment_type, bytes/1024/1024 MB  from dba_segments  where segment_type='tablename' and segment_name='Table_name' To check fragmentation  or reclaim select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE", (round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage" from all_tables WHERE table_name='&TABLE_NAME'; Select segment_name, segment_type, bytes/1024/1024 MB  from dba_segments  where segment_type='tablename' and segment_name='W_QUOTEITEM_F';

File Size(MB) Tablespace           RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1    2048     SYSTEM               ***     +ORADATA1/stby_aml/datafile/system.258.926452627 2    1500     SYSAUX               ***     +ORADATA1/stby_aml/datafile/sysaux.259.926452627 3    4096     UNDOTBS1             ***     +ORADATA1/stby_aml/datafile/undotbs1.260.926452627 4    1024     USERS                ***     +ORADATA1/stby_aml/datafile/users.261.926452627 5    1024     ZABBIX_TS            ***     +ORADATA1/stby_aml/datafile/zabbix_ts.257....

Presteps for patching =================================== check the opatch version 1.$ORACLE_HOME/OPatch/opatch version Opatch version should be 11.2.0.3.6 or higher. if the value is lower then perform below step. update Opatch utitlty for 11.2.0.3 GI and DB homes respectively. Go to GRID home and take the backup of OPatch folder as cp -r OPatch OPatch_backup   copy /dbaadm/media/Oracle/11gR2/x86_64/11.2.0.3/patch/p6880880_112000_Linux-x86-64.zip to $ORACLE_HOME and extract. as Grid user when copying to GRID home as oracle user when copying to DB home 2. Create ocm.rsp file for grid and oracle home if it does not exist already. change directory to /export/home/<username> cd $HOME [root@dog1141 bin]# chown -R grid:oinstall emocmrsp [root@dog1141 bin]# ls -ltr total 24 -rwxr----- 1 grid   oinstall 9063 Nov 27  2009 emocmrsp -rw-r--r-- 1 oracle oinstall  623 Jan 28  2013 ocm.rsp $ORACLE_HOME/OPatch/ocm/bin/emocmrsp---a...

Grid-1 [root@scnluatoradb02 lib]# su - grid Last login: Sun Nov 18 16:18:44 IST 2018 on pts/1 [grid@makuatoradb02 ~]$ [grid@makuatoradb02 ~]$ [grid@makuatoradb02 ~]$ /oracle/12.2.0/grid/OPatch/opatch lsinventory  -oh /oracle/12.2.0/grid Oracle Interim Patch Installer version 12.2.0.1.14 Copyright (c) 2018, Oracle Corporation.  All rights reserved. Oracle Home       : /oracle/12.2.0/grid Central Inventory : /oracle/app/oraInventory    from           : /oracle/12.2.0/grid/oraInst.loc OPatch version    : 12.2.0.1.14 OUI version       : 12.2.0.1.4 Log file location : /oracle/12.2.0/grid/cfgtoollogs/opatch/opatch2018-11-18_17-57-39PM_1.log Lsinventory Output file location : /oracle/12.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2018-11-18_17-57-39PM.txt -------------------------------------------------------------------------------- Local Machine Information:: Hostname:...

Please find  below steps Step 1: sql> show parameter log_archive_dest_state_2 Step 2: sql> alter system set log_archive_dest_state_2=DEFER; Step 3: sql> alter system set log_archive_dest_state_2=ENABLE; FYI: Please check the free space availability in the production archive destination also.