溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
標題：Linux下面oracle環境的搭建
主題： | Oracle數據庫環境準備 | |||
第一部分：安裝oracle軟件包 | ||||
1.安裝 VMware Tools | 安裝vmware-tools工具 步驟1、點擊---->虛擬機----->安裝Vmware Tools
步驟2、df -h [root@server253 ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 20G 8.3G 11G 45% / /dev/sda1 99M 12M 83M 12% /boot tmpfs 1.5G 0 1.5G 0% /dev/shm /dev/scd0 2.8G 2.8G 0 100% /media/Enterprise Linux dvd 20090908 步驟3、掛在VMwareTools鏡像 mount /dev/scd0 /mnt/ df -h [root@server253 ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 20G 8.3G 11G 45% / /dev/sda1 99M 12M 83M 12% /boot tmpfs 1.5G 0 1.5G 0% /dev/shm /dev/scd0 2.8G 2.8G 0 100% /media/Enterprise Linux dvd 20090908 /dev/scd0 2.8G 2.8G 0 100% /mnt 步驟4、cp /mnt/VMwareTools… /etc/opt/ cd /opt/ ls 步驟5、解壓VMwareTools包 tar -zxvf VMwareTools… cd vmware-tools-distrib ls 步驟6、./vmware-install.pl 一路回車 最后reboot一下 | |||
2.配置ip地址 計算機名 配置主機 防火墻設置
| 1，配置IP地址 vim /etc/sysconfig/network-scripts/ifcfg-eth0
/etc/init.d/network restart chkconfig network on ifconfig eth0
2，配置計算機名字 vim /etc/sysconfig/network
修改臨時主機名 hostname server253.oracle.com
3，配置主機 vim /etc/hosts
ping server253.oracle.com ping server253
4，防火墻設置 system-config-securitylevel
| |||
| 3.配置yum倉 庫 |
設備狀態---->勾選上已連接 df -h mount /dev/scd0 /mnt/ vim /etc/yum.repos.d/server.repo [base] name=rhel5.4 baseurl=file:///mnt/Server enabled=1 gpgcheck=0 | |||
4.檢查軟件的 必要性 | Checking the Software Requirements
[root@server253 ~]rpm -qa|grep compat-db [root@server253 ~]# yum -y install compat-db
[root@server253 ~]# rpm -qa|grep pdksh [root@server253 ~]# yum -y install pdksh
[root@server253 ~]# rpm -qa|grep sysstat [root@server253 ~]# yum -y install sysstat | |||
5.檢查網絡步 驟 | 1. ifconfig eth0
| |||
6.配置名稱解 析 | vim /etc/hosts
ping server253.oracle.com ping server253 | |||
7. 創建安裝軟 件需要賬戶 名和組 8. 判斷nobody 是否存在 | Creating Required Operating System Groups and Users 創建三個用戶 [root@server253 ~]# useradd dba [root@server253 ~]# useradd oinstall [root@server253 ~]# useradd oper
[root@server253 ~]# id oracle uid=500(oracle) gid=500(oracle) groups=500(oracle)
將用戶加入到組 usermod -g oinstall -G oinstall,dba,oper,oracle oracle [root@server253 ~]# id oracle uid=500(oracle) gid=502(oinstall) groups=502(oinstall),500(oracle),501(dba),503(oper) 8,Verifying that the User nobody Exists 判斷nobody是否存在 原因：外部作業必須存在nobody
[root@server253 ~]# id nobody uid=99(nobody) gid=99(nobody) groups=99(nobody) | |||
9. 內核參數 | Configuring Kernel Parameters vi /etc/sysctl.conf
-shmall :該參數表示系統依次可以使用的共享內存段的總容量（以頁為單位）。默認值是2097152，通常不需要修改 -shmmax:該參數定義了單個進程能夠使用的共享內存段的最大尺寸（以字節為單位），默認為32MB，對于ORACLE來說，該默認值太低了，通常將其設置為2GB -shmmin:該內核參數用于設置系統范圍內共享內存段的最大個數，該參數的默認值是4096，通常不需要更改 -sem:該參數表示設置的信號量 -file-max: 該參數表示文件句柄的最大數量，文件句柄設置表示在linux系統中可以打開的文件數量
[root@server253 ~]# sysctl -p | |||
10. 配置SHELL 限制 | 配置SHELL限制 a,vim /etc/security/limits.conf
b,vi /etc/pam.d/login
c,Depending on the oracle user's default shell vi /etc/profile
| |||
11. 創建所需 要的目錄 | 創建所需要的目錄 Identifying Required Software Directories a,Oracle Base Directory--- 根目錄 必須手工創建 mkdir -p /u01/app/oracle
b,Oracle Inventory Directory 名字：oracle_base/oraInventory --產品清單列表 /u01/app/oracle/oraInventory You do not need to create it. 安裝自動創建
c,Oracle Home Directory 軟件所安裝的目錄 每安裝一個產品都會有一相應的oralce home目錄，You do not need to create this directory. 建議你們創建 /u01/app/oracle/product/10.2.0/db_1
d,命令 # mkdir -p /u01/app/oracle # chown -R oracle:oinstall /u01/app/oracle # chmod -R 775 /u01/app/oracle 測試 ll /u01/app | |||
12.配置oracle 用戶環境 | Configuring the oracle User's Environment su - oracle cd /home/oracle vim .bash_profile
[root@server253 ~]# source .bash_profile
| |||
13.準備安裝介 質 | a,如何將安裝介質上傳到linux b,tool c,使用root上傳，使用解壓，查看權限 unzip 包名 chown -R oracle:oinstall /u01 chmod -R 775 /u01 | |||
14. su - oracle | [oracle@server253 ~]$ su - root [root@server253 ~]# xhost + access control disabled, clients can connect from any host
[root@server253 ~]# su - oracle [oracle@server253 ~]$ xclock
| |||
15. 安裝你的數據庫軟件
16.安裝過程要 做的事情
| [oracle@server253 ~]$ cd /u01/database/ [oracle@server253 database]$ ./runInstaller
a,vim /home/oracle/.base_profile
b,不要選默認創建數據庫，這里只是安裝數據庫軟件
c.最后以root身份執行2個腳本： orainstRoot.sh==用來更新最終的產品清單信息 root.sh ==根據當前主機的信息生成一些使用腳本，如dbca
[oracle@server253 ~]$ source .bash_profile [oracle@server253 ~]$ cd /u01/database/ [oracle@server253 database]$ ./runInstaller
[root@localhost /]# /u01/app/oracle/oraInventory/orainstRoot.sh [root@localhost /]# /u01/app/oracle/product/10.2.0/db_1/root.sh
| |||
第二部分：創建偵聽 | ||||
[oracle@server253 ~]$ netca -bash: netca: command not found
查看netca所在的路徑 [oracle@server253 bin]$ ls netca netca [oracle@server253 bin]$ pwd /u01/app/oracle/product/10.2.0/db_1/bin
[oracle@server253 ~]$ vim .bash_profile
[oracle@server253 ~]$ source .bash_profile
[oracle@server253 ~]$ netca
[oracle@server253 ~]$ lsnrctl status [oracle@server253 ~]$ netstat -tunlp |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 0.0.0.0:1521 0.0.0.0:* LISTEN 14633/tnslsnr
| ||||
第三部分：創建數據庫 | ||||
------------創建數據庫-----dbca-- 一是采用腳本的方式 二是采用圖形界面 [oracle@server253 ~]$ dbca
使用DBCA創建第一個數據庫：orcl 全局數據庫名：數據庫名+域名 oracle sid： 實例名 默認實例名和數據庫名一樣，也可以不一樣
enterprise manager (EM) EM采用網頁形式對數據庫進行管理 有2種類型的EM： 一是采用GRID CONTROL-它可以管理多臺主機以及多個數據庫，必須在主機上安裝agent 二是采用Database control 只能管理一個數據庫 數據庫管理方式 1 grid control 默認不可選，原因：你沒有配置grid contol agent 2 dbconsole 管理
一定要選擇此模板
| ||||
查看創建的數據庫信息 [oracle@server253 orcl]$ ls adump bdump cdump dpdump pfile udump [oracle@server253 orcl]$ pwd /u01/app/oracle/admin/orcl
cd admin/實例名/ 審計 跟蹤 警告日志 [oracle@server253 admin]$ cd orcl/bdump/ [oracle@server253 bdump]$ ls alert_orcl.log orcl_lgwr_16683.trc orcl_lgwr_16778.trc orcl_lgwr_16845.trc
cd /u01/app/oracle/oradata/實例名/ 數據庫文件 這里十二個文件要寫腳本 [oracle@server253 orcl]$ ls control01.ctl example01.dbf redo03.log temp01.dbf control02.ctl redo01.log sysaux01.dbf undotbs01.dbf control03.ctl redo02.log system01.dbf users01.dbf [oracle@server253 orcl]$ pwd /u01/app/oracle/oradata/orcl ps -elf |grep ora [oracle@server253 dbs]$ ps -elf|grep ora 0 S root 3548 3523 0 78 0 - 494 stext Aug13 ? 00:00:10 hald-addon-storage: polling /dev/scd0 4 S root 9164 6840 0 77 0 - 1230 wait Aug13 pts/2 00:00:00 su - oracle 4 S oracle 9165 9164 0 76 0 - 1135 wait Aug13 pts/2 00:00:00 -bash 4 S root 9261 9216 0 77 0 - 1230 wait Aug13 pts/2 00:00:00 su - oracle 4 S oracle 9262 9261 0 75 0 - 1135 wait Aug13 pts/2 00:00:00 -bash 4 S root 9893 9753 0 77 0 - 1230 wait Aug13 pts/2 00:00:00 su - oracle 4 S oracle 9894 9893 0 75 0 - 1135 wait Aug13 pts/2 00:00:00 -bash 4 S root 10010 9967 0 77 0 - 1230 wait Aug13 pts/2 00:00:00 su - oracle 4 S oracle 10011 10010 0 75 0 - 1135 wait Aug13 pts/2 00:00:00 -bash 4 S root 14109 4694 0 77 0 - 1230 wait Aug13 pts/1 00:00:00 su - oracle 4 S oracle 14110 14109 0 76 0 - 1135 - Aug13 pts/1 00:00:00 -bash 0 S oracle 14633 1 0 76 0 - 10567 stext Aug13 ? 00:00:00 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit 0 S oracle 16837 1 0 78 0 - 250810 - Aug13 ? 00:00:00 ora_pmon_orcl 0 S oracle 16839 1 0 78 0 - 250657 - Aug13 ? 00:00:00 ora_psp0_orcl 0 S oracle 16841 1 0 78 0 - 250657 - Aug13 ? 00:00:00 ora_mman_orcl 0 S oracle 16843 1 0 78 0 - 251174 - Aug13 ? 00:00:00 ora_dbw0_orcl 0 S oracle 16845 1 0 76 0 - 254545 - Aug13 ? 00:00:01 ora_lgwr_orcl 0 S oracle 16847 1 0 78 0 - 250781 - Aug13 ? 00:00:03 ora_ckpt_orcl 0 S oracle 16849 1 0 77 0 - 251051 - Aug13 ? 00:00:00 ora_smon_orcl 0 S oracle 16851 1 0 80 0 - 250657 - Aug13 ? 00:00:00 ora_reco_orcl 0 S oracle 16853 1 0 75 0 - 251056 - Aug13 ? 00:00:01 ora_cjq0_orcl 0 S oracle 16855 1 0 78 0 - 251353 - Aug13 ? 00:00:00 ora_mmon_orcl 0 S oracle 16857 1 0 78 0 - 250657 - Aug13 ? 00:00:00 ora_mmnl_orcl 0 S oracle 16859 1 0 78 0 - 250817 - Aug13 ? 00:00:00 ora_d000_orcl 0 S oracle 16861 1 0 75 0 - 250809 - Aug13 ? 00:00:00 ora_s000_orcl 0 S oracle 16866 1 0 79 0 - 250657 - Aug13 ? 00:00:00 ora_qmnc_orcl 0 S oracle 17090 1 0 78 0 - 251063 - Aug13 ? 00:00:02 ora_j000_orcl 0 S oracle 17811 1 0 75 0 - 251044 - Aug13 ? 00:00:00 ora_q000_orcl 0 S oracle 17813 1 0 78 0 - 250656 - Aug13 ? 00:00:00 ora_q001_orcl 0 S oracle 17856 1 0 75 0 - 2031 - Aug13 pts/1 00:00:00 /u01/app/oracle/product/10.2.0/db_1/perl/bin/perl /u01/app/oracle/product/10.2.0/db_1/bin/emwd.pl dbconsole /u01/app/oracle/product/10.2.0/db_1/server253.oracle.com_orcl/sysman/log/emdb.nohup 0 S oracle 17881 17856 0 78 0 - 148091 stext Aug13 pts/1 00:00:29 /u01/app/oracle/product/10.2.0/db_1/jdk/bin/java -server -Xmx256M -XX:MaxPermSize=96m -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 -Doracle.home=/u01/app/oracle/product/10.2.0/db_1/oc4j -Doracle.oc4j.localhome=/u01/app/oracle/product/10.2.0/db_1/server253.oracle.com_orcl/sysman -DEMSTATE=/u01/app/oracle/product/10.2.0/db_1/server253.oracle.com_orcl -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_server253.oracle.com_orcl/config/jazn.xml -Djava.security.policy=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_server253.oracle.com_orcl/config/java2.policy -Djava.security.properties=/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/u01/app/oracle/product/10.2.0/db_1/server253.oracle.com_orcl -Dsysman.md5password=true -Drepapi.oracle.home=/u01/app/oracle/product/10.2.0/db_1 -Ddisable.checkForUpdate=true -Djava.awt.headless=true -jar /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/home/oc4j.jar -config /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_server253.oracle.com_orcl/config/server.xml 0 S oracle 18060 1 0 77 0 - 253384 - Aug13 ? 00:00:03 oracleorcl (LOCAL=NO) 0 S oracle 18062 1 0 75 0 - 251086 - Aug13 ? 00:00:01 oracleorcl (LOCAL=NO) 0 S oracle 18064 1 0 75 0 - 251348 - Aug13 ? 00:00:05 oracleorcl (LOCAL=NO) 0 S oracle 19902 17856 0 77 0 - 14604 stext Aug13 pts/1 00:00:03 /u01/app/oracle/product/10.2.0/db_1/bin/emagent 0 S oracle 19945 1 0 76 0 - 251627 - Aug13 ? 00:00:02 oracleorcl (LOCAL=NO) 0 S oracle 19956 1 0 75 0 - 251093 - Aug13 ? 00:00:01 oracleorcl (LOCAL=NO) 0 S oracle 24484 1 0 76 0 - 251073 - Aug13 ? 00:00:00 oracleorcl (LOCAL=NO) 0 S oracle 24953 1 0 79 0 - 251081 - 00:00 ? 00:00:00 oracleorcl (LOCAL=NO) 0 S oracle 24957 1 0 78 0 - 251084 - 00:00 ? 00:00:00 oracleorcl (LOCAL=NO) 0 R oracle 25086 10011 0 77 0 - 1065 - 00:02 pts/2 00:00:00 ps -elf 0 R oracle 25087 10011 0 78 0 - 980 - 00:02 pts/2 00:00:00 grep ora 還多了一個文件夾 [oracle@server253 ~]$ cd $ORACLE_HOME [oracle@server253 db_1]$ ls assistants has log oraInst.loc server253.oracle.com_orcl bin hs md ord slax cdata install mesg oui sqlj cfgtoollogs install.platform mgw owm sqlplus clone inventory network perl srvm config javavm nls plsql sysman crs jdbc oc4j precomp uix css jdk odbc racg wwg ctx jlib olap rdbms xdk dbs jre OPatch relnotes demo ldap opmn root.sh diagnostics lib oracore root.sh.old [oracle@server253 db_1]$ pwd /u01/app/oracle/product/10.2.0/db_1 這里的實例在啟動的時候，第一個讀到的是spfileorcl.ora文件，一旦此文件丟了，你的實例就崩啦，以后排錯，第一個要修復的文件就是這個文件 [oracle@server253 db_1]$ cd dbs [oracle@server253 dbs]$ ls hc_orcl.dat initdw.ora init.ora lkORCL orapworcl spfileorcl.ora [oracle@server253 dbs]$ | ||||
| 如何證明創建數據庫成功 | [oracle@server253 ~]$ sqlplus sys/oracle as sysdba -bash: sqlplus: command not found [oracle@server253 ~]$ vim .bash_profile umask 022 ORACLE_BASE=/u01/app/oracle ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1 ORACLE_SID=orcl PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin export ORACLE_BASE ORACLE_SID ORACLE_HOME PATH [oracle@server253 ~]$ source .bash_profile [oracle@server253 ~]$ sqlplus sys/oracle as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 00:15:00 2017 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> 方法一： SQL> select * from tab; 3643 rows selected. 方法二： SQL> shutdown abort ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [oracle@server253 ~]$ sqlplus sys/oracle as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 00:22:38 2017 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> 啟動過程中有三個過程
如果三個過程都OK，那么數據庫啟動成功 SQL> startup ORACLE instance started. Total System Global Area 926941184 bytes Fixed Size 1222672 bytes Variable Size 243271664 bytes Database Buffers 679477248 bytes Redo Buffers 2969600 bytes Database mounted. Database opened. SQL> | |||
第四部分：oracle登錄模式 | ||||
| 1.數據庫的連 接方式介紹 | oracle登錄模式 連接基本使用
1、EM-企業化管理器（圖形管理） 2、isqlplus--（JAVA） 3、sqlplus | |||
| 2.sys賬號登錄 | sqlplus賬號/密碼 as sysdba [oracle@server253 ~]$ sqlplus sys/oracle as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 06:27:25 2017 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL>
| |||
| 3.sys賬戶采 用的是系統 身份驗證 | 例如： [oracle@server253 ~]$ sqlplus xiaoming/oracle as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 06:29:53 2017 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> show user USER is "SYS" SQL> 所以系統賬號還可以這樣登錄 [oracle@server253 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 06:31:08 2017 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> 注：只要你的oracle賬號可以登錄到你的系統上面來，那么你的SQL就可以登錄 | |||
| 4.查看當前登 錄的賬號 | SQL> show user USER is "SYS" | |||
| 5.退出登錄 | SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [oracle@server253 ~]$ | |||
| 6.普通賬號的 登錄 | oracle數據庫在創建的時候，提供三個賬號： sys scott hr scott賬號默認登錄的時候，密碼為tiger，但是登錄時候發現賬號被鎖定了 [oracle@server253 ~]$ sqlplus scott/tiger SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 06:44:42 2017 Copyright (c) 1982, 2005, Oracle. All rights reserved. ERROR: ORA-28000: the account is locked Enter user-name: | |||
| 7.解鎖普通賬 號 | [oracle@server253 ~]$ sqlplus / as sysdba SQL> show user USER is "SYS" SQL> alter user scott account unlock ; User altered. [oracle@server253 ~]$ sqlplus scott/tiger SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 14 06:49:14 2017 Copyright (c) 1982, 2005, Oracle. All rights reserved. ERROR: ORA-28001: the password has expired Changing password for scott New password: Retype new password: Password changed Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> show user USER is "SCOTT" SQL> | |||
| 8.修改普通賬 號密碼 | SQL> show user USER is "SCOTT" SQL> alter user scott identified by redhat; User altered. 注意：注意：用戶自己本身也可以修改自己的密碼，一般情況是不被允許的 | |||
| 9.同時解鎖和 修改密碼 | SQL> alter user scott account unlock identified by oracle; alter user scott account unlock identified by oracle * ERROR at line 1: ORA-01031: insufficient privileges SQL> show user; USER is "SYS" SQL> alter user scott account unlock identified by oracle; User altered. SQL> | |||
| 10.解鎖hr賬 號 | sqlplus / as sysdba; alter user hr account unlock identified by redhat; exit sqlplus hr/redhat; show user; | |||
| 11.用戶之間 的切換 | ----conn hr/redhat---- 切換到hr賬號上面 ----conn / as sysdba-- 切換到sys賬號上面 ---conn sys/oracle as sysdba---切換到sys賬號上面 SQL> show user USER is "SYS" SQL> alter user hr account unlock identified by redhat; User altered. SQL> show user; USER is "SYS" SQL> conn scott/oracle Connected. SQL> show user; USER is "SCOTT" SQL> | |||
| 12.幫助文件 | --------------------------幫助----------------------------------- ---help index ---幫助索引 ---？shutdown---- 查找shutdown的使用功能 ---？set-----查找set的使用功能 SQL> help index Enter Help [topic] for help. @ 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 SQL> ? shutdown SHUTDOWN -------- Shuts down a currently running Oracle Database instance, optionally closing and dismounting a database. SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]] SQL> ? set SET --- Sets a system variable to alter the SQL*Plus environment settings for your current session. For example, to: - set the display width for data - customize HTML formatting - enable or disable printing of column headings - set the number of lines per page In iSQL*Plus, you can also use the Preferences screen to set system variables. SET system_variable value where system_variable and value represent one of the following clauses: APPI[NFO]{OFF|ON|text} NUM[WIDTH] {10|n} ARRAY[SIZE] {15|n} PAGES[IZE] {14|n} AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} PAU[SE] {OFF|ON|text} AUTOP[RINT] {OFF|ON} RECSEP {WR[APPED]|EA[CH]|OFF} AUTORECOVERY {OFF|ON} RECSEPCHAR {_|c} AUTOT[RACE] {OFF|ON|TRACE[ONLY]} SERVEROUT[PUT] {ON|OFF} [EXP[LAIN]] [STAT[ISTICS]] [SIZE {n | UNLIMITED}] [FOR[MAT] BLO[CKTERMINATOR] {.|c|ON|OFF} {WRA[PPED] | CMDS[EP] {;|c|OFF|ON} WOR[D_WRAPPED] | COLSEP {_|text} TRU[NCATED]}] CON[CAT] {.|c|ON|OFF} *SHIFT[INOUT] {VIS[IBLE] | COPYC[OMMIT] {0|n} INV[ISIBLE]} COPYTYPECHECK {ON|OFF} *SHOW[MODE] {OFF|ON} DEF[INE] {&|c|ON|OFF} *SQLBL[ANKLINES] {OFF|ON} DESCRIBE [DEPTH {1|n|ALL}] SQLC[ASE] {MIX[ED] | [LINENUM {OFF|ON}] [INDENT {OFF|ON}] LO[WER] | UP[PER]} ECHO {OFF|ON} *SQLCO[NTINUE] {> | text} *EDITF[ILE] file_name[.ext] *SQLN[UMBER] {ON|OFF} EMB[EDDED] {OFF|ON} SQLPLUSCOMPAT[IBILITY] {x.y[.z]} ESC[APE] {\|c|OFF|ON} *SQLPRE[FIX] {#|c} FEED[BACK] {6|n|ON|OFF} *SQLP[ROMPT] {SQL>|text} FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} SQLT[ERMINATOR] {;|c|ON|OFF} *FLU[SH] {ON|OFF} *SUF[FIX] {SQL|text} HEA[DING] {ON|OFF} *TAB {ON|OFF} HEADS[EP] {||c|ON|OFF} *TERM[OUT] {ON|OFF} INSTANCE [instance_path|LOCAL] *TI[ME] {OFF|ON} LIN[ESIZE] {80|n} ({150|n} iSQL*Plus) TIMI[NG] {OFF|ON} LOBOF[FSET] {1|n} *TRIM[OUT] {ON|OFF} LOGSOURCE [pathname] *TRIMS[POOL] {OFF|ON} LONG {80|n} UND[ERLINE] {-|c|ON|OFF} LONGC[HUNKSIZE] {80|n} VER[IFY] {ON|OFF} MARK[UP] HTML [OFF|ON] WRA[P] {ON|OFF} [HEAD text] [BODY text] [TABLE text] XQUERY {BASEURI text| [ENTMAP {ON|OFF}] ORDERING{UNORDERED| [SPOOL {OFF|ON}] ORDERED|DEFAULT}| [PRE[FORMAT] {OFF|ON}] NODE{BYVALUE|BYREFERENCE| NEWP[AGE] {1|n|NONE} DEFAULT}| NULL text CONTEXT text} NUMF[ORMAT] format An asterisk (*) indicates the SET option is not supported in iSQL*Plus. | |||
| 13.啟用歷史 記錄功能 | ---需要安裝rlwrap包---- 因為是源碼包，所以有點麻煩 .gz結尾的包 所以gunzip rlwrap-0.37.tar.gz rlwrap-0.37.tar tar -xvf rlwrap-0.37.tar 源代碼安裝其實是最簡單的，不用搭建yum倉庫 第一種方式：分兩步解開 只要一步就可以解開帶.gz的壓縮包 tar -zxvf rlwrap-0.37.tar.gz vim REDEAME 查看安裝說明 搜索 ：/INSTALL /INSTALL 源碼包安裝方式，需要兩步，其實下面就合成了一步 ./configure; make install 需要root權限安裝rlwrap-0.37.tar ./configure; make install 如果安裝不成功需要檢查這兩個軟件包是否有安裝 ls |grep readline readline-5.1-3.el5.i386.rpm readline-devel-5.1-3.el5.i386.rpm rpm -qa|grep readline ls |grep libter libtermcap-2.0.8-46.1.i386.rpm libtermcap-devel-2.0.8-46.1.i386.rpm rpm -qa|grep libter ---------檢驗------------ su - oracle sqlplus / as sysdba; selecct * from tab; exit 發現報錯，使用不了 因為rlwrap是安裝在linux系統上面的，所以每次使用的時候都必須告訴oracle系統 rlwrap sqlplus / as sysdba; 現在發現是可以上翻看，下查看的 可以通過取別名來實現 alias sqlplus='rlwrap sqlplus' 不過這個是臨時的 要想要永久的生效需要在oracle家目錄下面配置.bash_profile文件 ls -a vim .bash_profile alias sqlplus='rlwrap sqlplus' 添加這一項即可 保存退出后source .bash_profile | |||
第五部分：oracle下面的文本編輯器 | ||||
為解決這個問題 SQL> select ename,sal,hiredata 2 from emp 3 where sal=800 4 ; select ename,sal,hiredata * ERROR at line 1: ORA-00904: "HIREDATA": invalid identifier ------需要修改編輯器------ su - oracle vim .bash_profile EDITOR=vim export EDITOR source .bash_profile SQL> conn scott/oracle Connected. SQL> show user USER is "SCOTT" SQL> select ename,sal,hiredata 2 from emp 3 where sal=800 4 ; select ename,sal,hiredata * ERROR at line 1: ORA-00904: "HIREDATA": invalid identifier 敲一個ed，進行糾正編輯 SQL> ed Wrote file afiedt.buf 1 select ename,sal,hiredate 2 from emp 3* where sal=800 4 ; 或者 SQL> ed Wrote file afiedt.buf 1 select ename,sal,hiredate 2 from emp 3* where sal=800 4 / ENAME SAL HIREDATE ---------- ---------- --------- SMITH 800 17-DEC-80 SQL> SQL> / ENAME SAL HIREDATE ---------- ---------- --------- SMITH 800 17-DEC-80 SQL> ！也可以退出oracle數據庫 l列出oracle數據里面的緩存信息 scott模式下面所有的對象 scott這人賬號里面一共有四個對象 這里的對象叫表 SQL> / TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE 得到表里面產生的信息 SQL> select * from EMP 2 ; 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 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 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 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 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 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. 我現在想要知道EMP里面有哪些列？ 從emp表里面得到了8個列 SQL> desc emp Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> SQL> select empno,ename,sal from emp; / 通過/可以再次執行一遍 -------------如何永久的保存-------- SQL> select ename,sal from emp; SQL> save /u01/a.sql Created file /u01/a.sql 注意保存一定要保存絕對路徑 ------------如何調用保存的腳步---------- 第一種方法：@/u01/a.sql 就可以直接的調出來 第二鐘方法：start /u01/a.sql -------將保存的命令調到緩存但是不執行------- SQL> get /u01/b.sql 1 select ename,sal 2 from emp 3* where sal=3000 get調出來，但是不執行 ----------------我想要臨時的切換到linux環境下面怎么辦----------------------- ！ 就可以到linux環境 exit 從linux環境又切換到oracle環境 ----------------直接退出oracle環境-------------- exit 在oracle環境下面直接的輸入exit就可以直接的退出oracle環境 host和！功能一樣 ---------在不退出oracle的環境下刪除u01下面的a.sql-------- SQL> rm -rf /u01/a.sql SP2-0734: unknown command beginning "rm -rf /u0..." - rest of line ignored. SQL> ---在前面添加一個感嘆號即可完成----- SQL> ! rm -rf /u01/a.sql SQL> ! ls /u01 10201_database_linux32.zip app database rlwrap-0.37.tar.gz.bak afiedt.buf b.sql rlwrap-0.37.tar.gz SQL> SQL> ! mkdir /u01/aa SQL> ! ls -l /u01 total 654232 -rwxr-xr-x 1 oracle oinstall 668734007 Nov 6 2009 10201_database_linux32.zip drwxr-xr-x 2 oracle oinstall 4096 Aug 13 00:48 aa -rw-r--r-- 1 oracle oinstall 43 Aug 13 00:33 afiedt.buf drwxr-xr-x 3 oracle oinstall 4096 Aug 8 22:38 app -rw-r--r-- 1 oracle oinstall 43 Aug 13 00:32 b.sql drwxr-xr-x 6 oracle oinstall 4096 Jul 3 2005 database -rwxrw-rw- 1 root root 251438 Nov 19 2011 rlwrap-0.37.tar.gz -rwxr--r-- 1 root root 251438 Aug 12 21:55 rlwrap-0.37.tar.gz.bak SQL> ---------------要求是把查詢使用的命令加上查詢的結果全部都保存下來---------------- SQL> spool /u01/a.txt SQL> select ename from emp; ENAME ---------- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS ENAME ---------- JAMES FORD MILLER 14 rows selected. SQL> spool off 結果大功告成 SQL> ! [oracle@server253 u01]$ ls 10201_database_linux32.zip app database aa a.txt rlwrap-0.37.tar.gz afiedt.buf b.sql rlwrap-0.37.tar.gz.bak [oracle@server253 u01]$ vim a.txt [oracle@server253 u01]$ vim a.txt |
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
