CentOS7.5安装oracle实例

最新推荐文章于 2024-10-22 20:02:37 发布

_Zephyrus_

最新推荐文章于 2024-10-22 20:02:37 发布

阅读量2.1k

点赞数 2

分类专栏： Oracle 文章标签： centos7.5 oracle实例 oracle单机配置

本文链接：https://blog.csdn.net/wangxw1803/article/details/84620740

版权

Oracle 专栏收录该内容

35 篇文章 1 订阅

订阅专栏

关闭防火墙

[root@localhost /]# systemctl stop firewalled service
[root@localhost /]# systemctl disable firewalled service

关闭selinux

[root@localhost /]# yum install vim
[root@localhost /]# vim /etc/selinux/config
[root@localhost /]# selinux=encourceing改为disabled

创建运行oracle数据库的用户组和系统用户

[root@localhost /]# groupadd oinstall //创建用户组oinstall
[root@localhost /]# groupadd dba //创建用户组dba
[root@localhost /]# useradd -g oinstall -g dba -m oracle //创建用户oracle，并加入到oinstall和dba用户组
[root@localhost /]# passwd oracle //设置用户oracle的登录密码
[root@localhost /]# id oracle //查看新建的用户oracle

创建数据库安装目录并授权

[root@localhost /]# mkdir -p /app/oracle //安装目录
[root@localhost /]# mkdir -p /app/oraInventory //配置文件目录
[root@localhost /]# mkdir -p /app/database // 解压目录
[root@localhost /]# chown -R oracle:oinstall /app/oracle
[root@localhost /]# chown -R oracle:oinstall /app/oraInventory
[root@localhost /]# chown -R oracle:oinstall /app/database

修改OS系统标识

# 注：CentOS系统默认不支持oracle数据库安装，需要修改系统标识为redhat-7
[root@localhost /]# cat /proc/version //查看Linux系统版本
[root@localhost /]# cat /etc/redhat-realease //查看CentOS系统版本
CentOS Linux release 7.2.1511(Core)
[root@localhost /]# vi /etc/redhat-realease  //CentOS7改为redhat7
redhat-7

修改内核参数

[root@localhost /]# vi /etc/sysctl.conf
#System default……
#To override……
#
#For more……

#添加下方配置参数
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.conf.all.rp_filter = 1
fs.file-max = 6815744 #设置最大打开文件数
fs.aio-max-nr = 1048576
kernel.shmall = 2097152 #共享内存的总量，8G内存设置：2097152*4k/1024/1024
kernel.shmmax = 2147483648 #最大共享内存的段大小
kernel.shmmni = 4096 #整个系统共享内存端的最大数
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500 #可使用的IPv4端口范围
net.core.rmem_default = 262144
net.core.rmem_max= 4194304
net.core.wmem_default= 262144
net.core.wmem_max= 1048576

使配置参数生效：

[root@localhost /]# sysctl -p

对oracle用户设置限制，提高软件运行性能

[root@localhost /]# vi /etc/security/limits.conf

#@student - maxlogins 4
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

# End of file

配置用户的环境变量

export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=orcl
export ORACLE_TERM=xterm
export PATH=$ORACLE_HOME/bin:/user/sbin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export LANG=C
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
#AMERICAN_AMERICA.ZHS16GBK #设置Oracle客户端字符集，必须 与Oracle安装时设置的字符集保持一致，如：ZHS16GBK，否则出现数据导入导出 中文乱码问题

#注释掉原来的#PATH和#export PATH
#使上述配置立即生效：
[root@localhost /]# source /home/oracle/.bash_profile

增加主机名

#注：若不执行此步，则配置监听时会出错，系统无法识别监听。
[root@localhost /]#  $hostname
[root@localhost /]# vi /etc/sysconfig/network
# Created by anaconda
[root@localhost /]# hostname=lgswork //增加hostname
[root@localhost /]# vi /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 lgswork
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
[root@localhost /]# hostname lgswork　　//执行

安装oracle数据库所需的软件包

[root@localhost /]# yum install gcc* gcc-* gcc-c++-* glibc-devle-* glibc-headers-* compat-libstdc* libstdc* elfutils-libelf-devel* libaio-devel* sysstat* unixODBC-* pdksh-* make ksh binutils -y

解压缩

[root@localhost /]#yum install unzip
# 将3个压缩文件放到/app下
[root@localhost /]# su - oracle
[oracle@localhost]$ cd /app
[oracle@localhost]$ unzip oracle_11.2.0.4_Linux-64_1of7.zip -d /app/database
[oracle@localhost]$ unzip oracle_11.2.0.4_Linux-64_2of7.zip -d /app/database

