select * from v$version
SELECT machine ,terminal FROM V$SESSION;
select * from all_tables where table_name like '%'
select * from all_tab_columns where table_name='??'
怎样计算一个表占用的空间的大小?
select owner,table_name,NUM_ROWS,BLOCKS*AAA/1024/1024 "Size M",EMPTY_BLOCKS,LAST_ANALYZED
from dba_tables
where table_name='XXX';
14. 如何查看系统被锁的事务时间?
select * from v$locked_object ;
16. 怎么获取有哪些用户在使用数据库?
select username from v$session;
怎样查得数据库的SID ?
select name from v$database;也可以直接查看 init.ora文件
如何在Oracle服务器上通过SQLPLUS查看本机IP地址 ?
select sys_context('userenv','ip_address') from dual;
如何将表移动表空间?
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;
v查询锁的状况的对象有?
V$LOCK, V$LOCKED_OBJECT, V$SESSION, V$SQLAREA, V$PROCESS ;
查询锁的表的方法:
SELECT S.SID SESSION_ID,S.USERNAME,DECODE(LMODE,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',TO_CHAR(LMODE)) MODE_HELD,DECODE(REQUEST,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',TO_CHAR(REQUEST)) MODE_REQUESTED,O.OWNER||'.'||O.OBJECT_NAME||' ('||O.OBJECT_TYPE||')',S.TYPE LOCK_TYPE,L.ID1 LOCK_ID1,L.ID2 LOCK_ID2 FROM V$LOCK L,SYS.DBA_OBJECTS O,V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID ;
30. 如何解锁?
ALTER SYSTEM KILL SESSION ‘SID,SERIR#’;
如何查看各个表空间占用磁盘情况?
SQL> col tablespace format a20
SQL> select
b.file_id 文件ID号,
b.tablespace_name 表空间名,
b.bytes 字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余空间,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id
请问如何在ORACLE中取毫秒?
9i之前不支持,9i开始有timestamp。
9i可以用select systimestamp from dual;
如何查出一条记录的最后更新时间?
可以用logminer 察看
如何对CLOB字段进行全文检索?
SELECT * FROM A WHERE dbms_lob.instr(a.a,'K',1,1)>0;
如何查有多少个数据库实例?
SQL>SELECT * FROM V$INSTANCE;
怎么把select出来的结果导到一个文本文件中?
SQL>SPOOL C:/ABCD.TXT;
SQL>select * from table;
SQL >spool off;
如何查找重复记录?
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);
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);
网友给的常用命令
最新推荐文章于 2024-09-25 11:06:23 发布