oracle

Segue abaixo as respostas corretas do tópico Entrevista de Empregos para DBA’s (parte 1).

  1. Export/import, CREATE TABLE … AS SELECT, COPY, etc.
  2. The IMPORT IGNORE option tells import to ignore already exists errors. If it is not specified the already existing tables will be skipped. The default is N.
  3. Use the rollback segment … shrink command.
  4. The user is assigned to the SYSTEM tablespace as a default and temporary tablespace. This is bad because no user object should be in SYSTEM.
  5. Owned by the SYS user: dbms_shared_pool, dbms_utility, dbms_sql, dbms_ddl, dbms_session, dbms_output, dbms_snapshot. Also CAT*.SQL and UTL*.SQL.
  6. The Oracle will use the default name of SYS_Cxxxx where xxx is a system generated number. Hard to track.
  7. This results in the index that is automatically generated being placed in the user’s default tablespace. Since this will be in the same tablespace as the table, this will cause serious performance problems.
  8. You can use ALTER TABLE for both. However, for the enable clause you must specify the USING INDEX and TABLESPACE clause.
  9. The index is created in the user’s default tablespace and all sizing information is lost.
  10. If the UNIX system is capable of asynchronous I/O, then only one is required. If the UNIX system is incapable of asynchronous I/O, then up to twice the number of disks used by Oracle or twice the number of CPUs.
  11. You can’t use a hot backup without being in ARCHICVELOG mode.
  12. This is caused by large or long running transactions that are either wrapped onto their own rollback space or had another transaction write on part of their rollback space. This can be mitigated by breaking the transactions into smaller pieces and running them separately or increasing the size of the rollback segments and their extents.
  13. By checking the status column in the DBA_OBJECTS or ALL_ and USER_ views.
  14. Check if the user specified the full name of the object (schema), or has synonym pointing to that object.
  15. You need to verify that the developer has direct grants on the tables on which the view is based. You can not create a stored object based with grants given through views.
  16. The best way is to analyze the table and then use the data provided in the dba_tables view to get the average row length and other data for the calculation. The quick and the dirty way is to look at the number of blocks the table is actually using and ratio the number of rows to its number of blocks against expected number of rows.
  17. Query V$SESSION and V$PROCESS. The other is to check the current_logins parameter in the V$SYSSTAT. Another way on UNIX is to do a “ps –ef|grep oracle| wc –l” command, but this works only against a single instance installation.
  18. Somehow two rows have been inserted into DUAL.
  19. Run the analyze index to validate structure and then calculate the ratio of LF_ROWS_LEN / LF_ROWS_LEN + DEL_LF_ROWS_LEN and if it is not at least 70% the index should be rebuilt. Or id the ratio of DEL_LF_ROWS_LEN / LF_ROWS_LEN + DEL_LF_ROWS_LEN is nearing 30%.

Perceberam que muitas perguntas são específicas de uma versão muito antiga né pessoal? Oracle 8 não está mais entre nós! 

Realmente essas questões são muito antigas, mesmo assim fiz questão de publicá-las pois é importante o DBA conhecer as diferenças das versões recentes para as versões mais antigas – e não se assuste se de repente alguma empresa acabar colocando uma questão deste tipo numa entrevista nos dias de hoje. Neste caso é importante estar preparado para responder nessa mesma entrevista de emprego as mudanças que ocorreram das versões antigas para a versão mais recente.

Esta entrevista foi elaborada por um profissional norte-americano, altamente qualificado e ex-funcionário da Oracle. Mesmo assim, algumas respostas podem gerar polêmica, por isso fiquem a vontade para debater abaixo, via comentários.

Publicarei mais entrevistas nos próximos dias, fiquem atentos!

Milton Bastos é DBA Oracle e Desenvolvedor PL/SQL, dividido entre Apucarana/PR e Curitiba/PR. Certificações: OCA (Oracle 11g DBA Certified Associate), Oracle Database 11g Data Warehousing Certified Implementation Specialist, Oracle Database 11g Sales Specialist Assessment, Oracle Database Appliance PreSales Specialist Assessment, Oracle Database Appliance Sales Specialist Assessment