oracle_cli 管理常用命令

数据库管理
 lsnrctl status                                                  监听状态
 lsnrctl start                                                   启动监听
 lsnrctl stop                                                    停止监听
 sqlplus / as  sysdba                                            使用sys用户s连接库
SQL> startup;                                                     启动数据库实例
SQL> shutdown;                                                    关闭数据库实例
SQL> SHUTDOWN IMMEDIATE                                           立即关闭数据库

1、注意账户锁定、密码锁定
2、数据文件数量限制

--全局
SQL> show user                                                   查看当前用户
SQL> select username from dba_users;                             所有用户
SQL> archive log list                                            归档相关信息
SQL> select service_id, name,pdb from v$services;                查看service情况
SQL> select INSTANCE_NAME from v$instance;                       实例名

select * from v$version　　               --版本信息，核心版本信息
select userenv('language') from dual;       --服务端字符集

SQL> show pdbs                                                   显示所有pdb 和状态
SQL> show con_name;                                              显示当前pdb数据库
SQL> select name,cdb from v$database;                            检查db是否为cdb
SQL> alter session set container=CDB1PDB;                        切换到指定pdb数据库
SQL> ALTER PLUGGABLE DATABASE cdb1pdb OPEN;                      开启
SQL> ALTER PLUGGABLE DATABASE cdb1pdb CLOSE;                     关闭
SQL> create pluggable database cdb1hf admin user hf identified by hf file_name_convert=('/u01/app/oracle/oradata/cdb1/pdbseed/','/data/hf/');   使用seed模板创建pdb
SQL> ALTER PROFILE default LIMIT password_life_time UNLIMITED;      --密码永不过期
SQL> alter system set deferred_segment_creation=FALSE;              --新建的空表给分配段


创建表空间(在12c版本下需要在每一个pdb中建立表空间，否则用户无法创建)
SQL> CREATE TABLESPACE ESENSOFT DATAFILE '/home/oracle/esensoft.dbf' size 40m autoextend on next 50m maxsize 20480m EXTENT MANAGEMENT LOCAL;
    指定表空间名、指定表空间文件位置、开启表空间自增长、每次增长50兆、最大为20480兆

--临时表空间

--临时表空间数据文件信息 
SQL> select * from dba_temp_files
SQL> select tablespace_name,file_name,bytes / 1024 / 1024 file_size,autoextensible from dba_temp_files

--临时表空间free空间信息，需要参考上条命令信息
SQL> SELECT TABLESPACE_NAME, FREE_SPACE / 1024 / 1024 / 1024 AS "FREE SPACE(G)" FROM DBA_TEMP_FREE_SPACE
-- WHERE TABLESPACE_NAME = TEMP --'&tablespace_name';

SQL> select * from V$TEMP_EXTENT_POOL

--创建新的临时表空间
CREATE TEMPORARY TABLESPACE cdm_temp
   TEMPFILE '+DATA/FXDB/TEMPFILE/cdm_temp01.dbf' SIZE 5M AUTOEXTEND ON;  

--数据文件自动扩展
ALTER DATABASE TEMPFILE '+DATA/FXDB/TEMPFILE/cdm_temp01.dbf' AUTOEXTEND ON NEXT 100M  MAXSIZE UNLIMITED;

--添加新的数据文件
ALTER TABLESPACE cdm_temp ADD TEMPFILE '+DATA/FXDB/TEMPFILE/cdm_temp02.dbf' SIZE 5M  AUTOEXTEND ON NEXT 128M  MAXSIZE UNLIMITED;

--切换用户的临时表空间
alter user GUICHIQU_ODS  temporary tablespace cdm_temp;


--表空间
SQL> create tablespace huangshan_ods datafile '/data/huangshan_ods01.dbf' size 100M autoextend on next 40m;
SQL> alter tablespace huangshan_ods add datafile '/data/huanshan_ods02.dbf' size 100m autoextend on next 40m;
SQL> select tablespace_name from dba_tablespaces;                                                 查看表空间列表
SQL> select * from v$tablespace;                                                                  表空间信息
SQL> select file_name,tablespace_name from dba_data_files;                                        表空间数据文件所处位置
    SQL> select tablespace_name,file_name,bytes from DBA_DATA_FILES;
