oracle11安裝及使用

安裝oracle11

官網下載地址?Oracle Database 11g Release 2 for Microsoft Windows (x64)

官網下載慢可訪問我的資源

也可以網盤獲取??

鏈接：https://pan.baidu.com/s/1RDrGkqDA7tfKRnpJXUBMDw?
提取碼：z3na

上傳安裝包到服務器

在指定目錄下創建文件夾installfile，我是在/newdisk/oracle/installfile，將安裝文件上傳到該文件夾

禁用防火墻

systemctl stop firewalld.servicesystemctl disable firewalld.service

安裝依賴包

yum -y install binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33*i686 compat-libstdc++-33*.devel compat-libstdc++-33 compat-libstdc++-33*.devel gcc gcc-c++ glibc glibc*.i686 glibc-devel glibc-devel*.i686 ksh libaio libaio*.i686 libaio-devel libaio-devel*.devel libgcc libgcc*.i686 libstdc++ libstdc++*.i686 libstdc++-devel libstdc++-devel*.devel libXi libXi*.i686 libXtst libXtst*.i686 make sysstat unixODBC unixODBC*.i686 unixODBC-devel unixODBC-devel*.i686

驗證依賴包

rpm -q binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXi libXtst make sysstat unixODBC unixODBC-devel

?創建orcale用戶

[root@localhost installfile]# groupadd -g 502 oinstall
[root@localhost installfile]# groupadd -g 503 dba
[root@localhost installfile]# groupadd -g 504 oper
[root@localhost installfile]# groupadd -g 505 asmadmin
[root@localhost oracle]# sudo useradd -u 502 -g oinstall -G oinstall,dba,asmadmin,oper -s /bin/bash -m oracle
[root@localhost oracle]# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@localhost oracle]#

創建oradata目錄，解壓oracle安裝包

mkdir /oradata

將/newdisk/oracle/oradatainstallfile/文件夾下的壓縮包剪切到/oradata下面

drwxr-xr-x. 2 root root 98 Feb 27 00:31 installfile
drwxr-xr-x. 2 root root  6 Feb 27 02:03 oradata
[root@localhost oracle]# cp installfile/* oradata/
[root@localhost oracle]# ls oradata/
p13390677_112040_Linux-x86-64_1of7.zip  p13390677_112040_Linux-x86-64_2of7.zip
[root@localhost oracle]#

將/newdisk/oracle/oradata上的目錄內的所有文件和文件夾的歸屬都修改為oracle用戶

chown -R oracle:oinstall /newdisk/oracle/oradata

登錄oracle用戶將兩個壓縮文件解壓

[root@localhost oradata]# su oracle
[oracle@localhost oradata]$ unzip p13390677_112040_Linux-x86-64_1of7.zip
[oracle@localhost oradata]$ unzip p13390677_112040_Linux-x86-64_2of7.zip

會多一個database文件夾?

root用戶下修改系統配置參數


[oracle@localhost oradata]$ su root
Password:
[root@localhost oradata]# vim /etc/security/limits.conf#在最后加上下面配置oracle          soft    nproc           2047
oracle          hard    nproc           16384
oracle          soft    nofile          1024
oracle          hard    nofile          65536
oracle          soft    stack           10240

配置oracle安裝目錄

用oracle用戶并創建安裝目錄

[root@localhost oradata]# su oracle
[oracle@localhost oradata]$ pwd
/newdisk/oracle/oradata
[oracle@localhost oradata]$ mkdir -p /newdisk/oracle/oradata/oracle11g

配置環境變量

用oracle用戶

[oracle@localhost /]$ cd
[oracle@localhost ~]$ vim .bash_profile

在最后一行輸入下面的命令：

#這邊地址根據安裝目錄定
export ORACLE_BASE=/newdisk/oracle/oradata/oracle11g
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/dbhome_1
export ORACLE_SID=prod
export NLS_LANG=.AL32UTF8
export PATH=${PATH}:${ORACLE_HOME}/bin:$ORACLE_HOME/lib64

使環境變量生效

[oracle@localhost ~]$ source .bash_profile

修改安裝配置文件

拷貝安裝文件db_install.rsp到/newdisk/oracle/oradata/目錄下

[oracle@localhost oradata]$ cp database/response/db_install.rsp /newdisk/oracle/oradata/
[oracle@localhost oradata]$ ll
total 2487228
drwxr-xr-x. 7 oracle oinstall        136 Aug 26  2013 database
-rw-r--r--. 1 oracle oinstall      25116 Feb 27 03:31 db_install.rsp
drwxr-xr-x. 2 oracle oinstall          6 Feb 27 03:29 oracle11g
-rw-r--r--. 1 oracle oinstall 1395582860 Feb 27 02:04 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r--. 1 oracle oinstall 1151304589 Feb 27 02:04 p13390677_112040_Linux-x86-64_2of7.zip

編輯db_install.rsp

[oracle@localhost oradata]$ vim db_install.rsp

