ORACLE常用SQL语句

1）创建用户

为用户创建表空间，要注意路径一定不要错误
CREATE TABLESPACE oraumpay DATAFILE 
  '/DataBase/data/umpay/oraumpay01.dbf' SIZE 10G AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
创建用户：
CREATE USER oraumpay
  IDENTIFIED BY oraumpay
  DEFAULT TABLESPACE oraumpay
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

  GRANT IMP_FULL_DATABASE TO oraumpay;
  GRANT CONNECT TO oraumpay;
  GRANT RESOURCE TO oraumpay;
  ALTER USER oraumpay DEFAULT ROLE ALL;

  GRANT UNLIMITED TABLESPACE TO oraumpay;
  GRANT CREATE ANY TRIGGER TO oraumpay;
  GRANT CREATE VIEW TO oraumpay;
  GRANT DROP ANY TABLE TO oraumpay;
  GRANT CREATE TABLE TO oraumpay;
  GRANT CREATE ANY PROCEDURE TO oraumpay;
  GRANT UPDATE ANY TABLE TO oraumpay;
  GRANT SELECT ANY TABLE TO oraumpay;
  GRANT CREATE ANY SYNONYM TO oraumpay;
  GRANT DELETE ANY TABLE TO oraumpay;
  GRANT INSERT ANY TABLE TO oraumpay;
  GRANT CREATE SEQUENCE TO oraumpay;
  GRANT DBA TO oraumpay;
  ALTER USER oraumpay QUOTA UNLIMITED ON oraumpay;

2） Oracle 数据库查看正在连接的用户信息

       set pagesize 1000;
       set linesize 1000;
       select a.username,default_tablespace,a.sid,a.serial# from v$session a,dba_users b where a.username=b.username;

3). 查看创建的directory位置信息

    set pagesize 1000;
    set linesize 1000;
    col owner for a5
    col directory_name for a25
    col directory_path for a60
    col origin_con_id for a30
    select * from dba_directories;
    创建的命令
    SQL>create directory dump_dir as '/DataBase/app/oracle/backup/udmp';
    SQL>grant read,write on directory dump_dir to system;
    更改的命令：
    SQL>create or replace directory dump_dir as '/ora12cbak/dump_ora';

4)解锁并更改新密码

alter user oracle account unlock identified by umpayoracle;

5）查看所有表空间的使用率

set linesize 500
col TABLESPACE_NAME for a20
select a.tablespace_name,
       round(total / 1024 / 1024 / 1024, 3),
       round(free / 1024 / 1024 / 1024, 3),
       round((total - free) / 1024 / 1024 / 1024, 3) ,
       round((total - free) / total, 4) * 100
  from (select tablespace_name, sum(bytes) free
          from dba_free_space
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) total
          from dba_data_files
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name;

6）修改表空间文件大小
alter database datafile '/DataBase/app/oracle/oradata/orcl/users01.dbf' resize 30G;
*7）/*查看表空间下有多少用户，tablespace_name表空间 的名字一定要大写 /
select distinct s.owner from dba_segments s where s.tablespace_name ='TBS_OPEN_DATA01';
8）查看用户所属的表空间
select default_tablespace from dba_users where username='HAYIN';
9）删除表空间及其对应的物理文件
drop tablespace TS_VEHICLE_DATA_01 including contents and datafiles cascade constraint;
10）查看表空间的位置和大小

set linesize 200;
col FILE_NAME for a80
col TABLESPACE_NAME for a50
select FILE_NAME,FILE_ID,BYTES/1024/1024/1024,TABLESPACE_NAME FROM dba_data_files where TABLESPACE_NAME='USERS';

11）扩展已存在用户的表空间

ALTER TABLESPACE UNDOTBS2 ADD DATAFILE 
'/oradata01/kthradb/undotbs2_04.dbf' SIZE 20G AUTOEXTEND off;

12）按用户导出，导入数据（新库提前建立好对应的用户和表空间）

expdp system/passwd schemas=openplatform dumpfile=full_openplatform.dmp directory=dump_dir logfile=2020-0408-full_openplatform.log
impdp system/passwd remap_schema=openplatform:openplatform dumpfile=full_openplatform.dmp directory=dump_dir logfile=full_openplatform.log