--查询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');
------------------------------------------------------------------------------------------------------------------------------------------------------------------