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;
|
|||
|
|
```
|