oracle数据库基本操作
编制时间：2010年2月12日-4月30日
写的很整洁,全是什么操作要用什么命令,多余的话,一句没有
一、表空间操作
1、文件系统：
创建表空间：
数据表空：
create tablespace tablespace_name logging datafile '/XXX/xxx/datafile_name1.ora' size 4095M;
临时表空间：
create temporay tablespae tmp_tablespace_name tempfile '/XXX/xxx/datafile_name1.ora' size 4095M;
回滚表空间：
create undo tablespace undotbs_tablespace datafile '/XXX/xxx/datafile_name1.ora' size 4095M;
增加表空间：
数据表空间：
alter tablespace tablespace_name add datafile '/XXX/xxx/datafile_name1.ora' size 4095M;
临时表空间：
alter tablespace temp_tablespace_name add tempfile '/XXX/xxx/datafile_name1.ora' size 4095M;
### Oracle数据库基本操作知识点
#### 一、表空间操作
**1. 文件系统：**
- **创建表空间：**
- **数据表空间：**
```sql
CREATE TABLESPACE tablespace_name LOGGING DATAFILE '/XXX/xxx/datafile_name1.ora' SIZE 4095M;
```
- **临时表空间：**
```sql
CREATE TEMPORARY TABLESPACE tmp_tablespace_name TEMPFILE '/XXX/xxx/datafile_name1.ora' SIZE 4095M;
```
- **回滚表空间：**
```sql
CREATE UNDO TABLESPACE undotbs_tablespace DATAFILE '/XXX/xxx/datafile_name1.ora' SIZE 4095M;
```
- **增加表空间：**
- **数据表空间：**
```sql
ALTER TABLESPACE tablespace_name ADD DATAFILE '/XXX/xxx/datafile_name1.ora' SIZE 4095M;
```
- **临时表空间：**
```sql
ALTER TABLESPACE temp_tablespace_name ADD TEMPFILE '/XXX/xxx/datafile_name1.ora' SIZE 4095M;
```
- **删除表空间：**
- **数据表空间：**
```sql
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
ALTER DATABASE DATAFILE '/XXX/xxx/datafile_name1.ora' OFFLINE DROP;
```
- **临时表空间：**
```sql
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
ALTER DATABASE DATAFILE '/XXX/xxx/tempfile_name1.ora' OFFLINE DROP;
```
**2. 裸卷：**
- 裸卷通常是指不经过文件系统的直接存储设备，这种类型的存储可以提高性能。
- 创建和管理裸卷表空间与文件系统的表空间类似，但路径会指向裸设备。
**3. ASM 存储管理表空间操作：**
- **ASM (Automatic Storage Management)** 是一种存储管理解决方案，用于管理数据库文件。
- 在ASM中创建表空间与普通文件系统中的创建方式不同，需要指定ASM磁盘组而不是物理文件路径。
- 示例：
```sql
CREATE TABLESPACE asm_tbs DATAFILE '+diskgroup' SIZE 4095M;
```
#### 二、用户、角色、权限
**1. 用户创建、修改、删除：**
- **创建用户：**
```sql
CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
```
- **修改用户：**
```sql
ALTER USER username ACCOUNT LOCK;
```
- **删除用户：**
```sql
DROP USER username CASCADE;
```
**2. 角色创建、修改、删除：**
- **创建角色：**
```sql
CREATE ROLE role_name;
```
- **修改角色：**
```sql
GRANT role1, role2 TO role_name;
```
- **删除角色：**
```sql
DROP ROLE role_name;
```
**3. 权限增加和收回：**
- **授予权限：**
```sql
GRANT create table, create view TO username;
```
- **收回权限：**
```sql
REVOKE create table FROM username;
```
#### 三、表操作
**1. 单表创建：**
- **创建表：**
```sql
CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
```
**2. 分区表创建：**
- **创建分区表：**
```sql
CREATE TABLE partitioned_table (column1 datatype, ...) PARTITION BY RANGE (column1) (PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200), ...);
```
**3. 分区操作：**
- **添加分区：**
```sql
ALTER TABLE partitioned_table ADD PARTITION new_partition VALUES LESS THAN (max_value);
```
- **删除分区：**
```sql
ALTER TABLE partitioned_table DROP PARTITION old_partition;
```
**4. 表状态查询：**
- **查询表状态：**
```sql
SELECT * FROM user_tables WHERE table_name = 'your_table_name';
```
**5. DDL语句操作：**
- **数据定义语言（DDL）包括CREATE、ALTER和DROP等命令。**
- **例如：**
```sql
CREATE TABLE test (id NUMBER(5));
ALTER TABLE test ADD (name VARCHAR2(50));
DROP TABLE test;
```
#### 四、视图操作
**1. 视图创建：**
- **创建视图：**
```sql
CREATE VIEW view_name AS SELECT * FROM table_name WHERE condition;
```
**2. 视图操作：**
- **修改视图：**
```sql
CREATE OR REPLACE VIEW view_name AS SELECT * FROM table_name WHERE condition;
```
- **查询视图：**
```sql
SELECT * FROM view_name;
```
**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_name PRIMARY KEY (column1);
```
- **创建唯一约束：**
```sql
ALTER TABLE table_name ADD CONSTRAINT uq_name UNIQUE (column1);
```
- **创建唯一索引：**
```sql
CREATE UNIQUE INDEX index_name ON table_name (column1);
```
#### 六、存储过程/job/函数/触发器操作
**1. 存储过程和函数：**
- **创建存储过程：**
```sql
CREATE OR REPLACE PROCEDURE procedure_name (p1 IN NUMBER, p2 OUT NUMBER) AS BEGIN ... END;
```
- **创建函数：**
```sql
CREATE OR REPLACE FUNCTION function_name (p1 NUMBER) RETURN NUMBER IS BEGIN ... END;
```
**2. 程序包：**
- **创建程序包：**
```sql
CREATE OR REPLACE PACKAGE pkg_name AS TYPE type_name IS ... END;
CREATE OR REPLACE PACKAGE BODY pkg_name AS ... END;
```
**3. 操作练习：**
- 包括创建、修改和删除存储过程、函数等。
**4. 触发器：**
- **创建触发器：**
```sql
CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN ... END;
```
**5. Job创建和操作：**
- **创建作业：**
```sql
DECLARE job_no NUMBER; BEGIN DBMS_SCHEDULER.create_job(job_name => 'job_name', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN ... END;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=DAILY', end_date => NULL, comments => 'This is a sample job', job_class => 1, enabled => TRUE, auto_drop => TRUE, store_outlines => FALSE, job_no => job_no); END;
```
**6. 后台脚本：**
- 可以编写shell脚本或其他类型的脚本来自动执行数据库操作。
#### 七、同义词操作
**1. 创建同义词：**
- **创建同义词：**
```sql
CREATE SYNONYM synonym_name FOR object_owner.object_name;
```
**2. 删除同义词：**
- **删除同义词：**
```sql
DROP SYNONYM synonym_name;
```
#### 八、dblink操作
**1. dblink创建：**
- **创建数据库链接：**
```sql
CREATE DATABASE LINK db_link_name CONNECT TO remote_user IDENTIFIED BY remote_password USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=remote_host)(PORT=port_number))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=remote_service_name)))';
```
**2. dblink操作：**
- **使用数据库链接：**
```sql
SELECT * FROM remote_owner.remote_table@db_link_name;
```
**3. dblink删除：**
- **删除数据库链接：**
```sql
DROP DATABASE LINK db_link_name;
```
#### 九、Sequences操作
**1. Sequences创建：**
- **创建序列：**
```sql
CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1 MAXVALUE 999999 NOCYCLE CACHE 10;
```
**2. Sequences删除：**
- **删除序列：**
```sql
DROP SEQUENCE sequence_name;
```
#### 十、数据库操作必须掌握技巧
**1. 中断正在执行的session连接：**
- **通过KILL命令终止会话：**
```sql
SELECT sid, serial# FROM v$session WHERE username = 'USER_NAME';
```
```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='/u01/app/oracle/admin/orcl/pfile/init.ora' FROM SPFILE;
```
**5. 创建参数文件：**
- **创建SPFILE：**
```sql
ALTER SYSTEM SET CONTROL_FILES='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orcl/control01.ctl' SCOPE=BOTH;
ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata/orcl' SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;
ALTER SYSTEM SET DB_NAME = 'orcl' SCOPE=BOTH;
ALTER SYSTEM SET DB_DOMAIN = '' SCOPE=BOTH;
ALTER SYSTEM SET DB_UNIQUE_NAME = 'orcl' SCOPE=BOTH;
ALTER SYSTEM SET DB_FILES = 200 SCOPE=BOTH;
ALTER SYSTEM SET DB_CACHE_SIZE = 512M SCOPE=BOTH;
ALTER SYSTEM SET DB_CHECKPOINT_INTERVAL = 120 SCOPE=BOTH;
ALTER SYSTEM SET DB_CHECKPOINT_TIMEOUT = 0 SCOPE=BOTH;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 3600 SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G SCOPE=BOTH;
ALTER SYSTEM SET DB_SECUREFILE = 'BASIC' SCOPE=BOTH;
ALTER SYSTEM SET DIAGNOSTIC_DEST = '/u01/app/oracle' SCOPE=BOTH;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 150 SCOPE=BOTH;
ALTER SYSTEM SET LARGE_POOL_SIZE = 50M SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT = 'arc_%t_%s_%r.arc' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/u01/app/oracle/arch' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_MAXSEQUENCES = 1000 SCOPE=BOTH;
ALTER SYSTEM SET LOG_BUFFER = 16M SCOPE=BOTH;
ALTER SYSTEM SET MEMORY_MAX_TARGET = 0 SCOPE=BOTH;
ALTER SYSTEM SET MEMORY_TARGET = 512M SCOPE=BOTH;
ALTER SYSTEM SET OPEN_CURSORS = 300 SCOPE=BOTH;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 128M SCOPE=BOTH;
ALTER SYSTEM SET PRE_PAGE_SGA = FALSE SCOPE=BOTH;
ALTER SYSTEM SET PROCESSES = 150 SCOPE=BOTH;
ALTER SYSTEM SET SGA_MAX_SIZE = 384M SCOPE=BOTH;
ALTER SYSTEM SET SGA_TARGET = 256M SCOPE=BOTH;
ALTER SYSTEM SET SHARED_POOL_SIZE = 128M SCOPE=BOTH;
ALTER SYSTEM SET STREAMS_POOL_SIZE = 50M SCOPE=BOTH;
ALTER SYSTEM SET UNDO_MANAGEMENT = 'AUTO' SCOPE=BOTH;
ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDOTBS1' SCOPE=BOTH;
ALTER SYSTEM SET UNDO_RETENTION = 900 SCOPE=BOTH;
```
**6. 控制文件备份和重建：**
- **备份控制文件：**
```sql
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
```
- **重建控制文件：**
```sql
RESTORE CONTROLFILE FROM '/path/to/backup/controlfile.trc';
```
**7. 操作小技巧：**
- 包括各种数据库管理的小技巧和命令。
**8. ORACLE10GRAC信息查看：**
- **查看集群信息：**
```sql
SELECT * FROM v$instance;
SELECT * FROM v$database;
SELECT * FROM v$clusterglobal;
```
**9. 审计开启：**
- **启用审计：**
```sql
AUDIT SELECT ON table_name;
AUDIT UPDATE ON table_name;
```
**10. TNS密码设置：**
- **设置TNS密码：**
```sql
ORACLE_SID=orcl sqlplus / as sysdba
ALTER USER username IDENTIFIED BY password;
```
**11. 访问限制数据库主机：**
- **限制远程访问：**
```sql
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE';
```
- **更改监听器设置：**
```sql
EDIT $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
```
**12. 文本文件向表的转换：**
- **导入数据：**
```sql
SQL*Loader data_file.csv BADFILE bad_file.csv LOG log_file.log TRUNCATE INTO TABLE your_table FIELDS TERMINATED BY ',' ENCLOSED BY '"'
```
#### 十一、Oracle锁管理
**1. Oracle锁模式：**
- **行共享（RS）：** 非排他性锁。
- **行排他（RX）：** 排他性锁。
- **共享（S）：** 多个事务可以同时读取同一数据对象。
- **共享排他（SX）：** 允许其他共享锁，但不允许任何排他锁。
- **排他（X）：** 不允许其他任何类型的锁。
**2. Oracle直接锁表方法：**
- **锁定表：**
```sql
LOCK TABLE table_name IN SHARE MODE;
LOCK TABLE table_name IN EXCLUSIVE MODE;
```
#### 十二、数据备份和迁移
**1. exp和imp工具：**
- **导出数据：**
```sql
EXPDP username/password DIRECTORY=dir_name DUMPFILE=dump_file.dmp
```
- **导入数据：**
```sql
IMPDP username/password DIRECTORY=dir_name DUMPFILE=dump_file.dmp
```
**2. 直接通过dblink进行表的insert：**
- **通过dblink插入数据：**
```sql
INSERT INTO local_table SELECT * FROM remote_table@db_link_name;
```
**3. RMAN备份：**
- **创建备份：**
```sql
RMAN TARGET=sys/password@orcl
RUN {
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
BACKUP AS COMPRESSED BACKUPSET DATABASE;
BACKUP ARCHIVELOG ALL;
BACKUP CURRENT CONTROLFILE;
}
```
- **恢复数据：**
```sql
RMAN TARGET=sys/password@orcl
RUN {
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
```
**4. 直接文件copy：**
- **复制文件：**
```sql
cp /path/to/source/file /path/to/destination
```
#### 十三、Oracle性能调优问题
**1. Oracle优化细则：**
- **调整缓存大小：**
```sql
ALTER SYSTEM SET DB_CACHE_SIZE=128M SCOPE=BOTH;
```
- **优化索引：**
```sql
ANALYZE INDEX index_name VALIDATE STRUCTURE;
```
- **调整参数：**
```sql
ALTER SYSTEM SET parameter_name=value SCOPE=BOTH;
```
**2. 常见Oracle hint方法：**
- **使用hint优化SQL：**
```sql
SELECT /*+ INDEX_DESC(table_name index_name) */ column1, column2 FROM table_name WHERE column1 = value;
```
**3. 动态性能视图(V$)：**
- **查询V$视图：**
```sql
SELECT * FROM V$SESSION;
SELECT * FROM V$BUFFER_POOL;
SELECT * FROM V$ROWCACHE;
```
**4. 问题查看SQL语句：**
- **查看执行计划：**
```sql
EXPLAIN PLAN FOR SELECT * FROM table_name WHERE column1 = value;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
```
**5. Oracle性能监控工具配置：**
- **配置AWR报告：**
```sql
DBMS_WORKLOAD_REPOSITORY.SWITCH_SNAPSHOT;
DBMS_WORKLOAD_REPOSITORY.CREATE_REPORT(SNAPSHOT_ID => SNAPSHOT_ID, REPORT_FORMAT=>'HTML');
```
- **配置ASH报告：**
```sql
SELECT * FROM V$SYSSTAT;
SELECT * FROM V$ASH;
```
- 1
- 2
- 3
- 4
- 5
前往页