Below are the questions asked to a candidate with 10 years of experience in Oracle DBA and postgres . There were 2 rounds.

Round 1 :

•  What are your day to day activities. And what is your role in the Team?
• Explain cluster startup sequence.
• Suppose users are connected to node1 and Os team, want to do emergency maintenance and they need to reboot. What wil happens to the transactions on node1 , and can we move them to node 2.
• What are the advantages of partitioning?
• Can we convert non-partitioned table to partitioned table online( in production)?
• What are the cloning methods. And what is the difference between duplicate method and restore method?
• How can we open the standby database as read write for testing and Once testing is done need to revert to previous position. But without using snapshot command?
• Explain how you did grid upgrade.
• Which advanced security features you have used ?
• What is audit purging?
• Lets says a query was running fine for last few days , but today it is not performing well. How you will troubleshoot it?
• What is oracle in memory ?
• Explain what type of patches you applied on grid.
• As per resume ,You have worked on ansible . So how good you are with ansible and what type of scripts you have wrote.
• How can you troubleshoot a shell script?

2nd round:

• Explain ASM architecture , means when ever user runs a query, how it gets the data from ASM.
• Why ASM performance is better
• Why VIP is used , Bcoz before 10g vip was not used. What benefits vip provide
• What is Current block and CR block in RAC
• What wait events are there in RAC??
• What is gc buffer busy wait
• What is gc 2 way current wait, gc 3 way current wait.
• What is node eviction. In which cases it happens.
• How you do load balancing using services in your project. Explain
• What activities you did using goldengate
• How you did upgrade/migration using goldengate.
• What is goldengate instantiation.
• What is bounded recovery in goldengate
• What is the difference between CSN in goldengate vs SCN in database?
• Which type of os tool/commands you have used for os monitoring/performance. And how you read it.
• How you troubleshoot I/O issues in oracle
• What type of sql performane tools you use.
• How you migrate a plan from one db to another db
• What is the dbms package for baseline and sql tuning?
• Lets say, despite importing the baseline, query is not picking the plan, What can the be reason?
• What is index fast full scan and index range scan?
• What is the purpose of standby redolog.
• Any automation tools you have used?
• Did you worked on jenkins or gitlab?
• What activities you did in postgres.
• How you implement backup in postgres.
• Is your postgres cluster active active or active passive?
• Can we create a active active postgres cluster?
• Do you remember any postgres extensions?
• What type of scripts you have written on ansible?
• Tell me some modules of ansible.
• How can we encrypt the server credentials in ansible.

If you wish to share your interview experiences, then please drop a mail to support@dbaclass.com. We will happy to publish it with your permission.

The post Real Interview questions for experienced DBAs – 1 appeared first on DBACLASS.

Keyword:

    INTERVAL(NUMTOYMINTERVAL(1,’MONTH’))

Example:

1. Lets create a monthly partitioned table ( with interval).

CREATE TABLE DBATEST.DBA_BREL_ATTRI
(
CORRELATION_ID VARCHAR2(100 BYTE),
ATTRIBUTE_KEY VARCHAR2(50 BYTE),
ATTRIBUTE_VALUE VARCHAR2(100 BYTE),
EVENT_TIMESTAMP TIMESTAMP(6)
)
TABLESPACE STCDBA
PARTITION BY RANGE (EVENT_TIMESTAMP)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION DBA_BREL_ATTRI_MIN VALUES LESS THAN (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

SQL> CREATE INDEX DBATEST.DBA_BREL_ATTRI_COR_ID_IDX ON DBATEST.DBA_BREL_ATTRI (CORRELATION_ID) LOCAL;

Index created.

Here the date 2016-05-01 is known as TRANSIT POINT . Any data inserted beyond this transit point will led to creation of a new partition automatically.

INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point

2. Now check dba_tab_partitions

SQL> SELECT table_name,partition_name,high_value from dba_tab_partitions where table_name='DBA_BREL_ATTRI';

TABLE_NAME PARTITION_NAME HIGH_VALUE
---------------- ---------------------------------- ---------------------------------------------------------------------
DBA_BREL_ATTRI DBA_BREL_ATTRI_MIN TIMESTAMP' 2016-05-01 00:00:00'

3. Lets insert some data and check the dba_tab_partitions:

SQL > insert into dbatest.DBA_BREL_ATTRI values ('387fade4-f0f4-4bc3-9ba3-41458fad7a4c','ApplicationName','ULA','01-JUN-2016')

commit

SQL> SELECT table_name,partition_name,high_value,interval  from dba_tab_partitions where table_name='DBA_BREL_ATTRI';

TABLE_NAME       PARTITION_NAME                     HIGH_VALUE                         INT
---------------- ---------------------------------- ---------------------------------- ---
DBA_BREL_ATTRI   SYS_P6066                          TIMESTAMP' 2016-07-01 00:00:00'    YES
DBA_BREL_ATTRI   DBA_BREL_ATTRI_MIN                 TIMESTAMP' 2016-05-01 00:00:00'    NO

We can see a new monthly partition has been created automatically .

Similarly

for weekly partition use the parameter – INTERVAL (numtodsinterval(7,’day’))
for yearly partition use the parameter – INTERVAL (NUMTOYMINTERVAL(1,’YEAR’))

Convert Existing Range partitioned table to interval partition:

ALTER TABLE dbatest.range_part SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));

Restriction of Interval Partitioning:

1. Cannot be created for Index organized table(IOT)

2. The partitioning column can be only one and it must be of type NUMBER or DATE

3. Cannot use the MAXVALUE clause 

The post Interval Partitioning in oracle appeared first on DBACLASS.

