数据库-oracle常用SQL语句整理（持续更新中）_multiple job modes requested, full and schemas.-优快云博客

这篇博客整理了Oracle数据库的一些常用操作，包括查询UUID、文件长度、复制表、查询用户、查看JOB、修改表名、添加列以及用户权限管理等。还涉及到表空间的大小查询、用户表空间的创建与权限分配，以及数据库的导入导出操作。此外，还分享了如何进行表连接、闪回操作以及数值函数的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--查询uuid
SELECT sys_guid() FROM dual;

--查询文件长度函数DBMS_LOB.GETLENGTH
SELECT DBMS_LOB.GETLENGTH(F."FILE") FROM T_EXPERT_FILE F WHERE ...

SELECT DBMS_LOB.GETLENGTH(F.filess) FROM T_EXPERT_FILE F WHERE ...

--复制表
create table newtable as select * from oldname where ...

--查询用户
SELECT * FROM all_users;

--查询函数
select * from dba_objects where object_type='FUNCTION';

--给某用户解锁
alter user cpist account unlock;

--查看job
SELECT * FROM Dba_Jobs;
SELECT * FROM dba_jobs WHERE log_user='CPIDBS';

--查看字符集
select userenv('language') from dual; --SIMPLIFIED CHINESE_CHINA.ZHS16GBK;

--查询某个表所有的触发器
select * from all_triggers where table_name =upper( 'chepb') AND owner=upper('cpixs');

--查询所有的存储过程
select * from user_objects where object_type='PROCEDURE';-- 一定要大写

--修改表名
alter table tableName_old rename to tableName_new

--添加列
alter table tableName add colName 字段类型

--修改列名
alter table taleName rename column colName to newColName

--修改列类型
alter table tableName modify colName 字段类型

--替换
从a中找到b,替换成c,若没有c表示从a中删掉b
SELECT REPLACE('a','b','c') FROM dual;

--表连接
select * from tableA cross join tableB;

--闪回
select * from meikxxb
--闪回过程
1，alter table meikxxb enable row movement;
2，flashback table meikxxb to timestamp to_timestamp ('2010-10-13 10:00:00','yyyy-mm-dd hh24:mi:ss');
3，alter table meikxxb disable row movement;

2014年07月31日更新

oracle用户的权限管理
-------------sys Login------------
1. 创建表空间及临时表空间
create tablespace csdn1 datafile 'csdn1' size 30m autoextend on;
create temporary tablespace csdn2 tempfile 'csdn2' size 30m autoextend on;

2. 创建用户指定表空间及临时表空间
create user csdn identified by csdn default tablespace csdn1 temporary tablespace csdn2;

3. 授予用户各种权利
grant create session to csdn;
grant unlimited tablespace to csdn;
grant connect to csdn;

grant resource to csdn;
grant create sequence to csdn;
grant create table to csdn;

4. 查询当前用户的权限
select * from user_sys_privs;

5. 撤销用户各种权限
revoke create table from csdn;
revoke create session from csdn;revoke create sequence to csdn;
revoke resource to csdn;revoke connect to csdn;
revoke unlimited tablespace to csdn;

6.通过角色来赋予用户各种权限
create user root identified by root default tablespace csdn1 temporary tablespace csdn2;
create role role1;grant create table to role1;
grant create session to role1;
grant connect to role1;grant resource to role1;
grant create sequence to role1;

(1) 将角色赋予给用户grant role1 to root;
(2) 删除角色drop role role1;

7.创建序列

create sequence xulie

minvalue 1

maxvalue 222222

start with 1

increment by 1

nocache

nocycle

8.数值函数
select ceil(13.2) from tb_stu; --向上取整
select floor(12.9) from tb_stu;--向下取整
select power(9,19) from tb_stu;--9的19次方
select sqrt(8) from tb_stu; --8的平方根
select sign(-2) from tb_stu; --正数返1 负数返-1 0返0
select trunc(12.232323123,5) from tb_stu;--取5位小数
select round(1232.343,2) from tb_stu;--小数位为2 四舍五入
select exp(3) from tb_stu; --求指数
select mod(12,8) from tb_stu; --求余数
select ln(10) from tb_stu;--自然对数
select log(10,100) from tb_stu;--以10为底 100的对数
select vsize(1010) from tb_stu;--返回1010所占空间的大小

9.常用的函数

