Oracle数据库创制表空间

 

   

--创建表空间create tablespace new_taspace1 --表空间关联的数据文件和位置DATAFILE 'D:\NEWTABLESPACE1.DBF' --文件初始大小SIZE 200M

翻开某表的大小

–日志文件
Logging
extent management local
segment space management  auto; –以分行停止

表空间:

size 200M –文件开端大小

翻看各日志文件组与日志文件成员的新闻:select
group#,members,archived,status from V$log;

create tablespace new_taspace 

select created, log_mode from v$database;

–打消顾客管理员剧中人物

   

–加多访问某张表的权柄

   

grant select on tep to orders

客户B 要 访问客户A 的表:

revoke dba from orders

conn /as sysdba 用sysdba登录

–创立图书表
create table book(
id number(11) primary key,
bookname varchar2(50) not null,
price number(11,2) not null,
storage number(11) not null
)

登录oracle

DATAFILE ‘D:\NEWTABLESPACE.DBF’   –表空间关系的数据文件和职位

select
instance_name,status,version,database_status from v$instance;

autoextend on next 20MB MAXSIZE 400MB;
–文件大小可自行扩充,每回扩张20MB,最大400MB

grant select any sequence,create
materialized view toceshi001;

–创立客商
create user orders
identified by 123456
default tablespace new_taspace; –给客商创设暗中认可表空间

CREATE USER test001 IDENTIFIED BY shuyi123
DEFAULT TABLESPACE test001 temporary tablespace temp001;

–Oracle数据库创制表空间

查看表空间的名号及大小

–表空间名

echo $ORACLE_HOME

–给客商付与角色
grant connect,resource to orders
grant dba from orders

grant create database link to ceshi001;

–删除表空间把物理文件一并剔除
drop tablespace new_taspace  including contents and datafiles

select *奥门永利402官方网站, from user_tables;

select
segment_name,tablespace_name,bytes B, bytes/1024 KB, bytes/1024/1024
MB from user_segments where segment_type=’TABLE’ and
tablespace_name=’TEST001′;

删去表空间:

源文档
<>

   

   

查询命令:

奥门永利402官方网站 1

查阅ASM磁盘路线

能够先将其offline:alter tablespace xx
offline;

select name,total_mb,free_mb from
v$asm_diskgroup;

   

select instance_name from v$instance;

create tablespace test001 datafile
‘/u01/app/oracle/oradata/test001.dbf’ size 10240m AUTOEXTEND ON EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

www.402.com,oracle实例设置开机运营:

alter database drop logfile member
‘f:\oracledata\redo4.log’;

select name,total_mb,free_mb from
v$asm_disk;

drop tablespace TEST including contents
and datafiles;

奥门永利402官方网站 2

select distinct table_name from
user_tab_columns; (查看当前user格局下具有表名)

奥门永利402官方网站 3

shutdown 关闭实例 shutdown immediate
(立刻关闭实例)

查看顾客下全部的表

commit:提交从前的操作

询问全体客商

   

   

成立有时表空间

conn 客户名/密码 也足以登陆

   

// alter table dse_task2 deallocate
unused keep 0

将磁盘上的数据文件一齐删除:drop
tablespace xxx including contents and datafiles;

打消权限 revoke

查询当前实例名

 

create table dse_task2 as select * from
dse_task1;

show parameter instance_name

select state,name,type,total_mb, free_mb
from v$asm_diskgroup_stat;

清空表数据并释放空间

select * from dba_tab_privs where
grantee=upper(‘username’);

或者用A登录,将表的select授权给public或者B

查阅监听:

清空日志:Alter database clear logfile
group 4;

内定有个别实例举行登陆:sqlplus
system(顾客名)/jhy(密码)@orcl(实例) as sysdba

一时表空间操作参考:

select TABLESPACE_NAME,FILE_NAME from
dba_temp_files;

   

create tablespace ceshi001 datafile
‘+DATA2/RAC/DATAFILE/ceshi001.dbf’ size 1024m autoextend on next
100m maxsize unlimited extent management
local segment space management auto;(next 100m:表空间大小当先原定大小时,每一遍活动扩大100m,这里中间不要加size,不然会报错,提醒数值无效)

select owner, object_type, status ,
count(*) count# from all_objects group by owner, object_type,
status;

翻开钦命客户有哪些系统权限

   

始建表空间

insert into dse_task1 value( select
rownum as id,rownum+10 as task_id,dbms_random.string(‘x’, 40) as
server_uuid,dbms_random.string(‘x’,40) as
proxy_uuid,dbms_random.string(‘x’,40) as repo_uuid from dual connect
by level<=100000);

select * from dba_role_privs where
grantee=upper(‘username’);

ALTER DATABASE DEFAULT TEMPORARY
TABLESPACE temp001;

翻看不时表空间路线

查看表空间物理文件的称谓及大小

grant select on A.table1 to public; grant
select on A.table to B;

   

奥门永利402官方网站 4

   

翻看数据库库对象

   

  

源文档
<>

授权

相关文章