oracle练习(二)

--1.创建临时表空间(dba用户)
create temporary tablespace SJZ_TEMP_TABLESPACE
tempfile '/home/oracle/oradata/GDEP/sjz_temp_tablespace.dbf'
size 50m autoextend on next 50m maxsize 20480m
extent management local;
--2.创建表空间(dba用户)
create tablespace SJZ_FORMAL_TABLESPACE
logging datafile '/home/oracle/oradata/GDEP/sjz_formal_tablespace.dbf'
size 50m autoextend on nextSJZ_TEMP_TABLESPACE 50m maxsize 20480m
extent management local;
--3.删除表空间(dba用户)
drop tablespace SJZ_TEMP_TABSPACE;
--4.查看表空间文件(dba用户)
select * from dba_data_files t where t.tablespace_name like '%SJZ%';
--5.临时表空间文件(dba用户)
select * from dba_temp_files t where t.tablespace_name like '%SJZ%';
--6.创建user(dba用户)
create user sjz_test identified by sjz_test default tablespace SJZ_FORMAL_TABLESPACE
temporary tablespace SJZ_TEMP_TABLESPACE;
--7.给用户授权(dba用户)
grant connect to sjz_test;--给用户授予连接权限
grant resource to sjz_test;--给用户赋予CREATE(CLUSTER、INDEXTYPE、OPERATOR、PROCEDURE、SEQUENCE、TABLE、TRIGGER、TYPE)权限
--8.查询当前用户创建时间和所属的表空间(当前连接用户)
select t.DEFAULT_TABLESPACE, t.temporary_tablespace, t.CREATED, t.USERNAME
  from user_users t;
--9.查看当前用户下有哪些表(当前连接用户)
select t.TABLE_NAME,t.TABLESPACE_NAME from user_tables t;
--10.查看某个表的创建时间(当前连接用户)
select t.object_name , t.created from user_objects t where t.object_name = upper('dept');
--11.查看客户端字符集(当前连接用户)
select * from nls_instance_parameters t where t.parameter = 'NLS_LANGUAGE';
--12.查看session的字符集(当前连接用户)
select * from nls_session_parameters t where t.parameter = 'NLS_LANGUAGE';
--13.查看当前数据库字符集配置
select userenv('language') from dual;--或者
select * from v$nls_parameters t where t.PARAMETER = 'NLS_CHARACTERSET' or t.PARAMETER = 'NLS_LANGUAGE';
--14.查看所有的表空间(当前连接用户)
select * from user_tablespaces;
--15.查看当前用户的权限角色(当前连接用户)
select * from user_role_privs;
--16.查看用户当前的系统权限(当前连接用户)
select * from user_sys_privs;
--17.查看当前用户的表权限(当前连接用户)
select * from user_tab_privs;
--18.查看当前会话所具有的权限(当前连接用户)
select * from session_privs;
--19.查看当前用户表中含有de字符的表(当前连接用户)
select object_name, object_id from user_objects  where instr(object_name, upper('de')) > 0;
--20.查询某个表属于哪个用户(dba用户)
select owner from dba_tables where table_name= upper('dept');
--21.空闲表空间表
select tablespace_name as "表空间名",
       sum(bytes) / 1024 / 1024 || 'MB' as "剩余",
       max(bytes) / 1024 / 1024 || 'MB' as "总共"
  from dba_free_space
 group by tablespace_name;
--22.查看数据库表空间状态使用情况：
select a.tablespace_name,
       a.bytes / 1024 / 1024 "Sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) || '%'  "percent_used"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc;