锁表问题

--dba权限下执行
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; 
--停止锁住的表
alter system kill session 'sid值,serial#值';   
1
2
3
4
5
6

获取某用户下所有的表名/视图、所属用户、表/视图备注

--获取某用户下所有的表名/视图、所属用户、表/视图备注  (普通用户权限即可)
select   *   from   all_tab_comments   where owner=upper('用户名') ;
1
2

获取某用户 某表下的:字段名、字段注释、字段类型

-- 获取某用户 某表下的:字段名、字段注释、字段类型  (普通用户权限即可)
select a.column_name,a.comments,b.data_type from
(select   column_name,comments   from   all_col_comments   where   table_name=upper('表名')  AND owner=upper('用户名') ) a,
(select column_name,data_type||'('||DATA_LENGTH||')' data_type  From all_tab_columns
where table_name=upper('与上述表名一致') AND owner=upper('与上述用户一致') )b 
where a.column_name=b.column_name;
1
2
3
4
5
6
7

将当前用户下 某表下所有表字段名合并成一列

-- 将当前用户下 某表下所有表字段名合并成一列(主要用于在写动态SQL上)
SELECT LISTAGG(column_name,',') WITHIN GROUP(ORDER BY column_name) AS columns
FROM (select column_name from user_tab_columns where Table_Name=upper('表名'));
1
2
3

随机获取表中的一条或0条数据

--随机获取表中的一条或0条数据
SELECT * FROM 表名 SAMPLE (1) WHERE ROWNUM = 1
1
2

获取当前用户下某表下的所有字段

-- 获取当前用户下某表下的所有字段
select column_name from user_tab_columns where Table_Name=upper('表名') order by COLUMN_NAME;
1
2

数据备份

备份表

导出：exp 用户名/用户密码@数据库服务器地址/orcl file=dmp地址
导入：imp 用户名/用户密码@数据库服务器地址/orcl file=dmp地址 full=y
1
2

只能在服务器端使用

-- 导出命令
expdp 用户名/密码@数据库IP:1521/orcl file=导出的目标文件名称.dmp tables=要导出的表名(多个表时用逗号隔开)

-- 导入命令
impdp 用户名/密码@数据库IP:1521/orcl file=导入的目标文件名称.dmp tables=要导入的表名(多个表时用逗号隔开)
1
2
3
4
5

备份用户

-- 导出指定用户1数据
exp system/manager@ip地址/orcl owner=用户名1 file=文件地址\文件名.dmp
-- 将用户1的数据导入进用户2
imp system/managerb@ip地址/orcl  file=文件地址\文件名.dmp fromuser=用户名1 touser=用户名2


1
2
3
4
5
6

ORACLE查询一张表的字段信息并且与原表字段顺序保存一致

使用场景 ： 当我们需要动态的去查询一些数据并且将数据按表原有的字段进行排序的时候

现在要将 TAX_70 的字段按照 TAX_70 表字段顺序插入到 TEST_FIELDS中；

我们可以看到查询出来的数据是与TAX_70 字段顺序一致的，现在将其插入进 TEST_FIELDS中；


这个时候可能发生不一致，所以一开始我们就可以带上序号

INSERT INTO TEST_FIELDS(FIELD,ORDER_NUM)
select a.COLUMN_NAME,a.column_id  
from USER_TAB_COLUMNS a,all_col_comments b   
where a.table_name=upper('TAX_70') 
and a.table_name=b.TABLE_NAME 
and a.column_name=b.COLUMN_NAME 
and b.OWNER=upper('TRANS') 
order by column_id; 
1
2
3
4
5
6
7
8

---

恢复误删的数据

delete from TEST where id=3;
commit;

select * from v$sql where sql_text like '%误删表数据的表名%' ;--得到时间

select * from 误删表数据的表名 as of timestamp to_timestamp('误删表数据时的时间','yyyy-mm-dd hh24:mi:ss')
where (删除表数据时所给的条件);

insert into 误删表数据的表名 (select * from 误删表数据的表名 as of timestamp to_timestamp('误删表数据的时间','yyyy-mm-dd hh24:mi:ss') where 误删表数据的条件);

1
2
3
4
5
6
7
8
9
10

修改数据库用户用户名

以windows 为例
(linux 比window多两步，使用ssh工具以root用户连接服务器，然后使用 su - oracle 切换到oracle用户 )

1. 使用sqlplus 连接数据库： sqlplus /nolog
2. 以管理员身份登录 conn sys/sys as sysdba
   
3. 查询所需要修改的用户名称：select user#,name from user$;
   
4. 例如我们现在将要修改 TEST 为 TEST_BACK；update user$ set name='TEST_BACK' where user#=87;
5. 提交：commit;
   
6. 不要忘记修改密码 alter user TEST_BACK IDENTIFIED BY test_back;
7. 提交 ： commit;
   
8. 测试 conn TEST_BACK/test_back
   
   修改成功

删除用户

--删除指定的用户
drop user username cascade
1
2