To Drop Or Not To Drop A Column – Find Out The Answer

Don’t you love it when people come to you for advice? I sure do! I got a call the other day from a developer. He didn’t understand why adding a column is very fast, almost instantaneous, but dropping a column takes 6-10 min to complete. I asked for all the details I needed, and told him I’ll get back within a few hours. Then I rolled up my sleeves and went into research mode. It’s been a while since my Oracle days in school, so I needed a little refresher on what is happening when you drop a column,and what other alternatives exist. And then I got curious. I asked myself some more questions that required some testing. Now I am here sharing my findings with you! 1.What’s Happening Behind The Scene When You Drop A Column? 2.What Is The Alternative? 3.Various Questions Answered On Setting Column Unused. 1.What’s Happening Behind The Scene When You Drop A Column? Dropping a column on a small table is no big deal. The operation completes pretty fast. However, when you have a large table, the story is completely different. The statement takes time to complete. One might think that Oracle is hung up. Why is dropping a column so time consuming? This is what happens when you issue the drop column command: Oracle reads each block of the table Oracle removes the column from each row Oracle rewrites the block where the column was removed. While the column drop is in progress, Oracle is holding an exclusive lock on the table, which means no DDL and no DML is allowed on the table. Anyone who issues DDL or DML, will be waiting for the drop column to complete. This could mean that parts of the application that runs on your database will not be available or will not be functioning properly while the drop command is running. SQL Statement: alter table test drop column X; 2.What Is The Alternative? There is another solution! You can set the column to unusable! Why is this a great approach? Because it is very quick! No more reading and rewriting blocks of data. No. All what’s happening is updates of the data dictionary. What are the cons for setting the column unusable? The only thing I can think of is the space that the column is using in the database. When you set the column to unused, the space is not freed. After you set the column unusable, and at a convenient time, you could actually drop the unused columns on the table. SQL Statement: alter table test set unused column X; alter table t drop unused columns; alter table t drop unused columns checkpoint 5000; --recommended. this will commit every 5000 rows In 12c, there is another option as well. You can set the column invisible. This will be the topic of a separate post. 3.Various Questions Answered On Setting A Column Unusable I can hear the questions coming… Q: After I set column X unusable, can I add another column with the same name X? A: Yes! Column is renamed to a different name, and it doesn’t show up anymore in the table Q: Can I undo an unusable column statement? A: No. Dropping or setting a column to unusable are irreversible statements. There is no undo or flashback. The only way to get it back is to restore your database. Q: Will dbms_metadata extract the unusable columns as well, when getting the ddl of a table? A: No. Column is no longer available in the table. Q: Will export/import datapump, extract the unusable columns as well? A: No. Column is no longer available in the table. Q: Where can I find information about unusable columns? A: dba_unused_col_tabs, dba_tab_cols Example: SQL>create table test (a number, b varchar2(2), c number); Table created. SQL>alter table test set unused column c; Table altered. SQL>select * from dba_unused_col_tabs where table_name='TEST'; OWNER TABLE_NAME COUNT ------------ --------------- ---------- DROBETE TEST 1 SQL>select TABLE_NAME,COLUMN_NAME from dba_tab_cols where table_name='TEST'; TABLE_NAME COLUMN_NAME ----------------- ------------------------------ TEST SYS_C00003_17062122:43:09$ -->>Column C was renamed TEST B TEST A SQL>desc test -->>Column C doesn't show up Name Null? Type --------- -------- ------------------ A NUMBER B VARCHAR2(2) SQL>alter table test add c number; -->> Add column C again Table altered. SQL>desc test Name Null? Type --------- -------- ------------------ A NUMBER B VARCHAR2(2) C NUMBER SQL>alter table test set unused column c; -->>Set column unused again Table altered. SQL>select * from dba_unused_col_tabs where table_name='TEST'; OWNER TABLE_NAME COUNT ------------ ------------------------------ ---------- DROBETE TEST 2 -->>Counts go up SQL>select TABLE_NAME,COLUMN_NAME from dba_tab_cols where table_name='TEST'; TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ TEST SYS_C00004_17062122:50:23$ TEST SYS_C00003_17062122:43:09$ TEST B TEST A --Extract DDL - no sign of unused column SQL>select dbms_metadata.get_ddl('TABLE','TEST','DROBETE') from dual; DBMS_METADATA.GET_DDL('TABLE','TEST','DROBETE') ----------------------------------------------------- CREATE TABLE "DROBETE"."TEST" ( "A" NUMBER, "B" VARCHAR2(2) ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" --Lets drop unused columns SQL>alter table drobete.test drop unused columns; Table altered. SQL>select * from dba_unused_col_tabs where table_name='TEST'; no rows selected SQL>select TABLE_NAME,COLUMN_NAME from dba_tab_cols where table_name='TEST'; TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ TEST B TEST A

Oracle database installation capacity planning details

