Oracle
1.查询表空间
SELECT
tablespace_name as 表空间,
round((sum_alloc - nvl(sum_free,0))/1024/1024,1) as 已用空间M,
round(sum_max/1024/1024,1) as 总大小M,
round(100*(sum_alloc - nvl(sum_free,0))/sum_max,1) As 使用百分比
FROM ( SELECT tablespace_name, sum(bytes) AS sum_alloc, sum(decode(maxbytes,0,bytes,maxbytes)) AS sum_max
FROM dba_data_files
GROUP BY tablespace_name),( SELECT tablespace_name AS fs_ts_name, sum(bytes) AS sum_free
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name(+)
order by 使用百分比 desc;
2.查看表空间路径
SELECT
a.file#,
a.name,
a.bytes / 1024 / 1024 currentmb,
ceil(hwm * a.block_size / 1024 / 1024) resizeto,
( a.bytes - hwm * a.block_size ) / 1024 / 1024 releasemb,
'alter database datafile '''
|| a.name
|| ''' resize '
|| ceil(hwm * a.block_size / 1024 / 1024)
|| 'M;' resizecmd
FROM
v$datafile a,
(
SELECT
file_id,
MAX(block_id + blocks - 1) hwm
FROM
dba_extents
GROUP BY
file_id
) b
WHERE
a.file# = b.file_id (+)
AND ( a.bytes - hwm * a.block_size ) > 0
ORDER BY
5;
3.新建表空间
create tablespace AAAAA
logging
datafile 'D:\oracle\oradata\tabsp_lob01.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
4.新建用户并授权
4.1创建用户
create user lisifan identified by 123456;
4.2修改用户默认表空间
alter user lisifan default tablespace AAAAA;
4.3或者创建用户并设置默认表空间
create user lisifan identified by 123456
default tablespace AAAAA;
4.4赋予权限
grant connect,resource to qinggang;
grant create any sequence to qinggang;
grant create any table to qinggang;
grant delete any table to qinggang;
grant insert any table to qinggang;
grant select any table to qinggang;
grant unlimited tablespace to qinggang;
grant execute any procedure to qinggang;
grant update any table to qinggang;
grant create any view to qinggang;
4.5或者赋DBA权限
grant connect,resource,dba to lisifan;
5.查询一个表所在的表空间语法
select TABLESPACE_NAME from tabs where TABLE_NAME = 表名;
6.修改Oracle用户密码
alter user 用户名 identified by 新密码;
7.查询当前表空间所包含的表
select table_name ,tablespace_name from dba_tables where tablespace_name = 'TEST1';
8.获取表字段信息
select column_name,data_type,DATA_LENGTH From all_tab_columns
where table_name=upper('表名')
9.oracle用户查看用户所属的默认表空间
select username,default_tablespace from user_users;
oracle字段自增
一、创建表
--创建student表
create table student(
id number not null, --学生id
name varchar2(20) not null --学生姓名
);
二、创建序列
create sequence student_id_seq; --创建序列
三、测试 (向表中添加数据)
insert into student values (student_id_seq.nextval,'张三');
insert into student values (student_id_seq.nextval,'李四');
select * from student; --查询表
四、使用触发器创建自增字段
create or replace trigger student_auto_incr
before insert on student --before:执行DML等操作之前触发
for each row --行级触发器
begin
select student_id_seq.nextval into :new.id from dual;
end;
/
触发时机:
before:能够防止某些错误操作发生而便于回滚或实现某些业务规则,适用于实现自增字段;
after:在DML等操作发生之后发生,这种方式便于记录该操作或做某些事后处理信息;
instead of:触发器为替代触发器。
五、测试
insert into student(name) values ('张三');
insert into student (name) values ('李四');
select * from student;
Oracle中NVL()函数用法
1.NVL函数
NVL函数的格式如下:NVL(expr1,expr2)
含义是:如果oracle第一个参数为null那么显示第二个参数的值,如果第一个参数的值不为null,
则显示第一个参数本来的值。
例如:SQL> select ename,NVL(comm, -1) from emp;
2.NVL2函数
NVL2函数的格式如下:NVL2(expr1,expr2, expr3)
含义是:如果该函数的第一个参数为null那么显示第三个参数的值,如果第一个参数的值不为null,
则显示第二个参数的值。
例如:SQL> select ename,NVL2(comm,-1,1) from emp;