oracle 入门常见问题

 解锁用户：

alter user scott identified by tiger account unlock;

修改字符集：

alter session set NLS_DATE_LANGUAGE = 'AMERICAN' ; 或'Simplified Chinese';

重建scott用户和它所拥有的一切：

@%ORACLE_HOME\RDDMS\ADMIN\scott.sql;

修改列属性：col 列名 for

col  hiredata for a15;

col ename for a8

获得iSQL*Plus服务的HTTP端口号：

打开%ORACLE_HOME%\install\portlist.ini 文件。

忘记system/sys的密码： 

删掉%ORACLE_HOME%/database/pwdtestdb.ora; 
使用命令：orapwd file=d:/pwdtestdb.ora password=admin1 entries=10;将密码设置为admin1； 
或则： 
sqlplus /@testdb as sysdba 
alter user sys identified by 新密码; 
alter user system identified by 新密码;

脚本启动服务：

如新建start_services.bat文件，并写入：

net start OracleServiceORCL
net start OracleDBConsoleorcl
net start OracleOraDb10g_home1iSQL*Plus
net start OracleOraDb10g_home1TNSListener

保存后，右键该文件以管理员身份运行。

 net start OracleOraDb10g_home1TNSListener   和 lsnrctl start 一样。

 net start OracleOraDb10g_home1iSQL*Plus 和 isqlplusctl start 一样。

 

查询语句：

SELECT *|{[DISTINCT] 列名…}

FROM 表名

[WHERE 条件]

[ORDER BY {列名|别名|表达式,…}[ASC|DESC]];

语句：一条完整的SQL命令

子句：关键字和后面的选项

条件：

列名、字符串、常量、算术表达式和比较运算符。

字符串和日期用单引号括起来

算术运算符：

+、-、*、/

比较运算符：

>、>=、<、<=、=、!=或<>、AND、IN、LIKE

另外，还有NOT

BETWEEN AND包含上限和下限

通配符（wildcard):

%:代表0个或多个字符

_:代表一个且只能是一个字符

SELECT * FROM emp WHERE job LIKE 'S_\_L%' escape '\';

转义操作符 escape '\'

order by 一定是SQL语句的最后一个子句

 

SQL*PLUS命令：

@	COPY	PAUSE	SHUTDOWN
@@	DEFINE	PRINT	SPOOL
/	DEL	PROMPT	SQLPLUS
ACCEPT	DESCRIBE	QUIT	START
APPEND	DISCONNECT	RECOVER	STARTUP
ARCHIVE LOG	EDIT	REMARK	STORE
ATTRIBUTE	EXECUTE	REPFOOTER	TIMING
BREAK	EXIT	REPHEADER	TTITLE
BTITLE	GET	RESERVED WORDS (SQL)	UNDEFINE
CHANGE	HELP	RESERVED WORDS (PL/SQL)	VARIABLE
CLEAR	HOST	RUN	WHENEVER OSERROR
COLUMN	INPUT	SAVE	WHENEVER SQLERROR
COMPUTE	LIST	SET
CONNECT	PASSWORD	SHOW

 

@ ("at" sign)
-------------
Runs the SQL*Plus statements in the specified script. The script can
called from the local file system or a web server. Only the url form
supported in iSQL*Plus.

@ {url|file_name[.ext]} [arg ...]

where url supports HTTP and FTP protocols in the form:

   http://host.domain/script.sql

@@ (double "at" sign)
---------------------

Runs the specified script. This command is almost identical to
the @ command. It is useful for running nested scripts because it
has the additional functionality of looking for the nested script
in the same url or path as the calling script. Only the url form
is supported in iSQL*Plus.

@@ {url|file_name[.ext]} [arg ...]

 

/ (slash)
---------

Executes the most recently executed SQL command or PL/SQL block
which is stored in the SQL buffer. Use slash (/) at the command
prompt or line number prompt in SQL*Plus command line, or use
slash (/) in the iSQL*Plus Workspace. The buffer has no command
history and does not record SQL*Plus commands.

/

 ACCEPT
------

Reads a line of input and stores it in a given substitution variable.
In iSQL*Plus, displays the Input Required screen for you to enter a
value for the substitution variable.

ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE]
[FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]

 

APPEND
------