安装

[oracle@localhost]$ cd /app/database
[oracle@localhost]$ ./runInstaller

Prerequisite Checks

#1 swap 空间不足解决：  （要求2.67 实际2）
[root@localhost /]# free -m  //查看当前虚拟内存
[root@localhost /]# dd if=/dev/zero of=/home/swap bs=1024 count=1024000　//将当前swap 空间由2048M增加到3048M新增一个2014的swap文件
[root@localhost /]# swapon /home/swap　//增加并启用虚拟内容
[root@localhost /]#  free -m　　//再次查看

#2 package检测失败
#因为现有的包的版本比检测要高，最后忽略即可。也可下载相应的package后：
[root@localhost /]# rpm -ivh pdksh-5.2.14-37.el5_8.1.x86_64.rpm(需cd到包所在目录)
#3 安装过程是一个漫长的过程，中间有几次卡住，没有出现任何画面，屏幕中间有 条小线，尝试多次，发现光标在该线上，右键点击Closed，也可esc或enter。

Install Product

#1 execute root scripts(根脚本执行)-failed
#切换到root用户：
[oracle@localhost]$ su - root
[root@localhost /]# cd /app/oraInventory
[root@localhost /]# ./orainstRoot.sh
[root@localhost /]# cd /app/oracle/product/11.2.0/db_1
[root@localhost /]# ./root.sh
#执行完这个脚本后在点击try便可执行成功

监听

[Oracle@localhost ~]$ netca //配置监听
[Oracle@localhost ~]$ lsnrctl status //查看监听状态
[Oracle@localhost ~]$ dbca //创建数据库实例orcl

Error in invoking target ‘agent nmhs’ of makefile

Error in invoking target 'agent nmhs' of makefile
安装Oracle11g报错：
Error in invoking target 'agent nmhs' of makefile  
解决方法：
cd $ORACLE_HOME/sysman/lib
vi ins_emagent.mk 
修改此处如下：
#===========================
#  emdctl
#===========================
$(SYSMANBIN)emdctl:
　　$(MK_EMAGENT_NMECTL)-lnnz11
*******VICTORY LOVES PREPARATION*******

DISPLAY not set.

Set DISPLAY environment variable, then re-run.

以root用户登录
在执行xhost +命令（使得所有客户都可以访问）
若返回信息为： 
access control disabled,clients can connect from any host   
表示xhost +可执行直接切换至oracle用户运行安装oracle
如果xhost +不能执行，
第一步：用root登陆linux，启动vnc服务；
第二步：根据vnc起来的端口，设置export DISPLAY=:1（1表示vnc在第一个tty上启动的），vnc的启动信息见下文的linux操作日志；
第三步：xhost + ip （ip为主机ip地址）

2、以oracle用户登录
那么在该oracle用户下执行一下export DISPLAY=:1.0
然后再执行xdpyinfo命令，如果能出现信息说明你已经成功了
这样估计就可以解决linux下安装oracle出现的xhost问题

3、执行命令 ./runInstaller 安装oracle
linux下的操作日志如下：
[root@localhost ~]# vncserver
New 'localhost:1 (root)' desktop is localhost:1
Starting applications specified in /root/.vnc/xstartupLog file is /root/.vnc/localhost:1.log
[root@localhost ~]# export DISPLAY=localhost:1
[root@localhost ~]# xhost +
access control disabled, clients can connect from any host

重建EM：emca -repos recreate

重建EM：emca -repos recreate

手工重新配置EM
oracle@linux:/oracle/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole>  emca -config dbcontrol db

STARTED EMCA at Jan 17, 2014 3:13:05 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /oracle/app/grid_home ]: 
Password for SYS user:  
Password for DBSNMP user:  
Password for SYSMAN user:  
Email address for notifications (optional): sin@sina.com
Outgoing Mail (SMTP) server for notifications (optional): 
ASM ORACLE_HOME [ /oracle/app/grid_home ]: 
ASM SID [ +ASM ]: 
ASM port [ 1521 ]: 
ASM username [ ASMSNMP ]: 
ASM user password:  
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /oracle/app/oracle/product/11.2.0/db_1

Local hostname ................ linux.site
Listener ORACLE_HOME ................ /oracle/app/grid_home
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ............... sin@sina.com
Outgoing Mail (SMTP) server for notifications ............... 
ASM ORACLE_HOME ................ /oracle/app/grid_home
ASM SID ................ +ASM
ASM port ................ 1521
ASM user role ................ SYSDBA
ASM username ................ ASMSNMP

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Nov 22, 2018 3:43:46 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /app/oracle/cfgtoollogs/emca/orcl/emca_2018_11_22_03_43_04.log.
Nov 22, 2018 3:43:47 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Nov 22, 2018 3:44:17 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Nov 22, 2018 3:44:19 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Nov 22, 2018 3:44:24 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Nov 22, 2018 3:44:24 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Nov 22, 2018 3:44:51 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Nov 22, 2018 3:44:52 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is 
   ##  https://localhost:1158/em 
