Oracle数据库运维常用SQL语句
Oracle数据库作为一款功能强大的数据库管理系统，在企业级应用中广泛应用。熟练掌握Oracle数据库的SQL语句是数据库管理员必备的技能。
数据库创建表空间
表空间查询
1select name from v$datafile;
或
1select tablespace_name,file_name from dba_data_files;
查找数据文件指标及路径
1select b.file_id 文件ID,
2 b.tablespace_name 表空间,
3 b.file_name 物理文件名,
4 b.bytes 总字节数,
5 (b.bytes-sum(nvl(a.bytes,0))) 已使用,
6 sum(nvl(a.bytes,0)) 剩余,
7 sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
8 from dba_free_space a,dba_data_files b
9 where a.file_id=b.file_id
10 group by b.tablespace_name,b.file_name,b.file_id,b.bytes
11 order by b.tablespace_name;
表空间创建
1create tablespace FRAME datafile '/data/ora11/oradata/FRAME.dbf' size 1024M autoextend on next 50M maxsize unlimited;
-
数据文件的物理位置和文件名为 '/data/ora11/oradata/FRAME.dbf'
-
数据文件的初始大小为1024MB
-
数据文件具有自动扩展功能，每次扩展50MB
-
数据文件的最大可扩展大小没有限制
创建用户
1create user test1
2 identified by "admin123"
3 default tablespace FRAME
4 temporary tablespace TEMP
5 profile DEFAULT;
- 创建了一个名为"test1"的用户
- 密码为"admin123"
- 默认表空间为"FRAME"
- 临时表空间为"TEMP"
- 配置文件为"DEFAULT"
用户授权
1grant connect to test1 with admin option -- 授予用户test1连接数据库的权限;
2grant dba to test1 with admin option-- 授予用户test1 DBA角色，该角色拥有数据库的所有权限，包括管理所有数据库对象、用户和权限的权限。;
3grant alter any sequence to test1 with admin option -- 授予用户test1修改任何序列的权限。;
4grant create any sequence to test1 with admin option -- 授予用户test1创建任何序列的权限;
5grant drop any sequence to test1 with admin option -- 授予用户test1删除任何序列的权限;
6grant select any sequence to test1 with admin option -- 授予用户test1查询任何序列的权限;
7grant unlimited tablespace to test1 with admin option -- 授予用户test1无限使用表空间的权限;
这些语句带有"with admin option"，意味着test1可以将这些权限授予其他用户。
用户权限取消
1revoke connect from test1 -- 取消用户test1连接数据库的权限;
2revoke dba from test1 -- 取消用户test1 DBA角色，该角色拥有数据库的所有权限，包括管理所有数据库对象、用户和权限的权限。;
3revoke alter any sequence from test1 -- 取消用户test1修改任何序列的权限。;
4revoke create any sequence from test1 -- 取消用户test1创建任何序列的权限;
5revoke drop any sequence from test1 -- 取消用户test1删除任何序列的权限;
6revoke select any sequence from test1 -- 取消用户test1查询任何序列的权限;
7revoke unlimited tablespace from test1 -- 取消用户test1无限使用表空间的权限;
8REVOKE ALL PRIVILEGES FROM test1; -- 取消用户test1所有权限
dblink
DBLINK 是 Database Link 的缩写，是 Oracle 数据库中的一个对象，用于定义一个数据库到另一个数据库的连接。DBLINK 允许用户在本地数据库中访问远程数据库中的数据和对象。
删除dblink
1drop database link TESTDBLINK.US.ORACLE.COM;
创建dblink
1create database link TESTDBLINK.US.ORACLE.COM
2 connect to 用户 identified by 密码
3 using '(DESCRIPTION =
4 (ADDRESS_LIST =
5 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1521))
6 )
7 (CONNECT_DATA =
8 (SERVICE_NAME =实例名)
9 )
10 )';
创建同名词
同名词（synonym）是指对数据库对象（表、视图、函数、过程、包等）的别名。同名词可以简化对象的访问，提高数据库的安全性
1create or replace synonym CITY
2 for TEST2.CITY@TESTDBLINK.US.ORACLE.COM;
创建了一个名为 CITY 的同名词，指向远程数据库 TESTDBLINK.US.ORACLE.COM 中的 TEST2 用户CITY的表
- 同名词验证
同名词创建后可以使用以下两条SQL查询验证是否正常，效果相同
1SELECT *
2FROM TEST2.CITY@TESTDBLINK.US.ORACLE.COM;
3-- 或
4SELECT *
5FROM CITY;
表解锁
1SELECT SESS.SID, SESS.SERIAL#, LO.ORACLE_USERNAME, LO.OS_USER_NAME, AO.OBJECT_NAME 被锁对象名, LO.LOCKED_MODE 锁模式, sess.LOGON_TIME 登录数据库时间,
2'ALTER SYSTEM KILL SESSION ''' || SESS.SID || ','||SESS.SERIAL#||'''' FREESQL
3FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS
4WHERE AO.OBJECT_ID = LO.OBJECT_ID AND LO.SESSION_ID = SESS.SID ORDER BY sid, sess.serial#;
字符集查询
1select userenv('language') from dual;
Oracle用户密码期限查询
默认用户密码必须在180天后修改问题，如果到时间没修改密码那用户就会被锁定，使远程用户无法使用工具连接。
1--查询期限
2select * from dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
将密码规则修改为无限制
1ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
数据库默认无法导出空表，修改数据库参数，防止数据库空表导出报错
1alter system set deferred_segment_creation=false;
注意该值设置后对以前导入的空表不产生作用，仍不能导出，只能对后面新增的表产生作用，所以我们在安装好数据库后就应该 马上执行此操作。
显示当前连接用户
1 show user
查看系统拥有哪些用户
1 select * from all_users;
新建用户并授权
1 create user a identified by a;（默认建在SYSTEM表空间下）
2 grant connect,resource to a;
查询当前用户下所有对象
1select * from tab;
建立第一个表
1 create table a(a number);
查询表结构
1 desc a
插入新记录
1insert into a values(1);
查询记录
1 select * from a;
更改记录
1update a set a=2;
删除记录
1 delete from a;
回滚
roll;
rollback; 提交
commit;