create tablespace 表空间名
datafile ' 路径(要先建好路径)\***.dbf ' size *M
tempfile ' 路径\***.dbf ' size *M
autoextend on --自动增长
--还有一些定义大小的命令,看需要
default storage(
initial 100K,
next 100k,
);
例如:
1、
create tablespace DEMOSPACE
datafile 'E:/oracle_tablespaces/DEMOSPACE_TBSPACE.dbf'
size 1024M
autoextend on next 5M maxsize 3000M;
//2、
create TABLESPACE 表空间名 ADD DATAFILE '数据文件路径' SIZE 500M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
2、创建用户:create user 用户名 identified by "用户名密码";
//创建用户并指定表空间
create user 用户名 identified by 用户名密码
default tablespace 表空间名字
3、给创建的用户赋权:
grant create session to 用户名;
grant create table to 用户名;
grant create tablespace to 用户名;
grant create view to 用户名;
grant connect,resource to 用户名;
grant create any sequence to 用户名;
grant create any table to 用户名;
grant delete any table to 用户名;
grant insert any table to 用户名;
grant select any table to 用户名;
grant unlimited tablespace to 用户名;
grant execute any procedure to 用户名;
grant update any table to 用户名;
grant create any view to 用户名
5、授予用户使用表空间的权限:
alter user 用户名 quota unlimited on 表空间;
或 alter user 用户名 quota *M on 表空间;
6、查看当前用户的缺省表空间 :select username,default_tablespace from user_users;
7、查看当前用户的角色:select * from user_role_privs;
8、查看当前用户的系统权限和表级权限:select * from user_sys_privs; select * from user_tab_privs;
9、查看用户下所有的表:select * from user_tables;
10、查看名称包含log字符的表:select object_name,object_id from user_objects where instr(object_name,'LOG')>0;
11、查看某表的创建时间:select object_name,created from user_objects where object_name=upper('&table_name');
12、查看某表的大小:select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');
13、查看放在ORACLE的内存区里的表:select table_name,cache from user_tables where instr(cache,'Y')>0;
14、查看索引个数和类别:select index_name,index_type,table_name from user_indexes order by table_name;
15、查看索引被索引的字段:select * from user_ind_columns where index_name=upper('&index_name');
16、查看索引的大小:select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');
17、查看函数和过程的状态:select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';
18、select file_name,tablespace_name,bytes,autoextensible,maxbytes from DBA_DATA_FILES where tablespace_name='USERS';
19、查询oracle的连接数
select count(*) from v$session;
20、查询oracle的并发连接数
select count(*) from v$session where status='ACTIVE';
21、查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;
22、查看所有用户:
select * from all_users;
23、查看表空间路径
select * from dba_data_files;
24、查询表空间
select upper(f.tablespace_name) "表空间名",s.file_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,sys.dba_data_files s
where d.tablespace_name = f.tablespace_name
order by 4 desc;
25.当前的数据库连接数
select count(*) from v$process --当前的数据库连接数
26.Sql数据库允许的最大连接数
select value from v$parameter where name ='processes'--数据库允许的最大连接数
27.Sql修改最大连接数
alter system set processes = 300 scope = spfile;--修改最大连接数:
28.重启数据库
shutdown immediate;
startup;
29、liunx上进入oracle并启动服务
(1)通过root切换进oracle用户:su - oracle
(2)lsnrctl start (启动监听)
(3)sqlplus /nolog
(4)conn / as sysdba
(5)startup