SQL> select default_tablespace from dba_users where username='SHOW';                              指定用户默认表空间
SQL> drop tablespace HFGAOXINQU_ODS including contents;                                           删除表空间及其内容
SQL> drop tablespace ANHUI_ODS including contents and datafiles;                                  删除表空间及其数据文件
SQL> alter database datafile '/home/oracle/esensoft.dbf' offline drop                             删除数据文件和表空间的关系
SQL> select name, bytes/1024/1024/1024 as G from v$datafile                                       数据文件大小

SQL> show parameter db_block_size                                                                 显示默认数据块大小

SQL> show parameter db_files;                                                                     查看数据文件上限
SQL> alter system set db_files=1024 scope=spfile;                                                 修改数据文件上限，修改参数文件

SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "BYTES(M)"
  FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '&tablespace_name'

--用户
SQL> create user c##show identified by c##show default tablespace esensoft;                       创建用户
SQL> alter user ah_sjpt_cdm identified by ah_sjpt_cdm;                                            修改用户密码
SQL> grant dba to c##show;                                                                        授予用户dba权限
SQL> alter user show identified by 123456;                                                        修改用户口令
SQL> drop user XXXX cascade;                                                                      删除用户,及其对象

SQL> select * from dba_users t where t.username like '%ODS';                                      查看所有用户信息
SQL> select table_name from user_tables;                                                          --查看当前用户下所有表信息  num_rows字段代表表内数据量

SQL> select count(*) from v$process;                                                                  当前连接数
SQL> select value from v$parameter where name = 'processes'                                           最大连接数

--表
SQL> truncate table OT_JBGW_DIABETES_FOLLOWUP;                                                                  删除表内所有数据(不可回滚)
SQL> alter table  ot_jbgw_hyper_followup rename column JBGW_HYP_FOLLOWUP_007 to OT_JBGW_HYP_FOLLOWUP_007;       修改字段名

SQL> select * from v$logfile                                                                      日志文件信息
SQL> select * from dba_data_files;                                                                数据文件信息
SQL> select * from v$controlfile;                                                                 控制文件信息
SQL> select con_id,name from v$containers;                                                        pdb信息
SQL> select name from v$database;                                                                 查看全局数据库名
SQL> select instance_name from v$instance;                                                        查看数据库实例名
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;                                   数据库当前时间

SQL> analyze table my_table compute statistics for table for all indexes for all columns;         分析指定表字段和索引
SQL> analyze table my_table delete statistics;                                                    删除指定表分析数据

--索引
    --重建索引
select 'alter index ' || index_name || ' rebuild tablespace AH_ZHIKONG; '
  from user_indexes

--被授权数  对象权限
select distinct owner from dba_tab_privs where grantee='TEST';


