How to enable Autoextend on in Oracle

>> Adding data file with Auto-extend on option.

SQL> ALTER TABLESPACE USERS ADD DATAFILE 'J:\ORADATA\KPILIVE\USERS03.DBF' SIZE 10G AUTOEXTEND ON NEXT 1024M;

Tablespace altered.


>> Autoextend the existing datafile to 1024m.

SQL> ALTER DATABASE DATAFILE 'J:\ORADATA\KPILIVE\USERS01.DBF' AUTOEXTEND ON NEXT 1024M;

Database altered.


>> Now query the auto extensible is enabled or not.


SQL> col FileName format a15
SQL> col AUTO format a5
SQL> select tablespace_name,file_name,round(bytes/1024/1024,0) bytesMB, autoextensible AUTO,(increment_by*8192)/1024/1024  Incrmnt,round(maxbytes/1024/1024,2) MaxBytes from dba_data_files;

TABLESPACE                 FILE_NAME                BYTESMB        AUTO  INCRMNT     MAXBYTES
------------------------------------------------------------------------------------------------------- -------------        
USERS            G:\IECT EST2\USERS01.DBF         18560           YES        1024          32767.98
         
SYSAUX         G:\IECTEST2\SYSAUX01.DBF        5120              YES         1024          32767.98
     
SYSTEM         G:\IECTEST2\SYSTEM01.DBF         5120              YES         1024          32767.98
       
UNDONEW     G:\IECTEST2\UNDOTBS009           30720           YES          1024           32767.98
         
4 rows selected.


Happy Reading ....

How to Kill the Session by Finding Logon Time in Oracle RAC

SQL> select username,inst_id,sid,serial# from gv$session where username='RNGLCA';

            USERNAME     INST_ID        SID    SERIAL#
          --------- ---------- ---------- -----------------------------
             RNGLCA                 1             1161      33560


SQL> select s.username,  s.sid,  s.serial#,p.spid, last_call_et,status,to_char(logon_time, 'hh24:mi dd/mm/yy')
    logon_time from GV$SESSION s,GV$PROCESS p where s.PADDR=p.ADDR and s.username='RNGLCA';

   USERNAME    SID      SERIAL#     SPID  LAST_CALL_ET       STATUS         LOGON_TIME
   --------- ---------- ---------- --------- ------------ -------- ---------------------------------------------------
      RNGLCA      1161      33560       14600         14                   INACTIVE        09:45 27/07/16
      RNGLCA      1161      33560       10320         14                   INACTIVE        09:45 27/07/16


SQL> ALTER SYSTEM KILL SESSION '1161,33560,@1';

System altered.


Happy Learning ):

Table Reorganizing by Alter Table Move in Oracle

Alter table move is the another option through which you can reorganize the tables in oracle.

Syntax:-

SQL> ALTER TABLE EMP MOVE;

Table altered


Advantages :-

1. When you reorganize the table oracle reset the HWM which removes the fragmentation of a table and increases the performance of a SQL's.

2. High Water Mark is a Marker where oracle stops writing in the block and put a mark where it ends or We can say its a barrier between used and unused blocks.


Drawbacks:-

1. Table is not available for DML during the move operation.
2. Additional space is needed.
3. Indexes need to be rebuilt manually because Row-Ids are changed.

• You can see in this output how row-ids will change after moving the table.
• All the indexes are created on this row-ids this is the reason indexes will not work. 

>> Before Moving Table <<

SQL> Select rowid from EMP;

ROWID
------------------
AAAVlnAAGAAEcPlAAO
AAAVlnAAGAAEcPlAAP
AAAVlnAAGAAEcPlAAQ
AAAVlnAAGAAEcPlAAR
AAAVlnAAGAAEcPlAAS
AAAVlnAAGAAEcP0AAa


>> After moving Table <<


SQL> Select rowid from EMP;

ROWID
------------------
AAAWITAAGAACzdNAAa
AAAWITAAGAACzdNAAb
AAAWITAAGAACzdNAAc
AAAWITAAGAACzdNAAd
AAAWITAAGAACzdOAAA
AAAWITAAGAACzdOAAB


Check the index status Now

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


STATUS   INDEX_NAME
-------- ---------------------------------
UNUSABLE      EMP_INDX
UNUSABLE      EMP_INDX_001

>> Indexes Should be rebuild now

SQL> Alter index emp_indx rebuild online;

SQL> Alter index emp_indx_001 rebuild online;


>> Now Gather Fresh Stats of a table to update optimizer.

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
         ownname =>'HMIS',
         tabname =>'EMP',
         degree => 2,
         cascade => TRUE,
         METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
         estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
         END;
         /

Thanks For Reading....

Reorganizing Table using CTAS in Oracle

Steps:-

•   Create a copy of the original table using CTAS.

           SQL> create table new_table as select * from old_table;

•      Drop the original table and all indexes/constraints

           SQL> drop table emp cascade constraints;

•      Rename new table

          SQL>  alter table new_emp rename to old table name;

•      Recreate all indexes because row-ids are change when you move the table.

         
Advantages:-

1. Easy to use.
2. With small maintenance window this CTAS option is very helpful for reorganizing table online.
2. Table is available for DML operations during CTAS (but these DMLs are not copied to target table).


Drawbacks:-

1. CTAS is slow.
2. Additional space is required.
3. Any DML that is performed on the table during CTAS operation is not recorded in target table.
    Differential data should be copied using insert into..select with required filters.
4. Indexes  need to be manually created on target table.


Thanks Reading....

Table Fragmentation in Oracle

What is Fragmentation :-

 When there are heavy transactions such as deletes and updates are running in a table, Fragmentation comes in picture. Oracle will read up to high water mark (HWM) Which is nothing but
 'Point at which Oracle Consumed the Most Extents'
This is the reason  users will face slowness in the application to retrieve the data,because oracle not reset the HWM after deletions.


High Water Mark : Is  a pointer where oracle stops writing in the blocks and put a mark where it ends. OR  we can say its an Marker between Used and Unused Blocks.

 Example :-
Assume that you have 5 lac rows in your table which has accommodated in 1000 blocks, when you are executing SELECT query its taking 20 minutes to give the output, and If you deleted 4 lac rows from this table. and run again same SELECT query it will take same 20 minutes to read again. Here oracle read 1000 blocks again This is because of HWM is not reset by oracle after deletion.


We Can Reset HWM By Using These Methods.

1.  Alter Table Move.

2. Expdp / Impdp

3. Shrink Command.

4. CTAS

5. DBMS Redefinition Package.

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


>> You can check this link how to reset the HWM  and Remove Fragmentation from a table.


http://moindba.blogspot.com/2016/06/how-to-reorganize-table-using.html



 Happy Learning...

How to Drop a Multi-Column Which is Having Constraints in Oracle

SQL> alter table pmpmcstk drop (pshscd,psbtn);

ERROR at line 1:
ORA-12991: column is referenced in a multi-column constraint


SQL> alter table pmpmcstk drop (pshscd,psbtn) cascade constraints;

Table altered.


Happy Learning...