How to print date on sqlplus report title

COLUMN report_date_col NEW_VALUE report_date
col report_date_col noprint
SELECT TO_CHAR ( SYSDATE ,'DD-Mon-YYYY:HH:MI') AS report_date_col FROM dual;

TTitle left "*** Rollback Information ( Date: &report_date ) ***" skip 2

How to get my Oracle session informations

In Oracle/PLSQL, the userenv function can be used to retrieve information about the current Oracle session.

The syntax for the userenv function is:

userenv( parameter )

parameter is the value to return from the current Oracle session.

For Example:

userenv('ENTRYID')	would return FALSE
userenv('LANGUAGE')	would return 'AMERICAN_AMERICA.WE8DEC'

 The possible values for parameter are:

Parameter	Explanation
CLIENT_INFO	Returns user session information stored using the DBMS_APPLICATION_INFO package
ENTRYID	Available auditing entry identifier
INSTANCE	The identifier number of the current instance
ISDBA	Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.
LANG	The ISO abbreviation for the language
LANGUAGE	The language, territory, and character of the session. In the following format:      language_territory.characterset
SESSIONID	The identifier of the auditing session
TERMINAL	The OS identifier of the current session

script to identify all objects belonging to a tablespace in Oracle

select owner
,      segment_name
,      segment_type
from   dba_segments
where  lower(tablespace_name) like lower('%&tablespace%')
order by owner, segment_name

Setting up Autotrace in SQL*Plus

AUTOTRACE is a facility within SQL*Plus to show us the explain plan of the queries we've executed, and
the resources they used. There is more than one way to get AUTOTRACE configured.

This is how i do to get AUTOTRACE working:
•  cd $ORACLE_HOME/rdbms/admin (or cd %ORACLE_HOME%/rdbms/admin on Windows)
•  log into SQL*Plus as SYSTEM ==> sqlplus system/pass@alias
•  run @utlxplan
•  run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;
•  run GRANT ALL ON PLAN_TABLE TO PUBLIC;
You can replace the GRANT TO PUBLIC with some user if you want. By making it public, you let
anyone trace using SQL*Plus. This prevents every user from having to install their own plan table. The alternative is for you to run @utlxplan in every schema from which you
want to use AUTOTRACE.
The next step is creating and granting the PLUSTRACE role:
•  cd $ORACLE_HOME/sqlplus/admin (or cd %ORACLE_HOME%/sqlplus/admin on Windows)
•  log into SQL*Plus as SYS or AS SYSDBA
•  run @plustrce
•  run GRANT PLUSTRACE TO PUBLIC;
Again, you can replace PUBLIC in the GRANT command with some user if you want.

This is how i customize my sqlplus prompt

I often use sqlplus to perform my daily administrative tasks and i work on multiple servers that contain multiple databases, each database contains multiple accounts.

When connected to a database, i need to know my username and my database connection to reassure me that I point to the correct database in order to avoid errors that could be catastrophic.

To customize your sqlplus prompt

• Navigate to the directory $ORACLE_HOME/sqlplus/admin
• Create a file login.sql (or edit the file if it already exists) using vi on unix / Linux or notepad on windows
• SET SQLPROMPT '&_USER.@&_CONNECT_IDENTIFIER.> '
  
    
   The next time you will login your sqlplus prompt will look like this : user@alias

Here is a complete list of SQL*Plus variables that you can use to customize your prompt.

There are eight variables defined during SQL*Plus installation. These variables only differ from user defined variables by having predefined values.

Table 12-3 Variables Predefined at SQL*Plus Installation

Variable Name	Contains
_CONNECT_IDENTIFIER	Connection identifier used to make connection, where available.
_DATE	Current date, or a user defined fixed string.
_EDITOR	Specifies the editor used by the EDIT command.
_O_VERSION	Current version of the installed Oracle Database.
_O_RELEASE	Full release number of the installed Oracle Database.
_PRIVILEGE	Privilege level of the current connection.
_SQLPLUS_RELEASE	Full release number of installed SQL*Plus component.
_USER	User name used to make connection.

Establish a direct connection to Oracle from sqlplus (without an alias)

People like us who change environments all the time with various customers, not to waste time configuring a connection alias in the tnsnames.ora file you can directly connect to an ORACLE database from sqlplus using


sqlplus user/pass@server_ip:port/sid

ex: sqlplus guy/fomi@10.10.10.10:1521/test

SQLPLUS SET System Variable Summary

I need to lookup these variables very often for my sqlplus scripts

