Oracle数据使用笔记-数据库表空间和倒库操作

 

1、查询用户所属表空间：

SELECT username, default_tablespace FROM user_users;

2、查询当前用户所有表分属的表空间：

SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES;

3、查看表空间文件位置：

select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

4、新建表空间：

create tablespace DATACENTER
datafile 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\DATACENTER.DBF'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

5、修改某表的表空间：

alter table SCM.SOC_MAPVIEWS  move tablespace DATACENTER;

6、按照某个字段分组统计，其他字段拼接

select nation,listagg(city,',') within GROUP (order by city)  
from temp group by nation 

7、远程连接数据库

sqlplus sys/dodo@ip/orcl

8、导入dmp

imp WYXZ/WYXZ@ip:1521/orcl.wx file=E:/桌面备份
20190710/SCM_WUYIXIAOZHEN/SCM_WUYIXIAOZHEN.dmp data_only=y fromuser=wuyi touser=WYXZ


imp scm/scm@ip:1521/orcl file=c:/file/scm.dmp full=y ignore=true

9、创建表空间、用户、指定表空间

--创建表空间
 create tablespace KSDLD    
 logging    
 datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\KSDLD.DBF'   
 size 50m  
 autoextend on    
 next 50m maxsize 20480m  
 extent management local;  
 
 -- 创建用户并指定默认表空间 
 create user ksdld identified by ksdld    
 default tablespace KSDLD;  
   
 -- 给创建的用户授权(dba权限)  
 grant dba to ksdld;
-- 给创建的用户授权(普通权限)  
GRANT CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
ALTER ANY TABLE, ALTER ANY PROCEDURE,
DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
TO WYXZ;

10、查询oracle的DBTime

12、	SELECT *
  FROM ( SELECT A.INSTANCE_NUMBER,
               A.SNAP_ID,
               B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
              to_char(END_INTERVAL_TIME,'yyyy-MM-dd hh24:mm:ss') END_TIME,
               ROUND(VALUE - LAG( VALUE, 1 , '0')
                     OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME"
          FROM (SELECT B.SNAP_ID,
                       INSTANCE_NUMBER,
                       SUM(VALUE ) / 1000000 / 60 VALUE
                  FROM DBA_HIST_SYS_TIME_MODEL B
                 WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
                   AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' ))
                 GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
               DBA_HIST_SNAPSHOT B
         WHERE A.SNAP_ID = B.SNAP_ID
           AND B.DBID = (SELECT DBID FROM V$DATABASE)
           AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
 WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') >= TO_CHAR(SYSDATE , 'YYYY-MM-DD')
 ORDER BY BEGIN_TIME;