-- 查询锁表 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 'alter system kill session '''||b.sid||','||b.serial#||''';' from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
-- 查看数据文件和表空间对应的大小 -- 数据文件最大32767M select file_id,file_name,tablespace_name,status,bytes/1024/1024 as "bytes MB" from dba_data_files -- 为表空间增加数据文件,并开启自动增长 ALTER TABLESPACE user ADD DATAFILE 'D:\APP\RONGHI\ORADATA\ORCL\USERS02.DBF' SIZE 1G AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED; -- 设置数据文件自动增长 -- alter database datafile '/oracle/oraoms/user.dbf' autoextend on;
-- 关联更新数据 https://www.cnblogs.com/relucent/p/4166544.html MERGE INTO TEST T1 USING (SELECT '2' as ID, 'newtest2' as NAME FROM dual) T2 on (T1.ID=T2.ID) WHEN MATCHED THEN UPDATE SET T1.NAME=T2.NAME WHEN NOT MATCHED THEN INSERT (T1.ID, T1.NAME) VALUES (T2.ID, T2.NAME );
-- 空表处理 select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
-- 列转行
select listagg(sysdate,',')within group(order by 1)name from dual