oracle数据库基本操作

### Oracle数据库基本操作知识点 #### 一、表空间操作 **1. 文件系统：** - **创建表空间：** - 数据表空间: 使用`CREATE TABLESPACE`命令创建一个新的数据表空间。例如: ```sql CREATE TABLESPACE tablespace_name LOGGING DATAFILE '/XXX/xxx/datafile_name1.ora' SIZE 4095M; ``` - 临时表空间: 使用`CREATE TEMPORARY TABLESPACE`命令创建一个临时表空间。例如: ```sql CREATE TEMPORARY TABLESPACE tmp_tablespace_name TEMPFILE '/XXX/xxx/datafile_name1.ora' SIZE 4095M; ``` - 回滚表空间: 使用`CREATE UNDO TABLESPACE`命令创建一个回滚表空间。例如: ```sql CREATE UNDO TABLESPACE undotbs_tablespace DATAFILE '/XXX/xxx/datafile_name1.ora' SIZE 4095M; ``` - **增加表空间：** - 数据表空间: 使用`ALTER TABLESPACE`命令添加新的数据文件到现有的数据表空间。例如: ```sql ALTER TABLESPACE tablespace_name ADD DATAFILE '/XXX/xxx/datafile_name1.ora' SIZE 4095M; ``` - 临时表空间: 同样使用`ALTER TABLESPACE`命令添加新的临时文件到现有的临时表空间。例如: ```sql ALTER TABLESPACE temp_tablespace_name ADD TEMPFILE '/XXX/xxx/datafile_name1.ora' SIZE 4095M; ``` - 回滚表空间: 使用`ALTER TABLESPACE`命令添加新的数据文件到现有的回滚表空间。例如: ```sql ALTER TABLESPACE undotbs_tablespace_name ADD DATAFILE '/XXX/xxx/datafile_name1.ora' SIZE 4095M; ``` - **删除表空间：** - 数据表空间: 使用`DROP TABLESPACE`命令删除数据表空间及其所有内容。例如: ```sql DROP TABLESPACE tablespace_name; ``` - 临时表空间: 同样使用`DROP TABLESPACE`命令删除临时表空间。在删除之前需要确保已经创建了新的临时表空间并使之生效。例如: ```sql -- 先创建新的临时表空间 CREATE TEMPORARY TABLESPACE new_tmp_tablespace_name TEMPFILE '/XXX/xxx/new_datafile_name1.ora' SIZE 4095M; -- 然后删除旧的临时表空间 DROP TABLESPACE old_tmp_tablespace_name; ``` - 回滚表空间: 使用`DROP TABLESPACE`命令删除回滚表空间，在删除前需要创建新的回滚表空间并使之生效。例如: ```sql -- 先创建新的回滚表空间 CREATE UNDO TABLESPACE new_undotbs_tablespace DATAFILE '/XXX/xxx/new_datafile_name1.ora' SIZE 4095M; -- 然后删除旧的回滚表空间 DROP TABLESPACE old_undotbs_tablespace; ``` - **表空间大小查看：** - 可以使用`DBA_DATA_FILES`或`DBA_TEMP_FILES`视图来查看数据文件的大小。例如: ```sql SELECT file_name, bytes/1024/1024 AS size_in_mb FROM dba_data_files; ``` #### 二、用户、角色、权限 **1. 用户创建、修改、删除：** - **创建用户：** ```sql CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; ``` - **修改用户：** ```sql ALTER USER username ACCOUNT LOCK; ``` 或者 ```sql ALTER USER username QUOTA unlimited ON users; ``` - **删除用户：** ```sql DROP USER username CASCADE; ``` **2. 角色创建、修改、删除：** - **创建角色：** ```sql CREATE ROLE role_name; ``` - **修改角色：** ```sql GRANT role_to_grant TO role_name; ``` - **删除角色：** ```sql DROP ROLE role_name; ``` **3. 权限增加和收回：** - **授予权限：** ```sql GRANT select, insert, update ON table_name TO username; ``` - **收回权限：** ```sql REVOKE select, insert, update ON table_name FROM username; ``` #### 三、表操作 **1. 单表创建：** ```sql CREATE TABLE table_name ( column1 datatype, column2 datatype, ... ); ``` **2. 分区表创建：** ```sql CREATE TABLE table_name ( column1 datatype, column2 datatype, ... ) PARTITION BY RANGE (column1) ( PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (MAXVALUE) ); ``` **3. 分区操作：** - **添加分区：** ```sql ALTER TABLE table_name ADD PARTITION p3 VALUES LESS THAN (30); ``` - **删除分区：** ```sql ALTER TABLE table_name DROP PARTITION p0; ``` - **合并分区：** ```sql ALTER TABLE table_name MERGE PARTITIONS p0, p1 INTO PARTITION p_new; ``` **4. 表状态查询：** ```sql SELECT * FROM dba_tables WHERE owner = 'SCHEMA_NAME'; ``` **5. DDL语句操作：** - 创建表: `CREATE TABLE` - 修改表: `ALTER TABLE` - 删除表: `DROP TABLE` - 重命名表: `RENAME TABLE` #### 四、视图操作 **1. 视图创建：** ```sql CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; ``` **2. 视图操作：** - 更新视图: 如果视图基于单一表且包含所有列，则可以更新。 - 删除行: 如果视图基于单一表，则可以从视图中删除行。 **3. 视图删除：** ```sql DROP VIEW view_name; ``` #### 五、索引、约束操作 **1. 单表索引：** ```sql CREATE INDEX index_name ON table_name (column1, column2, ...); ``` **2. 分区表索引：** ```sql CREATE INDEX index_name ON table_name (column1, column2, ...) LOCAL; ``` **3. 主键、约束和唯一索引：** - **创建主键约束：** ```sql ALTER TABLE table_name ADD CONSTRAINT pk_table PRIMARY KEY (column1); ``` - **创建唯一索引：** ```sql CREATE UNIQUE INDEX unique_index_name ON table_name (column1); ``` #### 六、存储过程/job/函数/触发器操作 **1. 存储过程和函数：** - **创建存储过程：** ```sql CREATE OR REPLACE PROCEDURE proc_name (param1 IN number, param2 OUT varchar2) AS BEGIN -- procedure body END; ``` - **创建函数：** ```sql CREATE OR REPLACE FUNCTION func_name (param1 number) RETURN varchar2 AS BEGIN -- function body END; ``` **2. 程序包：** - **创建程序包规范：** ```sql CREATE OR REPLACE PACKAGE pkg_name AS PROCEDURE proc_name; FUNCTION func_name RETURN varchar2; END pkg_name; ``` - **创建程序包体：** ```sql CREATE OR REPLACE PACKAGE BODY pkg_name AS PROCEDURE proc_name IS BEGIN -- procedure body END; FUNCTION func_name RETURN varchar2 IS BEGIN -- function body END; END pkg_name; ``` **3. 操作练习：** - **执行存储过程：** ```sql BEGIN proc_name(param1 => 10, param2 => 'value'); END; ``` **4. 触发器：** - **创建触发器：** ```sql CREATE OR REPLACE TRIGGER trg_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN -- trigger body END; ``` **5. Job创建和操作：** - **创建Job：** ```sql DECLARE job_no NUMBER; BEGIN DBMS_SCHEDULER.create_job( job_name => 'job_name', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN your_procedure_name; END;', start_date => SYSDATE, repeat_interval => 'FREQ=MINUTELY;INTERVAL=1', end_date => NULL, enabled => TRUE, job_class => 'DEFAULT_JOB_CLASS', comments => 'This is a sample job.', timeout => 100, auto_drop => TRUE, store_result => FALSE, job_id => job_no); END; ``` **6. 后台脚本：** - 创建后台脚本通常是指将一系列SQL命令或PL/SQL块封装在一个文件中，然后通过命令行或数据库工具执行该文件。例如: ```sql @script.sql ``` #### 七、同义词操作 **1. 创建同义词：** ```sql CREATE SYNONYM synonym_name FOR table_name; ``` **2. 删除同义词：** ```sql DROP SYNONYM synonym_name; ``` #### 八、dblink操作 **1. dblink创建：** ```sql CREATE DATABASE LINK dblink_name CONNECT TO remote_user IDENTIFIED BY password USING 'remote_db'; ``` **2. dblink操作：** - 查询远程表: 可以直接使用dblink查询远程表的数据。 - 插入数据到远程表: 也可以通过dblink向远程表插入数据。 **3. dblink删除：** ```sql DROP DATABASE LINK dblink_name; ``` #### 九、Sequences操作 **1. Sequences创建：** ```sql CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1 MAXVALUE 100000 CYCLE NOCACHE; ``` **2. Sequences删除：** ```sql DROP SEQUENCE seq_name; ``` #### 十、数据库操作必须掌握技巧 **1. 中断正在执行的session连接：** ```sql ALTER SYSTEM KILL SESSION 'sid, serial#'; ``` **2. 用户密码修改和用户账号加锁、解锁：** - **修改密码：** ```sql ALTER USER username IDENTIFIED BY new_password; ``` - **锁定用户：** ```sql ALTER USER username ACCOUNT LOCK; ``` - **解锁用户：** ```sql ALTER USER username ACCOUNT UNLOCK; ``` **3. 核心参数修改，数据库启动：** - **修改参数：** ```sql ALTER SYSTEM SET parameter_name = value SCOPE=BOTH; ``` - **启动数据库：** ```sql STARTUP ``` **4. 日志切换和初始化文件创建：** - **日志切换：** ```sql ALTER SYSTEM SWITCH LOGFILE; ``` - **初始化文件创建：** ```sql CREATE PFILE FROM SPFILE; ``` **5. 归档日志设置：** ```sql ALTER DATABASE ARCHIVELOG; ``` **6. 创建参数文件：** ```sql ALTER SYSTEM SET spfile='C:\oracle\product\11.2.0\dbhome_1\database\spfileorcl.ora' SCOPE=BOTH; ``` **7. 控制文件备份和重建：** - **备份控制文件：** ```sql ALTER DATABASE BACKUP CONTROLFILE TO TRACE; ``` - **重建控制文件：** ```sql RESTORE CONTROLFILE FROM 'C:\oracle\product\11.2.0\dbhome_1\database\control01.ctl'; ``` **8. 操作小技巧：** - **查看当前用户：** ```sql SHOW USER; ``` - **查看当前日期：** ```sql SELECT SYSDATE FROM DUAL; ``` **9. ORACLE10GRAC信息查看：** - **查看RAC实例信息：** ```sql SELECT instance_name, status, database_status FROM v$instance; ``` #### 十一、oracle锁管理 **1. ORACLE锁模式：** - **共享锁(S)：** - **排他锁(X)：** **2. ORACLE直接锁表方法：** ```sql LOCK TABLE table_name IN SHARE MODE; ``` #### 十二、数据备份和迁移 **1. exp和imp工具：** - **导出数据：** ```sql EXP userid=scott/tiger file=scott.dmp buffer=2048000 DIRECT=true ``` - **导入数据：** ```sql IMP userid=scott/tiger file=scott.dmp full=y ``` **2. 直接通过dblink进行表的insert：** ```sql INSERT INTO local_table (id, name) SELECT id, name FROM remote_table@dblink; ``` **3. rman备份：** - **全库备份：** ```sql RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT; ``` - **增量备份：** ```sql RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE; ``` **4. 直接文件copy：** - **复制数据文件：** ```bash cp /u01/app/oracle/oradata/orcl/datafile_name1.ora /backup/ ``` - **复制控制文件：** ```bash cp /u01/app/oracle/oradata/orcl/control01.ctl /backup/ ``` #### 十三、ORACLE性能调优问题 **1. Oracle优化细则：** - **索引使用：** - **查询优化：** **2. 常见oracle hint方法：** - **强制使用特定索引：** ```sql /*+ INDEX(table_name index_name) */ ``` - **禁止使用索引：** ```sql /*+ NO_INDEX(table_name) */ ``` **3. 动态性能视图(V$)：** - **查看等待事件：** ```sql SELECT * FROM V$WAITSTAT; ``` - **查看内存统计：** ```sql SELECT * FROM V$SGASTAT; ``` **4. 问题查看sql语句：** ```sql EXPLAIN PLAN FOR SELECT * FROM table_name WHERE column_name = 'value'; ``` **5. oracle性能监控工具配置：** - **AWR报告：** ```sql DBMS_WORKLOAD_REPOSITORY.snapshots; ``` - **ASH报告：** ```sql DBMS_SQL_MONITORING.report; ```