“Learn Oracle database programming with hundreds of Interview Questions and Answers and examples and get a better job as an Oracle DBA with this basic and advance Oracle Database Interview Questions and Answers guide”
A Program Global Area (PGA) is a memory buffer that is allocated for each individual database session and it contains session specific information such as SQL statement data or buffers used for sorting. The value specifies the total memory allocated by all sessions, and changes will take effect as new sessions are started.
A user account is identified by a user name and defines the user's attributes, including the following:
* Password for database authentication
* Privileges and roles
* Default tablespace for database objects
* Default temporary tablespace for query processing work space
User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.
If the DBA lost the password of the SYSTEM user account, he/she can go to the Oracle server machine, and run SQL*Plus on server locally with the operating system authentication method to gain access to the database. The tutorial exercise below shows you how:
(Terminal server to the Oracle server machine)
(Start SQL*Plus)
SQL>CONNECT / AS SYSDBA
Connected.
SQL> ALTER USER SYSTEM IDENTIFIED BY ssap_lgg;
User altered.
Notice that the (/) in the CONNECT command tells SQL*Plus to use the current user on local operating system as the connection authentication method.
A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users.
There are 4 types of commands you can run at the SQL*Plus command line prompt:
1. SQL commands - Standard SQL statements to be executed on target database on the Oracle server. For example: "SELECT * FROM ggl_faq;" is a SQL command.
2. PL/SQL commands - PL/SQL statements to be executed by the Oracle server. For example: "EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to www.rendc.org')" runs a PL/SQL command.
SQL*Plus commands - Commands to be executed by the local SQL*Plus program itself. For example: "SET NULL 'NULL'" is a SQL*Plus command.
OS commands - Commands to be executed by the local operating system. For example: "HOST dir" runs an operating system command on the local machine.
If you want to run a SQL command in SQL*Plus, you need to enter the SQL command in one or more lines and terminated with (;). The tutorial exercise below shows a good example:
SQL> SELECT 'Welcome!' FROM DUAL;
'WELCOME
--------
Welcome!
SQL> SELECT 'Welcome to rendc.org tutorials!'
2 FROM DUAL
3 ;
'WELCOMETOglobalguideline.COMTUTORIALS!'
-----------------------------------
Welcome to rendc.org tutorials!
A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include: tables, views, and other types of data objects.
If you want to run a single PL/SQL statement in SQL*Plus, you need to use the EXECUTE command as shown in the following tutorial example:
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Welcome to globalguideline!')
Welcome to globalguideline!
PL/SQL procedure successfully completed.
SQL*Plus environment is controlled a big list of SQL*Plus system settings. You can change them by using the SET command as shown in the following list:
* SET AUTOCOMMIT OFF - Turns off the auto-commit feature.
* SET FEEDBACK OFF - Stops displaying the "27 rows selected." message at the end of the query output.
* SET HEADING OFF - Stops displaying the header line of the query output.
* SET LINESIZE 256 - Sets the number of characters per line when displaying the query output.
* SET NEWPAGE 2 - Sets 2 blank lines to be displayed on each page of the query output.
* SET NEWPAGE NONE - Sets for no blank lines to be displayed on each page of the query output.
* SET NULL 'null' - Asks SQL*Plus to display 'null' for columns that have null values in the query output.
* SET PAGESIZE 60 - Sets the number of lines per page when displaying the query output.
* SET TIMING ON - Asks SQL*Plus to display the command execution timing data.
* SET WRAP OFF - Turns off the wrapping feature when displaying query output.
If you want to see the current values of SQL*Plus system settings, you can use the SHOW command as shown in the following tutorial exercise:
SQL> SHOW AUTOCOMMIT
autocommit OFF
SQL> SHOW HEADING
heading ON
SQL> SHOW LINESIZE
linesize 80
SQL> SHOW PAGESIZE
pagesize 14
SQL> SHOW FEEDBACK
FEEDBACK ON for 6 or more rows
SQL> SHOW TIMING
timing OFF
SQL> SHOW NULL
null ""
SQL> SHOW ALL
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.
Behaviors of SQL*Plus are also controlled a some environment variables predefined on the local operating system. Here are some commonly used SQL*Plus environment variables:
* ORACLE_HOME - The home directory where your Oracle client application is installed.
* PATH - A list of directories where SQL*Plus will search for executable or DLL files. PATH should include $ORACLE_HOMEin.
* SQLPLUS - The directory where localization messages are stored. SQLPLUS should be set to $ORACLE_HOMEsqlplusmesg
* TNS_ADMIN - The directory where the connect identifier file, tnsnames.ora is located. TNS_ADMIN should be set to $ORACLE_HOME/network/admin.
Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.
An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.
If you want your query output to be generated in HTML format, you can use the "SET MARKUP HTML ON" to turn on the HTML feature. The following tutorial exercise gives you a good example:
SQL> connect HR/retneclgg
SQL> SET MARKUP HTML ON
SQL> SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
<br>
2 FROM EMPLOYEES WHERE FIRST_NAME LIKE 'Joh%';
<br>
<p>
<table border='1' width='90%' align='center' summary='Script output'>
<tr>
<th scope="col">
FIRST_NAME
</th>
<th scope="col">
LAST_NAME
</th>
<th scope="col">
HIRE_DATE
</th>
</tr>
<tr>
<td>
John
</td>
<td>
Seo
</td>
<td>
12-FEB-98
</td>
</tr>
<tr>
<td>
John
</td>
<td>
Russell
</td>
<td>
01-OCT-96
</td>
</tr>
</table>
<p>
The output spooling a nice feature of the command-line SQL*Plus tool. If the spooling feature is turned on, SQL*Plus will send a carbon copy of the everything on your screen to a specified local file.
Output spooling is used mostly for quick dump of data to local files. Here are the commands to turn on and off output spooling in SQL*Plus:
* SPOOL fileName - Turning on output spooling with the specified file.
* SPOOL OFF - Turning off output spooling and close the spool file.
An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.
Normally, when you run a SELECT statement in SQL*Plus, the output will be displayed on your screen. If you want the output to be saved to local file, you can use the "SPOOL fileName" command to specify a local file and start the spooling feature. When you are done with your SELECT statement, you need to close the spool file with the "SPOOL OFF" command. The following tutorial exercise gives you a good example:
SQL> connect HR/retneclgg
SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SET LINESIZE 1000
SQL> SPOOL empemployees.lst
SQL> SELECT * FROM EMPLOYEES;
......
SQL> SPOOL OFF
You should get all records in employees.lst with fixed length fields.
Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views:
* An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
* A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
* A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.
rendc.org
Whether it is technological solutions or user-oriented innovations, We have put together for you the best sources of HR Interview Questions and Answers, Tech Ideas, and Programming Tutorials."
Get all the latest information on rendc.org
© 2022 | All rights reserved.
RENDC
welcome to www.rendc.org