select a.tablespace_name "表空间名",total "表空间大小",free "表空间剩余大小",(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小G",free / (1024 * 1024 * 1024) "表空间剩余大小G",(total - free) / (1024 * 1024 * 1024) "表空间使用大小G"
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;

--导入导出数据
select 'drop table '||table_name||' purge;' from user_tables;

exp tianchang_ods/tianchang_ods@10.10.27.120/orcl file=/home/oracle/tianchang_ods20190826093501.dmp log=home/oracle/tianchang_ods.log
imp tianchang_ods/tianchang_ods@10.10.27.120/orcl file=/home/oracle/tianchang_ods20190826093501.dmp ignore=y full=y
--使用ignore=y参数时，ORACLE根本不检查要导入的数据结构和现存在数据库中表的结构是否相同
--full=y，是导入文件中全部内容，有可能有多个用户的内容   没有full,导出指定的模式

--数据泵
SQL> create or replace directory kmdata_exp as '/opt/backup'
SQL> grant read,write on directory kmdata_exp to public 
SQL> grant read,write on directory KMDATA_EXP to anhui_ods
SQL> select * from dba_directories
SQL> drop directory  kmdata_exp


expdp system/Gxrj2020@orcl schemas=anhui_ods directory=DATABAK  dumpfile=anhui_ods.dmp logfile=anhui_ods_expdp.log  METRICS=Y VERSION=12 cluster=N
expdp system/Gxrj2020@10.10.27.100/orcl schemas=$i directory=DATABAK  dumpfile=$i$BAKUPTIME.dmp logfile=expdp$i$BAKUPTIME.log  METRICS=Y cluster=N
    schemas     指定模式
    directory   库内定义的目录名 对应实际路径
    dumpfile    导出数据文件名
    logfile     导出日志文件名
    METRICS=Y   跟踪每个步骤的时间
    cluster=N   集群情况下忽略共享存储

impdp
    
--删除用户
select sid,serial# from v$session where username='AH_ZHIKONG';
alter system kill session '11090,29827';
drop user ah_zhikong cascade;


--用户密码过期被锁定 根据实际情况修改密码
ORA-28001: the password has expired
ORA-28000: the account is locked

select * from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_LIFE_TIME';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
alter user c##mh_new identified by 123456;
alter user c##mh_new account unlock;

--锁表
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID; 
alter system kill session '3523,55283'immediate; 

--procedures创建运行权限
GRANT CREATE ANY PROCEDURE TO MONKEY  --創建,查看,替換的權限
GRANT EXECUTE ANY PROCEDURE TO MONKEY  --執行和查看的權限
GRANT ALTER ANY PROCEDURE TO MONKEY  --編譯的權限
GRANT DEBUG ANY PROCEDURE TO MONKEY  --查看和調試的權限

--RMAN mount状态或归档模式   下运行
> rman target sys/oracle@10.10.27.120:1521/

RMAN> backup database format 'c:\data\bak_%U';                          #全库备份
RMAN> backup tablespace feidong_ods format 'c:\data\bak_ts_%U';         #指定表空间
RMAN> backup current controlfile format 'c:\data\bak_cf_%U';            #备份控制文件
RMAN> configure controlfile autoautobackup off|on;                      #控制文件自动备份设置
RMAN> list backup of database;                                          #查看全库备份 list:列出备份信息
RMAN> list backup of tablespace feidong_ods;                            #查看关于指定表空间的备份
RMAN> list backup of controlfile;                                       #查看控制文件备份
RMAN> delete backupset 1;                                               #删除备份 noprompt不提示 删除备份记录及其物理文件
RMAN> delete backup;                                                    #删除所有备份
RMAN> delete obsolete;                                                  #删除过期备份
RMAN> delete expired backup;                                            #删除无效备份
RMAN> report need backup;                                               #显示需要备份的文件
RMAN> report need backup tablespace feidong_ods;                        #检测指定表空间是否需要备份
RMAN> report obsolete;                                                  #查看过期备份
RMAN> 
RMAN> 
RMAN> 
RMAN> 
RMAN>show all;                                                          #显示recovery manager默认配置


/u01/app/oracle/product/12.2.0.1/db_1/dbs/        实例初始化参数文件
cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/
cp init.ora initcdb1.ora

common user             通用用户    以c##开头   
local user              本地用户    在pdb中创建的用户
    每个pdb都是一个独立的单元，有自己的local user、表空间、数据文件，每个local user用户只能访问自己的pdb 

添加监听程序的参数（）
vim /u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
    SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
    SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8



#######  创建用户 CRATE USER  ######
CREATE USER user_name IDENTIFIED BY password 
[DEFAULT TABLESPACE default_tablespace | TEMPORARY TABLESPACE temp_tablespace]
PROFILE profile
QUOTA [intager K|M] | PASSWORD EXPIRE
ACCOUNT LOCK|UNLOCK

指定默认表空间 指定默认临时表空间 指定用户资源文件 指定配额选项默认无限制 将密码置为过期登录时必须修改 锁定或解锁账号

#######  修改用户 ALTER USER ######
ALTER USER user_name IDENTIFIED BY password 
[DEFAULT TABLESPACE default_tablespace | TEMPORARY TABLESPACE temp_tablespace]
PROFILE profile
QUOTA [intager K|M] | PASSWORD EXPIRE
ACCOUNT LOCK|UNLOCK

修改用户密码
ALTER USER user_name IDENTIFIED BY password;
GRANT CONNECT TO user_name IDENTIFIED BY password;


###### 删除用户 DROP USER ######
DROP USER user_name [CASCADE]
用户在数据库中创建了对象，必须指定cascade参数，将用户在数据库中创建的对象全部删除。被删除的用户不能处于连接的状态

##### 管理用户会话 #####

1、使用字典视图监视用户会话信息
select sid,serial#,username,status,logon_time,machine 
    from v$session 
    where username is not null;

2、终止用户会话
alter system kill session 'SID,SERIAL#';
SQL> alter system kill session '8713,6928';
SID和SERIAL#能够标识唯一会话信息



############## 权限 ##############

系统权限
1、oracle中的系统权限
    对整个oracle系统的操作权限        连接数据库、创建管理表或视图
    系统权限一般由数据库管理员赋予用户，并允许用户将被授予的权限赋予其它用户
SQL> select * from system_privilege_map;                            查看全部系统权限列表

2、授予系统权限
    一般授予权限由DBA完成的。其它用户必须有 CREATE ANY PRIVILEGE 系统权限

GRANT SYSTEM_PRIV [system_priv,...] TO {PUBLIC|role|user} [,{PUBLIC|role|user}]
[WITH ADMIN OPTION];

SYSTEM_PRIV指定系统权限，多个权限之间用逗号分隔   public全部用户 role角色 user指定用户  

3、显示系统权限
SQL> select * from dba_sys_privs where grantee='TEST';                              检索某个用户或角色拥有的系统权限
SQL> select * from system_privilege_map;
SQL> select * from session_privs;                                                   查看当前用户拥有的系统权限
SQL> select * from user_role_privs;                                                 查看当前用户拥有的角色
4、回收系统权限
REVOKE SYSTEM_PRIV[,SYSTEM_PRIV] FROM {PUBLIC | ROLE | USER} [,{user|role|public}]

SQL> revoke create table from show;                                                 回收来自show用户的建表权限


对象权限
1、对象权限的分类
table               alter delete        index insert            reference select update
view                      delete              insert                      select update
dierctory                                     rread 
function                         execute
procedure                        execute
package                          execute
sequence            alter                                                 select
多种权限组合组合在一起可以用ALL权限，表示对该对象的全部权限

2、授予对象权限
对象权限是由对象的拥有者为其它用户授权，非对象的拥有者不得向其它用户授权，获权用户可以对对象进行相应操作
DBA用户可以把任何对象权限授予其它用户

GRANT object_privilege | ALL ON <schema.>object_name 
TO {user_name | role_name | PUBLIC }
[WITH GRANT OPTION];

SQL> grant all on test.admisson_record_24h to show;                                 授予此表对象的所有对象权限

3、显示对象权限
SQL> select * from dba_tab_privs where grantee='SHOW';                              指定用户或对象的全部对象权限

4、对象权限的回收
REVOKE {object_priv [,object_priv] | ALL }
ON [schema.]object
FROM {user|role|PUBLIC}

授权者只能从自己授权的用户哪里回收权限，被授权的用户基于之前权限创建的 过程、视图 将变为无效
回收对象权限时，经过传递获得权限的用户将会被影响



##########  角色  ############
将一组相关权限授予某个角色，一组权限的集合

系统预定义角色
1、CONNECT
    ALTER SESSION           修改会话
    CREATE CLUSTER          建立聚簇
    CREATE DATABASE LINK    建立数据库连接
    CREATE SEQUENCE         建立序列
    CREATE SESSION          建立会话
    CREATE SYNONYM          建立同义词
    CREATE VIEW             建立视图
    CREATE TABLE            建立表
    



----------------------------------------------- 控制用户权限 -----------------------------------------------
----用户的系统权限 
--一般开发权限需求
create session
create table
create view
create sequence
create procedure

--创建表空间
create tablespace jreey datafile 'c:\data\jreey01.dbf' size 1G autoextend on next 100M;
create user jreey identified by jreey;

--指定用户使用指定的表空间并限制使用容量 --限额
alter user jreey quota 100m on jreey;
--对容量使用不作限制  
alter user jreey quota unlimited on jreey;
--给予用户建立会话的权限
grant create session to jreey;
--给予用户建表权限
grant create table to jreey;


----角色
--创建角色
create role manager
--为角色添加权限
grant create session to manager;
grant create table, create view to manager;
--将角色赋予用户
grant manager to jreey，tom;
--查看角色
select * from user_role_privs;

----对象权限
不同的对象具有不同的对象权限
对象的拥有者拥有全部权限
对象的拥有者可以向外分配权限

--分配对象权限
grant select,update on employees to tom,jreey;
grant select,update on employees to public;
--使被授予用户具有分配权限的权利
grant select on employees to jreey with grant option;

----收回对象权限
revoke select on employees from jreey;