EXAMPLE:

Create a normal table with index and constraints and insert some data:

conn oranet/oranet
Connected.


CREATE TABLE PRODUCT (
  id           NUMBER,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
);  2    3    4    5    6

Table created.

ALTER TABLE PRODUCT ADD (
  CONSTRAINT PRODUCT_PK PRIMARY KEY (id)
);  2    3

Table altered.

CREATE INDEX created_date_ind ON PRODUCT(created_date);

Index created.

INSERT INTO PRODUCT VALUES (1, 'ADAM', 'BOB', TO_DATE('05-JUN-2005', 'DD-MON-YYYY'));
1 row created.

INSERT INTO PRODUCT VALUES (2, 'ADAM', 'BOB', TO_DATE('05-JUN-2005', 'DD-MON-YYYY'));
1 row created.


INSERT INTO PRODUCT VALUES (4, 'ADAM', 'BOB', TO_DATE('05-AUG-2005', 'DD-MON-YYYY'));
1 row created.

COMMIT;
Commit complete.

select table_name,partitioned from dba_tabLES where table_name='PRODUCT';

TABLE_NAME                     PAR
------------------------------ ---
PRODUCT                        NO

Now create an empty interim partitioned table with similar structure as that of old table, which we will use for exchange the data from PRODUCT table. Make sure to define the one partition with maxvalue.

  CREATE TABLE PRODUCT_TEMP (
  id            NUMBER ,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
)
PARTITION BY RANGE (created_date)
(PARTITION partitioned_2006 VALUES LESS THAN (MAXVALUE));  2    3    4    5    6    7    8

Table created.

ALTER TABLE PRODUCT_TEMP ADD (
  CONSTRAINT PRODUCT_TEMP_pk PRIMARY KEY (id));  2

Table altered.

CREATE INDEX PRODUCT_TEMP_ind ON PRODUCT_TEMP (created_date) LOCAL;

Index created.

Now we will use the EXCHANGE PARTITION option , which will move the data from non-partitioned table(PRODUCT) to partitioned table(PRODUCT_TEMP).

 ALTER TABLE PRODUCT_TEMP
  EXCHANGE PARTITION partitioned_2006
  WITH TABLE PRODUCT
  WITHOUT VALIDATION
  UPDATE GLOBAL INDEXES;  2    3    4    5

Table altered.

select count(*) from PRODUCT_TEMP;

  COUNT(*)
----------
         3

select count(*) from PRODUCT;

  COUNT(*)
----------
         0

Once this is complete we can drop the old table and rename the new table and all it’s constraints.

drop table PRODUCT;

Table dropped.

 alter table PRODUCT_TEMP rename to PRODUCT;

Table altered.

alter table PRODUCT rename constraint PRODUCT_TEMP_PK to PRODUCT_PK;

Table altered.

ALTER INDEX PRODUCT_TEMP_PK rename to PRODUCT_PK;

Index altered.

ALTER INDEX PRODUCT_TEMP_IND rename to created_date_ind;

Index altered.

Lets split the existing partition.

select table_name,partitioned from dba_tabLES where table_name='PRODUCT';

TABLE_NAME                     PAR
------------------------------ ---
PRODUCT                        YES

 Select partition_name, high_value from user_tab_partitions where table_name = 'PRODUCT';


PARTITION_NAME                 HIGH_VALUE
------------------------------ -------------------------------------
PARTITIONED_2006               MAXVALUE

ALTER TABLE PRODUCT
  SPLIT partition partitioned_2006 AT (TO_DATE('30-JUN-2005','DD-MON-YYYY'))
  INTO (PARTITION partitioned_JUN2005,
        PARTITION partitioned_2006)
  UPDATE GLOBAL INDEXES;  2    3    4    5

Table altered.

Select partition_name, high_value from user_tab_partitions where table_name = 'PRODUCT';

PARTITION_NAME                 HIGH_VALUE
------------------------------ -----------------------------------------------------------------------------------------
PARTITIONED_2006               MAXVALUE
PARTITIONED_JUN2005            TO_DATE(' 2005-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 ALTER TABLE PRODUCT
  SPLIT partition partitioned_2006    AT (TO_DATE('30-JUL-2005','DD-MON-YYYY'))
  INTO (PARTITION partitioned_JULY2005,
        PARTITION partitioned_2006)
  UPDATE GLOBAL INDEXES;  2    3    4    5

Table altered.

 Select partition_name, high_value from user_tab_partitions where table_name = 'PRODUCT';

PARTITION_NAME                 HIGH_VALUE
------------------------------ -----------------------------------------------------------------------------------------
PARTITIONED_2006               MAXVALUE
PARTITIONED_JULY2005           TO_DATE(' 2005-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PARTITIONED_JUN2005            TO_DATE(' 2005-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

EXEC DBMS_STATS.gather_table_stats('ORANET', 'PRODUCT', cascade = > TRUE);

PL/SQL procedure successfully completed.

Select partition_name, high_value,num_rowS from user_tab_partitions where table_name = 'PRODUCT';

PARTITION_NAME                 HIGH_VALUE                                                                                  NUM_ROWS
------------------------------ ----------------------------------------------------------------------------------------- ----------
PARTITIONED_2006               MAXVALUE                                                                                           1
PARTITIONED_JULY2005           TO_DATE(' 2005-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                0
PARTITIONED_JUN2005            TO_DATE(' 2005-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                2

There are other methods like dbms_redefinition and exp imp method to partition and existing table. I will upload them very soon.

For any issue or if you need more information please contact support@dbaclass.com

The post Partition an existing table oracle using exchange method appeared first on DBACLASS.