Summary of oracle interview questions

Oracle interview Summary 1. The relational tablespace describing tablespace and datafile is a logical component of the database. Physically, the database data is stored in the data file. Logically, the database is stored in the tablespace, which is composed of one or more data files. 2. Describe the relationship between table/segment/extent/block. These are the logical structures stored in Oracle. BLOCK is the basic unit of Oracle storage. It is determined by DB_BLOCK_SIZE, which is usually 8 KB. It can also be defined as 2 kb, 4 kb, 16 KB, 32 KB, 64 KB, etc, the minimum disk storage unit is sector (512 bytes). Oracle data blocks are composed of consecutive sector, and Oracle read/write units are data blocks, the BLOCK size should be set to an integer multiple of the disk data BLOCK size to avoid IO waste of continuous data blocks to form a partition extent for convenient space management, including space allocation and release. Segment space is allocated in units of partitions. It improves the efficiency of space allocation, but brings about space fragmentation. Each table or index in Oracle corresponds to this segment. If a partition table or partition index is used, each partition corresponds to a segment. Each segment has a name, that is, the name of the object (table, index). The segment is composed of extent, but not consecutive. A table must be at least a segment. For a partition table, each partition is a segment. A table can be regarded as a logical concept, and a segment can be considered as a physical implementation of this logical concept; A segment consists of one or more extents. A segment cannot span tablespaces but data files. An extent consists of multiple consecutive blocks and cannot span data files; A block consists of 1-multiple OS blocks and is the minimum storage unit for oracle I/o. 3. The database startup process involves three steps: nomount, mount, and open. 4. query the deptno, ename, and annual salary in the emp table, and name the annual salary alias total salaryselect deptno, ename, 12 * sal "total salary" from emp; 5. Completely Delete table e. (After deleting the e table, the system will put the e table in the recycle bin. to completely delete the e table, purge recyclebin is required. You can also drop table e purge in two steps; after table e is deleted, scott does not have its records.) SQL-> drop table e purge; 6. Update the test table to set the ID number of a student named 'li _ si' to 3. 7. Add the bonus (comm) of each employee in the emp table to 500. SQL> update emp set comm = nvl (comm, 0) + 500; (Common + (plus),-(minus), * (multiplication),/() all four arithmetic operations can be used to query data. The arithmetic operator is only applicable to the calculation of multiple numeric fields or between fields and numbers. nvl indicates that this is a function for ORACLE to process null. If it is null, another value is given, this prevents NULL from being involved in the operation. nvl (comm, 0) is changed to 0 only when comm is empty, and the execution result is: query sal and comm from the emp table (when the comm value is empty, the comm value is 0) and sum sal and comm) 8. insert SQL> insert into B select * from B where B from Table a with the same table structure. id = 1