常用sql语句

查看时区：select sessiontimezone from dual;
查看数据库时间：select sysdate from dual;
修改服务器时区：timedatectl set-timezone Asia/Shanghai

alter table sm_msg_notice rename column billno to billno_tmp;
alter table sm_msg_notice add billno varchar2(256);
update sm_msg_notice set billno=trim(billno_tmp);
alter table sm_msg_notice drop column billno_tmp;

select * from user_indexes where index_name='PK_MD_TABLE';
alter index pk_md_table rebuild;

删除重复值：

创建索引：create index i_bd_area_01 on bd_areac1(innercode) tablespace nnc_index01 nologging;

select a.sid,a.event,a.blocking_session,a.wait_class from v$session a where a.module like 'Data Pump%';

通过SID查询具体的信息：select sid,serial#,machine,program from v$session where sid=2948;

alter system kill session '2948,19' immediate;

select a.sid,a.event,a.blocking_session,a.wait_class from v$session a where a.module like 'Data Pump%';

select a.sid,a.event,a.blocking_session,a.wait_class from v$session a where a.module like '%rman%';

expdp attach SYS_EXPORT_SCHEMA_01

SELECT THREAD#,SEQUENCE#,NAME,FIRST_CHANGE#,NEXT_CHANGE#,TO_CHAR(FIRST_TIME,'YYYY-MM-DD HH24:MI:SS') AS FIRST_TIME,TO_CHAR(NEXT_TIME,'YYYY-MM-DD HH24:MI:SS') AS NEXT_TIME FROM V$ARCHIVED_LOG WHERE NAME IS NOT NULL ORDER BY 1,2;

recover database noredo;

TMOUT

kernel.shmmax

select file_type,percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;

SELECT NVL(FRAU.FILE_TYPE, 'Total:') FILE_TYPE,
SUM(ROUND(FRAU.PERCENT_SPACE_USED / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) USED_GB,
SUM(FRAU.PERCENT_SPACE_USED) PERCENT_SPACE_USED,
SUM(FRAU.PERCENT_SPACE_RECLAIMABLE) PERCENT_SPACE_RECLAIMABLE,
SUM(ROUND(FRAU.PERCENT_SPACE_RECLAIMABLE / 100 * RFD.SPACE_LIMIT / 1024 / 1024 / 1024,3)) RECLAIM_GB,
SUM(FRAU.NUMBER_OF_FILES) NUMBER_OF_FILES
FROM V$FLASH_RECOVERY_AREA_USAGE FRAU, V$RECOVERY_FILE_DEST RFD
GROUP BY ROLLUP(FILE_TYPE);

select file#,to_char(checkpoint_change#,'999999999999'),to_char(last_change#,'999999999999') from v$datafile;

select * from v$database_block_corruption;

backup validate datafile 2;

select group_number,disk_number,mount_status,header_status,mode_status,state,failgroup,total_mb,free_mb,name,path,create_date from v$asm_disk;

recover database until cancel;

https://pan.yonyou.com/s/ty8CFCh4RA 密码：fff9