select initcap(stu_name) from tb_stu;--首字符转换成大写
select stu_name,instr(stu_name,'s') from tb_stu;--查找s在stu_name中的位置返回相应的值(0 n)
select length(stu_name) from tb_stu;--返回长度
select upper(stu_name) from tb_stu;--换大写
select lower(stu_name) from tb_stu;--换小写
select lpad(stu_name,11,'Hello') from tb_stu;--长度不够则在左边填充Hello 直到够11

2018年09月05日更新

查询oracle数据库表空间的大小，已使用空间，剩余空间

1. 查看所有表空间大小
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

2. 已经空闲的表空间大小
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

3. 空间使用情况查看
　　select a.tablespace_name,total,free,total-free used from
　（select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
　　group by tablespace_name) a,
　　( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
　　group by tablespace_name) b
　　where a.tablespace_name=b.tablespace_name;

4. 查看所有segment的大小。
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

2018年09月06日更新

（1）以oracle身份登录数据库，命令：su - oracle
（2）进入Sqlplus控制台，命令：sqlplus /nolog
（3）以系统管理员登录，命令：connect / as sysdba

导入数据库步骤：
dmp文件信息：
用户-大小-表空间-临时表空间
GBIA-195.85G-cargo-tempcargo
DSS-12.51G-dss-tempcargo
INFOLINK-14.95G-infolink-tempcargo

dmp文件导入步骤：
首先登陆管理员账号，或者有DBA权限的用户，
（1）以oracle身份登录数据库，命令：su - oracle
（2）进入Sqlplus控制台，命令：sqlplus /nolog
（3）以系统管理员登录，命令：connect / as sysdba
接下来依次：

--查询所有用户
select * from dba_users;

--创建新用户
create user DSS identified by DSS;

--查看所有用户所在表空间
select username,default_tablespace from dba_users;

--查询所有表空间路径
select * from dba_data_files ;

--创建一个表空间
create tablespace DSS_DATA datafile '/home/oracle/app/oradata/orcl/DSS_DATA_1.DBF' size 200m autoextend on;--创建成功

create tablespace DSS_DATA datafile '/home/oracle/app/oradata/orcl/DSS_DATA_1.DBF'
size 200m
autoextend on
next 32m maxsize 2048m
extent management local;

--创建临时表空间
--表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表，所以称作表空间。Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间，可自动释放；而表空间中存储表数据、函数、过程、序列等。是随数据库永久存在的。
create temporary tablespace tempcargo tempfile '/home/oracle/app/oradata/orcl/tempcargo.dbf' size 50M autoextend ON next 10M maxsize 100M;

--分配表空间和临时表空间
alter user DSS default tablespace DSS_DATA temporary tablespace tempcargo;

--给用户分配权限
grant create session,create table,create view,create sequence,unlimited tablespace to DSS;
grant dba to DSS;

--表空间重命名---不需要！
alter tablespace GPSPACE rename to GPMGT_DATA;
Tablespace altered

最后导入表数据。
--查询路径
select * from dba_directories;
或者
desc dba_directories;
--使用创建DIRECTORY的用户给导出用户授权
GRANT READ,WRITE ON DIRECTORY DUMP_TEST to DSS;

--将dmp文件移动到directory
mv /home/oracle/gbia.dmp /home/oracle/app/admin/orcl/dpdump/
mv /home/oracle/app/admin/orcl/dpdump/gbia.dmp /home/oracle/app/backupdata/

--导入
[oracle@V53 ~]$impdp DSS/DSS@orcl directory=DUMP_TEST dumpfile=gbia.dmp nologfile=y fromuser=DSS 执行，信息如下：
Import: Release 11.2.0.1.0 - Production on Thu Sep 6 19:12:14 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "fromuser=DSS" Location: Command Line, Replaced with: "schemas=DSS"
ORA-39002: invalid operation
ORA-39059: dump file set is incomplete
ORA-39246: cannot locate master table within provided dump files

改为下面
[oracle@V53 ~]$impdp DSS/DSS@orcl directory=DUMP_TEST dumpfile=gbia.dmp nologfile=y schemas=DSS 执行，信息如下：
Import: Release 11.2.0.1.0 - Production on Thu Sep 6 19:13:45 2018

改为下面
[oracle@V53 ~]$impdp DSS/DSS directory=DUMP_TEST dumpfile=gbia.dmp full=y nologfile=y schemas=DSS version=11.2.0.1.0 执行，信息如下：

