1、创建数据表空间初始大小10g,每次自动增长100m
create tablespace JNINFO datafile '/opt/oracle/oradata/orcl/JNINFO01.dbf' size 32000m autoextend ON next 100 m maxsize unlimited;
2、给表空间增加多个数据文件,每个文件大小10g,每次自动增长100m (这一步可直接给已有的表空间添加数据文件使用)
-- 根据需要添加一定数量的数据文件
alter tablespace JNINFO add datafile '/opt/oracle/oradata/orcl/JNINFO02.dbf' size 32000m autoextend ON next 100 m maxsize unlimited;
alter tablespace JNINFO add datafile '/opt/oracle/oradata/orcl/JNINFO03.dbf' size 32000m autoextend ON next 100 m maxsize unlimited;
alter tablespace JNINFO add datafile '/opt/oracle/oradata/orcl/JNINFO04.dbf' size 32000m autoextend ON next 100 m maxsize unlimited;
3、创建临时表空间,用于保存系统中短期活动的数据比如进行一些运算和索引创建等,多数使用完会自动清理
create temporary tablespace MYSPACE_temp tempfile '/home/oracle_datafile/MYSPACE_temp.dbf' size 10g autoextend ON next 100m maxsize 30G;
4、创建用户并指定表空间和临时表空间
create user PANDA identified by PANDA default tablespace MYSPACE temporary tablespace MYSPACE_temp;
5、分配权限
grant connect,resource,dba to PANDA;
删除所有表
-- 将查询所得结果,复制粘入command窗口即可
SELECT 'drop table '|| table_name || ';' FROM USER_TABLES ORDER BY TABLE_NAME;
删除表空间以及对应的表空间文件
drop tablespace MYSPACE INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
如何查看表空间名和数据文件位置
SELECT
tablespace_name "表空间名",
file_name "数据文件位置",
round( maxbytes / ( 1024 * 1024 ), 2 ) AS "可扩展最大值(M)",
autoextensible "自动扩展",
STATUS "状态",
increment_by "自动扩展增量"
FROM
dba_data_files
ORDER BY
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
4 DESC;
Select * FROM DBA_DATA_FILES;查询表空间中数据文件具体位置和文件名,表空间名等。
建议放在和 USERS 表空间同一目录下,如果不知道在哪个路径下,可以使用sql查询
SELECT * FROM Dba_Data_Files ddf WHERE ddf.tablespace_name = 'USERS';
imp命令:
nohup imp jninfo/join@orcl fromuser=jninfo touser=jninfo file=/bak/jninfo20210622.dmp ignore=y STATISTICS=NONE log=/bak/imp.log &
fromuser就是把当前的dmp文件中的某一个用户下的数据取出。
touser就是把现在dmp文件中的数据导入到目标库的指定user下。
具体命令这样。
exp userid=system/manager owner=username1 file=expfile.dmp
imp userid=system/manager fromuser=username1 touser=username2 ignore=y file=expfile.dmp full=y
删除用户:
drop user jninfo cascade
删除表空间:
drop tablespace JNINFO including contents and datafiles
创建用户:
create user jninfo identified by join default tablespace JNINFO temporary tablespace JNINFO_TEMP
授权:
grant connect,resource,dba to jninfo
grant create session,resource to jninfo
[admin@dataserver ~]$ su - oracle 密码: [oracle@dataserver ~]$
打开监听服务
[oracle@localhost ~]$ lsnrctl start
启动
[oracle@localhost ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 1 23:29:19 2017 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> conn /as sysdba Connected to an idle instance.
SQL> startup ORACLE instance started. Total System ... ... Database mounted. Database opened. SQL>
关闭数据库实例
SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down. SQL> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
关闭监听
[oracle@localhost ~]$ lsnrctl stop