linux下oracle 11g数据库从搭建到使用到连接到备份恢复

╰半夏微凉° 2021-12-08 16:09 696阅读 0赞

# 搭建数据库 #

[https://blog.csdn.net/ageeklet/article/details/81603482][https_blog.csdn.net_ageeklet_article_details_81603482]

# 搭建数据库过程中报错解决参考 #

1.dbca提示-bash: dbca: command not found  
[https://blog.csdn.net/shushugood/article/details/53020408][https_blog.csdn.net_shushugood_article_details_53020408]  
[https://blog.csdn.net/zq199692288/article/details/78871046][https_blog.csdn.net_zq199692288_article_details_78871046]  
[https://blog.csdn.net/shushugood/article/details/53020408][https_blog.csdn.net_shushugood_article_details_53020408]  
我的是多个用户，本来是在oracle下运行dbca，但是运行的确实fb用户下的vi .bash\_profile，文档里面的路径关系复制了进去，要看清楚路径是否正确（在ftp下看看找不找得到例如xftp工具）。只要路径正确，肯定可以把dbca弹出来。  
2. Oracle 安装执行./runInstaller 提示SEVERE: \[FATAL\] web01: web01  
[http://www.bubuko.com/infodetail-1714256.html][http_www.bubuko.com_infodetail-1714256.html]  
3. \[WARNING\] - My Oracle Support Username/Email Address Not Specified  
[https://blog.csdn.net/qq\_40687433/article/details/84236363][https_blog.csdn.net_qq_40687433_article_details_84236363]  
也有可能是自己的配置文件写的有问题 可以注释掉 自己重新写一个 可能就好使了  
解决办法  
SQL> startup  
ORA-01078: failure in processing system parameters  
LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/db/dbs/initorcl.ora’  
4. linux 上执行DBCA报错ora-00600 ora-03114  
linux上安装Oracle 11g，单机。在执行dbca后，在安装进度到12%的时候，报错ora-00600。查看日志信息，报错为ora-03114 not collect oracle。前期的准备都有的，怎么会报这个错呢，查了相关资料后，怀疑是主机名的问题，查看hostname 为bogon。在查看/etc/hosts ,和 /etc/sysconfig/network ，发现其中的主机名都是localhost.domain。改为bogon。在dbca界面继续执行安装。成功。  
修改/etc/hosts ，/etc/sysconfig/network 文件可以在另外一个终端中执行。不会影响  
5. centos 安装oracle 报Checking swap space: 0 MB available, 150 MB required. Failed  
[https://blog.csdn.net/qq\_35893120/article/details/80655645][https_blog.csdn.net_qq_35893120_article_details_80655645]

# 配置数据库 #

创建临时表空间  
create temporary tablespace CRM\_TEMP tempfile ‘/var/app/oradata/crm/CRM\_TEMP.bdf’ size 1024m reuse autoextend on next 200m maxsize unlimited;  
创建表空间  
create tablespace CRM datafile ‘/var/app/oradata/crm/crm.dbf’ size 1024M reuse autoextend on next 400M maxsize unlimited default storage(initial 128k next 128k minextents 2 maxextents unlimited);  
修改表空间大小  
ALTER TABLESPACE CRM ADD DATAFILE ‘/var/app/oradata/crm/crm1.dbf’ SIZE 20480M;  
创建用户绑定表空间  
create user crm1 identified by abc123DEF default tablespace CRM temporary tablespace CRM\_TEMP;  
修改用户绑定表空间  
alter user crm1 default tablespace CRM temporary tablespace CRM\_TEMP;  
分配用户权限  
grant dba to crm1;  
grant connect,resource to crm1;  
grant select any table to crm1;  
grant delete any table to crm1;  
grant update any table to crm1;  
grant insert any table to crm1;  
登陆新用户  
sqlplus crm1/abc123DEF@orcl11g  
内部切换用户  
conn crm1/abc123DEF@orcl11g  
删除用户和用户下面的所有数据  
drop user crm1 cascade;  
参考文档  
linux系统下创建oracle表空间和用户权限查询  
[https://kingxss.iteye.com/blog/1741076][https_kingxss.iteye.com_blog_1741076]

# 配置oracle数据库报错解决参考 #

1.ORA-00845: MEMORY\_TARGET not supported on this system：  
[https://www.cnblogs.com/killkill/archive/2010/09/10/1823690.html][https_www.cnblogs.com_killkill_archive_2010_09_10_1823690.html]  
2. ORA-00214: control file ‘/oradata/orcl/control02.ctl’ version 884 inconsistent with file '/orad：  
[https://blog.csdn.net/debimeng/article/details/79656458][https_blog.csdn.net_debimeng_article_details_79656458]  
[https://blog.csdn.net/dba\_monkey/article/details/60962322][https_blog.csdn.net_dba_monkey_article_details_60962322]  
3. ORA-00205错误解决：  
[https://blog.51cto.com/xjsunjie/538818][https_blog.51cto.com_xjsunjie_538818]  
[https://blog.csdn.net/u013082095/article/details/64442837][https_blog.csdn.net_u013082095_article_details_64442837]  
[https://www.cnblogs.com/sparkbj/articles/6146247.html][https_www.cnblogs.com_sparkbj_articles_6146247.html]  
4. ORA-32001:write to SPFILE requested but no SPFILE is in use：  
[https://blog.csdn.net/songyundong1993/article/details/53307931][https_blog.csdn.net_songyundong1993_article_details_53307931]  
5. ORA-01102: cannot mount database in EXCLUSIVE mode ：  
[https://blog.csdn.net/linucle/article/details/8560548][https_blog.csdn.net_linucle_article_details_8560548]  
6. ORA-01507: database not mounted：  
[https://blog.csdn.net/qq\_27966627/article/details/51062822][https_blog.csdn.net_qq_27966627_article_details_51062822]  
[https://www.cnblogs.com/thg999/p/9981766.html][https_www.cnblogs.com_thg999_p_9981766.html]  
7. ORA-00210，ORA-00202，ORA-27086错误：  
ORA-00210: cannot open the specified control file  
ORA-00202: control file: ‘/u01/app/oracle/oradata/orcl/control01.ctl‘  
ORA-27086: unable to lock file - already in use  
查看日志发现的  
![在这里插入图片描述][20190716103958811.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70]  
SQL> show parameter dump\_dest  
NAME TYPE VALUE

--------------------

background\_dump\_dest string /var/app/diag/rdbms/orcl11g/or  
a11g/trace  
core\_dump\_dest string /var/app/diag/rdbms/orcl11g/or  
a11g/cdump  
user\_dump\_dest string /var/app/diag/rdbms/orcl11g/or  
a11g/trace  
[http://www.mamicode.com/info-detail-1173983.html][http_www.mamicode.com_info-detail-1173983.html]  
8.oracle11g报错 ORA-01078和LRM-00109 解决方法：  
[https://blog.csdn.net/buffoon1900/article/details/51919869][https_blog.csdn.net_buffoon1900_article_details_51919869]

# oracle数据库命令记录 #

显示当前用户  
SQL> show user;  
USER is “SYS”  
显示实例名称  
SQL> select \* from v$instance;  
INSTANCE\_NUMBER INSTANCE\_NAME

--------------------

## HOST\_NAME ##

VERSION STARTUP\_T STATUS PAR THREAD\# ARCHIVE LOG\_SWITCH\_WAIT

--------------------

LOGINS SHU DATABASE\_STATUS INSTANCE\_ROLE ACTIVE\_ST BLO

--------------------

1 ora11g

localhost.localdomain  
11.2.0.1.0 25-JUN-19 OPEN NO 1 STOPPED  
ALLOWED NO ACTIVE PRIMARY\_INSTANCE NORMAL NO  
我们查该对象下有些什么表，就应该执行下列的语句：  
SQL>select table\_name from user\_tables;  
linux系统下创建oracle表空间和用户权限查询  
[https://www.cnblogs.com/gzggyy/p/3319315.html][https_www.cnblogs.com_gzggyy_p_3319315.html]  
查看数据库名称  
SQL> show parameter db\_name;  
NAME TYPE VALUE

--------------------

## db\_name string orcl11g   oracle数据库创建表   [https://blog.csdn.net/qq\_42914528/article/details/81509853][https_blog.csdn.net_qq_42914528_article_details_81509853]   oracle数据库之数据插入、修改和删除   [https://www.cnblogs.com/fighter007/p/8287780.html][https_www.cnblogs.com_fighter007_p_8287780.html]   oracle一个创建用户、创建表空间、授权、建表的完整过程   [https://blog.csdn.net/skyuck/article/details/83799440][https_blog.csdn.net_skyuck_article_details_83799440]   当前数据库名   SQL> select name from v$database;   NAME ##

## ORCL11G   Oracle - 数据库的实例、表空间、用户、表之间关系   [https://www.cnblogs.com/zouhao/p/3627522.html][https_www.cnblogs.com_zouhao_p_3627522.html]   查询当前数据库实例名：   SQL> select instance\_name from v$instance;   INSTANCE\_NAME ##

ora11g  
一会看这个 这个通了 应该就能明白一些东西  
oracle 连接数据库以及查看当前用户、当前数据库实例  
[https://blog.csdn.net/weixin\_34372728/article/details/85968907][https_blog.csdn.net_weixin_34372728_article_details_85968907]  
Oracle数据库 入门教程  
[https://blog.csdn.net/qq\_42589612/article/details/80943957][https_blog.csdn.net_qq_42589612_article_details_80943957]  
命令行(cmd) 登录Oracle数据库命令总结  
[https://blog.csdn.net/CQL\_K21/article/details/88688354][https_blog.csdn.net_CQL_K21_article_details_88688354]  
dos命令行连接操作ORACLE数据库  
[https://www.cnblogs.com/wanghongyun/p/6337143.html][https_www.cnblogs.com_wanghongyun_p_6337143.html]  
linux系统下创建oracle表空间和用户权限查询  
[https://www.cnblogs.com/gzggyy/p/3319315.html][https_www.cnblogs.com_gzggyy_p_3319315.html]

# 本地连接服务器上的oracle数据库 #

ORA-12514 TNS 监听程序当前无法识别  
[https://www.cnblogs.com/nichoc/p/6417505.html][https_www.cnblogs.com_nichoc_p_6417505.html]  
[https://www.cnblogs.com/qianyuliang/p/6520515.html][https_www.cnblogs.com_qianyuliang_p_6520515.html]  
3. listener.ora------listener监听器进程的配置文件  
关于listener进程就不多说了，接受远程对数据库的接入申请并转交给oracle的服务器进程。所以如果不是使用的远程的连接，listener进程就不是必需的，同样的如果关闭listener进程并不会影响已经存在的数据库连接。  
例子：  
\#listener.ora Network Configuration File: \#E:/oracle/product/10.1.0/Db\_2/NETWORK/ADMIN/listener.ora  
\#Generated by Oracle configuration tools.  
SID\_LIST\_LISTENER =  
(SID\_LIST =  
(SID\_DESC =  
(GLOBAL\_DBNAME = ora)  
(ORACLE\_HOME = E:/oracle/product/10.1.0/Db\_2)  
(SID\_NAME = ora)  
)  
)  
LISTENER =  
(DESCRIPTION =  
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))  
)  
LISTENER ：监听名称，可以配置多个监听，多个监听的端口号要区分开来。  
GLOBAL\_DBNAME ：全局数据库名。通过select \* from global\_name; 查询得出  
ORACLE\_HOME ：oracle软件的跟目录  
SID\_NAME ：服务器端（本机）的SID SELECT instance\_name FROM v i n s t a n c e ; P R O T O C O L ： 监 听 协 议 ， 一 般 都 使 用 T C P H O S T ： 本 机 I P 地 址 ， 双 机 时 候 使 用 浮 动 I P P O R T ： 监 听 的 端 口 号 ， 使 用 n e t s t a t – a n 检 查 该 端 口 不 被 占 用 。 O R A − 12514 : T N S : 监 听 程 序 当 前 无 法 识 别 连 接 描 述 符 中 请 求 的 服 务 正 在 查 l s n r c t l s t a r t 命 令 找 不 到 也 有 可 能 是 配 置 用 户 的 的 命 令 切 换 到 o r a c l e 用 户 下 面 可 以 再 查 一 下 命 令 找 不 到 ， 一 定 是 环 境 变 量 没 有 配 置 对 ， 或 者 由 于 没 有 正 常 关 闭 数 据 库 造 成 的 。 我 的 操 作 ： e x p o r t P A T H = / h o m e / o r a c l e / a p p / o r a c l e / p r o d u c t / 11.2.0 / d b h o m e 1 / b i n : instance; PROTOCOL：监听协议，一般都使用TCP HOST：本机IP地址，双机时候使用浮动IP PORT：监听的端口号，使用netstat –an 检查该端口不被占用。 ORA-12514: TNS:监听程序当前无法识别连接描述符中请求的服务 正在查 lsnrctl start 命令找不到 也有可能是配置用户的的命令 切换到oracle用户下面 可以再查一下 命令找不到，一定是环境变量没有配置对，或者由于没有正常关闭数据库造成的。我的操作： export PATH=/home/oracle/app/oracle/product/11.2.0/dbhome\_1/bin: instance;PROTOCOL：监听协议，一般都使用TCPHOST：本机IP地址，双机时候使用浮动IPPORT：监听的端口号，使用netstat–an检查该端口不被占用。ORA−12514:TNS:监听程序当前无法识别连接描述符中请求的服务正在查lsnrctlstart命令找不到也有可能是配置用户的的命令切换到oracle用户下面可以再查一下命令找不到，一定是环境变量没有配置对，或者由于没有正常关闭数据库造成的。我的操作：exportPATH=/home/oracle/app/oracle/product/11.2.0/dbhome1/bin:PATH  
export ORACLE\_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome\_1  
export ORACLE\_SID=orcl  
使用oracle用户临时配置上这三个环境变量，就可以正常的 lsnrctl start 启动监听。  
$ sqlplus /nolog //登录sqlplus  
SQL> connect /as sysdba //连接oracle  
SQL> startup //起动数据库  
妥了！  
原文：[https://blog.csdn.net/sdewendong/article/details/80978119][https_blog.csdn.net_sdewendong_article_details_80978119]  
正确理解tnsnames.ora中的service\_name  
[https://blog.csdn.net/qq\_38136705/article/details/80211350][https_blog.csdn.net_qq_38136705_article_details_80211350]  
SQL> show parameter service\_names  
NAME TYPE VALUE

--------------------

service\_names string [orcl11g.us.oracle.com][]  
SQL> show parameter db\_domain  
NAME TYPE VALUE

--------------------

db\_domain string [us.oracle.com][]  
listener.ora/sqlnet.ora/tnsnames.ora配置文件详解  
[https://www.cnblogs.com/qianyuliang/p/6520515.html][https_www.cnblogs.com_qianyuliang_p_6520515.html]  
如何查看Oracle数据库的SID  
[https://jingyan.baidu.com/article/f25ef2549a7e58482c1b82f7.html][https_jingyan.baidu.com_article_f25ef2549a7e58482c1b82f7.html]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 1]  
虚拟机里面 连接这个成功了  
conn hc\_notify/hc\_password@orcl11g  
ora–12154 ：TNS :could not resolve the connect identifier specified 错误处理  
感觉这个靠谱  
[https://blog.csdn.net/loadrunn/article/details/7895726][https_blog.csdn.net_loadrunn_article_details_7895726]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][20190716104347162.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 5]  
连接的SERVICE\_NAME 就是数据库里面的 service\_name

感觉这个有用  
[https://www.cnblogs.com/nichoc/p/6417505.html][https_www.cnblogs.com_nichoc_p_6417505.html]  
[https://blog.csdn.net/qq\_38136705/article/details/80211350][https_blog.csdn.net_qq_38136705_article_details_80211350]  
EM将配置数据上载到资料档案库出错  
[https://www.cnblogs.com/liuguosong/articles/5804497.html][https_www.cnblogs.com_liuguosong_articles_5804497.html]  
连接服务器oracle数据库 ORA-12170: TNS:连接超时  
一般是服务器的端口没开 可以使用  
若是要通过telnet查某个端口是否打开，可以这样做：  
如要查看 127.0.0.1 这个IP的 80 端口是否开启，可以在打开的dos命令行中输入 telnet 127.0.0.1 80，然后回车，若是出现以下这样，则证明这个端口开启了，也链接成功了。  
测试一下  
TELNET查看端口是否通

1.  查看端口是否通畅 telnet IP 端口号 如:telnet 10.150.159.71 5516  
    2.查看本机是否开启某个端口:netstat -an |findstr “端口号” 或 netstat -na |find “端口号”  
    端口和IP的区别:  
    PL/SQL连不上,报 ORA-12170:TNS 连接超时  
    [https://www.cnblogs.com/gw666/p/7889287.html][https_www.cnblogs.com_gw666_p_7889287.html]

# 导入导出数据报错参考 #

1.取消dba权限  
2.先执行导出的用户对象表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 6]  
IMP-00017: 由于 ORACLE 错误 6550, 以下语句失败:  
“DECLARE SREC DBMS\_STATS.STATREC; BEGIN SREC.MINVAL := ‘09’; SREC.MAXVAL :=”  
" ‘F5FC2EB1F0C0EB2020313532313338313236300D0A0D0AB9ABCBBED7E9D6AFD4’; SREC.E"  
“AVS := 4; SREC.CHVALS := DBMS\_STATS.CHARARRAY(’ 访客IP： 113.91.210.240 (查”  
“看’,’ 8-27九月份报，到时QQ找我，这’,’ 5-13挂断’,’ 11-4 停机了’,’ 5-25 无”  
“接’,’ 电话无人接听’,’-------------------------------  
',‘1’,'1,10在太原，要面”  
"授 不要直播可以’,‘1-13 无接’,'1-17没有打通过  
IMP-00017: 由于 ORACLE 错误 6550解决办法  
[https://blog.csdn.net/qq\_42909551/article/details/81985498][https_blog.csdn.net_qq_42909551_article_details_81985498]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 7]  
. . 正在导入表 “XY\_XYDA”  
IMP-00058: 遇到 ORACLE 错误 3113  
ORA-03113: 通信通道的文件结尾  
进程 ID: 16135  
会话 ID: 17 序列号: 3022  
IMP-00000: 未成功终止导入  
Import finished on 2019/7/9 17:11:24  
解决办法  
这个可能也是表空间大小 设置小了  
这个靠谱  
ORA-00257归档日志写满的解决方法  
[https://www.cnblogs.com/xwdreamer/p/3804509.html][https_www.cnblogs.com_xwdreamer_p_3804509.html]  
![在这里插入图片描述][2019071610462810.png]  
先导入用户表结构 再导入表数据  
解决oracle数据库 ora-00054:resource busy and acquire with NOWAIT specified 错误  
[https://www.cnblogs.com/jiafuwei/p/7054545.html][https_www.cnblogs.com_jiafuwei_p_7054545.html]  
解决ORA-00054: resource busy and acquire with NOWAIT specified需要注意  
[https://blog.csdn.net/stevendbaguo/article/details/23652959][https_blog.csdn.net_stevendbaguo_article_details_23652959]  
oracle报ORA-00054资源繁忙错误解决办法  
[https://jingyan.baidu.com/article/d621e8da6f59a42864913f7b.html][https_jingyan.baidu.com_article_d621e8da6f59a42864913f7b.html]  
Oracle 11g 删除归档日志  
[https://www.cnblogs.com/kofxxf/p/3614104.html][https_www.cnblogs.com_kofxxf_p_3614104.html]  
Oracle错误——ORA-03113:通信通道的文件结尾 解决办法  
[https://blog.csdn.net/evilcry2012/article/details/52788267][https_blog.csdn.net_evilcry2012_article_details_52788267]  
ORACLE归档与日志的路径查看  
2018-11-19 13:37:30 llj13258438698 阅读数 1132  
数据库归档路径查看命令  
sql>archive log list;  
日志路径查看  
sql>show parameter dump\_dest  
文件为.tec结尾的文件  
2，如果不是在默认位置，则可通过sql查询日志文件位置：  
SQL> show parameter dump\_dest  
NAME TYPE

--------------------

## VALUE ##

background\_dump\_dest string  
/home/oracle/oracle/admin/TESTDB/bdump  
core\_dump\_dest string  
/home/oracle/oracle/admin/TESTDB/cdump  
user\_dump\_dest string  
/home/oracle/oracle/admin/TESTDB/udump  
其中background\_dump\_dest的value值即为日志文件存放位置  
oracle数据库经常提示27102 out of memory问题的解决方法  
2018-02-13 10:06:56 来源：标配的小号  
收藏 我要投稿  
网上有很多解决方案，但是我一个也看不懂，不知道写的什么鬼的东西，为什么一句话就能解决的事，非得长篇大论说原理是什么，看得一脸懵逼  
我的解决方法入戏  
vi /etc/sysctl.conf  
修改kernel.shmall(共享内存总量)的值，该值原来为2097152，我把它给为4097152(暂时不知道应该改什么值，但是把这个值变大就对了，我就是随便吧第一位的2改为4，然后问题就自动解决)了，然后sqlplus / as sysdba  
执行select status from v$instance;没有再提示out of memory错误，而是正常显示open  
ORACLE删除当前用户下所有的表的方法  
[https://www.cnblogs.com/franson-2016/p/5925809.html][https_www.cnblogs.com_franson-2016_p_5925809.html]  
1、如果有删除用户的权限，则可以：  
drop user user\_name cascade;  
加了cascade就可以把用户连带的数据全部删掉。  
删除后再创建该用户。  
–创建管理员用户  
create user 用户名 identified by 密码 default tablespace space\_data（表空间名称） temporary tablespace space\_temp（临时表空间名称）;  
–授权  
grant connect,dba to 用户名;  
–修改限额  
ALTER USER “用户名” QUOTA UNLIMITED ON SPACE\_DATA(表空间名称);  
–查看所有用户对象  
select uo.object\_name,uo.object\_type from user\_objects uo where uo.object\_type<>‘LOB’ order by uo.object\_type desc

2、如果没有删除用户的权限，则可以执行：  
select ‘drop table ‘||table\_name||’;’  
from cat  
where table\_type=‘TABLE’  
将会输出一批删除表的sql语句，这些SQL语句执行一下就可以了。（需要有drop table的权限）  
解决ORA-01012:not logged on  
2013年10月10日 09:54:00 weixin\_34006468 阅读数 24  
原文链接：[https://my.oschina.net/psuyun/blog/167583][https_my.oschina.net_psuyun_blog_167583]  
为什么80%的码农都做不了架构师？>>>  
kill oracle 进程 或者关掉oracle

ps -ef|grep ora\_dbw0\_$ORACLE\_SID  
kill -9 pid  
重新启动oracle  
sqlplus sys as sysdba  
startup;  
转载于:[https://my.oschina.net/psuyun/blog/167583][https_my.oschina.net_psuyun_blog_167583]  
. . 正在导入表 "XY\_TFXX"导入了 34 行  
. . 正在导入表 "XY\_XCQD"导入了 9108 行  
. . 正在导入表 “XY\_XYDA”  
IMP-00058: 遇到 ORACLE 错误 3113  
ORA-03113: 通信通道的文件结尾  
进程 ID: 26976  
会话 ID: 9 序列号: 11  
IMP-00000: 未成功终止导入  
Import finished on 2019/7/10 17:38:12  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 8]  
可能是表空间大小设置小了  
Oracle数据库查看表空间sql语句  
[https://blog.csdn.net/zejunwzj/article/details/82349839][https_blog.csdn.net_zejunwzj_article_details_82349839]  
ORACLE system表空间满  
[https://www.cnblogs.com/Snowfun/p/8555085.html][https_www.cnblogs.com_Snowfun_p_8555085.html]  
添加数据文件时使用已存在的文件名的方法 ORA-00933  
[https://blog.csdn.net/lk\_db/article/details/78529841][https_blog.csdn.net_lk_db_article_details_78529841]  
ORA-01940 无法删除当前已连接的用户之解决方案  
[https://www.cnblogs.com/lwlxqlccc/p/8694696.html][https_www.cnblogs.com_lwlxqlccc_p_8694696.html]  
ORA-01940: cannot drop a user that is currently connected  
[https://blog.csdn.net/xionglang7/article/details/7804639][https_blog.csdn.net_xionglang7_article_details_7804639]  
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0  
Connected as crm1@TESTNEW  
SQL>  
SQL> SELECT  
2 a.tablespace\_name “表空间名”,  
3 total “表空间大小”,  
4 free “表空间剩余大小”,  
5 (total - free) “表空间使用大小”,  
6 total / (1024 \* 1024 \* 1024) “表空间大小(G)”,  
7 free / (1024 \* 1024 \* 1024) “表空间剩余大小(G)”,  
8 (total - free) / (1024 \* 1024 \* 1024) “表空间使用大小(G)”,  
9 round((total - free) / total, 4) \* 100 “使用率 %”  
10 FROM (SELECT tablespace\_name, SUM(bytes) free  
11 FROM dba\_free\_space  
12 GROUP BY tablespace\_name) a,  
13 (SELECT tablespace\_name, SUM(bytes) total  
14 FROM dba\_data\_files  
15 GROUP BY tablespace\_name) b  
16 WHERE  
17 a. tablespace\_name = b.tablespace\_name;  
表空间名 表空间大小 表空间剩余大小 表空间使用大小 表空间大小(G) 表空间剩余大小(G) 表空间使用大小(G) 使用率 %

--------------------

SYSAUX 639631360 34144256 605487104 0.595703125 0.03179931640625 0.56390380859375 94.66  
UNDOTBS1 382730240 355008512 27721728 0.3564453125 0.33062744140625 0.02581787109375 7.24  
USERS 5242880 3866624 1376256 0.0048828125 0.00360107421875 0.00128173828125 26.25  
SYSTEM 723517440 8192000 715325440 0.673828125 0.00762939453125 0.66619873046875 98.87  
CRM 1468006400 459014144 1008992256 1.3671875 0.427490234375 0.939697265625 68.73  
SQL> SELECT  
2 a.tablespace\_name “表空间名”,  
3 total “表空间大小”,  
4 free “表空间剩余大小”,  
5 (total - free) “表空间使用大小”,  
6 total / (1024 \* 1024 \* 1024) “表空间大小(G)”,  
7 free / (1024 \* 1024 \* 1024) “表空间剩余大小(G)”,  
8 (total - free) / (1024 \* 1024 \* 1024) “表空间使用大小(G)”,  
9 round((total - free) / total, 4) \* 100 “使用率 %”  
10 FROM (SELECT tablespace\_name, SUM(bytes) free  
11 FROM dba\_free\_space  
12 GROUP BY tablespace\_name) a,  
13 (SELECT tablespace\_name, SUM(bytes) total  
14 FROM dba\_data\_files  
15 GROUP BY tablespace\_name) b  
16 WHERE  
17 a. tablespace\_name = b.tablespace\_name;  
表空间名 表空间大小 表空间剩余大小 表空间使用大小 表空间大小(G) 表空间剩余大小(G) 表空间使用大小(G) 使用率 %

--------------------

SYSAUX 639631360 38076416 601554944 0.595703125 0.03546142578125 0.56024169921875 94.05  
UNDOTBS1 1751121920 1701576704 49545216 1.630859375 1.584716796875 0.046142578125 2.83  
USERS 5242880 3866624 1376256 0.0048828125 0.00360107421875 0.00128173828125 26.25  
WYZCDB 104857600 103809024 1048576 0.09765625 0.0966796875 0.0009765625 1  
SYSTEM 723517440 1507328 722010112 0.673828125 0.00140380859375 0.67242431640625 99.79  
NOTIFYDB 104857600 103677952 1179648 0.09765625 0.0965576171875 0.0010986328125 1.13  
CRM 1887436800 878444544 1008992256 1.7578125 0.818115234375 0.939697265625 53.46  
7 rows selectedSELECT \* FROM (SELECT SEGMENT\_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA\_SEGMENTS WHERE TABLESPACE\_NAME = ‘SYSTEM’ GROUP BY SEGMENT\_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;

SQL> SELECT \* FROM (SELECT SEGMENT\_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA\_SEGMENTS WHERE TABLESPACE\_NAME = ‘SYSTEM’ GROUP BY SEGMENT\_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;  
SEGMENT\_NAME MB

--------------------

IDL\_UB1$ 240  
SOURCE$ 64  
IDL\_UB2$ 30  
C\_TOID\_VERSION\# 23  
C\_OBJ\#\_INTCOL\# 14  
C\_OBJ\# 12  
I\_SOURCE1 select tablespace\_name, sum(bytes) / 1024 / 1024 from dba\_data\_files group by tablespace\_name; 12  
ARGUMENT$ 11  
JAVA M C MC MC 11  
9 rows selected

SQL> select tablespace\_name, sum(bytes) / 1024 / 1024 from dba\_data\_files group by tablespace\_name;  
TABLESPACE\_NAME SUM(BYTES)/1024/1024

--------------------

UNDOTBS1 1670  
SYSAUX 610  
USERS 5  
WYZCDB 100  
SYSTEM 690  
NOTIFYDB 100  
CRM 1800  
7 rows selected  
SQL>  
oracle数据库当前被连接的用户无法删除怎么办  
[https://jingyan.baidu.com/article/27fa732651a8c346f8271f90.html][https_jingyan.baidu.com_article_27fa732651a8c346f8271f90.html]  
oracle-system表空间已满，如何解决？  
[https://blog.csdn.net/liuyanggo/article/details/80940403][https_blog.csdn.net_liuyanggo_article_details_80940403]  
Oracle 11g用exp无法导出空表的处理方法  
[https://www.cnblogs.com/xusweeter/p/5837437.html][https_www.cnblogs.com_xusweeter_p_5837437.html]

[https_blog.csdn.net_ageeklet_article_details_81603482]: https://blog.csdn.net/ageeklet/article/details/81603482
[https_blog.csdn.net_shushugood_article_details_53020408]: https://blog.csdn.net/shushugood/article/details/53020408
[https_blog.csdn.net_zq199692288_article_details_78871046]: https://blog.csdn.net/zq199692288/article/details/78871046
[http_www.bubuko.com_infodetail-1714256.html]: http://www.bubuko.com/infodetail-1714256.html
[https_blog.csdn.net_qq_40687433_article_details_84236363]: https://blog.csdn.net/qq_40687433/article/details/84236363
[https_blog.csdn.net_qq_35893120_article_details_80655645]: https://blog.csdn.net/qq_35893120/article/details/80655645
[https_kingxss.iteye.com_blog_1741076]: https://kingxss.iteye.com/blog/1741076
[https_www.cnblogs.com_killkill_archive_2010_09_10_1823690.html]: https://www.cnblogs.com/killkill/archive/2010/09/10/1823690.html
[https_blog.csdn.net_debimeng_article_details_79656458]: https://blog.csdn.net/debimeng/article/details/79656458
[https_blog.csdn.net_dba_monkey_article_details_60962322]: https://blog.csdn.net/dba_monkey/article/details/60962322
[https_blog.51cto.com_xjsunjie_538818]: https://blog.51cto.com/xjsunjie/538818
[https_blog.csdn.net_u013082095_article_details_64442837]: https://blog.csdn.net/u013082095/article/details/64442837
[https_www.cnblogs.com_sparkbj_articles_6146247.html]: https://www.cnblogs.com/sparkbj/articles/6146247.html
[https_blog.csdn.net_songyundong1993_article_details_53307931]: https://blog.csdn.net/songyundong1993/article/details/53307931
[https_blog.csdn.net_linucle_article_details_8560548]: https://blog.csdn.net/linucle/article/details/8560548
[https_blog.csdn.net_qq_27966627_article_details_51062822]: https://blog.csdn.net/qq_27966627/article/details/51062822
[https_www.cnblogs.com_thg999_p_9981766.html]: https://www.cnblogs.com/thg999/p/9981766.html
[20190716103958811.png]: /images/20211209/0ebe672d54aa40898abeb044bf489180.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70]: /images/20211209/dcabb7578370405ebcfc814a8b14703f.png
[http_www.mamicode.com_info-detail-1173983.html]: http://www.mamicode.com/info-detail-1173983.html
[https_blog.csdn.net_buffoon1900_article_details_51919869]: https://blog.csdn.net/buffoon1900/article/details/51919869
[https_www.cnblogs.com_gzggyy_p_3319315.html]: https://www.cnblogs.com/gzggyy/p/3319315.html
[https_blog.csdn.net_qq_42914528_article_details_81509853]: https://blog.csdn.net/qq_42914528/article/details/81509853
[https_www.cnblogs.com_fighter007_p_8287780.html]: https://www.cnblogs.com/fighter007/p/8287780.html
[https_blog.csdn.net_skyuck_article_details_83799440]: https://blog.csdn.net/skyuck/article/details/83799440
[https_www.cnblogs.com_zouhao_p_3627522.html]: https://www.cnblogs.com/zouhao/p/3627522.html
[https_blog.csdn.net_weixin_34372728_article_details_85968907]: https://blog.csdn.net/weixin_34372728/article/details/85968907
[https_blog.csdn.net_qq_42589612_article_details_80943957]: https://blog.csdn.net/qq_42589612/article/details/80943957
[https_blog.csdn.net_CQL_K21_article_details_88688354]: https://blog.csdn.net/CQL_K21/article/details/88688354
[https_www.cnblogs.com_wanghongyun_p_6337143.html]: https://www.cnblogs.com/wanghongyun/p/6337143.html
[https_www.cnblogs.com_nichoc_p_6417505.html]: https://www.cnblogs.com/nichoc/p/6417505.html
[https_www.cnblogs.com_qianyuliang_p_6520515.html]: https://www.cnblogs.com/qianyuliang/p/6520515.html
[https_blog.csdn.net_sdewendong_article_details_80978119]: https://blog.csdn.net/sdewendong/article/details/80978119
[https_blog.csdn.net_qq_38136705_article_details_80211350]: https://blog.csdn.net/qq_38136705/article/details/80211350
[orcl11g.us.oracle.com]: http://orcl11g.us.oracle.com
[us.oracle.com]: http://us.oracle.com
[https_jingyan.baidu.com_article_f25ef2549a7e58482c1b82f7.html]: https://jingyan.baidu.com/article/f25ef2549a7e58482c1b82f7.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 1]: /images/20211209/4e6cf31fdb244fc589b5635cac49bccb.png
[https_blog.csdn.net_loadrunn_article_details_7895726]: https://blog.csdn.net/loadrunn/article/details/7895726
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190716104320490.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190716104331713.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM=,size_16,color_FFFFFF,t_70
[20190716104347162.png]: https://img-blog.csdnimg.cn/20190716104347162.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190716104401104.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20190716104415374.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM=,size_16,color_FFFFFF,t_70
[https_www.cnblogs.com_liuguosong_articles_5804497.html]: https://www.cnblogs.com/liuguosong/articles/5804497.html
[https_www.cnblogs.com_gw666_p_7889287.html]: https://www.cnblogs.com/gw666/p/7889287.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20190716104558584.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM=,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_qq_42909551_article_details_81985498]: https://blog.csdn.net/qq_42909551/article/details/81985498
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20190716104617381.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM=,size_16,color_FFFFFF,t_70
[https_www.cnblogs.com_xwdreamer_p_3804509.html]: https://www.cnblogs.com/xwdreamer/p/3804509.html
[2019071610462810.png]: https://img-blog.csdnimg.cn/2019071610462810.png
[https_www.cnblogs.com_jiafuwei_p_7054545.html]: https://www.cnblogs.com/jiafuwei/p/7054545.html
[https_blog.csdn.net_stevendbaguo_article_details_23652959]: https://blog.csdn.net/stevendbaguo/article/details/23652959
[https_jingyan.baidu.com_article_d621e8da6f59a42864913f7b.html]: https://jingyan.baidu.com/article/d621e8da6f59a42864913f7b.html
[https_www.cnblogs.com_kofxxf_p_3614104.html]: https://www.cnblogs.com/kofxxf/p/3614104.html
[https_blog.csdn.net_evilcry2012_article_details_52788267]: https://blog.csdn.net/evilcry2012/article/details/52788267
[https_www.cnblogs.com_franson-2016_p_5925809.html]: https://www.cnblogs.com/franson-2016/p/5925809.html
[https_my.oschina.net_psuyun_blog_167583]: https://my.oschina.net/psuyun/blog/167583
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20190716104645728.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnNpamlhMjE5ODM=,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_zejunwzj_article_details_82349839]: https://blog.csdn.net/zejunwzj/article/details/82349839
[https_www.cnblogs.com_Snowfun_p_8555085.html]: https://www.cnblogs.com/Snowfun/p/8555085.html
[https_blog.csdn.net_lk_db_article_details_78529841]: https://blog.csdn.net/lk_db/article/details/78529841
[https_www.cnblogs.com_lwlxqlccc_p_8694696.html]: https://www.cnblogs.com/lwlxqlccc/p/8694696.html
[https_blog.csdn.net_xionglang7_article_details_7804639]: https://blog.csdn.net/xionglang7/article/details/7804639
[https_jingyan.baidu.com_article_27fa732651a8c346f8271f90.html]: https://jingyan.baidu.com/article/27fa732651a8c346f8271f90.html
[https_blog.csdn.net_liuyanggo_article_details_80940403]: https://blog.csdn.net/liuyanggo/article/details/80940403
[https_www.cnblogs.com_xusweeter_p_5837437.html]: https://www.cnblogs.com/xusweeter/p/5837437.html