Import: Release 11.2.0.1.0 - Production on Thu Sep 6 19:39:08 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UDI-00010: multiple job modes requested, full and schemas.

把full=y去掉，再改为下面：
[oracle@V53 ~]$ impdp DSS/DSS directory=DUMP_TEST dumpfile=gbia.dmp nologfile=y schemas=DSS version=11.2.0.1.0 执行，信息如下：

Import: Release 11.2.0.1.0 - Production on Thu Sep 6 21:22:18 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39059: dump file set is incomplete？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？
ORA-39246: cannot locate master table within provided dump files？？？？？？？？？？？？？？？？？？？？？

imp help
USERID username/口令 FULL 导入整个文件 (N)

BUFFER 数据缓冲区大小 FROMUSER 全部人username列表

FILE 输入文件 (EXPDAT.DMP) TOUSER username列表

SHOW 仅仅列出文件内容 (N) TABLES 表名列表

IGNORE 忽略创建错误 (N) RECORDLENGTH IO 记录的长度

GRANTS 导入权限 (Y) INCTYPE 增量导入类型

INDEXES 导入索引 (Y) COMMIT 提交数组插入 (N)

ROWS 导入数据行 (Y) PARFILE 參数文件名称

LOG 屏幕输出的日志文件 CONSTRAINTS 导入限制 (Y)

DESTROY 覆盖表空间数据文件 (N)

INDEXFILE 将表/索引信息写入指定的文件

SKIP_UNUSABLE_INDEXES 跳过不可用索引的维护 (N)

FEEDBACK 每 x 行显示运行进度 (0)

TOID_NOVALIDATE 跳过指定类型 ID 的验证

FILESIZE 每一个转储文件的最大大小

STATISTICS 始终导入估计算的统计信息

RESUMABLE 在遇到有关空间的错误时挂起 (N)

RESUMABLE_NAME 用来标识可恢复语句的文本字符串

RESUMABLE_TIMEOUT RESUMABLE 的等待时间

COMPILE 编译过程, 程序包和函数 (Y)

STREAMS_CONFIGURATION 导入 Streams 的一般元数据 (Y)

STREAMS_INSTANITATION 导入 Streams 的实例化元数据 (N)

下列keyword仅用于可传输的表空间

TRANSPORT_TABLESPACE 导入可传输的表空间元数据 (N)

TABLESPACES 将要传输到数据库的表空间

DATAFILES 将要传输到数据库的数据文件

TTS_OWNERS 拥有可传输表空间集中数据的用户

/////////////////////////////////////
create user scs_can identified by scs_can default tablespace scs_test temporary tablespace temp;
imp scs_can/scs_can@orcl file=/home/oracle/scsdata20161117010001.dmp
////////////////////////////////////////////////////////////

oracle用户的权限管理
-------------sys Login------------
1. 创建表空间及临时表空间
create tablespace cfps datafile 'cfps' size 30m autoextend on;
create tablespace cfps datafile '/home/oracle/app/oradata/orcl/cfps.dbf' size ?????M autoextend on;

2. 创建用户指定表空间及临时表空间
create user scs_can identified by scs_can default tablespace scs_test temporary tablespace temp;

3. 授予用户各种权利
grant create session to cfps;
grant unlimited tablespace to cfps;
grant connect to cfps;
grant resource to cfps;
grant create sequence to cfps;
grant create table to cfps;

4.导入数据库
create user scs_can identified by scs_can default tablespace scs_test temporary tablespace temp;
imp scs_can/scs_can@orcl file=/home/oracle/scsdata20161117010001.dmp

==================================================================================================
查询oracle数据库表空间的大小，已使用空间，剩余空间
1. 查看所有表空间大小
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

2. 已经空闲的表空间大小
SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

3. 所以使用空间可以这样计算
　　select a.tablespace_name,total,free,total-free used from
　（select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
　　group by tablespace_name) a,
　　( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
　　group by tablespace_name) b
　　where a.tablespace_name=b.tablespace_name;

4. 下面这条语句查看所有segment的大小。
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

5. 还有在命令行情况下如何将结果放到一个文件里。
SQL> spool out.txt
SQL> select * from v$database;
SQL> spool off

Live together,or Die alone!

===============================================================================
du -sh : 查看当前目录总共占的容量。而不单独列出各子项占用的容量
du -lh --max-depth=1 : 查看当前目录下一级子文件和子目录占用的磁盘容量。