SQL> set pagesize 500
SQL> set linesize 500
查询数据库版本
SELECT * FROM V$VERSION;
select BUILD_VERSION from v$instance ; #查看小版本
查看当前有多少用户
SQL> SELECT USERNAME FROM DBA_USERS;
SELECT * FROM DBA_TABLESPACES;
set pagesize 500
set linesize 500
SELECT
TABLESPACE_NAME AS 表空间名,
ROUND(SUM(TOTAL_SIZE) / 1024.0 / 1024.0, 2) AS "总大小(MB)",
ROUND(SUM(USED_SIZE)/ 1024.0 / 1024.0, 2) AS "已使用大小(MB)",
CAST(ROUND(SUM(USED_SIZE) * 1.0 / SUM(TOTAL_SIZE) * 100,2) AS VARCHAR) || '%' AS 使用率
FROM
(
SELECT
UPPER(T1.TABLESPACE_NAME) AS TABLESPACE_NAME,
T1.BYTES AS TOTAL_SIZE,
T1.BYTES - T2.BYTES AS USED_SIZE
FROM
DBA_DATA_FILES T1,
DBA_FREE_SPACE T2
WHERE
T1.TABLESPACE_NAME = T2.TABLESPACE_NAME
AND T1.FILE_ID = T2.FILE_ID
)
GROUP BY
TABLESPACE_NAME;
--------------------查看表空间同数据文件对应关系
SELECT
TS.NAME AS 表空间名,
DF.PATH AS 数据文件
FROM
V$TABLESPACE AS TS,
V$DATAFILE AS DF
WHERE
TS.ID = DF.GROUP_ID
ORDER BY
1;
SELECT
USERNAME AS 用户名,
DEFAULT_TABLESPACE AS 默认数据表空间,
DEFAULT_INDEX_TABLESPACE AS 默认索引表空间,
TEMPORARY_TABLESPACE AS 临时表空间,
DECODE(ACCOUNT_STATUS,'OPEN','正常','LOCKED','锁定','未知') AS 用户状态,
CREATED AS 创建时间
FROM
DBA_USERS;
--------------------查看用户数据库限制
SELECT
T1.USERNAME AS 用户名,
DECODE(T2.AUTHENT_TYPE,1,'数据库密码认证',2,'操作系统认证',3,'远程认证','未知认证方式') AS 用户认证方式,
T2.SESS_PER_USER AS 用户最大会话数,
T2.CONN_IDLE_TIME AS "用户空闲期（分钟1-1440）",
T2.FAILED_NUM AS 用户登录失败次数限制,
T2.LIFE_TIME AS "口令有效期（天0-365）",
T2.REUSE_TIME AS "口令等待期（天0-365）",
T2.REUSE_MAX AS 口令变更次数,
T2.LOCK_TIME AS "用户锁定时间（分1-1440）",
T2.GRACE_TIME AS "口令宽限期1-30",
T2.PASSWORD AS 密码策略,
T2.RN_FLAG AS 只读,
T2.ALLOW_ADDR AS 允许访问的IP,
T2.NOT_ALLOW_ADDR AS 不允许访问的IP,
T2.ALLOW_DT AS 允许访问的时间,
T2.NOT_ALLOW_DT AS 不允许访问的时间,
T2.LAST_LOGIN_DTID AS 上次登录时间,
T2.LAST_LOGIN_IP AS 上次登录IP,
T2.FAILED_ATTEMPS AS 自上一次登录成功以来失败次数
FROM
DBA_USERS T1,
SYSUSERS T2
WHERE
T1.USER_ID = T2.ID;
--------------------查看用户对象统计信息
SELECT
S2.NAME AS 模式名,
S1.TYPE$ AS 主类型,
S1.SUBTYPE$ AS 子类型,
COUNT(*) AS 对象数量
FROM
SYSOBJECTS S1,
SYSOBJECTS S2
WHERE
S1.SCHID = S2.ID
AND S2.TYPE$ = 'SCH'
GROUP BY
S2.NAME,
S1.TYPE$,
S1.SUBTYPE$
ORDER BY
S2.NAME,
S1.TYPE$,
S1.SUBTYPE$;
--------------------查看用户占用空间大小
SELECT
USERNAME AS 用户名,
USER_USED_SPACE(USERNAME) * PAGE / 1024.0 / 1024.0 AS "用户占用空间(MB)"
FROM
DBA_USERS
ORDER BY
2 DESC;
SELECT
T2.NAME AS 模式名,
T1.NAME AS 表名,
TABLE_ROWCOUNT(T2.NAME,T1.NAME) AS 行数
FROM
SYSOBJECTS T1,
SYSOBJECTS T2
WHERE
T1.SCHID = T2.ID
AND T1.SUBTYPE$ = 'UTAB'
AND T2."TYPE$" = 'SCH'
ORDER BY 3 DESC;
--------------------统计所有用户表行数以及筛查某行数级别以上表行数 输入参数1：百万，千万，亿...等等，也可以共存，复制一行
SELECT
模式名,
COUNT(表名) AS 表数量,
COUNT(CASE WHEN 行数 > ? THEN 行数 ELSE NULL END) AS 百万表数量
FROM
(
SELECT
T2.NAME AS 模式名,
T1.NAME AS 表名,
TABLE_ROWCOUNT(T2.NAME,T1.NAME) AS 行数
FROM
SYSOBJECTS T1,
SYSOBJECTS T2
WHERE
T1.SCHID = T2.ID
AND T1.SUBTYPE$ = 'UTAB'
AND T2."TYPE$" = 'SCH'
--ORDER BY 3 DESC
)
GROUP BY 模式名
--------------------获取所有用户表定义
SELECT
T2.NAME AS 模式名,
T1.NAME AS 表名,
T1.CRTDATE AS 创建时间,
DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('TABLE',T1.NAME,T2.NAME)) AS 表定义
FROM
SYSOBJECTS T1,
SYSOBJECTS T2
WHERE
T1."SUBTYPE$" = 'UTAB'
AND T1.SCHID = T2.ID
AND T2."TYPE$" = 'SCH';
--------------------统计用户表列信息
SELECT
S1.NAME AS 表名,
S2.NAME AS 列名,
S2."TYPE$" AS 字段类型,
S2."LENGTH$" AS 字段长度
FROM
SYSOBJECTS S1,
SYSCOLUMNS S2
WHERE
S1."SUBTYPE$" = 'UTAB'
AND S1.ID = S2.ID;
--------------------查看表占用空间大小
SELECT
S2.NAME AS 模式名,
S1.NAME AS 表名,
TABLE_USED_SPACE(S2.NAME,S1.NAME) * PAGE /1024.0/1024.0 AS "表占用空间(MB)"
FROM
SYSOBJECTS S1,
SYSOBJECTS S2
WHERE
S1.SCHID = S2.ID
AND S1."SUBTYPE$" = 'UTAB'
AND S2."TYPE$" = 'SCH'
ORDER BY
3 DESC;
查询会话数
SELECT * FROM V$SESSIONS;
SELECT COUNT(*) FROM V$SESSIONS;
查看数据库的创建日期和归档方式
select create_time,arch_mode from v$database;
在某个用户下找所有的索引
select user_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type,
user_indexes.table_name,
user_indexes.index_name,
column_position;
查看字符集
返回建库时，指定的字符集。返回值：0 表示 GB18030，1 表示 UTF-8。
SELECT SF_GET_UNICODE_FLAG ();
查看数据库对象
select owner, object_type, status, count(*) count#
from all_objects
group by owner, object_type, status;
查看日志文件
select * from v$rlogfile;
查询事务数
SELECT COUNT(*) FROM V$TRX;
查询等待事件
SELECT * FROM V$TRXWAIT;
查询内存池
SELECT *FROM V$MEM_POOL;
查看实例的运行状态
SELECT * FROM V$INSTANCE;
守护集群启动/停止命令
[dmdba@~]$ /home/dmdba/dmdbms/bin/DmWatcherServiceWatcher start#A/B机器
[dmdba@~]$ /home/dmdba/dmdbms/bin/DmWatcherServiceWatcher stop#A/B机器
[dmdba@~]$/home/dmdba/dmdbms/bin/DmMonitorServiceMonitor start #监控机器
##实例启动/停止命令
[dmdba@~]$ /home/dmdba/dmdbms/bin/DmServiceGRP1_RT_01 start#A/B机器
[dmdba@~]$ /home/dmdba/dmdbms/bin/DmServiceGRP1_RT_01 stop#A/B机器
查看进程是否存在
ps -ef | grep dmwatcher
ps -ef | grep dmserver
添加disql环境变量
vi ~/.bash_profile
export PATH=$PATH:$DM_HOME/bin:$DM_HOME/tool
source ~/.bash_profile
DM重做主从
A机器:
SQL> ALTER DATABASE MOUNT;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL> SP_SET_OGUID(45331);
SQL> ALTER DATABASE PRIMARY;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
B机器:
SQL> ALTER DATABASE MOUNT;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL> SP_SET_OGUID(45331);
SQL> ALTER DATABASE STANDBY;
SQL>SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 0);
A/B机器：
SQL> SP_SET_PARA_VALUE(1, 'ALTER_MODE_STATUS', 1);
SQL> alter database normal;#单机模式，主从不用执行
SQL> alter database open force;
A/B机器监控启动
/home/dmdba/dmdbms/bin/dmwatcher /dmdata/data/DMTEST/dmwatcher.ini &
查看服务自启动
systemctl list-unit-files --type=service | grep enable |grep Dm
systemctl disable DmXXX.service #关闭服务自启动
查看DB 当前正在适用的Redo log：
SQL>select cur_file from V$RLOG;
查看当前Redo log 信息：
SQL>select file_id,path,rlog_size/1024/1024 as "Size" from V$RLOGFILE;
删除归档日志：
可以通过系统函数SF_ARCHIVELOG_DELETE_BEFORE_TIME删除指定时间之前的归档文件，该函数返回删除的归档日志文件个数。如下命令为删除系统当前时间10天前的归档日志文件
select sf_archivelog_delete_before_time(sysdate -10);
手动切换归档命令
alter system switch logfile;
归档日志的连续性检查
SELECT SF_REMOTE_ARCHIVE_CHECK('GRP1_RT_02');
查看主备库的状态：
SELECT CASE WHEN "STATUS$" = '1' THEN 'Startup' WHEN "STATUS$" = '2' THEN 'After Redo' WHEN "STATUS$" = '3' THEN 'MOUNT' WHEN "STATUS$" = '4' THEN 'OPEN' WHEN "STATUS$" = '5' THEN 'SUSPEND' END "库状态" , CASE WHEN "ROLE$" = '0' THEN 'Normal' WHEN "ROLE$" = '1' THEN 'Parmary' WHEN "ROLE$" = '2' THEN 'Standby' END "模式" FROM v$database;
查看主备库的SCN是否一致：
select file_LSN, cur_LSN from v$rlog;
查看主备库的permanent值是否一致：
select permanent_magic;
开启主备集群的逻辑日志参数
修改开启在主、备RLOG_APPEND_LOGIC逻辑追加日志参数，0为关闭，1为开启，注意参数PARA_TYPE为SYS类型可动态修改；其它参数类型详见管理员手册。如下为参数查询及修改:
SQL> SELECT PARA_NAME,PARA_VALUE,DEFAULT_VALUE,SESS_VALUE,FILE_VALUE,PARA_TYPE FROM V$DM_INI WHERE PARA_NAME LIKE 'RLOG_APPEND_LOGIC';
SQL> call sp_set_para_value(1,'RLOG_APPEND_LOGIC',1);
SQL> SELECT PARA_NAME,PARA_VALUE,DEFAULT_VALUE,SESS_VALUE,FILE_VALUE,PARA_TYPE FROM V$DM_INI WHERE PARA_NAME LIKE 'RLOG_APPEND_LOGIC';
查看所有的系统表
SELECT * FROM DBA_TABLES;
查看DDL
select owner, table_name from dba_tables where owner = 'SYSDBA' and table_name like 'DMHS%' and status ='VALID';
查看归档的模式
select name,arch_mode from v$database;
#查看归档文件
select * from sys.v$arch_file;
#查看归档配置
select * from v$dm_arch_ini;
主备切换
[dmdba@localhost bin]$ ./dmmonitor ./dmmonitor.ini
login
SYSDBA/SYSDBA
switchover GRP1_RT_02
查看可切换的列表
choose switchover GRP1_RT_01
分裂后，需要删掉分裂库生成的守护进程控制文件，守护进程在检测到本地库分裂时，自动创建 dmwatcher.ctl 文件.
rm -rf dmwatcher.ctl
然后主备备份后数据传给备份，依次启动数据库和守护进程。
备份与恢复
数据量小的可以使用dexp和dimp
数据库级
./dexp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=dbbak.dmp LOG=dbbak.log FULL=Y DIRECTORY=/backup/databak
用户级
./dexp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=db_users.dmp LOG=db_users.log
OWNER=TEST1,TEST2,...,TESTN DIRECTORY=/backup/FULL_BAK
模式级
./dexp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=db_users.dmp LOG=db_users.log
SCHEMAS=TEST1,TEST2,...,TESTN DIRECTORY=/backup/FULL_BAK
表级
./dexp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=db_users.dmp LOG=db_users.log
TABLES=模式.表名,...,模式.表名 DIRECTORY=/backup/FULL_BAK
数据库级
./dimp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log FULL=Y DIRECTORY=/backup/IMP_BAKDIR
模式级
./dimp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log SCHEMAS=TEST1,...,TESTN DIRECTORY=/backup/IMP_BAKDIR
不同schema需要加个参数
./dimp USERID=SYSDBA/SYSDBA@localhost:5236 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log REMAP_SCHEMA=MODE01:MODE02 DIRECTORY=/backup/IMP_BAKDIR
用户级
./dimp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log OWNER=TEST1,...,TESTN DIRECTORY=/backup/IMP_BAKDIR
不同用户需要加参数，与shcema一致。
./dimp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log FROMUSER=USER01 TOUSER=USER02 DIRECTORY=/backup/IMP_BAKDIR
表级
./dimp USERID=SYSDBA/SYSDBA@localhost:5237 FILE=/backup/FULL_BAK/db_bak.dmp LOG=db_recover.log TABLES=模式.表名,...,模式.表名 DIRECTORY=/backup/IMP_BAKDIR
创建某个schema模式下只读用户
select 'grant select on 模式名.'||table_name||' to 只读账号;' from user_tables;
查看schema对应的用户
select a.id scheid, a.name schename, b.id userid, b.name username
from SYS.SYSOBJECTS a, SYS.SYSOBJECTS b
where a."TYPE$" = 'SCH' and a.pid = b.id;
