oracle 杂项总结

–查看数据库版本
SELECT * FROM v$version;

–查看各个表占用用多少空间
SELECT segment_names AS tablename, bytes/1024/1024 mb FROM user_segments ORDER BY bytes desc;

–重构索引
ALTER INDEX DO_AD_TX_OPP_INFO_INDEXES REBUILD;

–间隔分区,创建语法
partition by range(DATA_DT)
INTERVAL(NUMTOYMINTERVAL(1, ‘day’))
(PARTITION P_DAY_1 VALUES LESS THAN (TO_DATE(’ 2021-01-01 00:00:00’, ‘YYYY-MM-DD HH24;MI:SS’, ‘NLS_CALENDAR-GREGORTAN’)));

–list分区，创建语法
partition by list (DATA_DT)(
partition part_CUR values (TD_DATE( ‘1900-01-01’ , 'YYYY-MM-DD"))
);

–新增分区
alter table t_test add partition PART_20210214 values less than (to_date(‘2021-02-14’,‘yyyy-mm-dd’));
–删除分区
alter table t_test drop partition PART_20210214;
–清空分区
alter table t_test truncate partition PART_20210214;

创维外部表加载环境(当量sql有执行]
一直看教征文件,加費用型|
SELECT . FRM dos directories NIERE DIRECTORY_HAVE=“COS_DIE”;

–创建数据文件加载路径
CREATE or replace directory DOS_DIR AS '/data/file/DOS";
creste or replaca directory DOS_HAD as ‘/dita/file/DQS/reject_file’;
create or replace directory DOS_LOG as ‘/data/file/DQS/log’;
drop directory DOS_DIR;
drop directory DOS_JMD;
drop dirustory DOS LOG;
–查看
select * from dba_directories;

–查看被锁对象0
SELECT L.SESSION_ID SID,
S.SERIAL#,
L.LOCKED_MODE AS 锁模式,
L.ORACLE_USERNAME AS 登录用户,
L.OS_USER_NAME AS 登录机器用户名,
S.MACHINE AS 机器名,
S.TERMINAL AS 终端用户名,
O.OBJECT_NAME AS 被锁对象名,
S.LOGON_TIME AS 登录数据库时间
FROM V$LOCKE{D}_{O}BJECTL,AL{L}_{O}BJECTSO,V$SESSION S
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
ORDER BY SID, S.SERIAL#;
–解锁
ALTER SYSTEM KILL SESSION ‘sid,SERIAL#’;