下面分別在文件中的對應行號內進行相應的設置

可以使用?:set number 顯示行號

29 oracle.install.option=INSTALL_DB_AND_CONFIG

37 ORACLE_HOSTNAME=你的虛擬機ip

42 UNIX_GROUP_NAME=oinstall

49 INVENTORY_LOCATION=/newdisk/oracle/oradata/oraInventory

86 SELECTED_LANGUAGES=en,zh_CN

91 ORACLE_HOME=/newdisk/oracle/oradata/oracle11g/product/11.2.0.3/dbhome_1

96 ORACLE_BASE=/newdisk/oracle/oradata/oracle11g

107 oracle.install.db.InstallEdition=EE

154 oracle.install.db.DBA_GROUP=dba

160 oracle.install.db.OPER_GROUP=oper

189 oracle.install.db.config.starterdb.type=GENERAL_PURPOSE

194 oracle.install.db.config.starterdb.globalDBName=prod

199 oracle.install.db.config.starterdb.SID=prod

213 oracle.install.db.config.starterdb.characterSet=AL32UTF8

221 oracle.install.db.config.starterdb.memoryOption=true

229 oracle.install.db.config.starterdb.memoryLimit=512

262 oracle.install.db.config.starterdb.password.ALL=oracle

336 oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE

344 oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=

/newdisk/oracle/oradata/oracle11g/data

351 oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=

/newdisk/oracle/oradata/oracle11g/fast_recovery_area

400 DECLINE_SECURITY_UPDATES=true

開始安裝oracle

輸入下面的命令：

進入/newdisk/oracle/oradata/database目錄

執行安裝命令

./runInstaller -silent -ignoreSysPrereqs -responseFile /newdisk/oracle/oradata/db_install.rsp -ignorePrereq

?等待直到出現下面代碼就成功了?

?連接oracle

sqlplus / as sysdba

正常情況會連接成功，而我出現了如下的情況

?這就是雖然安裝成功了，但問題可能在于sqlplus命令未能正確添加到系統路徑中

所以在安裝目錄找sqlplus命令

[oracle@localhost database]$ find /newdisk/ -name sqlplus
/newdisk/oracle/oradata/oracle11g/product/11.2.0.3/dbhome_1/bin/sqlplus
/newdisk/oracle/oradata/oracle11g/product/11.2.0.3/dbhome_1/sqlplus

我們先打印ORACLE_HOME

發現和我們安裝的目錄不一樣，查看~/.bash_profile文件

在打印ORACLE_BASE

發現是空，查看?~/.bash_profile文件

發現是有值的，那就是沒有刷新配置，刷新一下配置，在查看

再次連接oracle，成功

navicat連接測試?

使用sys用戶密碼是上面設置的oracle，

oracle.install.db.config.starterdb.password.ALL=oracle

使用sys登錄需要時sysdba角色，如下

卸載oracle

以root用戶身份登錄系統

停止所有Oracle相關進程。您可以使用以下命令來列出所有Oracle進程并停止它們（請注意，這里假設您正在使用Oracle 11g）：

ps -ef | grep oracle | grep -v grep

然后，您可以使用kill命令停止每個進程。例如，如果您想要停止進程ID為1234的進程，可以使用以下命令

kill -9 1234

?停止監聽

lsnrctl stop

刪除安裝目錄

rm -rf /newdisk/oracle/oradata/oracle11grm -rf /newdisk/oracle/oradata/oraInventory/

創建用戶并賦予權限

--創建用戶
CREATE USER testuser IDENTIFIED BY 123456;--分配連接權限GRANT CONNECT TO testuser;-- 分配創建會話權限
GRANT CREATE SESSION TO testuser;--把所有權限都給用戶
GRANT ALL PRIVILEGES TO testuser;

查詢所有表空間

SELECT --B.file_name "文件名",A.TABLESPACE_NAME "表空間名",TOTAL "表空間大小",FREE "表空間剩余大小",(TOTAL - FREE) "表空間使用大小",TOTAL / (1024 * 1024 * 1024) "表空間大小(G)",FREE / (1024 * 1024 * 1024) "表空間剩余大小(G)",(TOTAL - FREE) / (1024 * 1024 * 1024) "表空間使用大小(G)",ROUND((TOTAL - FREE) / TOTAL, 4) * 100 "使用率 %"
FROM   (SELECT TABLESPACE_NAME,SUM(BYTES) FREEFROM   DBA_FREE_SPACEGROUP  BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,--  file_name,SUM(BYTES) TOTALFROM   DBA_DATA_FILESGROUP  BY TABLESPACE_NAME/*,file_name*/) B
WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME;

創建表空間

CREATE TABLESPACE 表空間名稱 LOGGING DATAFILE 掛載地址 SIZE 大小 ;--舉例
CREATE TABLESPACE TBS_INDEX_ITMS LOGGING DATAFILE '/export/home/oradata/TBS_INDEX_ITMS' SIZE 5120M ;

刪除表空間

