1.如何查看系统被锁的事务时间
select * from v$locked_object ;
2.怎么获取有哪些用户在使用数据库
select username from v$session;
3.如何在Oracle服务器上通过SQLPLUS查看本机IP地址
select sys_context('userenv','ip_address') from dual;
4.何查询每个用户的权限
SELECT * FROM DBA_SYS_PRIVS;
5.如何将表移动表空间
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;
6.如何将索引移动表空间
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;
7.修改进程
alter system kill session 'sid,serial#';
8.查询当前用户对象
SELECT * FROM USER_OBJECTS;
9.如何获取错误信息
SELECT * FROM USER_ERRORS
10.给表、列加注释
comment on table 表 is '表注释';
comment on column 表.列 is '列注释';
11.修改oracel数据库的默认日期
alter session set nls_date_format='yyyymmddhh24miss';
12. 如何查询数据库有多少表
select * from all_tables;
13.查询某天的数据
select * from table_name where trunc(日期字段)＝to_date('2003-05-02','yyyy-mm-dd');
14. 插入全年日期
create table BSYEAR (d date);
insert into BSYEAR
select to_date('20030101','yyyymmdd')+rownum-1
from all_objects
where rownum <= to_char(to_date('20031231','yyyymmdd'),'ddd');
15.修改表名
alter table old_table_name rename to new_table_name;
16. 查找重复记录
SELECT * FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
17. 删除重复记录
DELETE FROM TABLE_NAME
WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D
WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
18.并行
/*+parallel(a,8) */ 查询 删除 时并行
/*+append(a,8)+/ 追加并行 添加时使用
19.查询所有表空间
select * from dba_tablespaces;
20.查看所有表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
21.已经使用的表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
22.所以使用空间可以这样计算
select a.tablespace_name,total,free,total-free used from
(select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name;
23.查看SEQUENCE：
SELECT SEQ_PRODUCT_DRAFT.nextval FROM dual