System Variable	Description
SET APPI[NFO]{ON | OFF | text}	Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
SET ARRAY[SIZE] {15 | n}	Sets the number of rows, called a batch, that SQL*Plus will fetch from the database at one time.
SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}	Controls when Oracle Database commits pending changes to the database.
SET AUTOP[RINT] {ON | OFF}	Sets the automatic printing of bind variables.
SET AUTORECOVERY [ON | OFF]	ON sets the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery.
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]	Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE or DELETE).
SET BLO[CKTERMINATOR] {. | c | ON | OFF}	Sets the non-alphanumeric character used to end PL/SQL blocks to c.
SET CMDS[EP] {; | c | ON | OFF}	Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
SET COLSEP { | text}	In iSQL*Plus, SET COLSEP determines the column separator character to be printed between column output that is rendered inside <PRE> tags. Sets the text to be printed between selected columns.
SET CON[CAT] {. | c | ON | OFF}	Sets the character you can use to terminate a substitution variable reference if you wish to immediately follow the variable with a character that SQL*Plus would otherwise interpret as a part of the substitution variable name.
SET COPYC[OMMIT] {0 | n}	Controls the number of batches after which the COPY command commits changes to the database.
SET COPYTYPECHECK {ON | OFF}	Sets the suppression of the comparison of datatypes while inserting or appending to tables with the COPY command.
SET DEF[INE] {& | c | ON | OFF}	Sets the character used to prefix variables to c.
SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]	Sets the depth of the level to which you can recursively describe an object.
SET ECHO {ON | OFF}	Controls whether the START command lists each command in a script as the command is executed.
*SET EDITF[ILE] file_name[.ext]	Sets the default filename for the EDIT command.
SET EMB[EDDED] {ON | OFF}	Controls where on a page each report begins.
SET ESC[APE] {\ | c | ON | OFF}	Defines the character you enter as the escape character.
SET FEED[BACK] {6 | n | ON | OFF}	Displays the number of records returned by a query when a query selects at least n records.
SET FLAGGER {OFF | ENTRY | INTERMED[IATE] | FULL}	Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
*SET FLU[SH] {ON | OFF}	Controls when output is sent to the user's display device.
SET HEA[DING] {ON | OFF}	Controls printing of column headings in reports.
SET HEADS[EP] { | | c | ON | OFF}	Defines the character you enter as the heading separator character.
SET INSTANCE [instance_path | LOCAL]	Changes the default instance for your session to the specified instance path.
SET LIN[ESIZE] {80 | n} SET LIN[ESIZE] {150 | n} in iSQL*Plus	Sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
SET LOBOF[FSET] {1 | n}	Sets the starting position from which CLOB and NCLOB data is retrieved and displayed.
SET LOGSOURCE [pathname]	Specifies the location from which archive logs are retrieved during recovery.
SET LONG {80 | n}	Sets maximum width (in bytes) for displaying LONG, CLOB, NCLOB and XMLType values; and for copying LONG values.
SET LONGC[HUNKSIZE] {80 | n}	Sets the size (in bytes) of the increments in which SQL*Plus retrieves a LONG, CLOB, NCLOB or XMLType value.
SET MARK[UP] HTML [ON | OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON | OFF}] [SPOOL {ON | OFF}] [PRE[FORMAT] {ON | OFF}]	Outputs HTML marked up text, which is the output used by iSQL*Plus.
SET NEWP[AGE] {1 | n | NONE}	Sets the number of blank lines to be printed from the top of each page to the top title.
SET NULL text	Sets the text that represents a null value in the result of a SQL SELECT command.
SET NUMF[ORMAT] format	Sets the default format for displaying numbers.
SET NUM[WIDTH] {10 | n}	Sets the default width for displaying numbers.
SET PAGES[IZE] {14 | n}	Sets the number of lines in each page.
SET PAU[SE] {ON | OFF | text}	Enables you to control scrolling of your terminal when running reports.
SET RECSEP {WR[APPED] | EA[CH] | OFF}	RECSEP tells SQL*Plus where to make the record separation.
SET RECSEPCHAR { | c}	Display or print record separators.
SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]	Controls whether to display the output (that is, DBMS_OUTPUT PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
*SET SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE]}	Enables correct alignment for terminals that display shift characters.
*SET SHOW[MODE] {ON | OFF}	Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET.
*SET SQLBL[ANKLINES] {ON | OFF}	Controls whether SQL*Plus puts blank lines within a SQL command or script.
SET SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]}	Converts the case of SQL commands and PL/SQL blocks just prior to execution.
*SET SQLCO[NTINUE] {> | text}	Sets the character sequence SQL*Plus displays as a prompt after you continue a SQL*Plus command on an additional line using a hyphen (–).
*SET SQLN[UMBER] {ON | OFF}	Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block.
SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}	Sets the behavior or output format of VARIABLE to that of the release or version specified by x.y[.z].
*SET SQLPRE[FIX] {# | c}	Sets the SQL*Plus prefix character.
*SET SQLP[ROMPT] {SQL> | text}	Sets the SQL*Plus command prompt.
SET SQLT[ERMINATOR] {; | c | ON | OFF}	Sets the character used to end and execute SQL commands to c.
*SET SUF[FIX] {SQL | text}	Sets the default file that SQL*Plus uses in commands that refer to scripts.
*SET TAB {ON | OFF}	Determines how SQL*Plus formats white space in terminal output.
*SET TERM[OUT] {ON | OFF}	Controls the display of output generated by commands executed from a script.
*SET TI[ME] {ON | OFF}	Controls the display of the current time.
SET TIMI[NG] {ON | OFF}	Controls the display of timing statistics.
*SET TRIM[OUT] {ON | OFF}	Determines whether SQL*Plus puts trailing blanks at the end of each displayed line.
*SET TRIMS[POOL] {ON | OFF}	Determines whether SQL*Plus puts trailing blanks at the end of each spooled line.
SET UND[ERLINE] {- | c | ON | OFF}	Sets the character used to underline column headings in SQL*Plus reports to c.
SET VER[IFY] {ON | OFF}	Controls whether SQL*Plus lists the text of a SQL statement or PL/SQL command before and after SQL*Plus replaces substitution variables with values.
SET WRA[P] {ON | OFF}	Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width.
SET XQUERY BASEURI {text}	Defines the base URI to use. This is useful to change the prefix of the file to access when writing generic XQuery expressions.
SET XQUERY ORDERING {UNORDERED | ORDERED | DEFAULT}	Controls the ordering of results from an XQuery.
SET XQUERY NODE {BYVALUE | BYREFERENCE | DEFAULT}	Sets the preservation mode for notes created or returned.
SET XQUERY CONTEXT {text}	Specifies an XQuery context item which can be either a node or a value.