oracle表分区12个月以及解锁_oracle如何解锁分区-CSDN博客

本文链接：https://blog.csdn.net/xinyoulinglei/article/details/8857464

drop table hello_bak cascade constraints

create table Thello(
GID           VARCHAR2(40) not null,
USERID    VARCHAR2(64),
NICKNAME VARCHAR2(512),
MSISDN        VARCHAR2(30) ,
EMAIL         VARCHAR2(512) ,
SESSIONID VARCHAR2(64),
STATUS NUMBER(38),
PROVINCE VARCHAR2(20),
CITY VARCHAR2(20),
OPERATETIME VARCHAR2(40),
LASTLOGINTIME VARCHAR2(40),
PARTITION_MONTH VARCHAR2(2) default to_char(sysdate,'mm') not null
)
partition by range (PARTITION_MONTH)
(
partition MEMBEROPERATE_PART_01 values less than ('02') tablespace IISS_LOG_DAT,
partition MEMBEROPERATE_PART_02 values less than ('03') tablespace IISS_LOG_DAT,
partition MEMBEROPERATE_PART_03 values less than ('04') tablespace IISS_LOG_DAT,
partition MEMBEROPERATE_PART_04 values less than ('05') tablespace IISS_LOG_DAT,
partition MEMBEROPERATE_PART_05 values less than ('06') tablespace IISS_LOG_DAT,
partition MEMBEROPERATE_PART_06 values less than ('07') tablespace IISS_LOG_DAT,
partition MEMBEROPERATE_PART_07 values less than ('08') tablespace IISS_LOG_DAT,
partition MEMBEROPERATE_PART_08 values less than ('09') tablespace IISS_LOG_DAT,
partition MEMBEROPERATE_PART_09 values less than ('10') tablespace IISS_LOG_DAT,
partition MEMBEROPERATE_PART_10 values less than ('11') tablespace IISS_LOG_DAT,
partition MEMBEROPERATE_PART_11 values less than ('12') tablespace IISS_LOG_DAT,
partition MEMBEROPERATE_PART_12 values less than (MAXVALUE) tablespace IISS_LOG_DAT
)
tablespace IISS_LOG_DAT;

====================================================

-- Create table
create table hello(
GID        NUMBER(38) not null,
LOGDATE    VARCHAR2(30) not null,
OFFDATE    VARCHAR2(30),
USERID     VARCHAR2(38) not null,
USERTOKEN VARCHAR2(64) not null,
CLIENTCODE VARCHAR2(30),
PORTALTYPE NUMBER(4),
AREACODE   VARCHAR2(30),
IP         VARCHAR2(30),
LOGINIPADDR VARCHAR2(30),
CLIENTVERSION VARCHAR2(32),
GWUA VARCHAR2(512),
ENTERDATE    DATE default sysdate,
PARTITION_MONTH AS (to_number(to_char(ENTERDATE,'MM')))
)
PARTITION BY LIST (PARTITION_MONTH)
(
PARTITION COMMONUSERLOG_PAR_01 VALUES (1),
PARTITION COMMONUSERLOG_PAR_02 VALUES (2),
PARTITION COMMONUSERLOG_PAR_03 VALUES (3),
PARTITION COMMONUSERLOG_PAR_04 VALUES (4),
PARTITION COMMONUSERLOG_PAR_05 VALUES (5),
PARTITION COMMONUSERLOG_PAR_06 VALUES (6),
PARTITION COMMONUSERLOG_PAR_07 VALUES (7),
PARTITION COMMONUSERLOG_PAR_08 VALUES (8),
PARTITION COMMONUSERLOG_PAR_09 VALUES (9),
PARTITION COMMONUSERLOG_PAR_10 VALUES (10),
PARTITION COMMONUSERLOG_PAR_11 VALUES (11),
PARTITION COMMONUSERLOG_PAR_12 VALUES (12)
)

=====

--解锁：
select t2.username,t2.sid,t2.serial#,t2.logon_time,t1.Os_User_Name
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

Select p.Spid,c.Object_Name,b.Session_Id,a.serial#,b.Oracle_Username,b.Os_User_Name
From V$process p, V$session a, V$locked_Object b, All_Objects c
Where p.Addr = a.Paddr And a.Process = b.Process And c.Object_Id = b.Object_Id
And c.object_name =UPPER( 'T_CMS_ENABLER');

alter system kill session '868,772';

================

select dbf.tablespace_name,
       dbf.totalspace "总量(M)",
       dbf.totalblocks as 总块数,
       dfs.freespace "剩余总量(M)",
       dfs.freeblocks "剩余块数",
       (dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
               sum(t.bytes) / 1024 / 1024 totalspace,
               sum(t.blocks) totalblocks from dba_data_files t group by t.tablespace_name) dbf,
       (select tt.tablespace_name,
               sum(tt.bytes) / 1024 / 1024 freespace,
               sum(tt.blocks) freeblocks from dba_free_space tt group by tt.tablespace_name) dfs where trim(dbf.tablespace_name) = trim(dfs.tablespace_name);