oracle92查版本号，DBA，手工建表间及用户等

 

--查一下数据版本号
select * from v$version

--select * from DBA_TABLESPACES

 

--查找普通用户是DBA权限的
select * from DBA_ROLE_PRIVS
WHERE GRANTED_ROLE = 'DBA'
AND ADMIN_OPTION = 'NO'

 

--查找某用户使用的表空间及临时表空间
select username,default_tablespace,temporary_tablespace from user_users

 

--增加system表空间
ALTER DATABASE DATAFILE 'X:/ORADATA/ORA92/SYSTEM01.DBF' RESIZE 512M

 

--增加临时temp表空间
ALTER DATABASE DATAFILE 'X:/ORADATA/ORA92/TEMP01.DBF'   RESIZE 256M

 

--增加回滚undo表空间
ALTER DATABASE DATAFILE 'X:/ORADATA/ORA92/UNDOTBS01.DBF' RESIZE  512M
   
--创建用户表空间 50G
create tablespace USERDATA datafile 'X:/ORADATA/ORA92/userdata01.dbf' size 51200M;

ALTER DATABASE
        DATAFILE 'X:/ORADATA/ORA92/TEST01.DBF' AUTOEXTEND ON NEXT  10M MAXSIZE UNLIMITED

--创建用户,并指向使用USERDATA表空间
connect sys/sys@jcl as sysdba;
create user user1 identified by user1 DEFAULT TABLESPACE USERDATA;
grant connect,resource to user1;
disconnect;

 

JCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora92)
    )
  )