--刪除空的表空間，但是不包含物理文件
drop tablespace tablespace_name;
--刪除非空表空間，但是不包含物理文件
drop tablespace tablespace_name including contents;
--刪除空表空間，包含物理文件
drop tablespace tablespace_name including datafiles;
--刪除非空表空間，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空間中的表有外鍵等約束關聯到了本表空間中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

執行sql文件


-bash-4.2$ sqlplus gtmsmanager/gtmsmanager@10.21.1.176:1521/gtmsdbSQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 27 09:34:03 2024Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> @/root/oracle/insert.sql

如果沒有權限，給所有用戶賦予文件夾的所有權限，再次執行就行了

chmod o+w /root/oracle/

導入導出dmp

更多導出查看?exp help=y

導出dmp

導出數據庫全部數據

僅輸入數據庫連接串和文件導出路徑就可以執行導出，導出文件后綴為dmp

exp  用戶名/密碼@IP/服務名  FILE=導出文件路徑 exp testuser/123456@192.168.31.130/prod FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp_all.dmp;[oracle@localhost oradata]$ exp testuser/123456@192.168.31.130/prod FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp_all.dmp;Export: Release 11.2.0.4.0 - Production on Tue Feb 27 22:29:54 2024Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TESTUSER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TESTUSER
About to export TESTUSER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TESTUSER's tables via Conventional Path ...
. . exporting table                        STUDENT          0 rows exported
. . exporting table                       TESTUSER          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@localhost oradata]$ ls dmpdir
cyz_expdp_all.dmp  cyz_expdp.dmp

按表名導出

exp user/pwd@ip/service FILE=導出文件地址 TABLES=表名1,表名2;exp testuser/123456@192.168.31.130/prod FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp.dmp TABLES=STUDENT,TESTUSER;[oracle@localhost oradata]$ exp testuser/123456@192.168.31.130/prod FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp.dmp TABLES=STUDENT,TESTUSER;Export: Release 11.2.0.4.0 - Production on Tue Feb 27 22:23:48 2024Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path ...
. . exporting table                        STUDENT          0 rows exported
. . exporting table                       TESTUSER          0 rows exported
Export terminated successfully without warnings.
[oracle@localhost oradata]$ ls dmpdir
cyz_expdp.dmp

導入dmp

更多導入查看?imp help=y

導入整個文件

imp user/pwd@ip/service FILE=導出文件地址  FULL=Yimp testuser/123456@192.168.31.130/prod  FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp_all.dmp   FULL=Y[oracle@localhost oradata]$ imp testuser/123456@192.168.31.130/prod  FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp_all.dmp   FULL=YImport: Release 11.2.0.4.0 - Production on Tue Feb 27 22:37:45 2024Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing TESTUSER's objects into TESTUSER
. . importing table                      "STUDENT"          0 rows imported
. . importing table                     "TESTUSER"          0 rows imported
Import terminated successfully without warnings.

按表名導入

導入文件中的多個表

imp user/pwd@ip/service FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp.dmp  TABLES=STUDENT,TESTUSERimp testuser/123456@192.168.31.130/prod  FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp.dmp  TABLES=STUDENT,TESTUSER[oracle@localhost oradata]$ imp testuser/123456@192.168.31.130/prod  FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp.dmp  TABLES=STUDENT,TESTUSERImport: Release 11.2.0.4.0 - Production on Tue Feb 27 22:42:07 2024Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing TESTUSER's objects into TESTUSER
. importing TESTUSER's objects into TESTUSER
. . importing table                      "STUDENT"          0 rows imported
. . importing table                     "TESTUSER"          0 rows imported
Import terminated successfully without warnings.

導入文件中的一個表

imp user/pwd@ip/service FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp.dmp  TABLES=STUDENTimp testuser/123456@192.168.31.130/prod  FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp.dmp  TABLES=STUDENT[oracle@localhost oradata]$ imp testuser/123456@192.168.31.130/prod  FILE=/newdisk/oracle/oradata/dmpdir/cyz_expdp.dmp  TABLES=STUDENT,TESTUSERImport: Release 11.2.0.4.0 - Production on Tue Feb 27 22:42:07 2024Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing TESTUSER's objects into TESTUSER
. importing TESTUSER's objects into TESTUSER
. . importing table                      "STUDENT"          0 rows imported
Import terminated successfully without warnings.

修改用戶密碼?

sqlplus  SYS/oracle@gtmsdb as sysdbaalter user GTMSMANAGER identified  by 123456;--例子
-bash-4.2$ sqlplus  SYS/oracle@gtmsdb as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 27 09:59:06 2024Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter user GTMSMANAGER identified  by 123456;User altered.SQL>

序列操作

新建序列

-- Create sequence  testseq是序列名
create sequence testseq
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

查詢序列當前值（必須執行一次nextval，否則會報錯）

--testseq是名稱
select testseq.currval  from dual

查詢序列下一個值

--testseq序列名
select testseq.nextval from dual

刪除序列

--testseq序列名
DROP SEQUENCE testseq;