oracle基本命令

登录：

su - oracle
lsnrctl start --开启监听
sqlplus / as sysdba --登录oracle
startup --启动实例
shutdown immediate --停止实例

创建用户

create user OGG_SOURCE identified by OGG_SOURCE default tablespace OGG_SOURCE quota unlimited on OGG_SOURCE;

修改密码

alter user zzg identified by unis;  

解锁用户

alter user 用户名 account unlock;
select LOCK_DATE,username from dba_users where username='用户名';  --查看

删除用户

DROP USER XX CASCADE;

查询数据库允许的最大连接数

select value from v$parameter where name = 'processes';　
select value from v$parameter where name = 'sessions';
或者：
show parameter processes;

修改数据库允许的最大连接数

alter system set processes = 1000 scope = spfile;
alter system set sessions=1105 scope=spfile;  --sessions=(1.1*process+5)

重启数据库

shutdown immediate;
startup;

设置用户名大小写不敏感

show parameter sec_case_sensitive_logon;--查看
alter system set sec_case_sensitive_logon = false; --更改

给用户赋予权限

grant resource,connect,create view to OGG_SOURCE;

恢复误删的表

FLASHBACK TABLE TEST TO BEFORE DROP;

查询当前数据库实例名（数据库实例名与ORACLE_SID一般相同）

方法一：select instance_name from v$instance;
方法二：show parameter instance

查询数据库服务名

方法一：select value from v$parameter where name = 'service_name';
方法二：show parameter service_name；
方法三：select global_name from global_name;

更新服务名

SQL>update system set service_names='new_orcl';

查看数据库名

方法一：select name from v$database;
方法二：show parameter db

查询数据库域名

方法一：select value from v$parameter where name = 'db_domain';
方法二：show parameter domain

全局数据库名=数据库名+数据库域名

查看数据文件路径

select * from dba_data_files;

查看归档日志是否打开

SQL> archive log list;

更改归档日志文件存放地址

SQL> alter system set log_archive_dest_1='location=/u01/newccs_archive';

开启归档日志

SQL> shutdown immediate
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

查看回收站状态：

SQL> select flashback_on from v$database;

    FLASHBACK_ON
    ------------------
    NO        --NO为开启

禁止回收站功能
session级别的关闭：

alter session set recyclebin=off;    重新登陆就会失效

系统级别的关闭：

alter system set recyclebin=off;     永久关闭

停掉数据库进程：
查看进程：

select job_name,state from dba_datapump_jobs;

停止进程：

expdp academyplanning0530/Wims#0423#Abp attach=SYS_EXPORT_TABLE_*

stop 若stop卡住，执行kill

DBA_ 描述的是数据库中的所有对象
ALL_ 描述的是当前用户有访问权限的所有对象
USER_* 描述的是当前用户所拥有的所有对象**

查看所有用户：

select * from dba_users; --->显示所有用户的详细信息
select * from all_users; --->显示用户及用户id和创建用户时间三个字段
select * from user_users; --->显示当前用户的详细信息。

查看当前用户：

select * from v$session; --->查看所有连接信息

查看角色:

select * from user_role_privs; --->当前用户被授予的角色
select * from dba_role_privs; --->全部用户被授予的角色
select * from dba_roles; --->查看所有角色
select * from ROLE_ROLE_PRIVS; --->查看所有角色被赋予的角色

基本权限查询：

select * from session_privs;　　--->当前用户所拥有的全部权限
select * from user_sys_privs;　　--->当前用户的系统权限
select * from user_tab_privs;　　--->当前用户的表级权限
select * from dba_sys_privs;　　--->所有用户的拥有的权限，可查询某个用户所拥有的系统权限
select * from role_sys_privs;　　--->查看角色(只能查看登陆用户拥有的角色)所包含的权限

查看用户对象权限：

select * from dba_tab_privs; --->查看所有用户的对象权限
select * from all_tab_privs; 　　--->查看当前用户的所拥有对象
select * from user_tab_privs;　　--->查看当前用户的对象权限
select * from V$PWFILE_USERS;　　--->查看当前用户有sysdba或sysoper系统权限(查询时需要相应权限)

查询open用户密码到期时间：

select username,account_status,expiry_date,profile from dba_users;

查询默认密码有效期（LIMIT）：

select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';

查询用户密码策略：

SELECT username,profile FROM dba_users;

使用某用户登录，查询该用户下表的总大小：

select sum(BYTES / 1024 / 1024）from user_segments where segment_type = 'TABLE' ;

查询用户最后登录时间：

select t1.username,t1.logon_time last_logon_time,t2.account_status,created 账号创建时间 from 
(select username,max(timestamp) logon_time from dba_audit_session where action_name='LOGON' and username in (select username from dba_users) group by username) t1
left join (select username,account_status,created from dba_users) t2 on t2.username=t1.username 

查询所有表的行数

select t.table_name,t.num_rows from all_tables t；

表空间

查询数据库中所有的表空间以及表空间所占空间的大小

select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,   
round(bytes/(1024*1024),0) total_space   
from dba_data_files   
order by tablespace_name; 

查询所有表空间以及每个表空间的大小，已用空间，剩余空间，使用率和空闲率

select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from 
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

查询某个具体的表所占空间的大小，把“TABLE_NAME”换成具体要查询的表的名称就可以了：

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) "占用空间(M)"
from dba_segments t
where t.segment_type='TABLE'
and t.segment_name like 'ANA_MIT_LV_CHA_MRG_202003%'
group by OWNER, t.segment_name, t.segment_type;

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) "占用空间(M)"
from dba_segments t
where t.segment_type='TABLE' and t.segment_name like 'ANA_MIT_LV_CHA_MRG_202004%'
or t.segment_type='TABLE' and t.segment_name like 'ANA_MIT_LV_CHA_202004%'
group by OWNER, t.segment_name, t.segment_type;

