Oracle学习系列笔记------数据库登陆、表空间管理

秒速五厘米 2021-12-24 15:19 216阅读 0赞

1 // 把下面显示文本保存到TablespaceStudy.txt文件中  
  2   
  3 SQL >  spool  ' F:\\app\\Administrator\\oradata\\TablespaceStudy.txt ' ;  
  4   
  5 // 创建临时表空间  
  6   
  7 SQL >  create  temporary  tablespace user\_temp  
  8   
  9 **1**  tempfile  ' F:\\app\\Administrator\\oradata\\user\_temp.dbf '   
  10   
  11 **2**  size 50m  
  12   
  13 **3**  autoextend  on   
  14   
  15 **4**  next  50m maxsize 20480m  
  16   
  17 **5**  extent management local;  
  18   
  19 // 创建数据表空间  
  20   
  21 SQL >  create  tablespace user\_data  
  22   
  23 **2**  logging  
  24   
  25 **3**  datafile  ' F:\\app\\Administrator\\oradata\\user\_data.dbf '   
  26   
  27 **4**  size 50m  
  28   
  29 **5**  autoextend  on   
  30   
  31 **6**  next  50m maxsize 20480m  
  32   
  33 **7**  extent management local;  
  34   
  35   
  36   
  37 // 创建用户并指定表空间  
  38   
  39 SQL >  create  user  username identified  by  password  
  40   
  41 **2**  default  tablespace user\_data  
  42   
  43 **3**  temporary  tablespace user\_temp;  
  44   
  45   
  46   
  47 // 给用户授予权限  
  48   
  49   
  50 SQL >  grant  connect,resource  to  username;  
  51   
  52   
  53 SQL >  connect system  / as  sysdba;  
  54 已连接。  
  55 SQL >  select  tablespace\_name, file\_id , file\_name , round (bytes / ( **1024** \* **1024** ), **0** ) total\_sapce  from  dba\_data\_files  order  by  tablespace\_name;  
  56   
  57 TABLESPACE  FILE\_ID  FILE\_NAME  TOTAL\_SAPCE  
  58 \-- \-------- ---------- ------------------------------ -----------   
  59 EXAMPLE \#\#\#\#\#\#\#\#\#\# F:\\APP\\ADMINISTRATOR\\ORADATA\\O \#\#\#\#\#\#\#\#\#\#  
  60  RCL\\EXAMPLE01.DBF  
  61   
  62 SYSAUX \#\#\#\#\#\#\#\#\#\# F:\\APP\\ADMINISTRATOR\\ORADATA\\O \#\#\#\#\#\#\#\#\#\#  
  63  RCL\\SYSAUX01.DBF  
  64   
  65 SYSTEM \#\#\#\#\#\#\#\#\#\# F:\\APP\\ADMINISTRATOR\\ORADATA\\O \#\#\#\#\#\#\#\#\#\#  
  66  RCL\\SYSTEM01.DBF  
  67   
  68 UNDOTBS1 \#\#\#\#\#\#\#\#\#\# F:\\APP\\ADMINISTRATOR\\ORADATA\\O \#\#\#\#\#\#\#\#\#\#  
  69  RCL\\UNDOTBS01.DBF  
  70   
  71 USERS \#\#\#\#\#\#\#\#\#\# F:\\APP\\ADMINISTRATOR\\ORADATA\\O \#\#\#\#\#\#\#\#\#\#  
  72  RCL\\USERS01.DBF  
  73   
  74 USER\_DATA \#\#\#\#\#\#\#\#\#\# F:\\APP\\ADMINISTRATOR\\ORADATA\\U \#\#\#\#\#\#\#\#\#\#  
  75  SER\_DATA.DBF  
  76   
  77   
  78   
  79 SQL >  alter  tablespace user\_data  
  80  **2**  add  datafile  ' F:\\app\\Administrator\\oradata\\user\_data2.dbf '   
  81  **3**  size 10m  
  82  **4**  autoextend  on   
  83  **5**  maxsize 100m  
  84  **6**  ;  
  85   
  86 表空间已更改。  
  87   
  88   
  89 SQL >  select  file\_name , file\_id ,tablespace\_name  from  dba\_data\_files;  
  90   
  91 FILE\_NAME  FILE\_ID  TABLESPACE  
  92 \-- \---------------------------- ---------- ----------   
  93 F:\\APP\\ADMINISTRATOR\\ORADATA\\O \#\#\#\#\#\#\#\#\#\# USERS  
  94 RCL\\USERS01.DBF  
  95   
  96 F:\\APP\\ADMINISTRATOR\\ORADATA\\O \#\#\#\#\#\#\#\#\#\# UNDOTBS1  
  97 RCL\\UNDOTBS01.DBF  
  98   
  99 F:\\APP\\ADMINISTRATOR\\ORADATA\\O \#\#\#\#\#\#\#\#\#\# SYSAUX  
 100 RCL\\SYSAUX01.DBF  
 101   
 102 F:\\APP\\ADMINISTRATOR\\ORADATA\\O \#\#\#\#\#\#\#\#\#\# SYSTEM  
 103 RCL\\SYSTEM01.DBF  
 104   
 105 F:\\APP\\ADMINISTRATOR\\ORADATA\\O \#\#\#\#\#\#\#\#\#\# EXAMPLE  
 106 RCL\\EXAMPLE01.DBF  
 107   
 108 F:\\APP\\ADMINISTRATOR\\ORADATA\\U \#\#\#\#\#\#\#\#\#\# USER\_DATA  
 109 SER\_DATA.DBF  
 110   
 111 F:\\APP\\ADMINISTRATOR\\ORADATA\\U \#\#\#\#\#\#\#\#\#\# USER\_DATA  
 112 SER\_DATA2.DBF  
 113   
 114   
 115 已选择7行。  
 116   
 117 SQL >  select  file\_name , file\_id ,tablespace\_name  from  dba\_data\_files  where  tablespace\_name = ' USER\_DATA ' ;  
 118   
 119 FILE\_NAME  FILE\_ID  TABLESPACE  
 120 \-- \---------------------------- ---------- ----------   
 121 F:\\APP\\ADMINISTRATOR\\ORADATA\\U \#\#\#\#\#\#\#\#\#\# USER\_DATA  
 122 SER\_DATA.DBF  
 123   
 124 F:\\APP\\ADMINISTRATOR\\ORADATA\\U \#\#\#\#\#\#\#\#\#\# USER\_DATA  
 125 SER\_DATA2.DBF  
 126   
 127 删除表空间数据文件  
 128 SQL >  alter  tablespace user\_data  
 129  **2**  drop  datafile  ' F:\\app\\Administrator\\oradata\\USER\_DATA2.DBF ' ;  
 130   
 131 表空间已更改。  
 132   
 133 SQL >  select  file\_name , file\_id ,tablespace\_name  from  dba\_data\_files  where  tablespace\_name = ' USER\_DATA ' ;  
 134   
 135 FILE\_NAME  FILE\_ID  TABLESPACE  
 136 \-- \---------------------------- ---------- ----------   
 137 F:\\APP\\ADMINISTRATOR\\ORADATA\\U \#\#\#\#\#\#\#\#\#\# USER\_DATA  
 138 SER\_DATA.DBF  
 139   
 140   
 141 SQL >  connect username / password;  
 142 已连接。  
 143 SQL >  create  table  Customer(ID  int , Name  varchar ( **30** )) tablespace user\_data;  
 144 成功创建表  
 145   
 146 查看当前用户下的表  
 147 SQL >  select  \*  from  tab;  
 148 TNAME TABTYPE CLUSTERID  
 149 \-- \---------------------------- ------- ---------   
 150 Customer  TABLE   
 151   
 152 SQL >  desc  customer;  
 153  名称 类型  
 154  ID UMBER( **38** )  
 155  NAME  Varchar2   
 156   
 157 \-- 切换到system账户   
 158 SQL >  connect system  / as  sysdba;  
 159   
 160 SQL >  select  \*  from  v$instance;  
 161   
 162 INSTANCE\_NUMBER INSTANCE\_NAME  HOST\_NAME  VERSION STARTUP\_TIME STATUS  
 163   
 164 **1**  orcl WWW \- 81A02E68C5B  **11.1** . **0.7** . **0**  **04** \- 3月  \- **11**  OPEN   
 165   
 166 \-- \----------查看表空间信息   
 167   
 168 SQL >  select  t.tablespace\_name,  round ( sum (bytes / ( **1024** \* **1024** )), **0** ) ts\_size  
 169  **2**  from  dba\_tablespaces t, dba\_data\_files d  
 170  **3**  where  t.tablespace\_name  =  d.tablespace\_name  
 171  **4**  group  by  t.tablespace\_name;  
 172   
 173 TABLESPACE TS\_SIZE  
 174 \-- \-------- -------   
 175 UNDOTBS1  **75**   
 176 SYSAUX  **609**   
 177 USERS  **5**   
 178 SYSTEM  **720**   
 179 EXAMPLE  **100**   
 180 USER\_DATA  **50**   
 181   
 182 已选择6行。  
 183   
 184 SQL >  grant  select  on  dba\_tablespaces  to  username;  
 185   
 186 授权成功。  
 187   
 188 SQL >  select  a.tablespace\_name,a.bytes / **1024** / **1024**  " Sum  MB",(a.bytes \- b.bytes) / **1024** / **1024**  "used MB",  
 189  **2**  b.bytes / **1024** / **1024**  "free MB", round (((a.bytes \- b.bytes) / a.bytes) \* **100** , **2** ) "percent\_used"  from   
 190  **3**  ( select  tablespace\_name, sum (bytes) bytes  from  dba\_data\_files  group  by  tablespace\_name) a,  
 191  **4**  ( select  tablespace\_name, sum (bytes) bytes, max (bytes) largest  from  dba\_free\_space  group  by  tablespace\_name) b  
 192  **5**  where  a.tablespace\_name = b.tablespace\_name  
 193  **6**  order  by  ((a.bytes \- b.bytes) / a.bytes)  desc   
 194  **7**  ;  
 195   
 196 TABLESPACE  Sum  MB used MB free MB percent\_used  
 197 \-- \-------- ------ ------- ------- ------------   
 198 SYSTEM  **720**  **714**  **6**  **99.17**   
 199 SYSAUX  **608.5**  **578**  **30.5**  **94.99**   
 200 USERS  **5**  **4.063**  . **9375**  **81.25**   
 201 EXAMPLE  **100**  **78.63**  **21.38**  **78.63**   
 202 UNDOTBS1  **75**  **18.06**  **56.94**  **24.08**   
 203 USER\_DATA  **50**  **1.188**  **48.81**  **2.38**   
 204   
 205 SQL >  spool  off ;

转载于:https://www.cnblogs.com/hubcarl/archive/2011/03/12/1982349.html