Oracle classic interview questions

1. Are you familiar with databases sql2005 and Oracle? Sql2005 is a database product of Microsoft. It is an RDBMS database, which is generally used in some medium-sized databases and cannot be used across platforms. Oracle is a data product of Oracle. It supports massive data storage, distributed heat distribution, multi-user, cross-platform, and data security integrity control. It is an ordbms and is generally used in large companies. 2. Can I design a database? How to Implement Database Import and Export updates using the powerdisine tool is generally enough to meet the third paradigm. Logical Import and Export of exp and IMP databases 3. How to show only duplicate data or show duplicate data: Select * From tablename group by ID having count (*)> 1 do not show duplicate: Select * From tablename group by ID having count (*) = 14. What is database ing? It is the process of ing database tables and fields to model layer class names and attributes. 5. What are the methods for writing pages? What are your common methods? Write a paging using SQL statements? How do I use stored procedures to write pages? Use top paging in sqlserver, rownum in Oracle, or the analysis function row_number use top: Select top 20, N. * From tablename N minus select top 10, M. * From tablename M use analysis function: Select * from (select n. *, row_number () over (order by columnname) num from tablename N) Where num> = 10 and num <= 20; when using the process, you only need to use two parameters for the paging range. In Oracle, to encapsulate the process in the package, you must use a dynamic cursor variable to return the dataset. 6. Left join and right join in Oracle left join: left join right join select n. column, M. column from tablename1 N left join tablename2 m on N. columnname = m. columnname is implemented using where: Select n. column, M. column from tablename1 N, tablename2 m where n. columnname () = M. columnname7. What are reflection, serialization, and deserialization? What are the transaction levels? Reflection is a way to dynamically access DDL when the program is running. Serialization stores objects directly to files in binary, XML, and other ways. Deserialization is the process of extracting objects stored in files. There are three transaction levels: Page Level, application level, and database level. 8. How to test data testing? In PLSQL, a special test tool may be used for a process or function. When transaction 9 is used, if the method in the database access layer is called in the business logic layer, there are many classes in the access layer, and classes have many methods. Each method must be implemented. How can this problem be solved? Implementation of the Universal Data access layer 10. When will trigger a security management, B log management, and C complex business logic be used? 11. How can I display tree control in the database? Use the parent ID and child id to achieve 12. How to optimize the database? A. design the data structure adjustment. This part is completed before the development of the information system. Programmers need to consider whether to use the Partition Function of the Oracle database and whether to create indexes for frequently accessed database tables. B. Adjust the application structure design. This part is also completed before the development of information systems. In this step, programmers need to consider what kind of architecture the application uses. It uses the traditional Client/Server two-layer architecture, or use Browser/Web/database's three-tier architecture. Different Application architectures require different database resources. C. Adjust Database SQL statements. The execution of the application is ultimately attributed to the execution of SQL statements in the database. Therefore, the execution efficiency of SQL statements ultimately determines the performance of the Oracle database. Oracle recommends using Oracle optimizer and row-level manager to adjust and optimize SQL statements. D. Adjust the server memory allocation. Memory Allocation is optimized during the operation of the information system. The database administrator can adjust the global zone of the database system (SGA) according to the database running status) the size of the data buffer, log buffer, and shared pool. You can also adjust the size of the global program area (PGA. Note that the larger the SGA area, the better. The higher the SGA area, the higher the memory occupied by the operating system during the Conference, resulting in Page Swap of virtual memory. This will reduce the system. E. Adjust the hard disk I/O. This step is completed before the information system development. The database administrator can store data files in the same tablespace on different hard disks to achieve I/O load balancing between hard disks. F. adjust operating system parameters. For example, for Oracle databases running on UNIX operating systems, you can adjust the size of the UNIX data buffer pool and the memory size that each process can use.