文章标签Oracle

删除oracle表空间

0

删除oracle表空间有如下2种情况:

第一,物理文件和逻辑文件都存在,这个时候通过以下命令就可以在删除对应表空间

DROP TABLESPACE 表空间名称 including CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

例如:DROP TABLESPACE boss including CONTENTS AND DATAFILES CASCADE CONSTRAINTS; (更多…)

oracle 查询锁表语句

0

SELECT A.OWNER 方案名,
A.OBJECT_NAME 表名,
B.XIDUSN 回滚段号,
B.XIDSLOT 槽号,
B.XIDSQN 序列号,
B.SESSION_ID 锁表SESSION_ID,
B.ORACLE_USERNAME 锁表用户名,
decode(D.type,
‘XR’,
‘NULL’,
‘RS’,
‘SS(Row-S)’,
‘CF’,
‘SS(Row-S)’,
‘TM’,
‘TABLE LOCK’,
‘PW’,
‘TABLE LOCK’,
‘TO’,
‘TABLE LOCK’,
‘TS’,
‘TABLE LOCK’,
‘RT’,
‘ROW LOCK’,
‘TX’,
‘ROW LOCK’,
‘MR’,
‘S(Share)’,
NULL) 锁定方式,
C.MACHINE 用户组,
C.TERMINAL 机器名,
B.OS_USER_NAME 系统用户名,
B.PROCESS 系统进程id,
DECODE(C.STATUS, ‘INACTIVE’, ‘不活动’, ‘ACTIVE’, ‘活动’) 活动情况,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM 连接方式,
C.LOGON_TIME
FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock d
WHERE (A.OBJECT_ID = B.OBJECT_ID)
AND (B.PROCESS = C.PROCESS)
and C.sid = d.sid
and B.LOCKED_MODE = D.LMODE
ORDER BY 1, 2;

Oracle over() 用法

0

–select * from tmn_zhbg order by id desc

select id, mc,fs,class,sum (fs) over (order by fs,id) 连续累加,sum (fs) over (order by fs) 连续累加XX,sum (fs) over (partition by id ) 分组求和,
sum (fs) over (partition by id order by fs ) 分组id连续
from
(
select 1 id,’yw’ mc,50 fs,1 class from dual
union all
select 1 , ‘sx’ , 81,1 from dual
union all
select 1 , ‘yy’ , 20,1 from dual
union all
select 2 , ‘yw’ , 81,1 from dual
union all
select 2 , ‘sx’ , 32,1 from dual
union all
select 2 , ‘yy’ , 21,1 from dual
union all
select 4 , ‘yw’ , 10,1 from dual
union all
select 4 , ‘sx’ , 82,1 from dual
union all
select 4 , ‘yy’ , 20,1 from dual
) a

Oracle是谁锁了表?

0

在多人协同开发中,往数据库中添加数据时常常会遇到有人锁定了整张表,查查谁锁掉表的用下面的语句吧!

直接在PL/SQL中新建一个SQL窗口运行即可。 (更多…)

Oracle 导表

0

exp 用户名/密码@配置文件 file=d:\daochu.dmp tables=( 欲导出的表用逗号分开)  full=y
imp 用户名/密码@配置文件  file=d:\daochu.dmp full=y

注:以上命令是在cmd下执行的

Oracle ALTER TABLE

0

常规的修改表结构使用以下语句:

添加一列:ALTER TABLE (表名) ADD (列名 数据类型);
修改字段:[置空修改:UPDATE (表名)  SET (列名)=NULL;] ALTER TABLE (表名) MODIFY (列名 数据类型);
字段改名:ALTER TABLE (表名) RENAME COLUMN (当前列名) TO (新列名);
删除字段:ALTER TABLE (表名) DROP COLUMN (列名);
表改名:ALTER TABLE (当前表名) RENAME TO (新表名);

(更多…)

回到顶部