ORACLE数据库相关操作

表操作

-- 截断表

1. TRUNCATE TABLE TABLE_NAME;

复制代码

-- 删除表

1. DROP TABLE TABLE_NAME;

复制代码

-- 查询表

1. SELECT * FROM TABLE_NAME;

复制代码

-- 添加一条记录

1. INSERT INTO TABLE_NAME(COLUMN) VALUES(VALUE);

复制代码

-- 删除记录

1. DELETE FROM TABLE_NAME WHERE COLUMN=VALUE;

复制代码

-- 修改记录

1. UPDATE TABLE_NAME SET COLUMN1='VALUE1' WHERE COLUMN2=VALUE2;

复制代码

-- 锁表查询

1. SELECT SESS.SID,SESS.SERIAL#,LO.ORACLE_USERNAME,LO.OS_USER_NAME,AO.OBJECT_NAME,LO.LOCKED_MODE FROM V$LOCKED_OBJECT LO,DBA_OBJECTS AO,V$SESSION "SESS" WHERE AO.OBJECT_ID=LO.OBJECT_ID AND LO.SESSION_ID=SESS.SID;

复制代码

-- 解除锁表

1. ALTER SYSTEM KILL SESSION 'XXX,XXX';

复制代码

时间

-- 查询系统时间

1. SELECT SYSDATE FROM DUAL;
2. SELECT CURRENT_DATE FROM DUAL;
3. SELECT SYSTIMESTAMP FROM DUAL;

复制代码

-- 字符串转时间

1. SELECT TO_DATE('1970-01-01 01:00:00','SYYYY-MM-DD HH24:MI:SS') FROM DUAL;

复制代码

-- 拼接当天第一秒和当天最后一秒

1. SELECT TO_DATE(TO_CHAR(SYSDATE,'yyyy-mm-dd') || ' 00:00:01','yyyy-mm-dd hh24:mi:ss') FROM DUAL; -- 当天第一秒
2. SELECT TO_DATE(TO_CHAR(SYSDATE,'yyyy-mm-dd') || ' 23:59:59','yyyy-mm-dd hh24:mi:ss') FROM DUAL; -- 当天最后一秒

复制代码

-- 查询系统数据库时区

1. SELECT DBTIMEZONE FROM DUAL;

复制代码

-- 修改系统数据库时区为 +8:00

1. ALTER DATABASE SET TIME_ZONE='+8:00';

复制代码

-- 查看session所属时区

1. SELECT SESSIONTIMEZONE FROM DUAL;

复制代码

-- 修改session所属时区

1. ALTER SESSION SET TIME_ZONE='+8:00'; --原本为-UTC

复制代码

系统

-- 查询数据库版本

1. SELECT * FROM V$VERSION;

复制代码

-- 系统游标修改

1. ALTER SYSTEM SET OPEN_CURSORS =2000 SCOPE=BOTH; --改为2000

复制代码

-- 查询当前SID

1. SELECT NAME FROM V$DATABASE;

复制代码

-- 查看连接数

1. SELECT COUNT(*) FROM V$PROCESS;

复制代码

-- 查看最大连接数

1. SELECT VALUE FROM V$PARAMETER WHERE NAME = 'processes';

复制代码

-- 修改最大连接数 sqlplus 需要重启

1. ALTER SYSTEM SET PROCESSES = 650 SCOPE = SPFILE;

复制代码

用户

-- 查询用户信息

1. SELECT * FROM DBA_USERS;
2. SELECT * FROM DBA_USERS WHERE ACCOUNT_STATUS='OPEN';
3. SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS='OPEN';

复制代码

-- 查看当前登录用户

1. -- SHOW USER;

复制代码

-- 创建用户

1. CREATE USER USERNAME IDENTIFIED BY USERPASSWORD;

复制代码

-- 用户授权

1. GRANT CONNECT,RESOURCE,DBA TO USERNAME; --连接权限，资源访问权限，DBA权限

复制代码

-- 存储过程授权

1. GRANT CREATE ANY PROCEDURE TO USERNAME; --创建权限
2. GRANT EXECUTE ANY PROCEDURE TO USERNAME; --执行权限
3. GRANT DEBUG ANY PROCEDURE,DEBUG CONNECT SESSION TO USERNAME; --DEBUG权限
4. GRANT SELECT ON SYS.V_$SESSION TO USERNAME; --对单一用户打开
5. GRANT SELECT ON SYS.V_$SESSTAT TO USERNAME; --对单一用户打开
6. GRANT SELECT ON SYS.V_$STATNAME TO USERNAME; --对单一用户打开
7. GRANT SELECT ANY DICTIONARY TO PRACTICE; --不采用
8. GRANT SELECT ON V$SESSION TO PUBLIC; --对所有用户打开，所有用户生效
9. GRANT SELECT ON V$SESSTAT TO PUBLIC; --对所有用户打开，所有用户生效
10. GRANT SELECT ON V$STATNAME TO PUBLIC; --对所有用户打开，所有用户生效
11. SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='USERNAME'; --查询角色权限
12. SELECT * FROM ROLE_SYS_PRIVS; ----系统权限
13. SELECT * FROM ROLE_TAB_PRIVS; --对象权限
14. SELECT * FROM ROLE_ROLE_PRIVS; --角色权限

复制代码

-- 撤销用户授权

1. REVOKE CONNECT,RESOURCE,DBA FROM USERNAME;

复制代码

-- 用户空间分配

1. ALTER USER USERNAME QUOTA UNLIMITED ON USERS; --不做用户空间资源限制
2. ALTER USER USERNAME QUOTA 300M ON USERS; --限制用户最大空间资源为300M

复制代码

-- 查询用户密码过期策略

1. SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

复制代码

-- 修改用户密码过期策略

1. ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; --设置密码永不过期

复制代码

-- 查询用户密码失败策略

1. SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS';

复制代码

-- 修改用户密码失败策略

1. ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED; --不限制认证失败次数
2. ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 30; --30累计认证失败锁定账户

复制代码

-- 查询 CONNECT_TIME 所在资源文件名

1. SELECT RESOURCE_NAME,PROFILE FROM DBA_PROFILES;

复制代码

-- 修改资源限制

1. ALTER PROFILE DEFAULT LIMIT CONNECT_TIME UNLIMITED; --不限制连接超时
2. ALTER PROFILE DEFAULT LIMIT CONNECT_TIME 60; --限制连接60分钟超时
3. ALTER PROFILE DEFAULT LIMIT IDLE_TIME UNLIMITED; --不限制会话超时
4. ALTER PROFILE DEFAULT LIMIT IDLE_TIME 20; --限制会话最长连续空闲时间为20分钟

复制代码

-- 查询资源限制值

1. SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT';

复制代码

-- 查询用户使用PROFILE文件

1. SELECT USERNAME,PROFILE FROM DBA_USERS;

复制代码

-- 查询用户资源使用监控是否开启

1. SELECT NAME,VALUE FROM GV$PARAMETER WHERE NAME='resource_limit';

复制代码

-- 开启用户资源使用监控

1. ALTER SYSTEM SET resource_limit=TRUE;

复制代码

存储过程

-- 创建存储过程

1. CREATE OR REPLACE PROCEDURE PROCNAME
2. AS
3. BEGIN
4.     DBMS_OUTPUT.PUT_LINE('STORED PROCEDURE PROCNAME'); --控制台输出
5. END;

复制代码

-- 查看存储过程

1. SELECT TEXT FROM USER_SOURCE WHERE NAME = 'PROCNAME' ORDER BY LINE;

复制代码

-- 调用存储过程

1. CALL PROCNAME();
2. 或
3. BEGIN
4.     PROCNAME;
5. END;

复制代码

-- 删除存储过程

1. DROP PROCEDURE PROCNAME;

复制代码

视图

-- 创建视图

1. CREATE VIEW VIEW_NAME AS
2.     SELECT SYSDATE FROM DUAL
3. WITH READ ONLY;

复制代码

-- 查看视图

1. SELECT * FROM VIEW_NAME;

复制代码

-- 视图授权

1. GRANT SELECT ON VIEW_NAME TO USERNAME;

复制代码

-- 撤销视图授权

1. REVOKE SELECT ON VIEW_NAME FROM USERNAME;

复制代码

-- 删除视图

1. DROP VIEW USERNAME;

复制代码

外部ORACLE连接

-- 创建一个外部ORACLE连接

1. create database link dblink_name
2.   connect to USERNAME identified BY "USERPASSWORD"
3.   using '(DESCRIPTION =
4.     (ADDRESS_LIST =
5.       (ADDRESS = (PROTOCOL = TCP)(HOST = "IPADDR")(PORT = "PORT"))
6.     )
7.     (CONNECT_DATA =
8.       (SERVICE_NAME = "ORCL")
9.     )
10.   )';

复制代码

-- 查询数据库相关连接

1. SELECT OWNER,OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='DATABASE LINK';
2. SELECT * FROM DBA_DB_LINKS;

复制代码

-- 删除数据库连接

1. DROP DATABASE LINK dblink_name;

复制代码

-- 从外部ORACLE连接查询数据

1. SELECT * FROM USERNAME.TABLENAME@dblink_name;
2. SELECT * FROM TABLENAME@dblink_name;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！