Capacity planning is an essentially important career profile that determines the resource demand in current, future. Personnel from infrastructure teams typically server engineers, DBA do take additional responsibility. Tools from popular vendors like CA BMC assists with capacity planning. This can be easily done using proper excel sheet with details on resources needed for the ongoing project, future project, growing trends etc. Now-a-days information is wealth. Bigdata in a way impacts capacity planning directly and indirectly as predictive trends in online space depends on data usage. This internally translates to capacity planning of the project. As a first step lets see what needs to be considered while planning installation of database. Oracle is a popular brand in use and lets take this exmaple
What is needed for installing an Oracle database?
Proper planning, good documentation, scheduling and appropriate execution is needed for proper installation of an oracle database.
What are the factors to be considered during planning of oracle database installation?
We need to make sure that we have adequate hardware resources, operating system resources, proper environment variable settings during planning oracle database installation
What are the environment variables to be set during oracle database installation?
ORACLE_HOME, ORACLE_BASE,ORACLE_SID,ORACLE_UNQNAME are some environment variables to be set during installation. These values should be obtained from system administration team beforehand and needs to be included in planning document
What is the first factor to be considered while planning an installation?
The first step is going to be capacity planning. This involves planning a database in accordance with availability of resources

How to Prepare for DBA Interview

Preparation for DBA Interview

Nobody can give us complete or most of questions about interview because it is like our school, college exam which we don't know what exact questions comes in.

For school, college exam we need to go through every books which related to exams topic same like any Interview not for DBA only we need to prepare our self and go through each and every topic related to Oracle Database.

So now question is change from Oracle DBA interview question TO How to prepare myself for DBA interview?

And now , here , we explained how we can prepare our self for DBA interview.

1. First understand we are going for DBA interview means Database Administrator.

“A person who’s responsible to handle and maintain database called DBA”

Means DBA position is very Hard, though, sensitive and confidential.

We can divide Oracle DBA interview in Four Major part.

1. Oracle Database Administration

2. Oracle Database Backup & Recovery Administration

3. Oracle Database Performance Tuning

4. Oracle Database Advanced administration

ü  Oracle Database Administration

1. How to install Oracle Database Software on Different platforms?

Above point include Oracle Database Server or Client installation on different flavor like LINUX/SORALIS/UNIX/WINDOWS etc. so whenever you are going for interview must go through each installation guide.

2. How to Create Database?

There is two option for create DATABASE.

DBCA or MANUALLY

3. How to create tablespace?

4. How to configure Oracle Client to connect to Oracle Server?

In Above point we need to create TNS_ENTRY for database client through NETCA tools.

5. Database User Management

Specific permission on database according user needs. User profile creation., role management, etc.

6. Database Security

In above point we need to do Database Auditing, User Logon, Logoff information.

ü   Oracle Database Backup & Recovery Administration

Ø  1. What is your backup strategy for database backup?

It is depends on many factor.

1. Database size It is in MB, GB or TB

2. Database type It is OLTP or DSS

3. Database availability It is 24x7

So whenever we design backup strategy for database keeps in mind above points. Because Oracle Expert Mr. Tom kyte said “We can FIX everything except BAD DATABASE BACKUP”.

Ø  2. What is your backup method for database backup?

It is also depend on condition.

1. Backup Type COLD backup or HOT backup

2. Backup Method RMAN or USER MANAGED

3. Backup Interval Every Day, Every Week, Every Month.

Oracle Corp. recommended to use RMAN (server managed) backup method for database backup but we can also use USER MANAGED backup if applicable.

NOTE: Always RUN production database in ARCHIVELOG mode.

After set proper backup strategy now practice on database recovery because interviewer must ask question about recovery side.

“Database Recovery is depend on Database backup”

There are so many or different types of recovery so we can divide recovery area in following.

Ø  How many types of recovery we can perform?

1. When datafile is lost

2. when controlfile is lost

3. when online redolog file is lost

Above three files is most important file for database if we lost them then we needed to perform recovery.

1. We can perform recovery at DATABASE level when complete database is lost.

2. We can perform recovery at TABLESPACE level when tablespace is lost.

3. We can perform recovery at DATAFILE level when datafile is lost.

4. We can perform recovery at DATA BLOCK level when any block is corrupted.

5. We can Multiplex CONTROLFILE or REDOLOG file for protect against LOST.

And suppose still we lost all controlfile or redolog files then recovery depends on many conditions.

NOTE: Before going for interview must go through backup and recovery user guide.

ü  Oracle Database Performance tuning administration

This is third big area where most of DBA questions come.

For database tuning we need years of experience. But we can start with bottom.

1. What are database activities?

2. What is database memory area means SGA or PGA size?

3.What is database background process like DBRW, LGWR, CKPT SMON, PMON etc?

For above question oracle providing same diagnostic tools

1. STATPACK or AWR report

2. AUTO TRACE

3. SQL trace

4.Tkprof

Above are same tool to use find out bottleneck of slow database performance so we must know below things about this tools

1. How to use?

2. How to understand?

3. How to use these tools to solve database performance issue.

Above question is simple but answer is difficult because answer want practical or years of experience in database.

Note: Search on Google or visit OTN site for starting.

ü  Oracle Database advanced administration

Oracle Advanced Features

1. Data Guard

2. Partitioning

3. Stream

4. RAC system

5. ASM

6. Others

DBA interviewer should ask question above points but it is also depend on DBA position because above points will include in senior level position.

End of the story is…

For Interview we need good knowledge about Oracle DB and Experience because if we read thousands of interview related questions without good knowledge or experience we can clear interview but can’t be become a good DBA