登录
sqlplus / as sysdba
sqlplus username/password@hostname:port/service_name
示例:
sqlplus scott/tiger@192.168.1.10:1521/orcl
退出SQL*Plus
exit
设置行格式和标题
set linesize 200;
set pagesize 30;
set heading on/off; -- 控制列标题显示与否
创建表
CREATE TABLE employees (
employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(25),
hire_date DATE
);
删除表
DROP TABLE employees CASCADE CONSTRAINTS;
修改表结构
ALTER TABLE employees ADD salary NUMBER(8,2);
插入数据
INSERT INTO employees (employee_id, first_name, last_name, hire_date)
VALUES (100, 'John', 'Doe', TO_DATE('2000-01-01', 'YYYY-MM-DD'));
查询数据
SELECT * FROM employees;
更新数据
UPDATE employees SET salary = 5000 WHERE employee_id = 100;
删除数据
DELETE FROM employees WHERE employee_id = 100;
DECLARE
v_employee_id employees.employee_id%TYPE;
BEGIN
FOR emp_rec IN (SELECT employee_id FROM employees) LOOP
v_employee_id := emp_rec.employee_id;
-- 进行其他操作...
END LOOP;
END;
创建用户
CREATE USER db_user IDENTIFIED BY password;
授权给用户
GRANT CONNECT, RESOURCE TO db_user;
撤销权限
REVOKE DELETE ON employees FROM db_user;
导出数据
expdp username/password directory=DATA_PUMP_DIR dumpfile=mydump.dmp logfile=expdp_mydump.log schemas=myschema
导入数据
impdp username/password directory=DATA_PUMP_DIR dumpfile=mydump.dmp logfile=impdp_mydump.log remap_schema=old_schema:new_schema
Oracle数据库的命令主要分为SQL*Plus命令、SQL命令和PL/SQL语句,以及一些操作系统级别的启动与关闭命令。以下是按功能分类的部分Oracle常用命令及其示例:
登录
sqlplus / as sysdba
sqlplus username/password@hostname:port/service_name
示例:
sqlplus scott/tiger@192.168.1.10:1521/orcl
退出SQL*Plus
exit
设置行格式和标题
set linesize 200;
set pagesize 30;
set heading on/off; -- 控制列标题显示与否
创建表
CREATE TABLE employees (
employee_id NUMBER(6) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(25),
hire_date DATE
);
删除表
DROP TABLE employees CASCADE CONSTRAINTS;
修改表结构
ALTER TABLE employees ADD salary NUMBER(8,2);
插入数据
INSERT INTO employees (employee_id, first_name, last_name, hire_date)
VALUES (100, 'John', 'Doe', TO_DATE('2000-01-01', 'YYYY-MM-DD'));
查询数据
SELECT * FROM employees;
更新数据
UPDATE employees SET salary = 5000 WHERE employee_id = 100;
删除数据
DELETE FROM employees WHERE employee_id = 100;
DECLARE
v_employee_id employees.employee_id%TYPE;
BEGIN
FOR emp_rec IN (SELECT employee_id FROM employees) LOOP
v_employee_id := emp_rec.employee_id;
-- 进行其他操作...
END LOOP;
END;
创建用户
CREATE USER db_user IDENTIFIED BY password;
授权给用户
GRANT CONNECT, RESOURCE TO db_user;
撤销权限
REVOKE DELETE ON employees FROM db_user;
导出数据
expdp username/password directory=DATA_PUMP_DIR dumpfile=mydump.dmp logfile=expdp_mydump.log schemas=myschema
导入数据
impdp username/password directory=DATA_PUMP_DIR dumpfile=mydump.dmp logfile=impdp_mydump.log remap_schema=old_schema:new_schema
创建索引
CREATE INDEX emp_idx ON employees (last_name);
删除索引
DROP INDEX emp_idx;
查看索引信息
SELECT * FROM user_indexes WHERE table_name = 'EMPLOYEES';
创建视图
CREATE VIEW emp_view AS SELECT first_name, last_name FROM employees;
查询视图
SELECT * FROM emp_view;
更新视图(如果视图支持更新)
UPDATE emp_view SET last_name = 'NewName' WHERE first_name = 'John';
删除视图
DROP VIEW emp_view;
创建同义词
CREATE SYNONYM emp_syn FOR hr.employees;
通过同义词查询数据
SELECT * FROM emp_syn;
删除同义词
DROP SYNONYM emp_syn;
创建序列
CREATE SEQUENCE emp_sequence START WITH 1 INCREMENT BY 1;
使用序列生成值
INSERT INTO employees (employee_id, first_name) VALUES (emp_sequence.NEXTVAL, 'Jane');
创建触发器
CREATE OR REPLACE TRIGGER emp_bir
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:new.employee_id := emp_sequence.nextval;
END;
查看当前会话信息
SELECT sid, serial#, status, username, osuser FROM v$session;
终止指定会话
ALTER SYSTEM KILL SESSION 'sid,serial#';
授予用户权限
GRANT SELECT, INSERT, UPDATE ON employees TO user1;
撤销用户权限
REVOKE UPDATE ON employees FROM user1;
为用户创建角色并分配权限
CREATE ROLE hr_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO hr_admin;
GRANT hr_admin TO user1;
数据库审计
AUDIT SELECT ON employees BY ACCESS;
-- 或者针对特定用户进行审计
AUDIT ALL ON employees BY user1 BY SESSION;
-- 查看审计策略
SELECT * FROM dba_auditing_options;
SELECT * FROM dba_audit_trail;
逻辑备份(导出)
使用expdp
或exp
工具进行数据泵导出,例如:
expdp system/password directory=DATA_PUMP_DIR dumpfile=emp.dmp logfile=expdp_emp.log schemas=hr
逻辑恢复(导入)
使用impdp
或imp
工具进行数据泵导入,例如:
impdp system/password directory=DATA_PUMP_DIR dumpfile=emp.dmp logfile=impdp_emp.log remap_schema=old_hr:new_hr
物理备份
执行RMAN(Recovery Manager)命令进行全库备份或增量备份。
执行一致性检查
ALTER TABLE employees ENABLE CONSTRAINTS;
使用DBMS_REPAIR包
在Oracle中,可以使用DBMS_REPAIR包进行表的完整性检查和修复。这通常用于处理较为严重的问题,如数据块损坏等情况。
跨数据库的数据迁移
可以通过数据泵、GoldenGate等工具实现不同数据库间的结构和数据迁移。
实时数据同步
Oracle GoldenGate或其他第三方工具可用于实现Oracle数据库之间的实时数据同步。
请注意,在实际操作时,请根据Oracle官方文档和最佳实践调整相应的命令和策略,并确保在对生产环境做任何重大变更之前,制定详尽的计划和备份方案。
python推荐学习汇总连接:
50个开发必备的Python经典脚本(1-10)
50个开发必备的Python经典脚本(41-50)
————————————————
?最后我们放松一下眼睛