Oracle常用语句 - liutianfeng.com

oracle查询表空间语句：

set linesize 200
set pagesize 100
col totalmb for 999999999 heading 'TOTAL(MB)'
col freemb for 9999999999 heading 'FREE(MB)'
col usedl for 99999 heading 'USE(%)'
col tsname for a40
col segment_space_management for a10
col block_size for 99999
col status for a10
select nvl(b.tablespace_name, nvl(a.tablespace_name, 'UNKNOWN')) tsname,
        c.segment_space_management,
        c.block_size,
        c.status,
        round(totalmb, 0) totalmb,
        round(totalmb - nvl(freemb, 0)) usedmb,
        round(nvl(freemb, 0), 0) freemb,
        round(((totalmb - nvl(freemb, 0)) / totalmb) * 100, 0) usedl
    from (select sum(bytes) / 1024 / 1024 freemb, tablespace_name
        from dba_free_space
        group by tablespace_name) a,
        (select sum(bytes) / 1024 / 1024 totalmb, tablespace_name
        from dba_data_files
        group by tablespace_name) b,
        (select segment_space_management,
                block_size,
                status,
                tablespace_name,
                CONTENTS
        from dba_tablespaces) c
    where a.tablespace_name(+) = b.tablespace_name
    and c.tablespace_name = b.tablespace_name
    order by usedl desc;

查询、resize表空间文件：

select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;
alter database datafile '/opt/oracle/oradata/LIUSDB/data01.dbf' resize 8192M;

扩展表空间（两种方法）：

alter tablespace 表空间 add datafile '表空间路径' size 10240M;  // 增加表空间文件
alter database datafile '/opt/oracle/oradata/data0.dbf' resize 4096M;   // 拓展原有的表空间文件，只要不大于31G（单个表空间文件），拓展不伤害数据。

建表语句查询：

set long 99999
set linesize 500
set pagesize 900
spool /home/oracle/create_tb.sql
select dbms_metadata.get_ddl('TABLE','TB_LIUS','USERLIUS') from dual;
spool off;

这样，建表语句会在/home/oracle/create_tb.sql中，方便查看。

创建查询用户：

create user query_user identified by my_password default tablespace query_user;

grant execute on SYS.DBMS_EXPORT_EXTENSION to QUERY_USER;
grant execute on SYS.SYS_PLSQL_DA3FBA35_644_1 to QUERY_USER with grant option;
grant connect to QUERY_USER;
grant resource to QUERY_USER;
grant create procedure to QUERY_USER;
grant create sequence to QUERY_USER;
grant create table to QUERY_USER;
grant create view to QUERY_USER;
grant select any dictionary to QUERY_USER;
grant select any table to QUERY_USER;
grant unlimited tablespace to QUERY_USER;

查看profile的名称:

select profile from dba_users where username='TB_LIUS';
默认是DEFAULT, 大小写敏感；

组织出改变profile过期时间的语句：

select 'alter profile ' || profile || ' limit ' || resource_name || ' ' ||decode(resource_name, 'PASSWORD_LIFE_TIME','UNLIMITED','PASSWORD_REUSE_TIME','UNLIMITED','PASSWORD_REUSE_MAX','UNLIMITED',
'PASSWORD_VERIFY_FUNCTION','null','PASSWORD_LOCK_TIME','UNLIMITED','PASSWORD_GRACE_TIME', 'UNLIMITED') ||' ;' sql from dba_profiles a where a.profile='DEFAULT' AND a.resource_name like 'PASS%';

重新修改密码：

alter user USER_LIUS identified by 123456;

转载请注明：liutianfeng.com » Oracle常用语句

相关推荐