先创建一个普通用户并授权：
C:\>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 31 21:49:45 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create user yang identified by yang;
用户已创建。
SQL> grant connect, resource to yang;
授权成功。
SQL>
普通用户登录创建表：
C:\>sqlplus "yang/yang@orcl"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 31 21:50:42 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table tb_user (
2 username varchar2(20) primary key,
3 password varchar2(20) not null
4 );
表已创建。
下面都是sys用户
Version：
SQL> SELECT banner FROM v$version;
BANNER
----------------------------------------------------------------
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
sys用户：
SQL> SELECT version FROM v$instance;
VERSION
-----------------
10.2.0.1.0
Comments：
SQL> SELECT 1 FROM dual --comment;
1
----------
1Current User：
SQL> SELECT user FROM dual;
USER
------------------------------
YANG
List Users：
SQL> SELECT username FROM all_users ORDER BY username;
USERNAME
------------------------------
ANONYMOUS
BI
CTXSYS
DBSNMP
DIP
DMSYS
EXFSYS
HR
IX
MDDATA
MDSYS
USERNAME
------------------------------
MGMT_VIEW
OE
OLAPSYS
ORDPLUGINS
ORDSYS
OUTLN
PM
SCOTT
SH
SI_INFORMTN_SCHEMA
SYS
USERNAME
------------------------------
SYSMAN
SYSTEM
TSMSYS
WMSYS
XDB
YANG
已选择28行。
SQL> SELECT name FROM sys.user$;
NAME
------------------------------
ANONYMOUS
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
AUTHENTICATEDUSER
BI
CONNECT
CTXAPP
CTXSYS
DBA
DBSNMP
DELETE_CATALOG_ROLE
NAME
------------------------------
DIP
DMSYS
EJBCLIENT
EXECUTE_CATALOG_ROLE
EXFSYS
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
GLOBAL_AQ_USER_ROLE
HR
HS_ADMIN_ROLE
IMP_FULL_DATABASE
NAME
------------------------------
IX
JAVADEBUGPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVAUSERPRIV
JAVA_ADMIN
JAVA_DEPLOY
LOGSTDBY_ADMINISTRATOR
MDDATA
MDSYS
MGMT_USER
NAME
------------------------------
MGMT_VIEW
OE
OEM_ADVISOR
OEM_MONITOR
OLAPSYS
OLAP_DBA
OLAP_USER
ORDPLUGINS
ORDSYS
OUTLN
PM
NAME
------------------------------
PUBLIC
RECOVERY_CATALOG_OWNER
RESOURCE
SCHEDULER_ADMIN
SCOTT
SELECT_CATALOG_ROLE
SH
SI_INFORMTN_SCHEMA
SYS
SYSMAN
SYSTEM
NAME
------------------------------
TSMSYS
WMSYS
WM_ADMIN_ROLE
XDB
XDBADMIN
XDBWEBSERVICES
YANG
_NEXT_USER
已选择63行。
List Password Hashes：
SQL> SELECT name, password, astatus FROM sys.user$;
NAME PASSWORD ASTATUS
------------------------------ ------------------------------ ----------
SYS 5638228DAF52805F 0
PUBLIC 0
CONNECT 0
RESOURCE 0
DBA 0
SYSTEM D4DF7931AB130E37 0
SELECT_CATALOG_ROLE 0
EXECUTE_CATALOG_ROLE 0
DELETE_CATALOG_ROLE 0
EXP_FULL_DATABASE 0
IMP_FULL_DATABASE 0
NAME PASSWORD ASTATUS
------------------------------ ------------------------------ ----------
OUTLN 4A3BA55E08595C81 9
RECOVERY_CATALOG_OWNER 0
GATHER_SYSTEM_STATISTICS 0
LOGSTDBY_ADMINISTRATOR 0
AQ_ADMINISTRATOR_ROLE 0
AQ_USER_ROLE 0
GLOBAL_AQ_USER_ROLE GLOBAL 0
SCHEDULER_ADMIN 0
DIP CE4A36B8E06CA59C 9
HS_ADMIN_ROLE 0
DMSYS BFBA5A553FD9E28A 9
NAME PASSWORD ASTATUS
------------------------------ ------------------------------ ----------
AUTHENTICATEDUSER 0
TSMSYS 3DF26A8B17D0F29F 9
OEM_ADVISOR 0
OEM_MONITOR 0
DBSNMP DB2BB2E7C4D59ABA 0
WMSYS 7C9BA362F8314299 9
WM_ADMIN_ROLE 0
JAVAUSERPRIV 0
JAVAIDPRIV 0
JAVASYSPRIV 0
JAVADEBUGPRIV 0
NAME PASSWORD ASTATUS
------------------------------ ------------------------------ ----------
EJBCLIENT 0
JAVA_ADMIN 0
JAVA_DEPLOY 0
EXFSYS 66F4EF5650C20355 9
CTXSYS 71E687F036AD56E5 9
CTXAPP 0
XDB 88D8364765FCE6AF 9
ANONYMOUS anonymous 9
XDBADMIN 0
_NEXT_USER 0
OLAPSYS 3FB8EF9DB538647C 9
NAME PASSWORD ASTATUS
------------------------------ ------------------------------ ----------
XDBWEBSERVICES 0
ORDSYS 7EFA02EC7EA6B86F 9
ORDPLUGINS 88A2B2C183431F00 9
SI_INFORMTN_SCHEMA 84B8CBCA4D477FA3 9
MDSYS 72979A94BAD2AF80 9
SYSMAN E0182D5446B5898F 0
OLAP_DBA 0
OLAP_USER 0
MDDATA DF02A496267DEE66 9
MGMT_USER 0
MGMT_VIEW F25A184809D6458D 0
NAME PASSWORD ASTATUS
------------------------------ ------------------------------ ----------
SCOTT F894844C34402B67 9
HR 6399F3B38EDF3288 9
OE 9C30855E7E0CB02D 9
IX 2BE6F80744E08FEB 9
SH 9793B3777CD3BD1A 9
PM 72E382A52E89575A 9
BI FA1D2B85B70213F3 9
YANG B30C5333A59ADA20 0
已选择63行。
SQL> SELECT name,spare4 FROM sys.user$ where rownum <= 10;
NAME
------------------------------
SPARE4
--------------------------------------------------------------------------------
SYS
PUBLIC
CONNECT
NAME
------------------------------
SPARE4
--------------------------------------------------------------------------------
RESOURCE
DBA
SYSTEM
NAME
------------------------------
SPARE4
--------------------------------------------------------------------------------
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
NAME
------------------------------
SPARE4
--------------------------------------------------------------------------------
EXP_FULL_DATABASE
已选择10行。
List Privileges：
SQL> SELECT * FROM session_privs where rownum <= 10;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
MANAGE TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
已选择10行。
SQL> SELECT * FROM dba_sys_privs WHERE grantee = 'DBSNMP';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBSNMP CREATE PROCEDURE NO
DBSNMP UNLIMITED TABLESPACE NO
DBSNMP SELECT ANY DICTIONARY NO
DBSNMP CREATE TABLE NO
SQL> SELECT grantee FROM dba_sys_privs WHERE privilege = 'SELECT ANY DICTIONARY';
GRANTEE
------------------------------
DBA
SYSMAN
OLAP_DBA
OEM_MONITOR
DBSNMP
IX
已选择6行。
SQL> SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE ROWNUM <= 10;
GRANTEE GRANTED_ROLE
------------------------------ ------------------------------
BI RESOURCE
HR RESOURCE
IX CONNECT
IX RESOURCE
IX AQ_USER_ROLE
IX SELECT_CATALOG_ROLE
IX AQ_ADMINISTRATOR_ROLE
OE RESOURCE
OE XDBADMIN
PM CONNECT
已选择10行。
List DBA Accounts：
SQL> SELECT DISTINCT grantee FROM dba_sys_privs WHERE ADMIN_OPTION = 'YES';
GRANTEE
------------------------------
AQ_ADMINISTRATOR_ROLE
DBA
SCHEDULER_ADMIN
SYSTEM
SYS
Current Database：
SQL> SELECT global_name FROM global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM
SQL> SELECT name FROM v$database;
NAME
---------
ORCL
SQL> SELECT instance_name FROM v$instance;
INSTANCE_NAME
----------------
orcl
SQL> SELECT SYS.DATABASE_NAME FROM DUAL;
DATABASE_NAME
--------------------------------------------------------------------------------
ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM
List Databases：
SQL> SELECT DISTINCT owner FROM all_tables;
OWNER
------------------------------
MDSYS
TSMSYS
DMSYS
OUTLN
CTXSYS
OLAPSYS
HR
YANG
SYSTEM
EXFSYS
SCOTT
OWNER
------------------------------
DBSNMP
ORDSYS
SYSMAN
OE
SH
PM
XDB
IX
SYS
WMSYS
已选择21行。
List Columns：
SQL> SELECT column_name FROM all_tab_columns WHERE table_name = 'TB_USER';
COLUMN_NAME
------------------------------
USERNAME
PASSWORD
这里表名一定要大写，因为ORACLE会自动把SQL语句转换成大写。
SQL> SELECT column_name FROM all_tab_columns WHERE owner = 'YANG';
COLUMN_NAME
------------------------------
USERNAME
PASSWORD
List Tables：
SQL> SELECT owner, table_name FROM all_tables WHERE table_name = 'TB_USER';
OWNER TABLE_NAME
------------------------------ ------------------------------
YANG TB_USER
Find Tables From Column Name：
SQL> SELECT owner, table_name FROM all_tab_columns WHERE column_name LIKE '%PASS%';
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS DBA_HIST_SQL_WORKAREA_HSTGRM
SYS DBA_HIST_SQL_WORKAREA_HSTGRM
SYS DBA_ROLES
SYS DBA_USERS
SYS EXU10LNK
SYS EXU10LNK
SYS EXU10LNK
SYS EXU10LNK
SYS EXU10LNKU
SYS EXU10LNKU
SYS EXU10LNKU
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS EXU10LNKU
SYS EXU8LNK
SYS EXU8LNKU
SYS EXU8PHS
SYS EXU8PHS
SYS EXU8ROL
SYS EXU8USR
SYS EXU8USRU
SYS EXU9LNK
SYS EXU9LNK
SYS EXU9LNKU
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS EXU9LNKU
SYS GV_$PGATARGET_ADVICE_HISTOGRAM
SYS GV_$PGATARGET_ADVICE_HISTOGRAM
SYS GV_$SQL_PLAN_STATISTICS_ALL
SYS GV_$SQL_PLAN_STATISTICS_ALL
SYS GV_$SQL_PLAN_STATISTICS_ALL
SYS GV_$SQL_WORKAREA
SYS GV_$SQL_WORKAREA
SYS GV_$SQL_WORKAREA
SYS GV_$SQL_WORKAREA_ACTIVE
SYS GV_$SQL_WORKAREA_HISTOGRAM
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS GV_$SQL_WORKAREA_HISTOGRAM
SYS KU$_10_1_DBLINK_VIEW
SYS KU$_10_1_DBLINK_VIEW
SYS KU$_DBLINK_VIEW
SYS KU$_DBLINK_VIEW
SYS KU$_PROFILE_VIEW
SYS KU$_PSW_HIST_LIST_VIEW
SYS KU$_PSW_HIST_LIST_VIEW
SYS KU$_ROLE_VIEW
SYS KU$_USER_VIEW
SYS LINK$
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS LINK$
SYS USER$
SYS USER_DB_LINKS
SYS USER_HISTORY$
SYS USER_HISTORY$
SYS V_$PGA_TARGET_ADVICE_HISTOGRAM
SYS V_$PGA_TARGET_ADVICE_HISTOGRAM
SYS V_$SQL_PLAN_STATISTICS_ALL
SYS V_$SQL_PLAN_STATISTICS_ALL
SYS V_$SQL_PLAN_STATISTICS_ALL
SYS V_$SQL_WORKAREA
OWNER TABLE_NAME
------------------------------ ------------------------------
SYS V_$SQL_WORKAREA
SYS V_$SQL_WORKAREA
SYS V_$SQL_WORKAREA_ACTIVE
SYS V_$SQL_WORKAREA_HISTOGRAM
SYS V_$SQL_WORKAREA_HISTOGRAM
SYS WRH$_SQL_WORKAREA_HISTOGRAM
SYS WRH$_SQL_WORKAREA_HISTOGRAM
SYSMAN MGMT_ARU_CREDENTIALS
SYSMAN MGMT_BCN_TXN_HTTP
SYSMAN MGMT_OB_ADMIN_HOSTS
SYSMAN MGMT_RCVCAT_CONFIG
OWNER TABLE_NAME
------------------------------ ------------------------------
SYSMAN MGMT_VIEW_USER_CREDENTIALS
YANG TB_USER
已选择68行。
Select Nth Row：
SQL> SELECT username FROM (SELECT ROWNUM r, username FROM all_users ORDER BY username) WHERE r=9;
USERNAME
------------------------------
MGMT_VIEW
Select Nth Char：
SQL> SELECT substr('abcd', 3, 1) FROM dual;
S
-
c
Bitwise AND：
SQL> SELECT bitand(6,2) FROM dual;
BITAND(6,2)
-----------
2
SQL> SELECT bitand(6,1) FROM dual;
BITAND(6,1)
-----------
0
ASCII Value -> Char：
SQL> SELECT chr(65) FROM dual;
C
-
A
Char -> ASCII Value：
SQL> SELECT ascii('A') FROM dual;
ASCII('A')
----------
65
Casting：
SQL> SELECT CAST(1 AS char) FROM dual;
C
-
1
SQL> SELECT CAST('1' AS int) FROM dual;
CAST('1'ASINT)
--------------
1
String Concatenation：
SQL> SELECT 'A' || 'B' FROM dual;
'A
--
AB
Case Statement：
SQL> SELECT CASE WHEN 1=1 THEN 1 ELSE 2 END FROM dual;
CASEWHEN1=1THEN1ELSE2END
------------------------
1
SQL> SELECT CASE WHEN 1=2 THEN 1 ELSE 2 END FROM dual;
CASEWHEN1=2THEN1ELSE2END
------------------------
2
Avoiding Quotes：
SQL> SELECT chr(65) || chr(66) FROM dual;
CH
--
AB
Time Delay：
SQL> SELECT UTL_INADDR.get_host_name('192.168.1.103') FROM dual;
UTL_INADDR.GET_HOST_NAME('192.168.1.103')
--------------------------------------------------------------------------------
20100617-1003Make DNS Requests：
SQL> SELECT UTL_INADDR.get_host_address('www.baidu.com') FROM dual;
UTL_INADDR.GET_HOST_ADDRESS('WWW.BAIDU.COM')
--------------------------------------------------------------------------------
115.239.210.26
SQL> SELECT UTL_HTTP.REQUEST('http://www.baidu.com/') FROM dual;
UTL_HTTP.REQUEST('HTTP://WWW.BAIDU.COM/')
--------------------------------------------------------------------------------
<!DOCTYPE html><!--STATUS OK--><html><head><meta http-equiv="content-type" conte
nt="text/html;charset=utf-8"><title>百度一下，你就知道</title><style >html,body{
height:100%}html{overflow-y:auto}#wrapper{position:relative;_position:;min-heigh
t:100%}#content{padding-bottom:100px;text-align:center}#ftCon{height:100px;posit
ion:absolute;bottom:44px;text-align:center;width:100%;margin:0 auto;z-index:0;ov
erflow:hidden}#ftConw{width:720px;margin:0 auto}body{font:12px arial;text-align:
;background:#fff}body,p,form,ul,li{margin:0;padding:0;list-style:none}body,form,
#fm{position:relative}td{text-align:left}img{border:0}a{color:#00c}a:active{colo
r:#f60}#u{color:#999;padding:4px 10px 5px 0;text-align:right}#u a{margin:0 5px}#
u .reg{margin:0}#m{width:720px;margin:0 auto}#nv a,#nv b,.btn,#lk{font-size:14px
}#fm{padding-left:110px;text-align:left;z-index:1}input{border:0;padding:0}#nv{h
UTL_HTTP.REQUEST('HTTP://WWW.BAIDU.COM/')
--------------------------------------------------------------------------------
eight:19px;font-size:16px;margin:0 0 4px;text-align:left;text-indent:137px}.s_ip
t_wr{width:418px;height:30px;display:inline-block;margin-right:5px;background:ur
l(/img/i-1.0.0.png) no-repeat -304px 0;border:1px solid #b6b6b6;border-color:#9a
9a9a #cdcdcd #cdcdcd #9a9a9a;vertical-align:top}.s_ipt{width:405px;height:22px;f
ont:16px/22px arial;margin:5px 0 0 7px;background:#fff;outline:0;-webkit-appeara
nce:none}.s_btn{width:95px;height:32px;padding-top:2px\9;font-size:14px;backgrou
nd:#ddd url(/img/i-1.0.0.png);cursor:pointer}.s_btn_h{background-position:-100px
0}.s_btn_wr{width:97px;height:34px;display:inline-block;background:url(/img/i-1
.0.0.png) no-repeat -202px 0;*position:relative;z-index:0;vertical-align:top}#lg
img{vertical-align:top;margin-bottom:3px}#lk{margin:33px 0}#lk span{font:14px "
宋体"}#lm{height:60px}#lh{margin:16px 0 5px;word-spacing:3px}.tools{position:abs
UTL_HTTP.REQUEST('HTTP://WWW.BAIDU.COM/')
--------------------------------------------------------------------------------
olute;top:-4px;*top:10px;right:7px}#mHolder{width:62px;position:relative;z-index
:296;display:none}#mCon{height:18px;line-height:18px;position:absolute;cursor:po
inter;padding:0 18px 0 0;background:url(/img/bg-1.0.0.gif) no-repeat right -134
Local File Access：
SQL> SELECT value FROM v$parameter2 WHERE name = 'utl_file_dir';
VALUE
--------------------------------------------------------------------------------
SQL>
Hostname, IP Address：
SQL> SELECT UTL_INADDR.get_host_name FROM dual;
GET_HOST_NAME
--------------------------------------------------------------------------------
20100617-1003
SQL> SELECT host_name FROM v$instance;
HOST_NAME
----------------------------------------------------------------
20100617-1003
SQL> SELECT UTL_INADDR.get_host_address FROM dual;
GET_HOST_ADDRESS
--------------------------------------------------------------------------------
192.168.1.103
SQL> SELECT UTL_INADDR.get_host_name('192.168.1.103') FROM dual;
UTL_INADDR.GET_HOST_NAME('192.168.1.103')
--------------------------------------------------------------------------------
20100617-1003
Location of DB files：
SQL> SELECT name FROM V$DATAFILE;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF