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;

posted @ 2022-09-29 09:41  爱思凡  阅读(38)  评论(0编辑  收藏  举报