Chapter 2 - Oracle Overview and Architecture
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla
Review Questions
1. Which component is not part of the Oracle instance?
A. System Global Area
B. Process monitor
C. Control file
D. Shared pool
---
The Oracle instance consists of memory structures and background processes. The Oracle database consists of the physical components such as data files, redo log files, and the control file. The System Global Area and shared pool are memory structures. The process monitor is a background process.
Ans: C.
2. Which background process and associated database component guarantee that committed data is saved even when the changes have not been recorded in the data files?
A. DBWn and database buffer cache
B. LGWR and online redo log file
C. CKPT and control file
D. DBWn and archived redo log file
----
The LGWR process writes the redo log buffer entries when a COMMIT occurs. The redo log buffer holds information on the changes made to the database. The DBWn process writes dirty buffers to the data file, but it is independent of the COMMIT. The dirty buffers can be written to the disk before or after a COMMIT. Writing the committed changes to the online redo log file ensures that the changes are never lost in case of a failure.
Ans: B
3. What is the maximum number of database writer processes allowed in an Oracle instance?
A. 1
B. 10
C. 256
D. Limit specified by an operating system parameter
----
By default, every Oracle instance has one database writer process: DBW0.
Additional processes can be started by setting the initialization parameter DB_WRITER_PROCESSES(DBW1 through DBW9).
Ans: B.
4. Which background process is not started by default when you start up the Oracle instance?
A. DBWn
B. LGWR
C. CKPT
D. ARCn
5. Which of the following best describes a Real Application Cluster configuration?
A. One database, multiple instances
B. One instance, multiple databases
C. Multiple databases on multiple servers
D. Shared server process takes care of multiple user processes
6. Choose the correct hierarchy, from largest to smallest, from this list of logical database structures.
A. Database, tablespace, extent, segment, block
B. Database, tablespace, segment, extent, block
C. Database, segment, tablespace, extent, block
D. Database, extent, tablespace, segment, block
7. Which component of the SGA contains the parsed SQL code?
A. Buffer cache
B. Dictionary cache
C. Library cache
D. Parse cache
8. Julie, one of the database analysts, is complaining that her queries are taking longer and longer to complete, although they seem to produce the correct results. The DBA suspects that the buffer cache is not sized correctly and is causing delays due to data blocks not being available in memory. Which initialization parameter should the DBA use to monitor the usage of the buffer cache?
A. BUFFER_POOL_ADVICE
B. DB_CACHE_ADVICE
C. DB_CACHE_SIZE
D. SHARED_POOL_SIZE
----
The parameter DB_CACHE_ADVICE can be set to YES to enable cache usage monitoring. DB_CACHE_SIZE and SHARED_POOL_SIZE are sizing parameters for SGA structures; the parameter BUFFER_POOL_ ADVICE does not exist.
Ans: B.
9. Which background process is responsible for writing the dirty buffers to the database files?
A. DBWn
B. SMON
C. LGWR
D. CKPT
E. PMON
10. Which component in the SGA has the dictionary cache?
A. Buffer cache
B. Library cache
C. Shared pool
D. Program Global Area
E. Large pool
11. When a server process is terminated abnormally, which background process is responsible for releasing the locks held by the user?
A. DBWn
B. LGWR
C. SMON
D. PMON
---
D. PMON, or the process monitor, is responsible for cleaning up failed user processes. It reclaims all resources held by the user and releases all locks on tables and rows held by the user.
Ans: D
12. What is a dirty buffer?
A. Data buffer that is being accessed
B. Data buffer that is changed but is not written to the disk
C. Data buffer that is free
D. Data buffer that is changed and written to the disk
---
Dirty buffers are the buffer blocks that need to be written to the data files. The data in these buffers has changed and is not yet written to the disk. A block waiting to be written to disk is on the dirty list and cannot be overwritten.
Ans: B
13. If you are updating one row in a table using the ROWID in the WHERE clause (assume that the row is not already in the buffer cache), what will be the minimum amount of information read to the database buffer cache?
A. The entire table is copied to the database buffer cache.
B. The extent is copied to the database buffer cache.
C. The block is copied to the database buffer cache.
D. The row is copied to the database buffer cache.
---
The block is the smallest unit that can be copied to the buffer cache.
Ans: C.
14. What happens next when a server process is not able to find enough free buffers to copy the blocks from disk?
A. Signals the CKPT process to clean up the dirty buffers
B. Signals the SMON process to clean up the dirty buffers
C. Signals the CKPT process to initiate a checkpoint
D. Signals the DBWn process to write the dirty buffers to disk
---
To reduce disk I/O contention, the DBWn process does not write the changed buffers immediately to the disk. They are written only when the dirty buffers reach a threshold, when there are not enough free buffers available, or when the checkpoint occurs.
Ans: D.
15. Which memory structures are shared? Choose two.
A. Sort area
B. Program Global Area
C. Library cache
D. Large pool
---
The sort area is allocated to the server process as part of the PGA. The PGA is allocated when the server process starts and is de- allocated when the server process completes. The library cache and the large pool are part of the SGA and are shared. The SGA is created when the instance starts.
Ans: C and D.
16. Which of the following initialization parameters does NOT determine the size of the buffer cache?
A. DB_KEEP_CACHE_SIZE
B. DB_CACHE_SIZE
C. DB_BLOCK_SIZE
D. DB_RECYCLE_CACHE_SIZE
----
The parameter DB_BLOCK_SIZE does not change the size of the buffer cache. It changes only the size of each Oracle block written to and read from disk.
Ans: C.
17. Which memory structure records all database changes made to the instance?
A. Database buffer
B. Dictionary cache
C. Redo log buffer
D. Library cache
-----
The redo log buffer keeps track of all changes made to the database before writing them to the redo log files. The database buffer contains the data blocks that are read from the data files, and are most recently used. The dictionary cache holds the most recently used data dictionary information. The library cache holds the parsed SQL statements and PL/SQL code.
Ans: C.
18. What is the minimum number of online redo log files required in a database?
A. One
B. Two
C. Four
D. Zero
19. When are the system change numbers assigned?
A. When a transaction begins
B. When a transaction ends abnormally
C. When a checkpoint occurs
D. When a COMMIT is issued
----
A system change number (SCN) is assigned when the transaction is committed. The SCN is a unique number acting as an internal time- stamp, used for recovery and read-consistent queries.
Ans: D.
20. Which of the following is not part of the database buffer pool?
A. KEEP
B. RECYCLE
C. LIBRARY
D. DEFAULT
----
There is no database buffer cache named LIBRARY. The DBA can configure multiple buffer pools by using the appropriate initialization parameters for performance improvements. The KEEP buffer pool retains the data blocks in memory; they are not aged out. The RECYCLE buffer pool removes the buffers from memory as soon as they are not needed. The DEFAULT buffer pool contains the blocks that are not assigned to the other pools.
Ans: C
21. Memory granules are not allocated at instance startup for which of the following SGA components?
A. Database buffer cache
B. Shared pool
C. Redo log buffers
D. Large pool
E. None of the above
----
All of these SGA components are allocated in granule units. A minimum of three granules are allocated for the SGA at instance startup: one for the fixed portion of the SGA, one for the database buffer cache, and one for the shared pool.
Ans: E.
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Other Answers
4. D. ARCn is the archiver process, which is started only when the LOG_ARCHIVE_START initialization parameter is set to TRUE. DBWn, LGWR, CKPT, SMON, and PMON are the default processes associated with all instances.
5. A. In a Real Application Cluster configuration, multiple instances (known as nodes) can mount one database. One instance can be associated with only one database. In a multithreaded configuration, one shared server process takes requests from multiple user processes.
6. B. The first level of logical database structure is the tablespace. A tablespace may have segments, segments have one or more extents, and extents have one or more contiguous blocks.
7. C. The library cache contains the parsed SQL code. If a query is executed again before it is aged out of the library cache, Oracle will use the parsed code and execution plan from the library cache. The buffer cache has data blocks that are cached. The dictionary cache caches data dictionary information. There is no SGA component named parse cache.
9. A. The DBWn process writes the dirty buffers to the data files under two circumstances—when a checkpoint occurs or when the server process searches the buffer cache for a set threshold.
10. C. The shared pool has three components: the library cache, the dictionary cache, and the control structures.
18. B. There should be at least two redo log files in a database. The LGWR process writes to the redo log files in a circular manner, so there should be at least two files.
Friday, August 18, 2006
Thursday, August 17, 2006
Fundamentals I Ch1- New Features for Administrators
Chapter 1 - Oracle9i New Features for Administrators
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla
Review Questions ( 14 total)
1. Which new feature of Oracle9i allows users to view the contents of a table at some point in the past?
A. LogMiner
B. Import
C. Metadata Viewer
D. Oracle Flashback
---
Ans D. The package DBMS_FLASHBACK allows the user to view the contents of a table or tables at a specified time in the past.
2. Choose the statement below that is true regarding enhancements to shared SQL statements in the shared pool.
A. The cursor sharing feature can re-use a SQL statement even if the columns in the statement are in a different order or the GROUP BY clause is different.
B. The new columns CPU_COST, IO_COST, and TEMP_SPACE in PLAN_ TABLE help the rule-based optimizer (RBO) to be more accurate.
C. Even if the only difference in SQL statements is in the literal values, the SQL statement may not be re-used if the histogram statistics are skewed for a column in the WHERE clause.
D. The CURSOR_SHARING parameter now supports the SIMILAR and DERIVED values.
-----
Ans C. If the execution plan is independent of the literal values used in the query,
it is likely that the query can be re-used. The new columns in the PLAN_TABLE assist the cost-based optimizer,
not the rule-based optimizer, and the CURSOR_SHARING parameter does not have DERIVED as a possible value.
3. Given the table declaration below, identify invalid use of timestamp datatypes in an expression or function. (Choose two.)
CREATE TABLE TRANSACTIONS
(TRANS_ID NUMBER,
AMOUNT NUMBER(10,2),
TRANS_START TIMESTAMP,
TRANS_END TIMESTAMP,
SHIP_DATE DATE,
EXPIRE_DATE INTERVAL DAY(0) TO SECOND(0));
A. TRANS_START - TRANS_END
B. TO_TIMESTAMP(AMOUNT, ‘YY-MM-DD HH:MI:SS’)
C. TRANS_START + INTERVAL ‘4’ DAY
D. TRANS_START + SHIP_DATE
----
Any reasonable combination of date and time data types is allowed. However, date fields cannot be added together, and dollar amounts are not valid arguments to date conversion functions.
Ans: B, D
4. Which of the following operations cannot be performed online without any disruption to ongoing online transactions?
A. Dropping a user-defined column
B. Rebuilding secondary IOT indexes
C. Adding new columns to a heap-based table
D. Rebuilding a primary IOT index
-----
Primary keys cannot be dropped nor can columns with user- defined data types be dropped without making a table unavailable.
Ans: A.
5. Which of the following types of joins are now allowed in the FROM clause of a SQL statement? (Choose all that apply.)
A. cross joins
B. inner joins
C. full outer joins
D. left outer joins
----
Ans: A, B, C, D. All the above joins are now specified in the WHERE clause. Previous versions of Oracle supported all these join types other than the full outer join in the WHERE clause.
6. How many panes exist in the new version of Oracle Enterprise Manager (OEM)?
A. One, with pop-up windows
B. Four, as in previous versions
C. Two, in a master/detail format
D. Two, with DBA tools in the right-hand pane
---
The new OEM not only uses a cleaner, easier-to-use two-pane layout, it integrates all the tools previously available through DBA Studio.
Ans: C
7. The DBA is importing a table and an index from a dump file that was exported from another Oracle9i database. Which options does the DBA have when using the statistics from this dump file? (Choose all that apply.)
A. Explicitly accept all statistics
B. Explicitly reject all statistics
C. Let IMPORT decide if the statistics are safe; otherwise recalculate
D. Accept statistics only for non-partitioned tables
E. Explicitly re-calculate statistics, regardless of whether the original statistics are good or bad
---
Ans A, B, C, E. IMPORT cannot reject statistics based on whether the table is partitioned.
8. The Secure Application Role feature in Oracle9i allows a user to authenticate role privileges by doing which of the following?
A. Calling a stored procedure
B. Using OS authentication
C. Using PWFILE authentication
D. Using an encrypted role password
-----
The stored procedure can restrict access to the role in a number of ways, such as by date and time or by the IP address of the user requesting access to the role.
Ans: A.
9. Chad normally runs queries against very small tables, but has informed the DBA that he will soon be running some queries against the data warehouse tables for the operations manager. What can the DBA do to make sure that these new queries won’t slow down OLTP operations? (Choose the best answer.)
A. The DBA can use the Active Session Pool feature to put Chad’s session on hold until another user in the same consumer group finishes their session.
B. The DBA can use the Automatic Consumer Group Switching feature to switch Chad’s consumer group to the same group as the OLTP users.
C. The DBA can use the Active Session Pool feature to suspend the session if there are too many active OLTP sessions.
D. The DBA can use the Automatic Consumer Group Switching feature to switch Chad’s consumer group to a secondary group that has a lower priority.
----
Ans D. Switching to another consumer group with a lower priority will allow the query to finish while minimizing the impact on the ongoing OLTP transactions.
The Active Session Pool feature controls resource usage within the same consumer group and will not necessarily reduce the contention with OLTP transactions.
10. Which of the following is not an advantage of having the data dictionary in the redo logs when using LogMiner for DML and DDL activity?
A. The LogMiner activity will not impact other users’ activity against the data dictionary.
B. The LogMiner reports will be more accurate against a snapshot of the data dictionary rather than a constantly changing live data dictionary.
C. Bad blocks in one of the redo logs will not stop the LogMiner analysis with a static data dictionary.
D. The database does not need to be open to use LogMiner, since all needed information is in the redo logs.
----
Bad blocks can be ignored in LogMiner; however this feature is independent of where LogMiner retrieves the data dictionary information.
Ans: C.
11. Which of the new RMAN options can the DBA use to save time when a backup does not complete successfully?
A. Restart the backup with the NOT BACKED UP option.
B. Use mirrored backups to send the backup to two different device types.
C. Include the archive logs in the backup.
D. There is no alternative to a failed backup other than to restart the backup.
-----
Running RMAN with the NOT BACKED UP option backs up only the missing or incomplete files.
Ans: A.
12. Identify the true statement regarding binary SPFILEs.
A. All changes to an SPFILE are implemented only after the instance is restarted.
B. Changes made to an SPFILE with the ALTER SYSTEM command can be made simultaneously with the change to the memory copy of the parameter.
C. An SPFILE can exist on the client side.
D. SPFILEs can be used in conjunction with a text-format PFILE.
----
Ans B. The changes to an SPFILE may be made at the same time the change is made to the memory copy of the parameter. SPFILEs only exist on the server side and are created using a PFILE. Once the SPFILE is activated, the PFILE is no longer needed.
13. Place the following block read options in order of access time, shortest to longest.
A. Block is read from remote cache without Cache Fusion
B. Block is read from a local cache
C. Block is read from a remote cache with Cache Fusion
D. Block is read from a shared disk
---
Blocks read from a remote cache without Cache Fusion must be written to the shared disk by the remote instance before the blocks can be retrieved by the local instance.
Ans: B,C, D, A
14. PL/SQL execution is significantly more efficient at runtime for which of the following reasons? (Choose two.)
A. Native C code is generated for PL/SQL procedures.
B. The compiled code resides in the SGA.
C. Byte code is generated by the compiler and therefore can easily be re-used by different transactions.
D. The compiled code resides in the PGA.
---
The compiled code is moved to the PGA to reduce contention on the SGA; interpreted byte code is inherently less efficient to execute than native compiled C code.
Ans: A, D.
OCA/OCP: Oracle9i DBA Fundamentals I Study Guide
by Biju Thomas and Bob Bryla
Review Questions ( 14 total)
1. Which new feature of Oracle9i allows users to view the contents of a table at some point in the past?
A. LogMiner
B. Import
C. Metadata Viewer
D. Oracle Flashback
---
Ans D. The package DBMS_FLASHBACK allows the user to view the contents of a table or tables at a specified time in the past.
2. Choose the statement below that is true regarding enhancements to shared SQL statements in the shared pool.
A. The cursor sharing feature can re-use a SQL statement even if the columns in the statement are in a different order or the GROUP BY clause is different.
B. The new columns CPU_COST, IO_COST, and TEMP_SPACE in PLAN_ TABLE help the rule-based optimizer (RBO) to be more accurate.
C. Even if the only difference in SQL statements is in the literal values, the SQL statement may not be re-used if the histogram statistics are skewed for a column in the WHERE clause.
D. The CURSOR_SHARING parameter now supports the SIMILAR and DERIVED values.
-----
Ans C. If the execution plan is independent of the literal values used in the query,
it is likely that the query can be re-used. The new columns in the PLAN_TABLE assist the cost-based optimizer,
not the rule-based optimizer, and the CURSOR_SHARING parameter does not have DERIVED as a possible value.
3. Given the table declaration below, identify invalid use of timestamp datatypes in an expression or function. (Choose two.)
CREATE TABLE TRANSACTIONS
(TRANS_ID NUMBER,
AMOUNT NUMBER(10,2),
TRANS_START TIMESTAMP,
TRANS_END TIMESTAMP,
SHIP_DATE DATE,
EXPIRE_DATE INTERVAL DAY(0) TO SECOND(0));
A. TRANS_START - TRANS_END
B. TO_TIMESTAMP(AMOUNT, ‘YY-MM-DD HH:MI:SS’)
C. TRANS_START + INTERVAL ‘4’ DAY
D. TRANS_START + SHIP_DATE
----
Any reasonable combination of date and time data types is allowed. However, date fields cannot be added together, and dollar amounts are not valid arguments to date conversion functions.
Ans: B, D
4. Which of the following operations cannot be performed online without any disruption to ongoing online transactions?
A. Dropping a user-defined column
B. Rebuilding secondary IOT indexes
C. Adding new columns to a heap-based table
D. Rebuilding a primary IOT index
-----
Primary keys cannot be dropped nor can columns with user- defined data types be dropped without making a table unavailable.
Ans: A.
5. Which of the following types of joins are now allowed in the FROM clause of a SQL statement? (Choose all that apply.)
A. cross joins
B. inner joins
C. full outer joins
D. left outer joins
----
Ans: A, B, C, D. All the above joins are now specified in the WHERE clause. Previous versions of Oracle supported all these join types other than the full outer join in the WHERE clause.
6. How many panes exist in the new version of Oracle Enterprise Manager (OEM)?
A. One, with pop-up windows
B. Four, as in previous versions
C. Two, in a master/detail format
D. Two, with DBA tools in the right-hand pane
---
The new OEM not only uses a cleaner, easier-to-use two-pane layout, it integrates all the tools previously available through DBA Studio.
Ans: C
7. The DBA is importing a table and an index from a dump file that was exported from another Oracle9i database. Which options does the DBA have when using the statistics from this dump file? (Choose all that apply.)
A. Explicitly accept all statistics
B. Explicitly reject all statistics
C. Let IMPORT decide if the statistics are safe; otherwise recalculate
D. Accept statistics only for non-partitioned tables
E. Explicitly re-calculate statistics, regardless of whether the original statistics are good or bad
---
Ans A, B, C, E. IMPORT cannot reject statistics based on whether the table is partitioned.
8. The Secure Application Role feature in Oracle9i allows a user to authenticate role privileges by doing which of the following?
A. Calling a stored procedure
B. Using OS authentication
C. Using PWFILE authentication
D. Using an encrypted role password
-----
The stored procedure can restrict access to the role in a number of ways, such as by date and time or by the IP address of the user requesting access to the role.
Ans: A.
9. Chad normally runs queries against very small tables, but has informed the DBA that he will soon be running some queries against the data warehouse tables for the operations manager. What can the DBA do to make sure that these new queries won’t slow down OLTP operations? (Choose the best answer.)
A. The DBA can use the Active Session Pool feature to put Chad’s session on hold until another user in the same consumer group finishes their session.
B. The DBA can use the Automatic Consumer Group Switching feature to switch Chad’s consumer group to the same group as the OLTP users.
C. The DBA can use the Active Session Pool feature to suspend the session if there are too many active OLTP sessions.
D. The DBA can use the Automatic Consumer Group Switching feature to switch Chad’s consumer group to a secondary group that has a lower priority.
----
Ans D. Switching to another consumer group with a lower priority will allow the query to finish while minimizing the impact on the ongoing OLTP transactions.
The Active Session Pool feature controls resource usage within the same consumer group and will not necessarily reduce the contention with OLTP transactions.
10. Which of the following is not an advantage of having the data dictionary in the redo logs when using LogMiner for DML and DDL activity?
A. The LogMiner activity will not impact other users’ activity against the data dictionary.
B. The LogMiner reports will be more accurate against a snapshot of the data dictionary rather than a constantly changing live data dictionary.
C. Bad blocks in one of the redo logs will not stop the LogMiner analysis with a static data dictionary.
D. The database does not need to be open to use LogMiner, since all needed information is in the redo logs.
----
Bad blocks can be ignored in LogMiner; however this feature is independent of where LogMiner retrieves the data dictionary information.
Ans: C.
11. Which of the new RMAN options can the DBA use to save time when a backup does not complete successfully?
A. Restart the backup with the NOT BACKED UP option.
B. Use mirrored backups to send the backup to two different device types.
C. Include the archive logs in the backup.
D. There is no alternative to a failed backup other than to restart the backup.
-----
Running RMAN with the NOT BACKED UP option backs up only the missing or incomplete files.
Ans: A.
12. Identify the true statement regarding binary SPFILEs.
A. All changes to an SPFILE are implemented only after the instance is restarted.
B. Changes made to an SPFILE with the ALTER SYSTEM command can be made simultaneously with the change to the memory copy of the parameter.
C. An SPFILE can exist on the client side.
D. SPFILEs can be used in conjunction with a text-format PFILE.
----
Ans B. The changes to an SPFILE may be made at the same time the change is made to the memory copy of the parameter. SPFILEs only exist on the server side and are created using a PFILE. Once the SPFILE is activated, the PFILE is no longer needed.
13. Place the following block read options in order of access time, shortest to longest.
A. Block is read from remote cache without Cache Fusion
B. Block is read from a local cache
C. Block is read from a remote cache with Cache Fusion
D. Block is read from a shared disk
---
Blocks read from a remote cache without Cache Fusion must be written to the shared disk by the remote instance before the blocks can be retrieved by the local instance.
Ans: B,C, D, A
14. PL/SQL execution is significantly more efficient at runtime for which of the following reasons? (Choose two.)
A. Native C code is generated for PL/SQL procedures.
B. The compiled code resides in the SGA.
C. Byte code is generated by the compiler and therefore can easily be re-used by different transactions.
D. The compiled code resides in the PGA.
---
The compiled code is moved to the PGA to reduce contention on the SGA; interpreted byte code is inherently less efficient to execute than native compiled C code.
Ans: A, D.
Tuesday, August 15, 2006
Demonstrating FULL, LEFT, RIGHT Join
SQL> select * from movie;
MID DID
---------- ----------
100 1
101 2
102 2
103 3
104 1
105
106
7 rows selected.
SQL> select * from director;
DID NAME
---------- --------------------
1 Jane
2 Bob
3 Denis
4 Jack
5 Dev
6 Kay
6 rows selected.
SQL> select * from movie join director using (did) order by mid;
DID MID NAME
---------- ---------- --------------------
1 100 Jane
2 101 Bob
2 102 Bob
3 103 Denis
1 104 Jane
SQL> select * from movie left join director using (did) order by mid;
DID MID NAME
---------- ---------- --------------------
1 100 Jane
2 101 Bob
2 102 Bob
3 103 Denis
1 104 Jane
105
106
7 rows selected.
SQL> select * from movie right join director using (did) order by mid;
DID MID NAME
---------- ---------- --------------------
1 100 Jane
2 101 Bob
2 102 Bob
3 103 Denis
1 104 Jane
4 Jack
5 Dev
6 Kay
8 rows selected.
SQL> select * from movie full join director using (did) order by mid;
DID MID NAME
---------- ---------- --------------------
1 100 Jane
2 101 Bob
2 102 Bob
3 103 Denis
1 104 Jane
105
106
4 Jack
5 Dev
6 Kay
10 rows selected.
ALL, SOME and subquery -A Test
SQL> select * from tab1;
A B C
---------- ---------- ----------
1 2 3
2 2 3
3 2 3
4 4 3
SQL> select * from tab1 where a = (select a from tab1);
select * from tab1 where a = (select a from tab1)
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
SQL> select * from tab1 where a = all (select a from tab1);
no rows selected
SQL> select * from tab1 where a = some (select a from tab1);
A B C
---------- ---------- ----------
1 2 3
2 2 3
3 2 3
4 4 3
SQL> select * from tab1 where a = all (select b from tab1);
no rows selected
SQL> select * from tab1 where a = some (select b from tab1);
A B C
---------- ---------- ----------
2 2 3
4 4 3
SQL> select * from tab1 where a = all (select c from tab1);
A B C
---------- ---------- ----------
3 2 3
SQL> select * from tab1 where a = some (select c from tab1);
A B C
---------- ---------- ----------
3 2 3
SQL> select * from tab1 where a = some (select b, c from tab1);
select * from tab1 where a = some (select b, c from tab1)
*
ERROR at line 1:
ORA-00913: too many values
Function TRIM examples
SQL> select trim('*' from '***comments***') from dual;
TRIM('*'
--------
comments
SQL> select trim(leading '*' from '***comments***') from dual;
TRIM(LEADIN
-----------
comments***
SQL> select trim(trailing '*' from '***comments***') from dual;
TRIM(TRAILI
-----------
***comments
SQL> select trim(both '*' from '***comments***') from dual;
TRIM(BOT
--------
comments
Subscribe to:
Comments (Atom)
