美文网首页
Oracle Notes

Oracle Notes

作者: 传棋Jaking | 来源:发表于2018-06-05 12:00 被阅读0次

    1.Oracle匹配

    #匹配第一个字母带'A'的ENAME
SQL> show user;
USER is "SCOTT"
SQL> select * from emp where ename like 'A%';

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20
#匹配第二个字母带'O'的ENAME
SQL> select * from emp where ename like '_O%';

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20
#匹配第三个字母带'N'的ENAME
SQL> select * from emp where ename like '__N%';

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
#匹配第四个字母带'G'的ENAME
SQL> select * from emp where ename like '___G%';

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT        17-NOV-81       5000            10

    2.Oracle转义

    SQL> update emp set ename='X_SCOTT' where ename='SCOTT';

1 row updated.
#匹配第二个字符带'_'的ENAME，结果和执行select * from emp;是一样的，匹配不到'_'，需要对'_'进行转义。
SQL> select * from emp where ename like '__%';

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7788 X_SCOTT    ANALYST         7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10

14 rows selected.

SQL> select * from emp;            

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7788 X_SCOTT    ANALYST         7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10

14 rows selected.
#定义'\'为转义符号
SQL> select * from emp where ename like '_\_%' escape '\';

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 X_SCOTT    ANALYST         7566 19-APR-87       3000            20
#定义'空格'为转义符号
SQL> select * from emp where ename like '_ _%' escape ' ';

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 X_SCOTT    ANALYST         7566 19-APR-87       3000            20
#定义'a'为转义符号
SQL> select * from emp where ename like '_a_%' escape 'a';

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 X_SCOTT    ANALYST         7566 19-APR-87       3000            20

