常用oracle sql语句

--查询oracle中，被锁的表

SELECT object_name, machine, s.sid, s.serial#

FROM gv$locked_object l, dba_objects o, gv$session s

WHERE l.object_id　= o.object_id

AND l.session_id = s.sid; 

--解锁

ALTER system kill session '15, 2095'; 

------------------------------------------------------------------------------------------------------------------------------------------------------------------

--解决 ora-28000:the account is locked

C:\Users\Admin>sqlplus sys/test@192.168.1.1/orcl as sysdba
SQL> ALTER USER test ACCOUNT UNLOCK;

用户已更改。

SQL> commit;

提交完成。

SQL>

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------

设置oracle数据库空表导出

alter system set deferred_segment_creation=false;--设置空表分配空间

 

 

select table_name from user_tables t where t.num_rows = 0 or t.num_rows is null;--查询空表

 

select 'alter table '||table_name||' allocate extent;' from user_tables t where t.num_rows =0 or t.num_rows is null;--组装空表

------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

查看表数量

select count(*) from user_tables

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------

oracle中去掉回车换行空格的方法详解

去除换行

update zhzl_address t set t.add_administration_num=replace(t.add_administration_num,chr(10),'');

去掉回车

update zhzl_address t set t.add_administration_num=replace(t.add_administration_num,chr(13),'');

去掉空格

update zhzl_address t set t.add_administration_num=trim(t.add_administration_num);

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------

快速删除表数据

truncate table test

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------

--查找delete删除的表数据

ALTER TABLE testENABLE row movement ;

select * from testAS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '100' MINUTE)

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------

--查看当前登录用户下的所有表
select table_name from user_tables;

--查看表注释
select * from user_tab_comments ;

--查看表字段注释
select * from user_col_comments ;

--查看当前登录用户test的所有表名、字段、字段类型、长度
select table_name,column_name,data_type,DATA_LENGTH From all_tab_columns where owner=upper('test');

------------------------------------------------------------------------------------------------------------------------------------------------------------------