# 示例：生成AWR报告

# 1. 连接到数据库
$ sqlplus / as sysdba

# 2. 生成AWR报告
SQL> @?/rdbms/admin/awrrpt.sql

# 3. 选择报告格式（HTML或TEXT）
Enter value for report_type: html

# 4. 输入开始快照ID
Enter value for begin_snap:

# 5. 输入结束快照ID
Enter value for end_snap:

# 6. 输入报告文件名
Enter value for report_name:

# 7. 查看AWR报告
– 打开生成的HTML文件
– 分析报告中的性能数据

# 示例：使用ADDM分析

# 1. 连接到数据库
$ sqlplus / as sysdba

# 2. 生成ADDM报告
SQL> @?/rdbms/admin/addmrpt.sql

# 3. 输入开始快照ID
Enter value for begin_snap:

# 4. 输入结束快照ID
Enter value for end_snap:

# 5. 查看ADDM报告
– 分析ADDM提供的建议
– 实施优化措施

# 示例：使用ASH报告

# 1. 连接到数据库
$ sqlplus / as sysdba

# 2. 生成ASH报告
SQL> @?/rdbms/admin/ashrpt.sql

# 3. 选择报告格式（HTML或TEXT）
Enter value for report_type: html

# 4. 输入开始时间
Enter value for begin_time:

# 5. 输入结束时间
Enter value for duration:

# 6. 查看ASH报告
– 分析活动会话历史
– 识别性能瓶颈

# 示例：监控实时性能

# 1. 连接到数据库
$ sqlplus / as sysdba

# 2. 查看系统状态
SQL> SELECT * FROM v$sysstat WHERE name LIKE ‘%CPU%’ OR name LIKE ‘%wait%’;

# 3. 查看会话状态
SQL> SELECT * FROM v$session WHERE status = ‘ACTIVE’;

# 4. 查看等待事件
SQL> SELECT * FROM v$session_wait WHERE wait_time = 0;

# 5. 查看SQL执行情况
SQL> SELECT * FROM v$sql WHERE elapsed_time > 1000000 ORDER BY elapsed_time DESC;

# 示例：数据库启动和关闭

# 1. 启动数据库
SQL> STARTUP;

# 2. 启动到挂载状态
SQL> STARTUP MOUNT;

# 3. 启动到只读状态
SQL> STARTUP READ ONLY;

# 4. 关闭数据库
SQL> SHUTDOWN IMMEDIATE;

# 5. 关闭数据库（正常关闭）
SQL> SHUTDOWN NORMAL;

# 6. 关闭数据库（事务关闭）
SQL> SHUTDOWN TRANSACTIONAL;

# 7. 关闭数据库（强制关闭）
SQL> SHUTDOWN ABORT;

# 示例：表空间管理

# 1. 创建表空间
SQL> CREATE TABLESPACE users DATAFILE ‘/oracle/app/oracle/oradata/ORCL/users01.dbf’ SIZE 100M AUTOEXTEND ON;

# 2. 扩展表空间
SQL> ALTER TABLESPACE users ADD DATAFILE ‘/oracle/app/oracle/oradata/ORCL/users02.dbf’ SIZE 100M AUTOEXTEND ON;

# 3. 调整数据文件大小
SQL> ALTER DATABASE DATAFILE ‘/oracle/app/oracle/oradata/ORCL/users01.dbf’ RESIZE 200M;

# 4. 查看表空间使用情况
SQL> SELECT tablespace_name, SUM(bytes)/1024/1024/1024 AS size_gb, SUM(bytes – free_bytes)/1024/1024/1024 AS used_gb, SUM(free_bytes)/1024/1024/1024 AS free_gb FROM (
SELECT tablespace_name, bytes, 0 AS free_bytes FROM dba_data_files
UNION ALL
SELECT tablespace_name, 0 AS bytes, bytes AS free_bytes FROM dba_free_space
) GROUP BY tablespace_name;

# 5. 删除表空间
SQL> DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

# 示例：用户管理

# 1. 创建用户
SQL> CREATE USER scott IDENTIFIED BY tiger DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

# 2. 授予权限
SQL> GRANT CONNECT, RESOURCE TO scott;

# 3. 授予角色
SQL> GRANT dba TO scott;

# 4. 修改用户密码
SQL> ALTER USER scott IDENTIFIED BY new_password;

# 5. 锁定用户
SQL> ALTER USER scott ACCOUNT LOCK;

# 6. 解锁用户
SQL> ALTER USER scott ACCOUNT UNLOCK;

# 7. 删除用户
SQL> DROP USER scott CASCADE;

# 示例：备份和恢复

# 1. 使用RMAN备份数据库
$ rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

# 2. 使用RMAN恢复数据库
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;

# 3. 使用Data Pump导出数据
$ expdp scott/tiger@orcl DIRECTORY=dump_dir DUMPFILE=scott.dmp LOGFILE=exp.log

# 4. 使用Data Pump导入数据
$ impdp scott/tiger@orcl DIRECTORY=dump_dir DUMPFILE=scott.dmp LOGFILE=imp.log

# 示例：创建定时任务

# 1. 创建程序
SQL> BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => ‘backup_program’,
program_type => ‘STORED_PROCEDURE’,
program_action => ‘backup_procedure’,
enabled => TRUE
);
END;
/

# 2. 创建调度
SQL> BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => ‘daily_backup_schedule’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0’,
end_date => NULL,
comments => ‘Daily backup at 2 AM’
);
END;
/

# 3. 创建任务
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => ‘daily_backup_job’,
program_name => ‘backup_program’,
schedule_name => ‘daily_backup_schedule’,
enabled => TRUE,
comments => ‘Daily backup job’
);
END;
/

# 4. 查看任务状态
SQL> SELECT job_name, status, next_run_date FROM dba_scheduler_jobs WHERE job_name = ‘DAILY_BACKUP_JOB’;

# 5. 运行任务
SQL> EXEC DBMS_SCHEDULER.RUN_JOB(‘daily_backup_job’);

# 6. 禁用任务
SQL> EXEC DBMS_SCHEDULER.DISABLE(‘daily_backup_job’);

# 7. 启用任务
SQL> EXEC DBMS_SCHEDULER.ENABLE(‘daily_backup_job’);

# 8. 删除任务
SQL> EXEC DBMS_SCHEDULER.DROP_JOB(‘daily_backup_job’);

# 示例：创建备份存储过程
SQL> CREATE OR REPLACE PROCEDURE backup_procedure AS
BEGIN
— 执行备份操作
EXECUTE IMMEDIATE ‘ALTER SYSTEM ARCHIVE LOG CURRENT’;
— 可以添加更多备份逻辑
END;
/

# 示例：创建统计信息收集任务

# 1. 创建程序
SQL> BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => ‘gather_stats_program’,
program_type => ‘STORED_PROCEDURE’,
program_action => ‘DBMS_STATS.GATHER_DATABASE_STATS’,
enabled => TRUE
);
END;
/

# 2. 创建调度
SQL> BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => ‘weekly_stats_schedule’,
start_date => SYSTIMESTAMP,
repeat_interval => ‘FREQ=WEEKLY; BYDAY=SUN; BYHOUR=1; BYMINUTE=0; BYSECOND=0’,
end_date => NULL,
comments => ‘Weekly stats gathering at 1 AM on Sunday’
);
END;
/

# 3. 创建任务
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => ‘weekly_stats_job’,
program_name => ‘gather_stats_program’,
schedule_name => ‘weekly_stats_schedule’,
enabled => TRUE,
comments => ‘Weekly stats gathering job’
);
END;
/