SQL>

    3.设置Oracle开机启动

    [oracle@localhost Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 5 23:39:06 2018

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

Connected to an idle instance.

SQL> select status from v$instance
  2  ;
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
#Oracle默认是不启动的，此时查询Oracle状态会报错。

SQL> Disconnected
[oracle@localhost Desktop]$ su -
Password: 
[root@localhost ~]# vim /etc/oratab 
[root@localhost ~]# cat /etc/oratab
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
orcl:/oracle/app/oracle/product/11.2.0/db_1:Y
[root@localhost ~]# vim /etc/rc.local
[root@localhost ~]# cat /etc/rc.local
#!/bin/sh
touch /var/lock/subsys/local
su - oracle '-c dbstart $ORACLE_HOME'
[root@localhost ~]#

    重启操作系统后测试：

    [oracle@localhost Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 5 23:50:19 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options



SQL> select status from v$instance;

STATUS
------------
OPEN
#Oracle已处于启动状态
SQL>

    4.如何判断Oracle是否启动？

    常规方法：

    [oracle@localhost ~]$ netstat -anpl | grep 1521
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 :::1521                     :::*                        LISTEN      2768/tnslsnr        
tcp        0      0 ::1:12265                   ::1:1521                    ESTABLISHED 15679/ora_pmon_orcl 
tcp        0      0 ::1:1521                    ::1:12265                   ESTABLISHED 2768/tnslsnr        
#查看Oracle默认端口状态，看到ESTABLISHED说明Oracle已经启动。
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 6 03:55:49 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;
#直接登录进来用命令查看Oracle状态，看到OPEN说明Oracle已经启动。
STATUS
------------
OPEN

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ netstat -anpl | grep 1521
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 :::1521                     :::*                        LISTEN      2768/tnslsnr        
tcp        0      0 ::1:12265                   ::1:1521                    TIME_WAIT   -

    利用进程查看，比较繁琐，不推荐：

    [oracle@localhost ~]$ ps -ef | grep oracle
oracle     2768      1  0 Jun05 ?        00:00:00 /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle     3299      1  0 Jun05 ?        00:00:00 /usr/bin/gnome-keyring-daemon --daemonize --login
oracle     3309   3266  0 Jun05 ?        00:00:00 gnome-session
oracle     3317      1  0 Jun05 ?        00:00:00 dbus-launch --sh-syntax --exit-with-session
oracle     3318      1  0 Jun05 ?        00:00:00 /bin/dbus-daemon --fork --print-pid 5 --print-address 7 --session
oracle     3392      1  0 Jun05 ?        00:00:00 /usr/libexec/gconfd-2
oracle     3399      1  0 Jun05 ?        00:00:01 /usr/libexec/gnome-settings-daemon
oracle     3404      1  0 Jun05 ?        00:00:00 seahorse-daemon
oracle     3407      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfsd
oracle     3413   3309  0 Jun05 ?        00:00:01 metacity
oracle     3417      1  0 Jun05 ?        00:00:01 /usr/bin/pulseaudio --start --log-target=syslog
oracle     3418   3309  0 Jun05 ?        00:00:00 gnome-panel
oracle     3432   3417  0 Jun05 ?        00:00:00 /usr/libexec/pulse/gconf-helper
oracle     3433   3309  0 Jun05 ?        00:00:01 nautilus
oracle     3435      1  0 Jun05 ?        00:00:00 /usr/libexec/bonobo-activation-server --ac-activate --ior-output-fd=18
oracle     3442      1  0 Jun05 ?        00:00:00 /usr/libexec/wnck-applet --oaf-activate-iid=OAFIID:GNOME_Wncklet_Factory --oaf-ior-fd=18
oracle     3443      1  0 Jun05 ?        00:00:00 /usr/libexec/trashapplet --oaf-activate-iid=OAFIID:GNOME_Panel_TrashApplet_Factory --oaf-ior-fd=24
oracle     3445      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfs-gdu-volume-monitor
oracle     3451      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfsd-trash --spawner :1.7 /org/gtk/gvfs/exec_spaw/0
oracle     3457   3309  0 Jun05 ?        00:00:00 python /usr/share/system-config-printer/applet.py
oracle     3458      1  0 Jun05 ?        00:00:11 /usr/lib/vmware-tools/sbin64/vmtoolsd -n vmusr --blockFd 3
oracle     3474   3309  0 Jun05 ?        00:00:00 gnome-power-manager
oracle     3478   3309  0 Jun05 ?        00:00:00 abrt-applet
oracle     3486   3309  0 Jun05 ?        00:00:00 /usr/libexec/gdu-notification-daemon
oracle     3492      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfs-afc-volume-monitor
oracle     3503   3309  0 Jun05 ?        00:00:00 gnome-volume-control-applet
oracle     3505   3309  0 Jun05 ?        00:00:00 rhsm-icon
oracle     3507   3309  0 Jun05 ?        00:00:00 nm-applet --sm-disable
oracle     3509      1  0 Jun05 ?        00:00:00 /usr/libexec/im-settings-daemon
oracle     3510   3309  0 Jun05 ?        00:00:00 /usr/sbin/restorecond -u
oracle     3512   3309  0 Jun05 ?        00:00:00 bluetooth-applet
oracle     3513   3309  0 Jun05 ?        00:00:00 /usr/libexec/polkit-gnome-authentication-agent-1
oracle     3514   3309  0 Jun05 ?        00:00:00 gpk-update-icon
oracle     3529      1  0 Jun05 ?        00:00:01 gnome-screensaver
oracle     3536      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfs-gphoto2-volume-monitor
oracle     3674      1  0 Jun05 ?        00:00:00 /usr/libexec/gconf-im-settings-daemon
oracle     3681      1  0 Jun05 ?        00:00:00 /usr/bin/gnote --panel-applet --oaf-activate-iid=OAFIID:GnoteApplet_Factory --oaf-ior-fd=19
oracle     3683      1  0 Jun05 ?        00:00:00 /usr/libexec/notification-area-applet --oaf-activate-iid=OAFIID:GNOME_NotificationAreaApplet_Factory --oaf-ior-fd=28
oracle     3686      1  0 Jun05 ?        00:00:00 /usr/libexec/clock-applet --oaf-activate-iid=OAFIID:GNOME_ClockApplet_Factory --oaf-ior-fd=34
oracle     3687      1  0 Jun05 ?        00:00:00 /usr/libexec/gdm-user-switch-applet --oaf-activate-iid=OAFIID:GNOME_FastUserSwitchApplet_Factory --oaf-ior-fd=40
oracle     3702      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfsd-burn --spawner :1.7 /org/gtk/gvfs/exec_spaw/1
oracle     3713      1  0 Jun05 ?        00:00:17 /usr/bin/gnome-terminal -x /bin/sh -c cd '/home/oracle/Desktop' && exec $SHELL
oracle     3714   3713  0 Jun05 ?        00:00:00 gnome-pty-helper
oracle     3715   3713  0 Jun05 pts/0    00:00:00 /bin/bash
oracle     3968      1  0 Jun05 ?        00:00:00 /usr/libexec/notification-daemon
oracle    17272   3715  0 04:01 pts/0    00:00:00 ps -ef
oracle    17273   3715  0 04:01 pts/0    00:00:00 grep oracle
#查不到Oracle进程，说明Oracle没有启动。
[oracle@localhost ~]$ ps -ef | grep pmon
oracle    17276   3715  0 04:01 pts/0    00:00:00 grep pmon
#查不到Oracle进程，说明Oracle没有启动。
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 6 04:01:37 2018

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size          2228784 bytes
Variable Size         939527632 bytes
Database Buffers      654311424 bytes
Redo Buffers            7344128 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ ps -ef | grep pmon
oracle    17328      1  0 04:01 ?        00:00:00 ora_pmon_orcl_new
oracle    17425   3715  0 04:01 pts/0    00:00:00 grep pmon
#查到Oracle进程，说明Oracle已经启动。
[oracle@localhost ~]$ ps -ef | grep oracle
oracle     2768      1  0 Jun05 ?        00:00:00 /oracle/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle     3299      1  0 Jun05 ?        00:00:00 /usr/bin/gnome-keyring-daemon --daemonize --login
oracle     3309   3266  0 Jun05 ?        00:00:00 gnome-session
oracle     3317      1  0 Jun05 ?        00:00:00 dbus-launch --sh-syntax --exit-with-session
oracle     3318      1  0 Jun05 ?        00:00:00 /bin/dbus-daemon --fork --print-pid 5 --print-address 7 --session
oracle     3392      1  0 Jun05 ?        00:00:00 /usr/libexec/gconfd-2
oracle     3399      1  0 Jun05 ?        00:00:01 /usr/libexec/gnome-settings-daemon
oracle     3404      1  0 Jun05 ?        00:00:00 seahorse-daemon
oracle     3407      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfsd
oracle     3413   3309  0 Jun05 ?        00:00:01 metacity
oracle     3417      1  0 Jun05 ?        00:00:01 /usr/bin/pulseaudio --start --log-target=syslog
oracle     3418   3309  0 Jun05 ?        00:00:00 gnome-panel
oracle     3432   3417  0 Jun05 ?        00:00:00 /usr/libexec/pulse/gconf-helper
oracle     3433   3309  0 Jun05 ?        00:00:01 nautilus
oracle     3435      1  0 Jun05 ?        00:00:00 /usr/libexec/bonobo-activation-server --ac-activate --ior-output-fd=18
oracle     3442      1  0 Jun05 ?        00:00:00 /usr/libexec/wnck-applet --oaf-activate-iid=OAFIID:GNOME_Wncklet_Factory --oaf-ior-fd=18
oracle     3443      1  0 Jun05 ?        00:00:00 /usr/libexec/trashapplet --oaf-activate-iid=OAFIID:GNOME_Panel_TrashApplet_Factory --oaf-ior-fd=24
oracle     3445      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfs-gdu-volume-monitor
oracle     3451      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfsd-trash --spawner :1.7 /org/gtk/gvfs/exec_spaw/0
oracle     3457   3309  0 Jun05 ?        00:00:00 python /usr/share/system-config-printer/applet.py
oracle     3458      1  0 Jun05 ?        00:00:11 /usr/lib/vmware-tools/sbin64/vmtoolsd -n vmusr --blockFd 3
oracle     3474   3309  0 Jun05 ?        00:00:00 gnome-power-manager
oracle     3478   3309  0 Jun05 ?        00:00:00 abrt-applet
oracle     3486   3309  0 Jun05 ?        00:00:00 /usr/libexec/gdu-notification-daemon
oracle     3492      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfs-afc-volume-monitor
oracle     3503   3309  0 Jun05 ?        00:00:00 gnome-volume-control-applet
oracle     3505   3309  0 Jun05 ?        00:00:00 rhsm-icon
oracle     3507   3309  0 Jun05 ?        00:00:00 nm-applet --sm-disable
oracle     3509      1  0 Jun05 ?        00:00:00 /usr/libexec/im-settings-daemon
oracle     3510   3309  0 Jun05 ?        00:00:00 /usr/sbin/restorecond -u
oracle     3512   3309  0 Jun05 ?        00:00:00 bluetooth-applet
oracle     3513   3309  0 Jun05 ?        00:00:00 /usr/libexec/polkit-gnome-authentication-agent-1
oracle     3514   3309  0 Jun05 ?        00:00:00 gpk-update-icon
oracle     3529      1  0 Jun05 ?        00:00:01 gnome-screensaver
oracle     3536      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfs-gphoto2-volume-monitor
oracle     3674      1  0 Jun05 ?        00:00:00 /usr/libexec/gconf-im-settings-daemon
oracle     3681      1  0 Jun05 ?        00:00:00 /usr/bin/gnote --panel-applet --oaf-activate-iid=OAFIID:GnoteApplet_Factory --oaf-ior-fd=19
oracle     3683      1  0 Jun05 ?        00:00:00 /usr/libexec/notification-area-applet --oaf-activate-iid=OAFIID:GNOME_NotificationAreaApplet_Factory --oaf-ior-fd=28
oracle     3686      1  0 Jun05 ?        00:00:00 /usr/libexec/clock-applet --oaf-activate-iid=OAFIID:GNOME_ClockApplet_Factory --oaf-ior-fd=34
oracle     3687      1  0 Jun05 ?        00:00:00 /usr/libexec/gdm-user-switch-applet --oaf-activate-iid=OAFIID:GNOME_FastUserSwitchApplet_Factory --oaf-ior-fd=40
oracle     3702      1  0 Jun05 ?        00:00:00 /usr/libexec/gvfsd-burn --spawner :1.7 /org/gtk/gvfs/exec_spaw/1
oracle     3713      1  0 Jun05 ?        00:00:18 /usr/bin/gnome-terminal -x /bin/sh -c cd '/home/oracle/Desktop' && exec $SHELL
oracle     3714   3713  0 Jun05 ?        00:00:00 gnome-pty-helper
oracle     3715   3713  0 Jun05 pts/0    00:00:00 /bin/bash
oracle     3968      1  0 Jun05 ?        00:00:00 /usr/libexec/notification-daemon
oracle    17328      1  0 04:01 ?        00:00:00 ora_pmon_orcl_new
oracle    17330      1  0 04:01 ?        00:00:00 ora_psp0_orcl_new
oracle    17332      1  1 04:01 ?        00:00:00 ora_vktm_orcl_new
oracle    17336      1  0 04:01 ?        00:00:00 ora_gen0_orcl_new
oracle    17338      1  0 04:01 ?        00:00:00 ora_diag_orcl_new
oracle    17340      1  0 04:01 ?        00:00:00 ora_dbrm_orcl_new
oracle    17342      1  0 04:01 ?        00:00:00 ora_dia0_orcl_new
oracle    17344      1  1 04:01 ?        00:00:00 ora_mman_orcl_new
oracle    17346      1  0 04:01 ?        00:00:00 ora_dbw0_orcl_new
oracle    17348      1  0 04:01 ?        00:00:00 ora_lgwr_orcl_new
oracle    17350      1  0 04:01 ?        00:00:00 ora_ckpt_orcl_new
oracle    17352      1  0 04:01 ?        00:00:00 ora_smon_orcl_new
oracle    17354      1  0 04:01 ?        00:00:00 ora_reco_orcl_new
oracle    17356      1  0 04:01 ?        00:00:00 ora_mmon_orcl_new
oracle    17358      1  0 04:01 ?        00:00:00 ora_mmnl_orcl_new
oracle    17360      1  0 04:01 ?        00:00:00 ora_d000_orcl_new
oracle    17362      1  0 04:01 ?        00:00:00 ora_s000_orcl_new
oracle    17401      1  0 04:01 ?        00:00:00 ora_qmnc_orcl_new
oracle    17417      1  0 04:01 ?        00:00:00 ora_cjq0_orcl_new
oracle    17419      1  0 04:01 ?        00:00:00 ora_j000_orcl_new
oracle    17421      1  0 04:01 ?        00:00:00 ora_j001_orcl_new
oracle    17423      1  0 04:01 ?        00:00:00 ora_j002_orcl_new
oracle    17427      1  0 04:01 ?        00:00:00 ora_q000_orcl_new
oracle    17429      1  0 04:01 ?        00:00:00 ora_q001_orcl_new
oracle    17451   3715  0 04:02 pts/0    00:00:00 ps -ef
oracle    17452   3715  0 04:02 pts/0    00:00:00 grep oracle
#查到Oracle进程，说明Oracle已经启动。
[oracle@localhost ~]$

    5.设置linesize永久生效

    配置：

    [oracle@localhost ~]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@localhost admin]$ ls
glogin.sql  help  libsqlplus.def  plustrce.sql  pupbld.sql
[oracle@localhost admin]$ vim glogin.sql 
[oracle@localhost admin]$ cat glogin.sql 
--
-- Copyright (c) 1988, 2011, Oracle and/or its affiliates. 
-- All rights reserved. 
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set linesize 100
[oracle@localhost admin]$

    测试：

    [oracle@localhost Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 7 02:54:49 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> conn scott/scott
Connected.
SQL> select * from emp;
#执行这条命令时终端宽度一定要足够，否者显示的效果可能不是linesize 100

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10

14 rows selected.

    6.drop表和flashback表

    SQL> select * from tab;

TNAME                  TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                  TABLE
DEPT                   TABLE
EMP                TABLE
SALGRADE               TABLE

SQL> drop table emp;

Table dropped.

SQL> select * from tab;

TNAME                  TABTYPE  CLUSTERID
------------------------------ ------- ----------
#出现这段代码，说明表被放到了回收站。
BIN$bgqB/QJjEJHgVQAAAAAAAQ==$0 TABLE
BONUS                  TABLE
DEPT                   TABLE
SALGRADE               TABLE


SQL> select * from "BIN$bgqB/QJjEJHgVQAAAAAAAQ==$0";

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300     30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500     30
      7566 JONES      MANAGER         7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400     30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500      0     30
      7876 ADAMS      CLERK       7788 23-MAY-87       1100            20

     EMPNO ENAME      JOB          MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK       7698 03-DEC-81        950            30
      7902 FORD       ANALYST         7566 03-DEC-81       3000            20
      7934 MILLER     CLERK       7782 23-JAN-82       1300            10

14 rows selected.


SQL> select * from tab;

TNAME                  TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$bgqB/QJjEJHgVQAAAAAAAQ==$0 TABLE
BONUS                  TABLE
DEPT                   TABLE
SALGRADE               TABLE

SQL> drop table bonus;  

Table dropped.

SQL> select * from tab;

TNAME                  TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$bgqB/QJjEJHgVQAAAAAAAQ==$0 TABLE
BIN$bgqB/QJkEJHgVQAAAAAAAQ==$0 TABLE
DEPT                   TABLE
SALGRADE               TABLE

SQL> flashback table emp to before drop;
#还原emp表
Flashback complete.

SQL> flashback table bonus to before drop;
#还原bonus表
Flashback complete.


SQL> select * from tab;
#emp表和bonus表已还原
TNAME                  TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                  TABLE
DEPT                   TABLE
EMP                TABLE
SALGRADE               TABLE

SQL>

    相关文章

      网友评论

          本文标题：Oracle Notes

          本文链接：https://www.haomeiwen.com/subject/ptypsftx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|Oracle Notes|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！