debasis maity

12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect. AWS Certified Solution Architect and Senior Oracle DBA
158

debasis maity 's Posts

What is PGA The Program Global Area (PGA) is a private memory region that contains the data and control information for a server process. Only a server process can access the PGA. Oracle Database reads and writes information in the PGA on behalf of the server process. An example of… Continue Reading Oracle PGA (Program Global Area) in 12c

What are base tables? When and how they will get created? Ans:-Are read-only tables(Data Dictionary Tables) that hold information about the database. Base tables are own by SYS. When and how they will get created? They get created when the database is built. https://docs.oracle.com/cd/B19306_01/server.102/b14220/datadict.htm Why to execute catproc.sql script? Ans:-Used… Continue Reading Database Architecture Interview Questions

1.What are new features of datapump in 12c? https://oracle-base.com/articles/12c/data-pump-enhancements-12cr1 2.which case we need to use flashback_time or flashback_scn parameter? If you need consistent backup for example when you do initial load using datapump for golden gate. 3.During impdp with full=y caluse , do we need to create user before import?… Continue Reading EXPDP IMPDP Datapump interview questions

Listener related questions:- Answer Credit:- https://www.linkedin.com/in/saibal-ghosh-ccsk-prince2-%C2%AE-469b0a7/ 1.Can we manually force a particular scan ip (out of the three) to relocate on a particular host (lets say – on node 5 of 5 node RAC) ? Ans:- srvctl relocate scan_LISTENER -i 1 -n node1 $ srvctl relocate scan -h Usage: srvctl… Continue Reading Generic DBA

Clustering Factor and it’s effect on non-assm and assm tablespace. The clustering factor is a measure of the ordernesss of an index in comparison to the table that it is based on. It is used to check the cost of a table lookup following an index access (multiplying the clustering… Continue Reading Clustering Factor and it’s effect on non-assm and assm tablespace

A. B-Tree Indexes I have discussed more details in the below blog. http://clouddba.co/index-internal-oracle-using-simple-test-case/ B.Invisible Indexes Deciding which columns to index is hard. The primary key is automatically indexed, the foreign keys should also be indexed, but then what? Even more difficult is deciding which index to remove that might be… Continue Reading Index in 12c by example test case

Index internal oracle using simple test case First of all I will create a table with char(1000) so that in one block I can fit 6-7 entries . Here are some relevant notes regarding Index split For Leaf block kdxlespl: bytes of uncommitted data at time of block split that… Continue Reading Index internal oracle using simple test case

How histogram can affect query plan change hence performance. 1.Let me create a test case.I am loading the table with following distribution.Obviously it means data is somewhat skewed. Value:-1 Range:-10 Value:-2 Range:-100 Value:-3 Range:-1000 Value:-4 Range:-10000 Value:-5 Range:-100000 C##TEST@TESTDB1> create table tt1(c1 number,c2 char(10)); Table created. C##TEST@TESTDB1> declare begin for… Continue Reading How histogram can affect query plan change hence performance

1.Let us create a test case first:- oracle@TST:TESTDB1[/home/oracle]$ sqlplus c##test/test C##TEST@TESTDB1> create table mytst1 as select * from dba_objects; Table created. C##TEST@TESTDB1> create table mytst2 as select * from dba_objects where rownum<5000; Table created. 2.Let us see what application executes SQL query and complaining poor performance select a.owner,a.OBJECT_NAME,a.OBJECT_TYPE,b.STATUS from mytst1… Continue Reading Poor index identify and resolve manually or using sql tuning advisor

1. On the source node to target node, create a copy of the Oracle Grid Infrastructure home. For example, as root on Linux systems, run the cp command:- # cp -prf root@rac1:/u01/app/product/12.1.0/grid/* root@rac2:/u01/app/product/12.1.0/grid 2. Delete unnecessary files from the copy.The Oracle Grid Infrastructure home contains files that are relevant only… Continue Reading RAC one node to another node clone if another rac crash due to file deletion or permission change in grid home