当前位置:数据库 > Oracle >>

oracle操作命令

oracle操作命令
 
---1.创建数据库实例
CMD> oradim.exe -new -sid TEST
CMD> type initTEST.ora db_name=TEST
cmd> sqlplus "/ as sysdba"
 
--2.进入SQLPLUS
sql>  startup nomount pfile=initTEST.ora (加载数据实例,并指定初始化文件)
sql> shoutdown immediate;
sql> startup mount exclusive restrict pfile=initTEST.ora
sql> drop database;
exit
 
--3.退出
CMD> oradim.exe -delete -sid TEST
 
CMD> emctl start dbconsole (启动后台控制台)
 
cmd>ECHO %path%
cmd>set oracle_sid=orcl(orcl为数据库全局实例)
cmd>lsnrctl start (启动监听)
cmd> net start OracleServiceOrcl (启动服务)
cmd> sqlplus sys as sysdba(用户sys 以DBA身份登录)
 
--4.启动数据库实例
sql>startup
 
--5.修改数据初始文件
sql> alter system set processes=200
sql> alter system set processes=200 scope=spfile(写入文件,永久)
 
--6.创建表空间 TEST
create tablespace test logging datafile 'D:\APP\ORACL11G\ORADATA\ORCL\TEST01.DBF' size 32m  autoextend on next 32m maxsize 2048m extent management local;
 
---7.创建用户并指定表空间
create user testserver_user identified by testserver_user(密码)
default tablespace test_data
#temporary tablespace test_temp;
 
--8 给用户授予权限
grant connect,resource to testserver_user; (db2:指定所有权限)
 
--9.赋权限
sql> grant create table to xxx (给XXX用户创建表权限)
     grant create session to xxx
     alter user xxx quota 128m on users(给XXX用户128兆表空间 users)
     grant select on TEST to PUBLIC (给所有人TEST查询权限)
 
--10.删除权限
sql> revoke select on TEST from PUBLIC
 
select tablespace_name from user_segments where segments_name='TEST'
 
---11.显示表空间大小
 
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size   
from dba_tablespaces t, dba_data_files d   
where t.tablespace_name = d.tablespace_name   
group by t.tablespace_name
 
---12.查看表空间物理文件的名称及大小    D:\APP\ORACL11G\ORADATA\ORCL\USERS01.DBF
select tablespace_name, file_id, file_name,   
round(bytes/(1024*1024),0) total_space   
from dba_data_files   
order by tablespace_name;  
 
----13.查看回滚段名称及大小   
select segment_name, tablespace_name, r.status,   
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,   
max_extents, v.curext CurExtent   
From dba_rollback_segs r, v$rollstat v   
Where r.segment_id = v.usn(+)   
order by segment_name ;  
 
--14.查看控制文件   
select name from v$controlfile;  
 
--15.查看日志文件   
select member from v$logfile; 
 
---16.查看表空间的使用情况   
select sum(bytes)/(1024*1024) as free_space,tablespace_name   
from dba_free_space   
group by tablespace_name;   
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,   
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"   
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C   
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;  
 
--17.查看数据库库对象   
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;   
 
--18.查看数据库的版本    
Select version FROM Product_component_version   
Where SUBSTR(PRODUCT,1,6)='Oracle';  
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,