• Oracle

    Install oracle 18C redhat centos vmware on laptop SQL DBA 10 steps

    Being a database administrator you have asked to take support of other databases as well like Oracle, noSQL, Cloud PaaS and IaaS etc. In Indian service based company, we need attest more than one database knowledge and support experience to lead a database team as well. I have been working in IT more than 12 years and had supported Oracle not in top level and it was older days and I thought of writing lot once, I have strong working experience, but I did not get much after that. I just started again quarantine time, let us install 18c and do some test ourselves.   Take a reference of old post:…

  • Oracle

    Learn Oracle in short notes SQL DBA

    Oracle Short Notes   Database – It’s a collection of files. Physical file locations: Listener.ora, TNSname.ora – $ORACLE_HOME/network/admin/ Password & Parameter file – $ORACLE_HOME/dbs/ Control file – flash_recovery_area & /opt/oracle/oradata/   Instance – It’s a collection of SGA+BG process SGA – Shared pool, Database buffer cache, redo log cache, Java pool, large pool & stream pool Shared Pool – Most recently executed statements and definitions. Data Dictionary – most recently used definitions, including table, index & privilege. Library cache – most recently used SQL & PL/SQL statements Shared SQL area – Shared PL/SQL area Database buffer pool – It stores a copy of data block. Default, keep & Recycle –…

  • Oracle

    Deep drive Indexes tuning query tuning-30

    When do we rebuild an Index and Table https://mwidlake.wordpress.com/2009/11/18/depth-of-indexes-on-vldbs/ https://community.oracle.com/thread/589369?tstart=0 http://www.orafaq.com/node/2903 – SQL vs Oracle rebuild   Depth/height of index – Blevel + 1. Blevel = branch+root, not leaf. Ex: Blevel 3 = Depth is 4, which has a rowid pointer to original table, So the IO taken to read a data is 5. For IOT only 4, since leaf and data is same. select max(blevel) from dba_indexes; select index_name,blevel,leaf_blocks from dba_indexes where owner=’PNNORPT’ and index_name like ‘SLM_STEP%’; select owner,index_name from dba_indexes where blevel=3;   SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor,max(blevel) FROM DBA_tables t, DBA_indexes i WHERE t.table_name = i.table_name AND i.index_name like ‘SLM_STEP’ group by t.table_name, i.index_name, t.blocks, t.num_rows,…

  • Oracle

    Oracle DBA table needs to know and maintenance job-29

    DBA Tables needs to know   To know the database properties and parameters. Show parameter <Parameter name> Select * from v$parameters; – Parameres Select name from v$controlfile; – Control file Select * from v$logfile; – Members info Select * from v$log; – Log size Archive log list; – Archive details   Sql> select * from v$sgastat – SGA size Sql> select * from v$pgastat;  – PGA size sql> select * from v$tablespace;  – Tablespace size sql> select * from dba_tablespaces; – Tablespace size sql> select * from v$datafile; – Datafile size sql> select * from dba_data_files; – Datafile size   Rename data file Code Offline tablespace alter tablespace <ts name>…

  • Oracle

    Oracle Locking and isolation levels-28

    Locking DML locking: It acquire locks at both the table level and the row level. Row_locking Always – Low level locking – Row lock – Keyword: TX Intent – High level locking – table lock – Keyword: TM Row level lock- DML operations Table level lock – when this a DDL & DML. Table locks do not affect concurrency of DML. Table lock modes Row share lock (RS) — ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. Row exclusive lock (RX) — ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode…