Oracle数据库主要由 实例(Instance) + 数据库(Database) 两大部分构成

• 实例Instance：内存SGA+PGA + 后台进程(PMON、SMON、DBWR、LGWR、CKPT)
• Database：物理文件，数据文件dbf、控制文件ctl、重做日志redo、归档日志arch、参数文件spfile

SGA组成

• 共享池Shared Pool：库缓存、数据字典缓存
• 数据库缓冲区高速缓存Database Buffer Cache
• 重做日志缓冲区 Redo Log Buffer
• 大池、Java池、流池

默认端口：1521，集群/多实例环境可自定义，比如1522、1523

# 监听基础命令
lsnrctl status        # 查看监听状态、端口、注册实例
lsnrctl start         # 启动监听
lsnrctl stop          # 关闭监听
lsnrctl reload        # 重载监听配置（改listener.ora后不用重启）
lsnrctl services      # 查看监听注册的服务
lsnrctl set log_status on  # 开启监听日志
lsnrctl show log_file      # 查看监听日志路径

# 测试连通性
tnsping orcl

# 监听配置文件路径
# $ORACLE_HOME/network/admin/listener.ora
# $ORACLE_HOME/network/admin/tnsnames.ora

# 简单listener.ora示例
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
  )

# tnsnames.ora 本地服务名配置
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

# 修改监听端口步骤
# 1、修改listener.ora 和 tnsnames.ora 里面PORT
# 2、lsnrctl reload
# 3、防火墙放行新端口（firewalld/iptables）
# 4、确认 v$listener_network 视图验证端口

--查看数据库版本
SELECT * FROM v$version;

--查看实例状态
SELECT instance_name,status,startup_time FROM v$instance;

--查看表空间大小与使用率
SELECT
    t.tablespace_name,
    round(SUM(bytes)/1024/1024,2) total_mb,
    round(SUM(maxbytes)/1024/1024,2) max_mb,
    round((SUM(bytes)-SUM(nvl(f.bytes,0)))/1024/1024,2) used_mb
FROM dba_data_files t
LEFT JOIN dba_free_space f ON t.tablespace_name=f.tablespace_name
GROUP BY t.tablespace_name;

--查看当前会话
SELECT sid,serial#,username,program,status FROM v$session WHERE username IS NOT NULL;

--查看等待事件
SELECT event,count(*) FROM v$session_wait GROUP BY event ORDER BY count(*) DESC;

--查看锁阻塞
SELECT s.sid,s.serial#,s.username,l.type,l.lmode,l.request,o.object_name
FROM v$session s,v$lock l,dba_objects o
WHERE s.sid=l.sid AND l.id1=o.object_id;

--查看SQL执行计划
SELECT sql_text,elapsed_time,executions FROM v$sql ORDER BY elapsed_time DESC;

--创建表空间
CREATE TABLESPACE test_ts DATAFILE '/u01/oracle/test01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

--创建用户授权
CREATE USER testuser IDENTIFIED BY "Test@123" DEFAULT TABLESPACE test_ts;
GRANT connect,resource,dba TO testuser;

--修改密码
ALTER USER testuser IDENTIFIED BY "NewPass@123";

--建表
CREATE TABLE student(
    id NUMBER(10) PRIMARY KEY,
    name VARCHAR2(50),
    create_time DATE
);

--序列
CREATE SEQUENCE seq_student START WITH 1 INCREMENT BY 1 NOCACHE;

--创建索引
CREATE INDEX idx_student_name ON student(name);

--分区表 范围分区
CREATE TABLE tab_part(
    id NUMBER,create_date DATE
) PARTITION BY RANGE(create_date)(
PARTITION p2025 VALUES LESS THAN (TO_DATE('2026-01-01','yyyy-mm-dd')),
PARTITION p2026 VALUES LESS THAN (TO_DATE('2027-01-01','yyyy-mm-dd'))
);

--分页查询 Oracle经典分页
SELECT * FROM (
    SELECT t.*,ROWNUM rn FROM student t WHERE ROWNUM <= 20
) WHERE rn >=11;

--日期转换示例
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual;
SELECT ADD_MONTHS(SYSDATE,1) FROM dual;

--decode 示例
SELECT DECODE(sex,'1','男','2','女','未知') FROM user_info;

--开窗函数
SELECT name,sal,ROW_NUMBER() OVER(PARTITION BY dept ORDER BY sal DESC) rn FROM emp;

#设置oracle环境变量
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

#启动监听
lsnrctl start

#关闭监听
lsnrctl stop

#启动数据库
sqlplus / as sysdba <
        

• ORA‑01034：ORACLE not available → 实例未启动，执行startup
• ORA‑12541：TNS‑no listener → 监听未启动 lsnrctl start
• ORA‑12505：TNS:listener does not currently know of SID given in connect descriptor → SID/服务名不对，或者监听没注册上
• ORA‑00001：违反唯一约束条件 → 主键/唯一索引重复值
• ORA‑01403：no data found → select into无返回数据
• ORA‑01653：表空间不足 → 增加数据文件或者开启自动扩展
• ORA‑04031：SGA共享池内存不足 → 调大shared_pool_size
• ORA‑00060：死锁检测 → 业务SQL并发锁冲突，查看v$lock锁视图定位SQL

日志位置

alert告警日志路径：$ORACLE_BASE/diag/rdbms/数据库名/实例名/trace/alert_实例名.log