Essential DM Database Management SQL Commands You Need to Know

DM Database Common Management SQL Commands

1. Basic Information Queries

1. Version and License Information

-- Query database version (DM7/DM8)
SELECT * FROM v$version;  -- DM7
SELECT id_code();        -- DM8

-- Query license information
SELECT * FROM v$license;

2. Server Configuration Queries

-- Server basic information
SELECT * FROM V$SYSTEMINFO;

-- All configuration parameters
SELECT * FROM v$dm_ini;

-- Comprehensive info (charset, page size, case sensitivity, etc.)
SELECT 'Instance Name'   AS option, INSTANCE_NAME   AS value FROM v$instance
UNION ALL
SELECT 'Database Version', substr(svr_version, instr(svr_version, '(')) FROM v$instance
UNION ALL
SELECT 'Charset', CASE SF_GET_UNICODE_FLAG()
    WHEN '0' THEN 'GBK18030'
    WHEN '1' THEN 'UTF-8'
    WHEN '2' THEN 'EUC-KR'
END
UNION ALL
SELECT 'Page Size', CAST(PAGE() / 1024 AS varchar)
UNION ALL
SELECT 'Extent Size', CAST(SF_GET_EXTENT_SIZE() AS varchar)
UNION ALL
SELECT 'Case Sensitive', CAST(SF_GET_CASE_SENSITIVE_FLAG() AS varchar);

2. Session and Performance Monitoring

1. Session Management

-- View all sessions
SELECT * FROM v$sessions;

-- Session count by state
SELECT count(*), state FROM v$sessions GROUP BY state;

-- Session count by client IP
SELECT count(*), clnt_ip FROM v$sessions GROUP BY clnt_ip;

-- Terminate a specific session
SP_CLOSE_SESSION(session_id);

2. SQL Monitoring

-- Query recently executed SQL statements
SELECT * FROM v$sql_history;

-- Long‑running SQL statements
SELECT * FROM v$long_exec_sqls;

-- Lock wait information
SELECT * FROM v$lock WHERE blocked = 1;

3. Object Management

1. Table and Column Queries

-- All tables of the current user
SELECT * FROM user_tables;

-- All tables owned by a specific user
SELECT * FROM all_tables WHERE owner = 'USER01';

-- Column information of a table
SELECT * FROM all_tab_cols WHERE owner = 'USER01';

-- Table comments
SELECT * FROM ALL_TAB_COMMENTS WHERE owner = 'USER01';

-- Column comments
SELECT * FROM ALL_COL_COMMENTS WHERE owner = 'USER01';

2. Index Queries

-- View indexes of a table
SELECT * FROM user_indexes WHERE table_name = 'TABLE_NAME';

-- View index columns
SELECT * FROM user_ind_columns WHERE index_name = 'INDEX_NAME';

4. Tablespace Management and Backup

1. Tablespace Management

-- Tablespace information
SELECT * FROM v$tablespace;

-- Data files
SELECT * FROM v$datafile;

-- Tablespace usage
SELECT df.tablespace_name "Tablespace Name",
       df.bytes/1024/1024 "Total Size (MB)",
       (df.bytes - fs.bytes)/1024/1024 "Used (MB)",
       fs.bytes/1024/1024 "Free (MB)",
       ROUND(100*(df.bytes - fs.bytes)/df.bytes) "Usage (%)"
FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name;

-- Simplified free space query
SELECT tablespace_name, SUM(bytes)/1024/1024 AS "Free Space (MB)"
FROM dba_free_space
GROUP BY tablespace_name;

2. Backup and Recovery

-- View backup information
SELECT * FROM v$backupset;

-- Perform an online full backup
BACKUP DATABASE FULL BACKUPSET '/backup/full_bak';

5. Performance Optimization

1. Parameter Adjustment

-- Modify a parameter (memory parameters require restart)
SP_SET_PARA_VALUE(1, 'parameter_name', value);

2. Statistics Collection

-- Gather table statistics
DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name');

-- View statistics
SELECT * FROM user_tab_statistics;

-- Memory pool usage
SELECT * FROM v$mem_pool;

Mastering these common SQL commands can greatly improve DM database management efficiency. It is recommended to bookmark this guide for future reference and apply the commands flexibly according to actual needs.