删除空的表空间，但是不包含物理文件

drop tablespace tablespace_name;

删除非空表空间，但是不包含物理文件

drop tablespace tablespace_name including contents;

删除空表空间，包含物理文件

drop tablespace tablespace_name including datafiles;

删除非空表空间，包含物理文件

drop tablespace tablespace_name including contents and datafiles;

如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS

drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

表空间管理：
创建表空间：

create tablespace OGG_SOURCE datafile '/u01/app/oracle/oradata/orcl/oggsource1.dbf' size 20g autoextend on next 5m;

修改表空间增加数据文件（看情况使用）

alter tablespace OGG_SOURCE add datafile '/u01/app/oracle/oradata/orcl/oggsource2.dbf' size 20g autoextend on next 5m;

删除表空间

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
drop tablespace HMABPREPORT including contents and datafiles cascade constraint;

查看当前存在的表空间

select * from v$tablespace;

查看表空间使用情况

select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; -- 单位是M

查看用户默认表空间

select username,default_tablespace from dba_users;

查询数据库中表空间对应的数据文件路径

select file_name,tablespace_name from dba_data_files;

修改用户表空间

alter user user1 default tablespace test_tbsp;

查看表空间是否自动扩展

select file_name,autoextensible,increment_by from dba_data_files where tablespace_name='TEST_TBSP';

查看临时表空间是否自动扩展

select file_name,autoextensible,increment_by from dba_temp_files;

查看表空间块大小

show parameter db_block；

表空间开启自动扩展

alter database datafile '/home/oracle/oradata/test_tbsp.dbf' autoextend on;

表空间关闭自动扩展

alter database datafile '/home/oracle/oradata/test_tbsp.dbf' autoextend off;

查看当前表空间

select username,default_tablespace from user_users;

查看表空间下所有表

select TABLE_NAME,TABLESPACE_NAME from all_tables where tablespace_name='A_DATA_BW_CZGL';

删除表

drop table tablename；

查询每个用户所占的表空间和每个表所占空间大小

select owner,tablespace_name ,sum(bytes)/1024/1024 from dba_segments group by owner,tablespace_name;

查看表空间的名字及文件所在位置

select tablespace_name,file_id,file_name round(bytes / (1024 * 1024), 0) total_space from sys.dba_data_files,order by tablespace_name

查询表空间信息

select username,default_tablespace,t.* from dba_users t

查询某个用户下的全部数据表的表名和大小

select a.segment_name,a.segment_type,a.bytes,a.bytes / 1024 / 1024 byte_m,b.created
from dba_segments a
inner join all_objects b on b.object_type = 'TABLE' and a.owner = b.owner and a.segment_name = b.object_name
where a.owner = 'USER' and a.segment_type = 'TABLE' /* and a.bytes>50000000*/
order by a.bytes desc;
a.owner后面跟想查询的用户名，查询没数据，可以试下a.owner = upper('USER')

查询当前表空间下使用情况

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) "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;

扩展表空间：

select name from v$datafile;

1，增加数据文件

alter tablespace POTEVIO add datafile '/u01/app/oracle/oradata/orcl/POTEVIO8.dbf' size 31g autoextend on next 100m;

2，或者增大空间（默认的一个数据文件最大扩展到32G，达到32G后只能增加数据文件）

alter database datafile '/u01/app/oracle/oradata/orcl/POTEVIO.dbf' resize 100g;

RAC 只需要指定 asm磁盘的名称即可，单节点实例的oracle 才需要指定到具体的路径

alter tablespace system add datafile '+YYDB_DATA' size 30g autoextend on next 5m;

创建表空间时，创建大文件表空间（默认数据文件最大为32T，但只能拥有一个数据文件）

create bigfile tablespace POTEVIO datafile '/u01/app/oracle/oradata/orcl/POTEVIO.dbf' size 2g autoextend on next 100m;

表空间使用率查询

SELECT D.TABLESPACE_NAME,
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)", FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
     (SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.tablespace_name,NVL(used_space,0) || 'M' "USED_SPACE(M)",
       Round(Nvl(used_space, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
       NVL((SPACE - (NVL(used_space,0))),0) || 'M' "FREE_SPACE(M)"
FROM (SELECT tablespace_name,Round(SUM(bytes) / (1024 * 1024), 2) SPACE,SUM(blocks) BLOCKS FROM dba_temp_files GROUP BY tablespace_name) D,
     (SELECT tablespace,Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE FROM v$sort_usage GROUP BY tablespace) F
WHERE D.tablespace_name = F.tablespace(+) order by 1；