Adds text to the end of the current line in the SQL buffer.

A[PPEND] text

Not available in iSQL*Plus

  

ARCHIVE LOG
 -----------

 Starts or stops automatic archiving of online redo log files,
 manually (explicitly) archives specified redo log files, or
 displays information about redo log files.

 ARCHIVE LOG {LIST|STOP} | {START|NEXT|ALL|integer} [TO destination]

 

 BREAK
 -----

 Specifies where changes occur in a report and the formatting
 action to perform, such as:
 - suppressing display of duplicate values for a given column
 - skipping a line each time a given column value changes
   (In iSQL*Plus, only when Preformatted Output is ON)
 - printing computed figures each time a given column value
   changes or at the end of the report.
 Enter BREAK with no clauses to list the current BREAK definition.

 BRE[AK] [ON report_element [action [action]]] ...

 where report_element has the following syntax:
     {column | expression | ROW | REPORT}

 and where action has the following syntax:
     [SKI[P] n | [SKI[P]] PAGE] [NODUP[LICATES] | DUP[LICATES]]

 The SKIP option is not supported in iSQL*Plus

 
 BTITLE
 ------

 Places and formats a specified title at the bottom of each report
 page, or lists the current BTITLE definition.

 BTI[TLE] [printspec [text|variable] ...] | [OFF|ON]

 where printspec represents one or more of the following clauses:

     COL n          LE[FT]        BOLD
     S[KIP] [n]     CE[NTER]      FORMAT text
     TAB n          R[IGHT]

 

 CHANGE
 ------

 Changes the first occurrence of the specified text on the current
 line of the SQL buffer. The buffer has no command history list and
 does not record SQL*Plus commands.

 C[HANGE] sepchar old [sepchar [new[sepchar]]]

 Not available in iSQL*Plus

CLEAR
-----

Resets or erases the current value or setting for the specified option.

CL[EAR] option ...

where option represents one of the following clauses:
    BRE[AKS]
    BUFF[ER]
    COL[UMNS]
    COMP[UTES]
    SCR[EEN]
    SQL
    TIMI[NG]

CLEAR SCREEN is not available in iSQL*Plus

COLUMN
------

Specifies display attributes for a given column, such as:
    - text for the column heading
    - alignment for the column heading
    - format for NUMBER data
    - wrapping of column data
Also lists the current display attributes for a single column
or all columns.

COL[UMN] [{column | expr} [option ...] ]

where option represents one of the following clauses:
    ALI[AS] alias
    CLE[AR]
    ENTMAP {ON|OFF}
    FOLD_A[FTER]
    FOLD_B[EFORE]
    FOR[MAT] format
    HEA[DING] text
    JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
    LIKE {expr | alias}
    NEWL[INE]
    NEW_V[ALUE] variable
    NOPRI[NT] | PRI[NT]
    NUL[L] text
    OLD_V[ALUE] variable
    ON|OFF
    WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]

COMPUTE
-------

In combination with the BREAK command, calculates and prints
summary lines using various standard computations. Also lists
all COMPUTE definitions.

COMP[UTE] [function [LAB[EL] text] ...
  OF {expr|column|alias} ...
  ON {expr|column|alias|REPORT|ROW} ...]

CONNECT
-------

Connects a given username to the Oracle Database. When you run a
CONNECT command, the site profile, glogin.sql, and the user profile,
login.sql, are processed in that order. CONNECT does not reprompt
for username or password if the initial connection does not succeed.

CONN[ECT] [{logon|/|proxy} [AS {SYSOPER|SYSDBA}]]

where logon has the following syntax:
    username[/password][@connect_identifier]

DEFINE
------

Specifies a substitution variable and assigns a CHAR value to it, or
lists the value and variable type of a single variable or all variables.

DEF[INE] [variable] | [variable = text]

DEL
---

Deletes one or more lines of the SQL buffer. The buffer has no
command history list and does not record SQL*Plus commands.

DEL [n | n m | n * | n LAST | * | * n | * LAST | LAST]

Not available in iSQL*Plus

DESCRIBE
--------

Lists the column definitions for a table, view, or synonym,
or the specifications for a function or procedure.

DESC[RIBE] {[schema.]object[@connect_identifier]}

