Oracle subquery is a sql query within another sql query. Some example: select tab.name, tab.surname from (select name, surname from customer where surname like ‘S%’) tab select id from myusers where (name, surname) in (select name, surname from customer where surname like ‘S%’) tab select (select ‘1’ from dual) from table.
Often you may need to load SQL Plans into SPM but isn’t possibile because query is out of cache. There is a simple procedure for loading SQL plans into SPM using AWR data. The first step, if not exists, is to create a SQL Tuning Set (STS). exec dbms_sqltune.create_sqlset(sqlset_name => ‘1ffbnq9wwkhtz_sqlset_test’, description => ‘query test’); Name and description are customizable. In sqlset_name I using even sql_id because in this way I can remember the query. Next step is to take initial and final snapshot id. I prefer generate AWR report to make sure that the query was running. Obviously...
Of course, you can do that using glogin.sql and login.sql. What’s the differences between glogin.sql and login.sql? glogin.sql is used for any user, and login.sql is used for you only. In fact, login.sql is called after glogin.sql where you start a sqlplus session. glogin.sql is usually located in $ORACLE_HOME/sqlplus/admin, login.sql can be located where you launch sqlplus (or in the $SQLPATH, if you want to launch sqlplus from several path) So, the examples below can be applied to login.sql and glogin.sql without differences. This is a simple but useful example. login.sql: set lin 160 set pages 50000 define_editor=vi Alter...
In order to solve the above error the solutions are (considering database 10g and above.) 1.) Increase the free space where archiver archives the archivelog. The location where archiver archives the log is determined by parameter file pfile or spfile. This can be determined by loging into sqlplus and issuing SQL> show parameter log_archive_dest If archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by : SQL> show parameter db_recovery_file_dest; Find the space used in flash recovery area by : SQL> SELECT * FROM V$RECOVERY_FILE_DEST; It is possibile in case of free space on diskgroup or filesystem to add...
Starting of 2005 version, was introduced the concept of table partitioning (as in Oracle.) At the time of choosing the type of partitioning, you have to be careful about the choice: LEFT or RIGHT. From my experience I would recommend to use the partitioning Left, or with values less than the value of partitioning. This must be done in order to release (so then archive or delete) the last partition without data handling (which means that there is no operation of read / write disk, which takes time and resources). This is important to reduce the time and easier operation....
The Oracle RDBMS provides various control functions of the system security. Among these, there is also a function of AUDIT TRAIL that lets you record any activity carried out on the database of interest. The control possibilities are very sophisticated and flexible, but it is necessary to avoid excessive control to avoid needlessly heavy and impossible access checks. Controls POSSIBLE And ‘possible to activate controls aimed at: • specific SQL statement (eg. connect, create table, …), • specific privileges (eg. grant system, …), • operations (select, insert, alter, execute, …) of objects RDBMS. Can be controlled actions that returned errors...