DM資料庫操作實踐

資料庫啟停操作

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;