<<<<<<<<<<<
Nov 22, 2018 3:44:53 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING: 
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /app/oracle/product/11.2.0/db_1/localhost_orcl/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost. 

***********************************************************
Enterprise Manager configuration completed successfully

[root@db01 /]# /oracle/app/oraInventory/orainstRoot.sh
Changing permissions of /oracle/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /oracle/app/oraInventory to oinstall.
The execution of the script is complete.
[root@db01 /]# /oracle/app/oracle/product/11.2.0/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /oracle/app/oracle/product/11.2.0
Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...
   
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product‐specific root actions will be performed.
Finished product‐specific root actions.

[oracle@db01 oracle]$ cd /oracle
[oracle@db01 oracle]$ ls
app  lost+found
[oracle@db01 oracle]$ mkdir oradata
[oracle@db01 oracle]$ pwd
/oracle

安装完成后检查是否已安装成功

[oracle@db01 database]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 25 14:48:59 2017
Copyright (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
options
SQL> select status,instance_name from v$instance;
STATUS       INSTANCE_NAME
‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
OPEN         db01
SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
DB01      READ WRITE

[oracle@db01 database]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://db01:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
Logs are generated in directory
/oracle/app/oracle/product/11.2.0/db01_db01/sysman/log
[oracle@db01 database]$

查看是否有报错信息
SQL> show parameter dump
NAME                                 TYPE        VALUE
‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
‐‐‐‐‐‐‐‐‐
background_core_dump                 string      partial
background_dump_dest                 string     
/oracle/app/oracle/diag/rdbms/
                                                 db01/db01/trace
core_dump_dest                       string     
/oracle/app/oracle/diag/rdbms/
                                                 db01/db01/cdump
max_dump_file_size                   string      unlimited
shadow_core_dump                     string      partial
user_dump_dest                       string     
/oracle/app/oracle/diag/rdbms/
                                                 db01/db01/trace
SQL> exit   
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.4.0 ‐ 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options

[oracle@db01 oracle]$ cd
/oracle/app/oracle/diag/rdbms/db01/db01/trace/
[oracle@db01 trace]$ ls
[oracle@db01 trace]$ cat alert_db01.log |grep ORA‐
ORA‐1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ORA‐00313: open failed for members of log group 1 of thread 1
ORA‐00312: online log 1 thread 1: '/oracle/oradata/db01/redo01.log'
ORA‐27037: unable to obtain file status
ORA‐00313: open failed for members of log group 1 of thread 1
ORA‐00312: online log 1 thread 1: '/oracle/oradata/db01/redo01.log'
ORA‐27037: unable to obtain file status
Clearing online redo logfile 5 complete
Online log /oracle/oradata/db01/redo01.log: Thread 1 Group 1 was
previously cleared
Online log /oracle/oradata/db01/redo02.log: Thread 1 Group 2 was
previously cleared
Online log /oracle/oradata/db01/redo03.log: Thread 1 Group 3 was
previously cleared
Online log /oracle/oradata/db01/redo04.log: Thread 1 Group 4 was
previously cleared
Online log /oracle/oradata/db01/redo05.log: Thread 1 Group 5 was
previously cleared
可以发现有报错信息，但数据库已经自动实例恢复了。

ORACLE is not in the sudoers file

解决办法：只要修改一下/etc/sudoers文件就行了。
1.进入超级用户模式。也就是输入"su-",系统会让你输入超级用户密码，输入密码后就进入了超级用户模式。（注：您也可以直接用root登录）；
2.添加文件的写权限，因为sudoers这个文件只允许读，不允许写入。输入命令"chmod u+w /etc/sudoers"；
3.编辑/etc/sudoers文件。输入命令"vi/etc/sudoers",输入"i"进入编辑模式，找到这一行："root ALL=(ALL) ALL"，在这行下面添加"xxxALL=(ALL) ALL"(这里的xxx是你的用户名)，然后按Esc键，输入":wq"，保存退出；
4.撤销文件的写权限，还原文件的读写状态。输入命令"chmod u-w /etc/sudoers"(一定要记得撤销，否则可能引起其他问题)

ping unknown host

[root@localhost ~]# ping www.baidu.com
提示 ping: unknown host
这是因为DNS服务器没有配置好，如果网络没有问题，可以按下面的步骤进行检查：
1） vi /etc/resolv.conf
添加
nameserver 192.168.59.2       本机的网关地址（路由器的地址）
search localdomain    # search 参数指定域名查找顺序
2）设置网关
vi /etc/sysconf/network-scripts/ifcfg-eth0
添加
GATEWAY=192.168.59.2
或者
使用命令设置
route add default gw 192.168.59.2
然后重新启动网络服务：
service network restart
3）确保可用DNS解析
[root@localhost Desktop]# grep hosts /etc/nsswitch.conf
输出
hosts:      files dns
该文件/etc/nsswith.conf是SUN公司开发的一种扩展name server switch。用于设置文件读取的顺序，如是先读取/etc/hosts文件，还是先查找DNS服务器。
hosts:      files dns  指定先查找/etc/hosts文件，如果失败，则到DNS服务器查找与域名相对应的主机信息。
4）测试DNS
ping www.baidu.com

使用PL/SQL连接Oracle时报错ORA-12541: TNS: 无监听程序

出现这个报错，思路：
思路一：IP地址（服务器IP地址）能否ping通
打开DOS窗口（cmd），输入：ping 10.10.1.11
能ping通；
思路二：IP地址（服务器IP地址）及端口是否可以用
打开DOS窗口（cmd），输入：telnet 10.10.1.11 1521
可能会报错：'telnet' 不是内部或外部命令，也不是可运行的程序或批处理文件。
解决办法：参考：telnet不是内部或外部命令，也不是可运行的程序
打开“控制面板”找到“程序”
点击“启用或关闭Windows功能”
勾选“Telnet客户端”，点击确定
重新打开dos命令窗口，输入“telnet ip地址 端口号”，测试成功！

TNS-12541: TNS:no listener , TNS-12542: TNS:address already in use

查看数据库监听状态不对
$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production on 05-NOV-2012 08:54:08

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
  IBM/AIX RISC System/6000 Error: 79: Connection refused
然后手动启动数据库报如下错误：
$ lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production on 05-NOV-2012 08:54:12

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Starting /oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production
System parameter file is /oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/product/10.2.0/db_1/network/log/listener.log
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
  IBM/AIX RISC System/6000 Error: 67: Address already in use

Listener failed to start. See the error message(s) above...

/oracle/product/10.2.0/db_1/network/trace/listener.trc

明明监听没启动起，却说已经在运行了，后来终于找到原因，网上看到别人也碰到这个问题，说是HOSTS文件设置有问题，查看本机主机名和hosts文件，果然有问题，两个IP地址指向了同一个主机名，将一台主机删除后，再启动监听，一切OK。

用root用户进行修改：
# cat /etc/hosts

# Internet Address      Hostname        # Comments
# 192.9.200.1          net0sample      # ethernet name/address
# 128.100.0.1          token0sample    # token ring name/address
# 10.2.0.2              x25sample      # x.25 name/address
# 2000:1:1:1:209:6bff:feee:2b7f        ipv6sample      # ipv6 name/address
127.0.0.1              loopback localhost      # loopback (lo0) name/address
192.168.101.189  test
192.168.101.190  test

再次启动监听：
$ lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production on 05-NOV-2012 08:58:47
Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Starting /oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production
System parameter file is /oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                    LISTENER
Version                  TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Production
Start Date                05-NOV-2012 08:58:49
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level              off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File  /oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File        /oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
总结：/etc/hosts文件设置很重要，这里设置不正确，很多地方都要出错，特别是RAC，会报很多奇怪的错误，配置的时候要多注意。

oracle 11g 脚本创建表空间和用户

/*分为四步 */
/*第1步：创建临时表空间 */
create temporary tablespace test_temp 
tempfile 'D:\app\Administrator\product\11.2.0\dbhome_1\database\test_temp.dbf' 
size 10m 
autoextend on 
next 10m maxsize 20480m 
extent management local; 
/*第2步：创建数据表空间 */
create tablespace test 
logging 
datafile 'D:\app\Administrator\product\11.2.0\dbhome_1\database\test.dbf' 
size 10m 
autoextend on 
next 10m maxsize 20480m 
extent management local; 
/*第3步：创建用户并指定表空间 */
create user test identified by test1234 
default tablespace testi
temporary tablespace test_temp; 
/*第4步：给用户授予权限 */
grant connect,resource,dba to test;

--查询当前表空间
select tablespace_name,file_id,file_name,bytes
from dba_data_files
order by file_id

where tablespace_name='test'
--删除表空间
drop tablespace em;
--删除用户
drop user e [CASCADE];