oracle常用命令梳理

1、打开sqlplus连接
su - oracle
sqlplus /nolog
2打开数据库
conn sys / as sysdba
sqlplus / as sysdba
---使用sys_context查看属于哪个容器
select sys_context('USERENV','CON_NAME') from dual; 
-- 查看表空间
select * from v$datafile;
select * from v$tempfile;
--查看表空间创建语句
select dbms_lob.substr(dbms_metadata.get_ddl('TABLESPACE',a.tablespace_name)) from dba_tablespaces a;
-- 设置数据库模式
alter pluggable database pdb1 open read write;
--查询当前容器具有哪些pdbs
show pdbs；
 -- 修改数据库会话
 alter session set contAIner=pdb1;
-- 关闭指定pdb
alter pluggable database 容器名称 close immediate ;

--若pdb状态为need sync，则需要：同步文件
select pdb_name,status from cdb_pdbs;

 
--创建表空间
 create tablespace ams datafile '/u01/App/oracle/oradata/ams.pdf' size 2048m autoextend on next 10m maxsize 4000m;
 
--修改用户默认表空间
 alter user 用户名 default tablespace to 表空间名称；
-- 删除表空间    including contents 表示删除表空间的同时删除表空间中的数据。如果不指定including contents 参数，而该表空间又存有数据时，则Oracle会提示错误。
                           cascade constraints: 表示当删除表空间时也删除相关的完整性限制。
drop tablespace 表空间名称 including contents  ;
 
 --授予用户使用表空间权限
 grant unlimited tablespace to 用户;
grant create session to ams;


在Oracle12c中分为两类用户一类为PDB本地用户，一类为公共用户，创建PDB用户需要切换到PDB数据库。


-- oracle查看当前库中所有表空间
select file_name,tablespace_name,bytes from dba_data_files;
create user 用户名 identified by 密码 default tablespace  表空间名称；

create user  ams  identified by ams132  default tablespace ams;

grant sysdba to ams;

-- 通过dba_data_files查看oracle系统的数据文件。
col file_name for a50;
set linesize 100;
select file_name,tablespace_name from dba_data_files;
--通过dba_temp_files查看临时数据文件信息
select file_name,tablespace_name from dba_temp_files;
-- 控制文件信息查看
select name from v$controlfile;
-- 日志文件信息查看
select member from v$logfile;
 -- 查看当前系统是否采用递归模式
select dbid,name,log_mode from v$database;
  
 -- 查看归档日志文件的所在路径
show parameter log_archive_dest;
若显示spfile 的指定参数的信息，则只需使用show parameter + 参数。
-- 告警文件存储路径查看
show parameter background_dump_dest;
-- 显示当前进程的PGA信息。
show parameter pga;
-- 显示数据写入进程个数。
show parameter db_writer_processes;

使用set time on 命令设置在sqlplus命令提示符“SQL>”显示当前的系统时间。
set  time on;
set pagesize value; --一页中所显示数据行的数量。
show pagesize;-- 显示当前sql*plus环境中一页有多少行。


日志模式切换：
1、改变日志操作模式时，用户必须以sysdba的身份执行相应的操作。接下来讲解数据库由非归档模式切换为归档模式的具体操作步骤。
1）、查看当前的日志模式
  select log_mode from v$database;
2)、关闭并重新启动数据库 (改变日志操作模式必须在mount状态下进行）
   shutdown immediate;
   startup mount;
3）改变日志模式
   alter database archivelog;   
   alter database noarchivelog; (将归档模式切换为非归档模式）
4）、打开数据库
   alter database open;

 -- 数据库归档模式
 archive log list;


-- 查看指定表空间，有哪些表
select table_name  from dba_tables where tablespace_name='AMS' order by table_name;
select yd_acct_no from ams.yd_accounts_all;


oracle逻辑备份

1、创建directory
create or replace directory 目录名 as '目录路径'；
2、查看有哪些directory
select owner,directory_name,directory_path from dba_directories;
3、赋权
grant  read,write on directory directory名称 to 用户；
4、删除指定directory
drop directory dump_dir;


oracle 版本查看
sqlplus -v   


-- 可插拔数据库创建
create pluggable database 数据库名 admin user 用户名 identified by pwd_report
 roles=(connect)
 file_name_convert=('/oracle/oradata/orcl/pdbseed','/oracle/oradata/orcl/reportpdb');

-- 查看该用户模式下，拥有的数据表
select table_name from user_tables;
-- 另外，用户也可以在system模式下，通过检索dba_tables表来显示ams模式下拥有的4个数据表。


-- 查看当前用户表空间
select username,default_tablespace from user_users;
-- 查看当前用户下所有表空间的使用情况
SELECT
	a.tablespace_name "表空间名",
	total / ( 1024 * 1024 ) "表空间大小(M)",
	free / ( 1024 * 1024 ) "表空间剩余大小(M)",
	( total - free ) / ( 1024 * 1024 ) "表空间使用大小(M)",
	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;


SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       D.TOT_GROOTTE_MB "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                     2),
               '990.99') || '%' "使用比",
       F.TOTAL_BYTES "空闲空间(M)",
       F.MAX_BYTES "最大块(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 1;
-- oracle 数据库整库大小
select sum(bytes)/1024/1024/1024 from dba_data_files;
-- 查看某表的创建时间
select object_name,created from user_objects where object_name=upper('表名');
-- 查看某表的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('表名');
-- 查看索引
select index_name,index_type,table_name from user_indexes order by table_name;
select * from user_ind_columns where index_name=upper('索引名称');
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('索引名称');

-- sqlplus 连接到可插拔数据
conn  用户名/密码@实例名；