Oracle Commands.............!!!!!!!!

SQL.RELEASE Oracle Version show release
SQL.SQLCODE Current error code show sqlcode
SQL.USER Currently connected user show user
List SQL*Plus Parameters show all
SQL> show all
CONN as SYS CONN <logon> AS <SYSDBA | SYSOPER>
conn / as sysdba

conn sys@orabase AS SYSDBA
Define / Undefine
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Nov 19 15:20:10 2004

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> DEFINE _CONNECT_IDENTIFIER
DEFINE _CONNECT_IDENTIFIER = "ORABASE" (CHAR)

SQL> DEFINE _DATE
DEFINE _DATE = "19-NOV-04" (CHAR)

SQL> DEFINE _EDITOR
DEFINE _EDITOR = "Notepad" (CHAR)

SQL> DEFINE _O_VERSION
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options" (CHAR)

SQL> DEFINE _PRIVILEGE
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)

SQL> DEFINE _SQLPLUS_RELEASE
DEFINE _SQLPLUS_RELEASE = "1001000200" (CHAR)

SQL> DEFINE _USER
DEFINE _USER = "SYS" (CHAR)

SQL>UNDEFINE _USER

Display

Clear Screen		 clear scr
Display Headers SET HEAD <OFF | ON> 
SELECT table_name FROM all_tables;

set head off

SELECT table_name FROM all_tables;

set head on

Line Size		 SET LINESIZE <integer>
SELECT text
FROM all_source
WHERE ROWNUM < 21;

set linesize 121

SELECT text
FROM all_source
WHERE ROWNUM < 21;

Page Size		 SET PAGESIZE <integer>
SELECT object_name
FROM all_objects
WHERE ROWNUM < 60;

set pagesize 20

SELECT object_name
FROM all_objects
WHERE ROWNUM < 60;

set pagesize 0

SELECT object_name
FROM all_objects
WHERE ROWNUM < 60;

Page Break		 BREAK ON <column_name> [SKIP PAGE]
break on overload skip page

set pagesize 20
set linesize 121
col overload format a8

SELECT overload, position, argument_name, in_out, data_type
FROM all_arguments
WHERE object_name = 'CREATE_TUNING_TASK'
ORDER BY overload, position;

Pause		 SET PAUSE <OFF | ON>
SELECT object_name
FROM all_objects
WHERE ROWNUM < 60;

set pause on

SELECT object_name
FROM all_objects
WHERE ROWNUM < 60;

set pause off

Timing		 SET TIMING <OFF | ON>
SELECT COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

set timing on

SELECT COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

set timing off

Display Output From DBMS_OUTPUT.PUT_LINE built-in package		 set serveroutput <ON | OFF>
DECLARE
 x VARCHAR2(20) := 'This is a test';
BEGIN
  dbms_output.put_line(x);
END;
/

set serveroutput on

DECLARE
 x VARCHAR2(20) := 'This is a test';
BEGIN
  dbms_output.put_line(x);
END;
/
Edit / Editor
Define An Editor _editor
define _editor=vi
Edit The Last Command ed
ed
Execute

Run a stored procedure		 exec <procedure_name>
CREATE OR REPLACE PROCEDURE demoexec IS
BEGIN
  dbms_output.put_line('*** Executed ***');
END demoexec;
/

set serveroutput on

exec demoexec
Help
Display SQL*Plus Help help <command>
help index

help variable
Host
Shell to the operating system host
host

exit
Unix Shell !
SQL> !

 exit
Windows Shell $
SQL> $

 exit
Password
Change Password password
password
Quit
Exit exit
exit
Quit quit
quit
Run
Run RUN
set serveroutput on

BEGIN
  dbms_output.put_line('test');
END;
/

run

SQL> SET SQLPROMPT '_CONNECT_IDENTIFIER> '

SQL> set sqlprompt 'SQL> '

Comments

Post a Comment

Popular posts from this blog

How to Get Back Asus Zenphone 5 from Soft Brick

Image
Dear Folks, Very Warm Welcome, Now I'm Writing this post for those who have facing hard bricked problem in #AsusZenphone5. Before some days same problem i have faced and i was fully fade up with my phone and i go too many shops for  repairing the phone but they charged too much for this. then i decided too solve it. So when your phone is in USB Logo mode or Asus Logo Mode Then it's confirmed your phone has been bricked. So, let’s start! There are some files, please prepare to download :   IntelAndroidDrvSetup1.5.0.exe Asus USB Driver. Download  here  Asus Zenfone 5 RAW firmware. Download   here   xfstk-downloader-setup-1.5.1.exe   iSocUSB-Driver-Setup-1.0.4.exe Recovery Image. Download  here ADB + Fastboot. Download  here Instruction :  install above driver as per ranking. follow these instruction while installing: Right click on setup Select run as administrator  Note  : while installing isoc drive system may ask you to connect your device or resta
Read more

How to configure Glowroot with Liferay

Image
 Dear Folks, First of all we need to know what is Glowroot and what can we get from it. Features Trace capture for slow requests and errors Continuous profiling (with very handy filtering) Response time breakdown charts Response time percentile charts SQL capture and aggregation Service call capture and aggregation MBean attribute capture and charts Configurable alerting Historical rollup of all data (1m, 5m, 30m, 4h) with configurable retention Full support for async requests that span multiple threads Responsive UI with mobile support Optional central collector Step 1: Download Glowroot from Official website and extract that zip into your drive (                     Like: C:\glowroot). Now open your Liferay workspace and in the Servers tab double click on your server and click on the Browse External properties button and write your glowroot file path with javaagent. (Ex:-  -javaagent:path/to/glowroot.jar) After that restart your Liferay server and your gloroot will start on default
Read more

Restore Database from .idb & .frm files in MySQL

Image
  Prerequisites:                Backup of .ibd and .frm files: Ensure you have all the relevant .ibd (InnoDB data files) and .frm (table definition files) for the tables you want to restore. MySQL Server: Ensure you have a running instance of MySQL server where you want to restore the database. Step 1: Stop Current Running MySQL Service Step 2: Copy ibdata1, ib_logfile0 and ib_logfile1 files from Old Mysql forlder Which is exist (C:\ProgramData\MySQL\MySQL Server x.x\Data).   Step 3: Copy your Database Folder which you want to restore and paste into your current mysql instance data folder.   Step 4: Change the configuration of my.ini file Add  –innodb_force_recovery=6 after the [mysqld] (please search inside my.ini file [mysqld]). Step 5: S tart the mysql service. Step 6: After recovering the database please remove the line innodb_force_recovery=6 from my.ini configuration and restart again mysql service.  
Read more