Commits pending changes to the database and logs the current
user out of Oracle, but does not exit SQL*Plus.
In SQL*Plus command line, use EXIT or QUIT to log out of Oracle
and return control to your computer's operating system.
In iSQL*Plus, click the Logout button to log out of Oracle.

DISC[ONNECT]

EDIT
----

Invokes an operating system text editor on the contents of the
specified file or on the contents of the SQL buffer. The buffer
has no command history list and does not record SQL*Plus commands.

ED[IT] [file_name[.ext]]

Not available in iSQL*Plus

QL> help execute

EXECUTE
-------

Executes a single PL/SQL statement or runs a stored procedure.

EXEC[UTE] statement

 GET
 ---

 Loads a SQL statement or PL/SQL block from a script into the SQL buffer.
 In iSQL*Plus click the Load Script button to load a script into the
 Workspace. The buffer has no command history list and does not record
 SQL*Plus commands.

 GET [FILE] file_name[.ext] [LIST | NOLIST]

 Not available in iSQL*Plus

 HELP
 ----

 Accesses this command line help system. Enter HELP INDEX or ? INDEX
 for a list of topics. In iSQL*Plus, click the Help button to display
 iSQL*Plus online help.
 You can view SQL*Plus resources at http://otn.oracle.com/tech/sql_plus/
 and the Oracle Database Library at http://otn.oracle.com/documentation/

 HELP|? [topic]

Executes an operating system command without leaving SQL*Plus.
Enter HOST without command to display an operating system prompt.
You can then enter multiple operating system commands.

HO[ST] [command]

Not available in iSQL*Plus

INPUT
-----

Adds one or more new lines of text after the current line in the
SQL buffer. The buffer has no command history list and does not
record SQL*Plus commands.

I[NPUT] [text]

Not available in iSQL*Plus

LIST
----

Lists one or more lines of the most recently executed SQL command
or PL/SQL block which is stored in the SQL buffer. Enter LIST with
no clauses to list all lines. In SQL*Plus command-line you can also
use ";" to list all the lines in the SQL buffer. The buffer has no
command history list and does not record SQL*Plus commands.

L[IST] [n | n m | n  * | n LAST | * | * n | * LAST | LAST]

PASSWORD
--------

Allows you to change a password without displaying it on an input device.
In iSQL*Plus, use the Password screen to change your password.

PASSW[ORD] [username]

Not available in iSQL*Plus

PAUSE
-----

Displays the specified text then waits for the user to press RETURN.
In iSQL*Plus, displays the Next Page button which the user must click
to continue.

PAU[SE] [text]

PROMPT
------

Sends the specified message or a blank line to the user's screen.

PRO[MPT] [text]

QUIT (Identical to EXIT)
----

Commits or rolls back all pending changes, logs out of Oracle,
terminates SQL*Plus and returns control to the operating system.
In iSQL*Plus, commits or rolls back all pending changes, stops
processing the current iSQL*Plus script and returns focus to the
Workspace. There is no way to access the return code in iSQL*Plus.
In iSQL*Plus click the Logout button to log out of Oracle Database.

{QUIT|EXIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
  [COMMIT|ROLLBACK]

RECOVER
-------

Performs media recovery on one or more tablespaces, one or more
datafiles, or the entire database.

Because of possible network timeouts, it is recommended that you
use SQL*Plus command-line, not iSQL*Plus, for long running DBA
operations such as RECOVER.

RECOVER {general | managed} | BEGIN BACKUP | END BACKUP}

where the general clause has the following syntax:
  [AUTOMATIC] [FROM location]
  { {full_database_recovery | partial_database_recovery |LOGFILE filename}
  [ {TEST | ALLOW integer CORRUPTION | parallel_clause}
  [TEST | ALLOW integer CORRUPTION | parallel_clause] ...]
  |CONTINUE [DEFAULT]|CANCEL}

  where the full_database_recovery clause has the following syntax:
    [STANDBY] DATABASE
    [{ UNTIL {CANCEL | TIME date | CHANGE integer}
     | USING BACKUP CONTROLFILE}
     [ UNTIL {CANCEL | TIME date | CHANGE integer}
     | USING BACKUP CONTROLFILE] ...]

  where the partial_database_recovery clause has the following syntax:
    {TABLESPACE tablespace [, tablespace] ...
     | DATAFILE filename | filenumber} [,filename | filenumber] ...
     | STANDBY
      {TABLESPACE tablespace [, tablespace] ...
      | DATAFILE filename | filenumber} [,filename | filenumber] ...}
    UNTIL [CONSISTENT WITH] CONTROLFILE }

