--登录sysdba
conn / as sysdba
--创建备份路径 create directory 名称 as '路径';
create directory DUMPDIR as '/home/oracle/app/dmp';
--使路径生效
desc dba_directories;
--查看所有备份路径
select * from dba_directories;
创建表空间
--创建NNC_DATA01表空间
create tablespace nnc_data01 datafile '/data/u01/app/oracle/oradata/NCC147/nnc_data01.dbf' size 30720m autoextend on next 500m EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
--创建NNC_INDEX01表空间
create tablespace nnc_index01 datafile '/data/u01/app/oracle/oradata/NCC147/nnc_index01.dbf' size 30720m autoextend on next 500m EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
创建用户并授权
--创建ncc用户
CREATE USER ncc0418 IDENTIFIED BY GCPszr##831 DEFAULT TABLESPACE NNC_DATA01 TEMPORARY TABLESPACE temp;
--授权
GRANT connect,dba to ncc_0408;
oracle查询表空间是否已满
select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)；
再查看表空间是否开启了自动扩展的功能
SELECT T.TABLESPACE_NAME,
D.FILE_NAME,
D.AUTOEXTENSIBLE,
D.BYTES,
D.MAXBYTES,
D.STATUS
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME, FILE_NAME;
扩展数据文件
--扩展NCC_DATA01数据文件
alter tablespace NNC_DATA01 add datafile '/data2/orcl_dbf/nnc_data0101.dbf' size 30720M autoextend on next 500M;
--扩展NCC_INDEX01数据文件
alter tablespace nnc_index01 add datafile '/data2/orcl_dbf/nnc_index0101.dbf' size 10240M autoextend on next 500M;
修改表空间文件路径
1.先登录sqlplus：
sqlplus / as sysdba
2.修改表空间为Offline：
alter tablespace 表空间名 offline;
3.拷贝表空间文件
cp 旧路径/.dbf /新路径/
4.修改oracle表空间指向地址
alter tablespace 表空间名 rename datafile ‘原路径\.dbf' to '文件新路径*.dbf';
5.修改表空间为Online
alter tablespace 表空间名 online;
