平时用到的比较多的语句,记录下来
– 创建临时表空间
create temporary tablespace PLAT_TEMP
tempfile 'D:\Oracle\oradata\orcl\PLAT_TEMP.dbf'
size 2G
autoextend on
next 200m maxsize unlimited
extent management local;
– 创建数据表空间
create tablespace PLAT_DATA
logging
datafile 'D:\Oracle\oradata\orcl\PLAT_DATA.DBF'
size 2G
autoextend on
next 200m maxsize unlimited
extent management local;
– 为用户解锁。
alter user system account unlock;
– 重新设定密码。
alter user system identified by 88888888;
– 删除表空间
drop tablespace PLAT_TABLESPACE including contents;
– 查看用户的连接状态
select username,sid,serial# from v$session;
– 找到要删除用户的sid和serial并杀死
alter system kill session'$sid,$serial';
– 删除用户
drop user plat cascade; --cascade 级联
– 创建用户
create user plat identified by plat default tablespace PLAT_DATA temporary tablespace plat_temp;
– 给用户授权
grant connect,resource,dba to plat;
– 导出
exp plat/plat@172.18.1.189/orcl file='temp.dmp' full=y
exp plat/plat@10.82.71.15/orcl file='jinzhou_20190409.dmp' full=y
exp plat/plat@10.82.71.15/orcl file='jinzhou_20190409.dmp' full=y compress=y
exp plat/plat@orcl file='d:\20190403.dmp' full=y
– 导出兼容版本,最好是直接用低版本的客户端连高版本库直接导出,以下方法没试过
expdp plat/plat@orcl dumpfile=local_12c_to_11g.dmp version=11.2.0.1.0
– 导入
imp plat/plat@10.82.71.15/orcl file='dim.dmp' full=y
imp plat/plat@192.168.1.114/orcl file='plat2018.dmp' full=y
imp plat/plat@ORCL file=d:\plat_20190314.dmp ignore=y full=y
imp plat_1892/plat_1892@ORCL file=e:\db_back_file\plat.dmp ignore=y fromuser=plat touser=plat_1892
imp plat/plat@ORCL file=e:\db_back_file\plat.dmp ignore=y fromuser=plat touser=plat
imp plat/plat@ORCL file=e:\plat.dmp ignore=y fromuser=plat touser=plat
imp plat20181226/plat@ORCL file=e:\db_back_file\plat_20181226.dmp ignore=y fromuser=plat touser=plat20181226
– 查连接数sql
select count(*) from v$session;
– 查版本信息sql
select * from v$version;
– 查询用户表,所在的表空间
select * from dba_extents where owner='PLAT';
– 修改表所在的表空间
alter table DIM_DISPLAY move tablespace PLAT_DATA
– 修改表的索引所在的表空间
alter index PK_DIM_DISPLAY rebuild tablespace PLAT_DATA
– 生产批量修改表/索引 所在表空间的语句
select 'alter table '|| table_name ||' move tablespace PLAT_DATA;' from user_tables;
select 'alter index '|| index_name ||' rebuild tablespace PLAT_DATA;' from user_indexes;
–当我们建立一个含有lob字段的表时,oracle会自动为lob字段建立两个单独的segment,一个用来存放数据(LOBSEGMENT),另一个用来存放索引(LOBINDEX),并且它们都会存储在对应表指定的表空间中。但是当我们用alter table tablename move tablespace tbs_name 来对表做空间迁移时,只能移动非lob字段以外的数据。而如果我们要同时移动lob相关字段的数据,我们就必需用如下的含有特殊参数据的文句来完成,它就是:
alter table tablename move lob(columeName) store as (tablespace newTableSpaceName);
alter table DIM_DISPLAY move lob(CHART_JSON) store as (tablespace PLAT_DATA);
– 查询表空间详情
select * from dba_data_files where tablespace_name ='PLAT_DATA';
select t.*,bytes/1024/1024 from dba_data_files t ; -- 查询数据表空间
select t.*,bytes/1024/1024 from dba_temp_files t ; -- 查询临时表空间
– 查询表空间使用情况
select a.tablespace_name as tablespace_name ,total,free,total-free as
used ,(total-free)/total*100 as usepercent from ( select
tablespace_name,sum(bytes)/1024/1024 total from dba_data_files group
by tablespace_name) a, ( select tablespace_name,sum(bytes)/1024/1024
free from dba_free_space group by tablespace_name) b where
a.tablespace_name=b.tablespace_name
– Oracle中表空间文件大于32G,需增加表空间文件
alter tablespace med_data add datafile
‘/data/app/oracledata/med_data1.dbf’ size 100m autoextend on next 50m
maxsize 32767m;alter tablespace med_data add datafile
‘/data/app/oracledata/med_data2.dbf’ size 100m autoextend on next 50m
maxsize 32767m;
– 表空间自动扩展
ALTER DATABASE DATAFILE 'E:\APP\86152\ORADATA\ORCL\PLAT_DATA1.DBF' AUTOEXTEND ON NEXT 100M ;
– 扩充数据文件大小
alter database datafile 'E:\APP\86152\ORADATA\ORCL\SYSTEM01.DBF' resize 2048M;