表被锁无法删除:
SELECT SESSION_ID FROM V$LOCKED_OBJECT;
--查看被锁对象的ID
SELECT SID,SERIAL#,USERNAME,OSUSER FROM V$SESSION WHERE SID=10;
--SESSION_ID是第一步查出来会话ID,查出session会话的sid和serial#
ALTER SYSTEM KILL SESSION '10,8662';
--对该会话进行终止.SID会被重用,同一个SID被重用时,SERIAL会增加,不会重复,所以结束会话时要指出SID和SERIAL#
创建表按季分区:
create table PAY_BANK_MESSAGE
(
BANKMSG_ID VARCHAR2(32),
ORDER_ID VARCHAR2(32),
ORDER_SENDING_MESSAGE VARCHAR2(2000),
ORDER_RECEIVED_MESSAGE VARCHAR2(2000),
ASYNCHRONOUS_RETURN_MESSAGE VARCHAR2(2000),
ORDER_QUERY_MESSAGE VARCHAR2(2000),
CREATE_TIME DATE,
CREATE_BY VARCHAR2(32),
UPDATE_TIME DATE,
UPDATE_BY VARCHAR2(50),
REMARK VARCHAR2(500)
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(3, 'month'))
(partition p_bank_interval values less than(to_date('2018-01-01', 'yyyy-mm-dd')))
/
comment on table PAY_BANK_MESSAGE is '银行报文信息表'
/
comment on column PAY_BANK_MESSAGE.BANKMSG_ID is '银行报文信息id'
/
comment on column PAY_BANK_MESSAGE.ORDER_ID is '订单Id'
/
comment on column PAY_BANK_MESSAGE.ORDER_SENDING_MESSAGE is '下单发送报文'
/
comment on column PAY_BANK_MESSAGE.ORDER_RECEIVED_MESSAGE is '下单同步接收报文'
/
comment on column PAY_BANK_MESSAGE.ASYNCHRONOUS_RETURN_MESSAGE is '银行异步返回报文'
/
comment on column PAY_BANK_MESSAGE.ORDER_QUERY_MESSAGE is '查单返回报文'
/
comment on column PAY_BANK_MESSAGE.CREATE_TIME is '创建时间'
/
comment on column PAY_BANK_MESSAGE.CREATE_BY is '创建人'
/
comment on column PAY_BANK_MESSAGE.UPDATE_TIME is '最后修改时间'
/
comment on column PAY_BANK_MESSAGE.UPDATE_BY is '最后修改人'
/
comment on column PAY_BANK_MESSAGE.REMARK is '备注'
/
create unique index PK_PAY_BANK_MESSAGE
on PAY_BANK_MESSAGE (BANKMSG_ID)
/
哈希分区:
create table PAY_GAS_USER
(
USERID NUMBER(20),
USER_TYPE VARCHAR2(1),
DEPT_ID NUMBER(20),
REGISTRATION_DATE DATE,
WECHAT_IMAGE_PATH VARCHAR2(200),
OPENID VARCHAR2(200),
NICK_NAME VARCHAR2(200),
STATUS VARCHAR2(10) default '0',
REMARK VARCHAR2(500)
unique,
CREATE_TIME DATE,
CREATE_BY VARCHAR2(32),
UPDATE_TIME DATE,
UPDATE_BY VARCHAR2(50)
)
PARTITION BY HASH (OPENID)
PARTITIONS 4
/
comment on table PAY_GAS_USER is '燃气用户'
/
comment on column PAY_GAS_USER.USERID is '燃气用户id'
/
comment on column PAY_GAS_USER.USER_TYPE is '用户类型 字典'
/
comment on column PAY_GAS_USER.DEPT_ID is '所属公司id'
/
comment on column PAY_GAS_USER.REGISTRATION_DATE is '注册时间'
/
comment on column PAY_GAS_USER.WECHAT_IMAGE_PATH is '图像路径'
/
comment on column PAY_GAS_USER.OPENID is '对应的Openid(加密存储)'
/
comment on column PAY_GAS_USER.NICK_NAME is '昵称'
/
comment on column PAY_GAS_USER.STATUS is '数据状态'
/
comment on column PAY_GAS_USER.REMARK is '备注'
/
comment on column PAY_GAS_USER.CREATE_TIME is '创建时间'
/
comment on column PAY_GAS_USER.CREATE_BY is '创建人'
/
comment on column PAY_GAS_USER.UPDATE_TIME is '最后修改时间'
/
comment on column PAY_GAS_USER.UPDATE_BY is '最后修改人'
/
create unique index PK_PAY_GAS_USER
on PAY_GAS_USER (USERID)
/
create unique index IPENIDINDEX
on PAY_GAS_USER (OPENID, USER_TYPE)
/
create unique index OPEINIDINDEX
on PAY_GAS_USER (USER_TYPE, OPENID)
/
查询分区:
select
table_name,partition_name
from
user_tab_partitions
where
table_name=
'PAY_ORDER'
;
oracle 获取n 个月前的时间:
select add_months(sysdate, -24) from dual;
oracle获取n天前的时间:
select sysdate-4 from dual;
19c单节点数据库异常断电后启动不了处理方法:
cd cd $ORACLE_BASE/admin/hnjj/pfile
cp init.ora.5302023144240 /u01/app/oracle/product/19.3.0/db_1/dbs/initorcl.ora
lsnrctl start
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
sqlplus / as sysdba
startup;
show pdbs;
alter session set container=pdb1;
alter plugggable database pdb1 open;
创建索引:
create unique index com_paytype_index on PAY_BANK_MERCHANT(COMPANY_CODE,PAY_TYPE);
oracle归档日志处理:
select * from v$flash_recovery_area_usage; --查看空间占用率
select * from v$recovery_file_dest; --查看归档日志的存放地址;
使用rman清空归档日志
crosscheck archivelog all; --查看可以所有的归档文件
delete expired archivelog all; --清空过期的归档文件
如何确认归档日志是否过期,rman有一个保留策略,可以定义多少天之前的日志算为过期;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
让恢复窗口成为14天大小。
show all --查看所有的rman策略.
select count(*) from v$archived_log where archived='YES' and deleted='NO'; --查看所有归档,未删除的归档日志