数据库启停操作

systemctl stop  DmServiceDMSERVER
systemctl start  DmServiceDMSERVER

open、mount状态转换

Alter database mount;

Alter database open;

开启归档操作

SQL> alter database mount;

executed successfully

used time: 377.035(ms). Execute id is 0.

SQL> alter database archivelog;

executed successfully

used time: 103.251(ms). Execute id is 0.

SQL> select arch_mode from v$database;

LINEID     ARCH_MODE

---------- ---------

1          Y

used time: 0.934(ms). Execute id is 520.

SQL> alter database add archivelog 'type=local,dest=/dm8/arch,file_size=64,space_limit=1024';

executed successfully

used time: 94.927(ms). Execute id is 0.

SQL> alter database open;

executed successfully

关闭归档操作

SQL> alter database mount;

操作已执行

已用时间: 438.355(毫秒). 执行号:0.

SQL> alter database noarchivelog;

操作已执行

已用时间: 41.445(毫秒). 执行号:0.

SQL> alter database delete archivelog 'type=local,dest=/dm8/arch';

操作已执行

已用时间: 0.519(毫秒). 执行号:0.

SQL> alter database open;

操作已执行

已用时间: 322.559(毫秒). 执行号:0.

SQL> select arch_mode from v$database;

行号 ARCH_MODE

---------- ---------

1 N

已用时间: 0.654(毫秒). 执行号:714.

SQL> select * from v$dm_arch_ini;

未选定行

查看归档信息

SQL> select * from v$dm_arch_ini;

LINEID     ARCH_NAME      ARCH_TYPE ARCH_DEST ARCH_FILE_SIZE ARCH_SPACE_LIMIT ARCH_HANG_FLAG ARCH_TIMER_NAME ARCH_IS_VALID ARCH_WAIT_APPLY ARCH_INCOMING_PATH ARCH_CURR_DEST

---------- -------------- --------- --------- -------------- ---------------- -------------- --------------- ------------- --------------- ------------------ --------------

           ARCH_FLUSH_BUF_SIZE ARCH_RESERVE_TIME ARCH_LOCAL_SHARE ARCH_LOCAL_SHARE_CHECK ARCH_SEND_DELAY

           ------------------- ----------------- ---------------- ---------------------- ---------------

1          ARCHIVE_LOCAL1 LOCAL     /dm8/arch 64             1024             1              NULL            Y             NULL            NULL               /dm8/arch

           0                   0                 0                0                      0

手工切换归档操作

alter SYSTEM ARCHIVE LOG CURRENT;

alter SYSTEM SWITCH LOGFILE;

alter DATABASE ARCHIVELOG CURRENT;

收集单表统计信息

dbms_stats.gather_table_stats('TEST','EMP01');

查看某表和索引的统计信息

dbms_stats.table_stats_show('HRTEST','T_EMP01');

dbms_stats.index_stats_show('HRTEST','IX_EMP01_EMPLOYEENAME');

索引监控

alter index HRTEST.IX_EMP01_EMPLOYEENAME MONITORING USAGE; --开启索引监控

alter index HRTEST.IX_EMP01_EMPLOYEENAME NOMONITORING USAGE; --关闭索引监控

select * from v$object_usage;

在线重建索引

alter index HRTEST.IX_EMP01_EMPLOYEENAME rebuild ONLINE;

创建用户

SQL> create user user01 identified by dameng123;

executed successfully

used time: 224.042(ms). Execute id is 522.

用户锁定和解锁

alter user hr ACCOUNT UNLOCK;

alter user hr ACCOUNT LOCK;

修改用户默认表空间

alter user hr DEFAULT TABLESPACE dmtbs;

删除用户

drop user if EXISTS hr;

drop user if EXISTS hr CASCADE ; 

给用户赋予查询权限

grant select on dmhr.emp to test;

查看当前用户拥有的权限

select * from session_privs;

回收权限

SQL> revoke select on dmhr.emp from test;