where the parallel clause has the following syntax:
  {NOPARALLEL | PARALLEL [integer]}

where the managed clause has the following syntax:
  MANAGED STANDBY DATABASE recover_clause | cancel_clause | finish_clause

where the recover_clause has the following syntax:
  {{DISCONNECT [FROM SESSION] | {TIMEOUT integer | NOTIMEOUT} }
   |{NODELAY | DEFAULT DELAY | DELAY integer} | NEXT integer
   |{EXPIRE integer | NO EXPIRE} | parallel_clause
   | USING CURRENT LOGFILE | UNTIL CHANGE integer
   | THROUGH {[THREAD integer] SEQUENCE integer
             | ALL ARCHIVELOG  | {ALL | LAST | NEXT } SWITCHOVER} }
     [DISCONNECT [FROM SESSION ]  | {TIMEOUT integer | NOTIMEOUT}
      | {NODELAY | DEFAULT DELAY | DELAY integer} | NEXT integer
      | {EXPIRE integer | NO EXPIRE} | parallel_clause
      | USING CURRENT LOGFILE | UNTIL CHANGE integer
      | THROUGH {[THREAD integer] SEQUENCE integer
                | ALL ARCHIVELOG  | {ALL | LAST | NEXT } SWITCHOVER} ]...

where the cancel_clause has the following syntax:
  CANCEL [IMMEDIATE] [WAIT | NOWAIT]

where the finish_clause has the following syntax:
  [DISCONNECT [FROM SESSION]] [parallel_clause]
  FINISH [SKIP [STANDBY LOGFILE]] [WAIT | NOWAIT]

where the parallel_clause has the following syntax:
  {NOPARALLEL | PARALLEL [integer] }

REMARK
------

Begins a comment in a script. SQL*Plus does not interpret the comment
as a command.

 REPFOOTER
 ---------

 Places and formats a footer at the bottom of a report, or lists the
 REPFOOTER definition.

 REPF[OOTER] [PAGE] [printspec [text|variable] ...] | [OFF|ON]

 where printspec represents one or more of the following clauses:
     COL n          LE[FT]        BOLD
     S[KIP] [n]     CE[NTER]      FORMAT text
     TAB n          R[IGHT]

REPHEADER
---------

Places and formats a header at the top of a report, or lists the
REPHEADER definition.

REPH[EADER] [PAGE] [printspec [text|variable] ...] | [OFF|ON]

where printspec represents one or more of the following clauses:
    COL n          LE[FT]        BOLD
    S[KIP] [n]     CE[NTER]      FORMAT text
    TAB n          R[IGHT]

RESERVED WORDS (SQL)
--------------------

SQL Reserved Words have special meaning in SQL, and may not be used for
identifier names unless enclosed in "quotes".

An asterisk (*) indicates words are also ANSI Reserved Words.

Oracle prefixes implicitly generated schema object and subobject names
with "SYS_". To avoid name resolution conflict, Oracle discourages you
from prefixing your schema object and subobject names with "SYS_".

RUN
---

Lists and executes the most recently executed SQL command or
PL/SQL block which is stored in the SQL buffer. The buffer has
no command history list and does not record SQL*Plus commands.

R[UN]

SAVE
----

Saves the contents of the SQL buffer in a script. In iSQL*Plus, click
the Save Script button to save the Workspace contents to a script. The
buffer has no command history list and does not record SQL*Plus commands.

SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]

Not available in iSQL*Plus

SHOW
----

Shows the value of a SQL*Plus system variable, or the current
SQL*Plus environment. SHOW SGA requires a DBA privileged login.

SHO[W] option

where option represents one of the following terms or clauses:
    system_variable
    ALL
    BTI[TLE]
    ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
       | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
    LNO
    PARAMETERS [parameter_name]
    PNO
    RECYC[LEBIN] [original_name]
    REL[EASE]
    REPF[OOTER]
    REPH[EADER]
    SGA
    SPOO[L]
    SQLCODE
    TT[ITLE]
    USER

