Oracle管理策略

PLSQL

主键自增长

• 使用表格所属用户登录
• 设置表的键值（以表T_USER，主键PID为例）

•  创建序列（seg_t_user）---New---Seguence

•  创建触发器（TR_T_user）

create or replace trigger TR_T_user
  before insert
  on t_user
  for each row WHEN (new.pid is null)
declare
  -- local variables here
begin
  select seg_t_user.nextval into:new.pid from dual; 
end TR_T_user;

Oracle Client 版本

客户端安装路径：\app\Administrator\product\11.2.0\client_1\inventory\ContentsXML\comps.xml

PLAT="NT_AMD64"表示安装的是64位的Oracle 客户端

PLAT="NT_X86"则表示安装的是32位的Oracle客户端

Oracle Home 

SQL语句

表空间

可用空间

 SELECT D.TABLESPACE_NAME,
       SPACE || 'M' "SUM_SPACE(M)",  
       BLOCKS "SUM_BLOCKS",  
       SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",  
       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",  
       FREE_SPACE || 'M' "FREE_SPACE(M)"  
 FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_DATA_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE  
            FROM DBA_FREE_SPACE  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)   
 AND D.TABLESPACE_NAME like '%%' 

表空间位置

select * from dba_data_files

表空间存储追加

alter tablespace SDE_TBS add datafile 'E:\APP\DATABASE\sys01.DBF' size 100M autoextend on next 50M maxsize 20G;

更改口令

alter user ps identified by ps01

误删提交事务找回

select * from TP.LAYER  as of timestamp 
 to_timestamp('2022/1/5 14:39:00','yyyy-mm-dd hh24:mi:ss') 
  where name like '%2020%'