PLSQL questions
here are some general questions
PL/SQL Questions:
1. Describe the difference between a procedure, function and anonymous pl/sql block.
A function must return a value while a procedure doesn’t have to. Functions can be called from SQL.
anonymous pl/sql block begins with a DECLARE statement.
[@more@]
2. Describe the use of %ROWTYPE and %TYPE in PL/SQL
%TYPE associates a variable with a single column type.
%ROWTYPE associates a variable with an entire table row.
% TYPE is used to declare a field with the same type as that of a specified table’s column.
% ROWTYPE is used to declare a record with the same types as found in the specified database table, view and cursor.
3. What packages (if any) has Oracle provided for use by developers?
Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_OUTPUT, DBMS_DDL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_JOB, DBMS_UTILITY, UTL_FILE.
4. Describe the use of PL/SQL tables
PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations.
5. What are SQLCODE and SQLERRM
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered.
6. How can you find within a PL/SQL block, if a cursor is open?
Use the %ISOPEN cursor status variable.
7. How can you generate debugging output from PL/SQL?
Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.
8. What are the types of triggers?
There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words:
Ø BEFORE ALL ROW INSERT
Ø AFTER ALL ROW INSERT
Ø BEFORE INSERT
Ø AFTER INSERT etc.
DBA:
4. If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Is this bad or good? Why?
The user is assigned the SYSTEM tablespace as a default and temporary tablespace. This is bad because it causes user objects and temporary segments to be placed into the SYSTEM tablespace resulting in fragmentation and improper table placement
(only data dictionary objects and the system rollback segment should be in SYSTEM).
How can you tell if a database object is invalid?
By checking the status column of the DBA_, ALL_ or USER_OBJECTS views, depending upon whether you own or only have permission on the view or are using a DBA account.
If you have an example table, what is the best way to get sizing data for the production table implementation?
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 pertinent data for the calculation. The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of expected rows.
17. How can you find out how many users are currently logged into the database? How can you find their operating system id?
There are several ways. One is to look at the v$session or v$process views. Another way is to check the current_logins parameter in the v$sysstat view. Another if you are on UNIX is to do a "ps -ef|grep oracle|wc -l? command, but this only works against a single instance installation.
19. How can you determine if an index needs to be dropped and rebuilt?
Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio
BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.
8. What special Oracle feature allows you to specify how the cost based system treats a SQL statement?
The COST based system allows the use of HINT to control the optimizer path selection.
10. What is a Cartesian product?
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.
18. How do you generate file output from SQL?
By use of the SPOOL command
14. If you see contention for library caches how can you fix it?
Increase the size of the shared pool.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/14377/viewspace-1060086/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Interview-QuestionsView
- Laravel interview questionsLaravelView
- PHP Interview Questions (11)PHPView
- SAP WM Interview QuestionsView
- JDBC-Interview Questions翻譯JDBCView
- RCE_sample_ctf_questions(ing)
- oracle plsqlOracleSQL
- 題解:CF1773G Game of QuestionsGAM
- PLSQL Developer 14SQLDeveloper
- plsql use skillsSQL
- plsql配置使用SQL
- PLSQL程式語言SQL
- PLSQL Developer 行號SQLDeveloper
- plsql中文亂碼SQL
- windows安裝plsqlWindowsSQL
- PLSQL Developer配置使用SQLDeveloper
- 0710_plsql 函式SQL函式
- [20180511]PLSQL與fetchsize.txtSQL
- PLSQL Developer 12 註冊碼SQLDeveloper
- plsql 除錯 pipelined 函式SQL除錯函式
- plsql developer 視窗的使用SQLDeveloper
- 6. PLSQL 編寫規範SQL
- 配置PLSQL Developer詳細步驟SQLDeveloper
- PLSQL只匯出表結構SQL
- plsql developer 15的安裝配置SQLDeveloper
- plsql developer工具生成sql monitor reportSQLDeveloper
- Oracle vs PostgreSQL Develop(29) - COMMIT in PLSQLOracleSQLdevMIT
- [20220708]PLSQL – choosing the BEST data type.txtSQL
- [20210621]plsql_code_type=native.txtSQL
- PLSQL一些常用的知識點SQL
- PLSQL安裝與使用問題彙總SQL
- plsql以及instantclient下載安裝配置使用SQLclient
- 使用plsql 匯出欄位為json 格式SQLJSON
- [20211116]plsql_code_type=native補充.txtSQL
- 關於plsql,crt登入比較慢SQL
- 解決plsql中中文亂碼問題SQL
- PLSQL Developer 複製查詢結果 卡頓SQLDeveloper
- plsql中資料生成工具data generator的使用SQL
- [20211027]引數plscope_settings分析PLSQL.txtSQL