oracle課堂隨筆--第十七天

標籤：obj   區別   set   strong   dba   taf   訪問   font   ott   

資料字典 / 動態效能檢視

 

 

SQL> conn hr/hr

SQL> desc user_tables  目前使用者名下所有的表

SQL> select TABLE_NAME from user_tables;

SQL> desc user_views

SQL> select VIEW_NAME from user_views;

SQL> desc user_indexes

SQL> select INDEX_NAME, TABLE_NAME from user_indexes;

 

SQL> conn scott/tiger

SQL> select TABLE_NAME from user_tables;

SQL> select VIEW_NAME from user_views;

SQL> select INDEX_NAME, TABLE_NAME from user_indexes;

 

SQL> conn hr/hr

SQL> select count(*) from all_tables  有許可權訪問的對象（和自己的）

 

SQL> conn scott/tiger

SQL> select count(*) from all_tables;

 

SQL> conn hr/hr

SQL> select count(*) from dba_tables;      管理員所用

 

SQL> conn scott/tiger

SQL> select count(*) from dba_tables;

 

SQL> desc v$instance

SQL> desc v$database

儲存管理

 

 

 

查詢預定義資料表空間：

SQL> select TABLESPACE_NAME,CONTENTS from dba_tablespaces;

SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files;

建立新資料表空間：

SQL> create tablespace tbs01 datafile ‘/u01/app/oracle/oradata/orcl/tbs01.dbf‘ SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M;

在指定的資料表空間中建立表：

SQL> create table t1 tablespace tbs01 as select * from dba_objects where 1=0;

SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name=‘T1‘;

SQL> insert into t1 select * from dba_objects;  對象表（所有的）

SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name=‘T1‘;select extent_id, bytes, blocks from dba_extents where segment_name=‘T1‘;

SQL>

SQL> insert into t1 select * from t1;

SQL> insert into t1 select * from t1;

SQL> insert into t1 select * from t1;

SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name=‘T1‘;

SQL> select extent_id, bytes, blocks from dba_extents where segment_name=‘T1‘;

SQL> insert into t1 select * from t1; 空間不足，報錯

SQL> rollback;

SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name=‘T1‘; 空間不釋放

SQL> alter table t1 move; 釋放空間

唯讀資料表空間：

SQL> alter tablespace tbs01 read only;

SQL> delete t1; 禁止dml

SQL> insert into t1 select * from t1; 禁止dml

SQL> create table t2 (x int) tablespace tbs01; 失敗

SQL> alter table t1 add (x int); 成功

SQL> update t1 set x=1; 失敗

SQL> drop table t1; 成功

dml和ddl的區別

改變資料表空間大小：

resize，autoextend，add datafile

刪除資料表空間：

SQL> drop tablespace tbs01 including contents and datafile;

oracle課堂隨筆--第十七天