[ad_1]
DBA Technical Interview Questions:
1. Give one technique for transferring a desk from one schema to a different:
There are a number of attainable strategies, export-import, CREATE TABLE… AS SELECT, or COPY.
2. What’s the objective of the IMPORT possibility IGNORE? What’s it is default setting?
The IMPORT IGNORE possibility tells import to disregard “already exists” errors. If it’s not specified the tables that exist already will probably be skipped. Whether it is specified, the error is ignored and the desk’s information will probably be inserted. The default worth is N.
3. If the DEFAULT and TEMPORARY tablespace clauses are unnoticed of a CREATE USER command what occurs? Is that this unhealthy or good? Why?
The consumer is assigned the SYSTEM tablespace as a default and momentary tablespace. That is unhealthy as a result of it causes consumer objects and momentary segments to be positioned into the SYSTEM tablespace leading to fragmentation and improper desk placement (solely information dictionary objects and the system rollback section must be in SYSTEM).
4. What are a few of the Oracle supplied packages that DBAs ought to concentrate on?
Oracle offers various packages within the type of the DBMS_ packages owned by the SYS consumer. The packages utilized by DBA might embrace: DBMS_SHARED_POOL, DBMS_UTILITY, DBMS_SQL, DBMS_DDL, DBMS_SESSION, DBMS_OUTPUT and DBMS_SNAPSHOT.
5. What occurs if the constraint identify is unnoticed of a constraint clause?
The Oracle system will use the default identify of SYS_Cxxxx the place xxxx is a system generated quantity. That is unhealthy because it makes monitoring which desk the constraint belongs to or what the constraint does more durable.
6. What occurs if a tablespace clause is left off of a main key constraint clause?
That ends in the index that’s mechanically generated being positioned in then customers default tablespace. Since this may normally be the identical tablespace because the desk is being created in, this may trigger severe efficiency issues.
7. What’s the correct technique for disabling and re-enabling a main key constraint?
You utilize the ALTER TABLE command for each. Nonetheless, for the allow clause you should specify the USING INDEX and TABLESPACE clause for main keys.
8. What occurs if a main key constraint is disabled after which enabled with out absolutely specifying the index clause?
The index is created within the consumer’s default tablespace and all sizing data is misplaced. Oracle does not retailer this data as part of the constraint definition, however solely as a part of the index definition, when the constraint was disabled the index was dropped and the knowledge is gone.
9. You’re utilizing scorching backup with out being in archivelog mode, are you able to get well within the occasion of a failure? Why or why not?
You can not use scorching backup with out being in archivelog mode. So no, you could not get well.
10. What causes the “snapshot too old” error? How can this be prevented or mitigated?
It happens when the redo required for a learn constant view of the information is now not accessible. To keep away from or to forestall by growing the quantity and dimension of redo segments in order that the longest working transaction might be contained in a single section. – or – Improve the undo retention interval to a price >= the longest working transaction within the database and be sure you have ample disk to help that a lot redo.
11. How are you going to inform if a database object is invalid?
By checking the STATUS column of the DBA_, ALL_ or USER_OBJECTS views, relying upon whether or not you personal or solely have permission on the view or are utilizing a DBA account.
12. A consumer is getting an ORA-00942 error but you recognize you’ve got granted them permission on the desk, what else do you have to verify?
It’s essential verify that the consumer has specified the complete identify of the article (SELECT empid FROM scott.emp; as a substitute of SELECT empid FROM emp;) or has a synonym that factors to the article (CREATE SYNONYM emp FOR scott.emp;)
13. How are you going to learn the way many customers are at the moment logged into the database? How are you going to discover their working system id?
There are a number of methods. One is to have a look at the v$session or v$course of views. One other method is to verify the current_logins parameter within the v$sysstat view. One other in case you are on UNIX is to do a “ps -ef|grep oracle|wc -l
14. A consumer selects from a sequence and will get again two values, his choose is:
SELECT pk_seq.nextval FROM twin; What’s the downside?
By some means two values have been inserted into the twin desk. This desk is a single row, single column desk that ought to solely have one worth in it.
15. How are you going to decide if an index must be dropped and rebuilt? (Anticipate reply should not be that lengthy. That is extra for clarification objective).
There are two guidelines of thumb to assist decide if the index must be rebuilt.
1. If the index has peak larger than 4, rebuild the index.
2. The deleted leaf rows must be lower than 20%.
You’ll find the above values from the next SQL:
Instance 1:
SQL> ANALYZE INDEX IDX_GAM_ACCT VALIDATE STRUCTURE;
Assertion processed.
SQL> SELECT identify, peak,lf_rows,lf_blks,del_lf_rows FROM
INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROW
———————- ———– ———- ———- —————-
DX_GAM_ACCT 2 1 3 6
On this instance, the HEIGHT column is clearly displaying the worth 2. This isn’t a superb candidate for rebuilding. If the worth is bigger then 4, then rebuild the index in non-business hours utilizing:
ALTER INDEX REBUILD | REBULID ONLINE
Instance 2:
SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;
Assertion processed.
SQL> SELECT identify, peak, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)
*100 as ratio FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROW RATIO
—————————— ———- ———- ———- ——-
IDX_GAM_FID 1 189 62 32.80
1 row chosen.
On this instance, the ratio of deleted leaf rows to whole leaf rows
is clearly above 20%. This can be a good candidate for rebuilding.
Let’s rebuild the index and look at the outcomes
SQL> ANALYZE INDEX IDX_GAM_FID REBUILD;
Assertion processed.
SQL> ANALYZE INDEX IDX_GAM_FID VALIDATE STRUCTURE;
Assertion processed.
SQL> SELECT identify, peak, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*
100 as ratio FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROW RATIO
—————————— ———- ———- ———- ——-
IDX_GAM_FID 1 127 0 0
1 row chosen.
Analyzing the INDEX_STATS desk reveals that the 62 deleted leaf rows have been dropped from the index. Discover that the overall variety of leaf rows went from 189 to 127, which is a distinction of 62 leaf rows (189-127). This index ought to present higher efficiency for the applying.
[ad_2]
index
#Oracle #Database #Administrator #Job #Interview #Preparation #Questions
Publish byBedewy for information askme VISIT GAHZLY
Leave a Reply