SHOW SPOOL is not available in iSQL*Plus

SHUTDOWN
--------

Shuts down a currently running Oracle Database instance, optionally
closing and dismounting a database.

SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]

 SPOOL
 -----

 Stores query results in a file, or optionally sends the file to a printer.
 In iSQL*Plus, use the Preferences screen to direct output to a file.

 SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

 Not available in iSQL*Plus

SQLPLUS
-------

Starts SQL*Plus from the operating system prompt.

SQLPLUS [ [option] [logon] [start] ]

where option has the following syntax:
    -H[ELP]
    | -V[ERSION]
    | [[-C[OMPATIBILITY] x.y[.z]] [-L[OGON]]
       [-M[ARKUP] markup_option] [-R[ESTRICT] {1|2|3}] [-S[ILENT]]]

and where markup_option has the following syntax:
    HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
         [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]

and where logon has the following syntax:
    {username[/password][@connect_identifier] | /}
    [AS {SYSOPER|SYSDBA}] | /NOLOG

and where start has the following syntax:
    @{url|filename[.ext]} [arg ...]

 

 

 START
 -----

 Runs the SQL*Plus statements in the specified script. The script can be
 called from the local file system or a web server. Only the url form is
 supported in iSQL*Plus.

 STA[RT] {url|file_name[.ext]} [arg ...]

 where url supports HTTP and FTP protocols in the form:

    http://host.domain/script.sql

 STARTUP
 -------

 Starts an Oracle instance with several options, including mounting,
 and opening a database.

 STARTUP options | upgrade_options

 where options has the following syntax:
    [FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
    [ OPEN [open_options] [dbname] ] |
    NOMOUNT ]

 where open_options has the following syntax:
    READ {ONLY | WRITE [RECOVER]} | RECOVER

 and where upgrade_options has the following syntax:
    [PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]

STORE
-----

Saves attributes of the current SQL*Plus environment in a script.

STORE {SET} file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]

Not available in iSQL*Plus

TTITLE
------

Places and formats a title at the top of each report page.
Enter TTITLE with no clause to list its current definition.

The old form of TTITLE is used if only a single word or
a string in quotes follows the TTITLE command.

TTI[TLE] [printspec [text|variable] ...] | [OFF|ON]

where printspec represents one or more of the following clauses:

    COL n          LE[FT]        BOLD
    S[KIP] [n]     CE[NTER]      FORMAT text
    TAB n          R[IGHT]

 UNDEFINE
 --------

 Deletes one or more substitution variables that you defined either
 explicitly (with the DEFINE command), or implicitly (with a START
 command argument).

 UNDEF[INE] variable ...

VARIABLE
--------

Declares a bind variable that can be referenced in PL/SQL, or
lists the current display characteristics for a single variable
or all variables.

AR[IABLE] [variable [type]]

where type represents one of the following:

    NUMBER         CHAR          CHAR (n [CHAR|BYTE])
    NCHAR          NCHAR (n)     VARCHAR2 (n [CHAR|BYTE])
    NVARCHAR2 (n)  CLOB          NCLOB
    REFCURSOR      BINARY_FLOAT  BINARY_DOUBLE

 WHENEVER OSERROR
 ----------------

 Performs the specified action (exits SQL*Plus by default) if an
 operating system error occurs (such as a file writing error).
 In iSQL*Plus, performs the specified action (stops the current
 script by default) and returns focus to the Workspace if an
 operating system error occurs.

 WHENEVER OSERROR {EXIT [SUCCESS|FAILURE|n|variable|:BindVariable]
                   [COMMIT|ROLLBACK] | CONTINUE [COMMIT|ROLLBACK|NONE]}

 WHENEVER SQLERROR
 -----------------

 Performs the specified action (exits SQL*Plus by default) if a
 SQL command or PL/SQL block generates an error.
 In iSQL*Plus, performs the specified action (stops the current
 script by default) and returns focus to the Workspace if a SQL
 command or PL/SQL block generates an error.

 WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
                    [COMMIT|ROLLBACK] | CONTINUE [COMMIT|ROLLBACK|NONE]}