oracle-常用数据库问题解决

1、解决oracle 记录被另一用户锁住

select object_id,session_id,locked_mode from v$locked_object;

select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;

alter system kill session '2357,5644';
alter system kill session '163,23831';

2、ORA-01653 无法通过表空间扩展

说明表空间可能已经不够用了，此时可以扩展一下表空间的大小。

（1）、用system账户登陆plsql，或者其他可视化工具。（普通用户无法查看表空间文件）

（2）、查看表空间大小及使用情况（使用超过90%的建议可扩展）

SELECT a.tablespace_name "表空间名",
a.bytes / 1024 / 1024 "表空间大小(M)",
(a.bytes - b.bytes) / 1024 / 1024 "已使用空间(M)",
b.bytes / 1024 / 1024 "空闲空间(M)",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"
FROM (SELECT tablespace_name, sum(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC ;

（3）、查看具体表空间的位置

SELECT file_name,
tablespace_name,
bytes / 1024 / 1024 "bytes MB",
maxbytes / 1024 / 1024 "maxbytes MB"
FROM dba_data_files
WHERE tablespace_name = 'WHSFFZ_V2';

（4）、查看表空间是否自动扩展

SELECT file_id, file_name, tablespace_name, autoextensible, increment_by
FROM dba_data_files
WHERE tablespace_name = 'WHSFFZ_V2'
ORDER BY file_id desc;

上AUTOEXTENSIBLE为YES说明开启了自动扩展。

（5）、扩大表空间大小方法

（5.1）、方法一：扩大表空间文件的大小

----先查表空间文件的全路径

SELECT t.bytes / 1024 / 1024 "表空间大小(M)",t.* FROM dba_data_files t WHERE t.tablespace_name='GFPORTAL';

----重新设置表空间大小（上面看到1000M，以为设置为1001M，验证扩展1M）

alter database datafile 'D:\ORACLE_DATABASE\PRODUCT\11.2.0\DBHOME_1\DATABASE\GFPORTAL.DBF' resize 1001M;

----再次查询验证表空间大小修改后的情况

SELECT t.bytes / 1024 / 1024 "表空间大小(M)",t.* FROM dba_data_files t WHERE t.tablespace_name='GFPORTAL';

（5.2）、方法二：增加数据文件（需要确认磁盘空间大小是否足够）

----给需要扩展的表空间增加数据文件，size指定新数据文件大小，autoextend on maxsize 1M为开启数据文件自动扩展，每次扩展1M  （注：本例仅为测试验证，生产建议以G为单位扩展）

alter tablespace GFPORTAL add datafile 'D:\ORACLE_DATABASE\PRODUCT\11.2.0\DBHOME_1\DATABASE\GFPORTAL1.DBF' size 1M
autoextend on maxsize 1M;

----再次查询验证表空间大小修改后的情况，可以查看到该表空间对应有两个数据文件，说明上执行成功了。

SELECT t.bytes / 1024 / 1024 "表空间大小(M)",t.* FROM dba_data_files t WHERE t.tablespace_name='GFPORTAL';

---删除表空间数据文件（如果需要的话）

alter tablespace GFPORTAL
DROP datafile 'D:\ORACLE_DATABASE\PRODUCT\11.2.0\DBHOME_1\DATABASE\GFPORTAL1.DBF'

----再次查询验证表空间情况

SELECT t.bytes / 1024 / 1024 "表空间大小(M)",t.* FROM dba_data_files t WHERE t.tablespace_name='GFPORTAL';

（6）、查看表空间的创建语句

SELECT dbms_metadata.get_ddl('TABLESPACE', 'GFPORTAL') FROM dual;

（7）、创建表空间，将表空间赋给指定的用户

创建表空间

create tablespace AAA  datafile '/data/oracle/product/11.2.0/db_1/dbs/AAA.dbf' 
size 1000M autoextend on next 100M;

赋给用户

alter user  AAA identified by AAA default tablespace AAA;