【Oracle】Oracle常用指令

1. 增删改字段

# 增加字段
alter table test_number_yst add (id_test number(38,-84));
# 修改字段
alter table tablename modify (id_test number(38,-84));
# 删除字段
alter table tablename drop (id_test);

2. 查询用户所有表

select * from tabs;
select * from user_tables;
# 查看表创建时间
select object_name,created from user_objects where object_name=upper('&table_name');
# 查看表大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');

3. bfile字段建表

# 创建目录
mkdir -p /tmp/bfile/
# 在目录下新建文件
vi test1.txt
# oracle创建directory：
create directory BFILEDIR as '/tmp/bfile';
# oracle建表
create table falt_bfile ( id number, bfile_name bfile);
# 插入数据
insert into falt_bfile values (1, BFILENAME('BFILEDIR','test1.txt')); commit;

4. 锁表问题定位

# 相关被锁表查询
SELECT * FROM V$LOCK;
SELECT * FROM V$SQLAREA;
SELECT * FROM V$SESSION;
SELECT * FROM V$PROCESS;
SELECT * FROM V$LOCKED_OBJECT;
SELECT * FROM ALL_OBJECTS;
SELECT * FROM V$SESSION_WAIT;
# 查看被锁的表
SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE FROM V$LOCKED_OBJECT A, DBA_OBJECTS B WHERE B.OBJECT_ID = A.OBJECT_ID;
# 查看哪个用户哪个进程造成死锁：
SELECT B.USERNAME, B.SID, B.SERIAL#, LOGON_TIME FROM V$LOCKED_OBJECT A, V$SESSION B WHERE A.SESSION_ID = B.SID ORDER BY B.LOGON_TIME;
# 查看连接的进程：
SELECT SID, SERIAL#, USERNAME, OSUSER FROM V$SESSION;
# 查看数据库引起锁表的SQL语句：
SELECT A.USERNAME, A.MACHINE, A.PROGRAM, A.SID, A.SERIAL#, A.STATUS, C.PIECE, C.SQL_TEXT  FROM V$SESSION A, V$SQLTEXT C  WHERE A.SID IN (SELECT DISTINCT T2.SID FROM V$LOCKED_OBJECT T1, V$SESSION T2  WHERE T1.SESSION_ID = T2.SID)  AND A.SQL_ADDRESS = C.ADDRESS(+)  ORDER BY C.PIECE;