165 lines
4.9 KiB
Markdown
165 lines
4.9 KiB
Markdown
## 简介
|
||
记录在使用`oracle`过程在遇到的一些问题,我所用的操作系统是`kali rolling`
|
||
## 问题如下
|
||
* 在安装过程中遇到的监听器打开失败的问题
|
||
在`$ORACLE_HOME/network/admin/listerer.ora`中添加
|
||
```
|
||
STENER =
|
||
(DESCRIPTION_LIST =
|
||
(DESCRIPTION =
|
||
(ADDRESS = (PROTOCOL = TCP)(HOST = zeek)(PORT = 1521))
|
||
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
|
||
)
|
||
)
|
||
ADR_BASE_LISTENER = /home/u01/app/oracle
|
||
```
|
||
在`$ORACLE_HOME/network/admin/tnsnames.ora`中添加
|
||
```
|
||
ORAL =
|
||
(DESCRIPTION =
|
||
(ADDRESS = (PROTOCOL = TCP)(HOST =zeek)(PORT = 1521))
|
||
(CONNECT_DATA =
|
||
(SERVER = DEDICATED)
|
||
(SERVICE_NAME = ORAL)
|
||
)
|
||
)
|
||
```
|
||
在`/etc/hosts`中添加 (或者在`listener.ora`中host直接写成ip)
|
||
```
|
||
127.0.0.1 zeek
|
||
```
|
||
* 在创建表的过程中报错
|
||
```
|
||
ORA-65096: invalid common user or role name
|
||
```
|
||
产生原因是没有指定数据库,主要出现在可插拔式数据库中
|
||
解决方案
|
||
```sql
|
||
select con_id,dbid,name,open_mode from v$pdbs;
|
||
```
|
||
得到
|
||
```
|
||
CON_ID DBID NAME OPEN_MODE
|
||
2 3159417430 PDB$SEED READ ONLY
|
||
3 874804722 ORALP MOUNTED
|
||
```
|
||
接着在输入
|
||
```sql
|
||
alter session set container=ORALP;
|
||
```
|
||
* 数据库实例没注册到listener
|
||
报错信息如下，主要出现在用`sqlplus`或者`plsql`登录时报错
|
||
```
|
||
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
|
||
```
|
||
在listener.ora里面添加了一行
|
||
```
|
||
SID_LIST_LISTENER =
|
||
(SID_LIST =
|
||
(SID_DESC =
|
||
(GLOBAL_DBNAME = ORAL)
|
||
(SID_NAME = ORAL)
|
||
)
|
||
)
|
||
```
|
||
* scott.emp 表不存在
|
||
造成这个问题的原因是scott用户没有解锁，所以解决这个问题的办法就是解锁scott用户:
|
||
执行脚本`/rdbms/admin/utlsampl.sql `(执行这个脚本的脚本是因为在12c中可能没有这个用户)
|
||
接着执行下面语句
|
||
```sql
|
||
alter user scott account unlock;
|
||
alter user scott identified by tiger;
|
||
commit;
|
||
```
|
||
* 在执行`lsnrctl start`的时候出现如下报错
|
||
```
|
||
TNS-12535: TNS:operation timed out
|
||
TNS-12560: TNS:protocol adapter error
|
||
TNS-00505: Operation timed out
|
||
```
|
||
很可能是你自己不小心将`/etc/hosts` 的`127.0.0.1 zeek `删掉了，补上即可
|
||
* `dbms_output.put_line('content' )`中文乱码
|
||
在`~/.zshrc`或者`~/.bashrc`或者`/etc/profile` 中添加`export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"`
|
||
|
||
* 操作临时表时报错信息如下
|
||
```
|
||
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
|
||
```
|
||
解决办法：执行下面sql语句,得到一个sql语句,执行语句即可
|
||
```sql
|
||
select 'alter system kill session '''||sid||','||serial#||''''
|
||
from v$session
|
||
where sid in (
|
||
select sid
|
||
from v$lock
|
||
where id1 in (
|
||
select object_id
|
||
from dba_objects
|
||
where object_name in (
|
||
select table_name
|
||
from dba_tables
|
||
where owner='EODA')
|
||
)
|
||
);
|
||
```
|
||
* sqlplus 中文字符显示乱码或者显示?
|
||
登陆ORACLE
|
||
```sh
|
||
sqlplus eoda/foo
|
||
```
|
||
查询当前的数据库的字符集
|
||
```sql
|
||
SELECT * FROM nls_database_parameters t
|
||
WHERE t.parameter = 'NLS_CHARACTERSET';
|
||
```
|
||
我的ORACLE查询的结果`AL32UTF8`
|
||
在`.bash_profile`中配置(如果想要在除oracle用户下面也正常使用则需要在终端的配置文件里面添加下面内容)
|
||
```
|
||
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
|
||
```
|
||
* oracle 报错资源忙，报错信息如下
|
||
```
|
||
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
|
||
```
|
||
解决方案
|
||
```sql
|
||
select session_id from v$locked_object;
|
||
SELECT sid, serial#, username, osuser FROM v$session where sid = 142; -- sid 的值是上条语句查询出来的
|
||
ALTER SYSTEM KILL SESSION '142,38'; -- 值是 sid,serial#
|
||
```
|
||
* 修改会话支持pdml时，报错详细信息如下
|
||
```
|
||
ORA-12841: Cannot alter the session parallel DML state within a transaction
|
||
```
|
||
造成错误的主要原因是会话中存在未提交的事务，只需要在提交事务就行
|
||
```sql
|
||
commit;
|
||
alter session enable parallel dml;
|
||
```
|
||
* 在已经支持了pdml的会话中查看执行计划时，报错详细信息如下
|
||
```
|
||
ORA-12838: cannot read/modify an object after modifying it in parallel
|
||
```
|
||
造成错误的原因是存在未提交的事务
|
||
```sql
|
||
commit;
|
||
explain plan for update big_table set status = 'done';
|
||
select * from table(dbms_xplan.display(null,null, 'BASIC +PARALLEL'));
|
||
```
|
||
* 在对varchar2类型的字符串进行trim操作之后比较是否相等，需要注意trim结果是否为null
|
||
```sql
|
||
-- 比较字符串是否相同,执行结果为same
|
||
begin
|
||
IF trim(' ') <> trim('1') THEN
|
||
dbms_output.put_line(' not same');
|
||
ELSE
|
||
dbms_output.put_line('same');
|
||
END IF;
|
||
END;
|
||
/
|
||
```
|
||
可以选择使用
|
||
```sql
|
||
select decode(trim(' '),trim('1'),'same','not same') from dual;
|
||
```
|