Oracle Classroom Essay--day 17th

Data Dictionary / Dynamic Performance View

Sql> Conn Hr/hr

sql> desc user_tables All tables in the current user name

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 with access to objects (and their own)

Sql> Conn Scott/tiger

Sql> Select COUNT (*) from All_tables;

Sql> Conn Hr/hr

Sql> Select COUNT (*) from dba_tables; used by the administrator

Sql> Conn Scott/tiger

Sql> Select COUNT (*) from Dba_tables;

Sql> desc v$instance

Sql> desc v$database

Storage Management

To query a predefined table space:

Sql> select tablespace_name,contents from Dba_tablespaces;

Sql> Select file_name, tablespace_name from Dba_data_files;

To create a new tablespace:

sql> Create tablespace tbs01 datafile '/u01/app/oracle/oradata/orcl/tbs01.dbf ' SIZE 10M autoextend on NEXT 10M MAXSIZE 100M;

To create a table in the specified table space:

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; Table of Objects (all)

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; Lack of space, error

sql> rollback;

Sql> Select bytes, blocks, extents, tablespace_name from dba_segments where segment_name= ' T1 '; Space does not release

sql> ALTER TABLE T1 move; Free space

Read-only table space:

Sql> alter tablespace TBS01 Read only;

sql> Delete T1; Prohibit DML

sql> INSERT INTO T1 select * from T1; Prohibit DML

Sql> CREATE TABLE t2 (x int) tablespace tbs01; Failed

Sql> ALTER TABLE T1 add (x int); Success

sql> update T1 set x=1; Failed

sql> drop table T1; Success

The difference between DML and DDL

Change table space Size:

Resize,autoextend,add datafile

To delete a table space:

Sql> drop tablespace tbs01 including contents and datafile;

Oracle Classroom Essay--day 17th