désert/workarea/ztey

awr report

sqlplus / as sysdba
@?/rdbms/admin/awrrpt.sql
=> @?/rdbms/admin/awrrpti.sql FOR RAC
7days
begin to end
filename

create schema(user)

• 查询gzdt11bim用户(SCHEMA)的信息

conn gzdt11bim
DESC DBA_USERS;
COLUMN USERNAME FORMAT A30
COLUMN DEFAULT_TABLESPACE FORMAT A20
# 查看当前用户的缺省表空间
select USERNAME, DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from DBA_USERS where USERNAME='GZDT11BIM';

select username,default_tablespace,temporary_tablespace from dba_users;
select username,default_tablespace,temporary_tablespace from dba_users where username='GZDT11BIM';


# 查看当前用户的系统权限和表级权限
select * from user_role_privs;
select * from user_sys_privs;

# 查看一个用户的所有系统权限(包含角色的系统权限)
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='GZDT11BIM' );

set linesize 300
SELECT upper(f.tablespace_name) "tablespace_name",
       d.Tot_grootte_Mb "tablespace(M)",
       d.Tot_grootte_Mb - f.total_bytes "used(M)",
       round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "use%",
       f.total_bytes "free_space(M)",
       round(f.total_bytes / d.Tot_grootte_Mb * 100,2) "free%"
 FROM      
    (SELECT tablespace_name,
            round(SUM(bytes)/(1024*1024),2) total_bytes,
            round(MAX(bytes)/(1024*1024),2) max_bytes
      FROM sys.dba_free_space
     GROUP BY tablespace_name) f,
    (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
      FROM   sys.dba_data_files dd
      GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name    
ORDER BY 4 DESC
/

select tablespace_name, maxbytes/1024/1024 MAX_SIZE from dba_data_files;
SELECT NAME from v$datafile;

• Displays Space Usage for Each Datafile

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Tablespace Name" FORMAT A20
COLUMN "File Name" FORMAT A80
 
SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
        Substr(df.file_name,1,80) "File Name",
        Round(df.bytes/1024/1024,0) "Size (M)",
        decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
        decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
        decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM    DBA_DATA_FILES DF,
       (SELECT file_id,
               sum(bytes) used_bytes
        FROM dba_extents
        GROUP by file_id) E,
       (SELECT Max(bytes) free_bytes,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE    e.file_id (+) = df.file_id
AND      df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name
/

• database size

select
( select sum(bytes)/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) "Size in MB"
from
dual;

Create user

CREATE TABLESPACE gzdt11bimA_TS DATAFILE '+DATA' size 1G AUTOEXTEND ON NEXT 50M MAXSIZE 10G;

create user gzdt11bimA identified by gzdt11bimA default tablespace gzdt11bimA_TS quota unlimited on gzdt11bimA_TS;

• create user gzdt11bimA identified by gzdt11bimA;
• grant connect,resource,dba to gzdt11bimA;
• select USERNAME,PASSWORD,EXPIRY_DATE from DBA_USERS;