Oracle Small Knowledge

Common view Table Structure commands

Elect table_name from User_tables; -- The current user 's table

Select table_name from All_tables; -- All users ' tables

Select table_name from Dba_tables; -- including system tables

Select table_name from dba_tables where owner= ' user name '

User_tables:

Table_name,tablespace_name,last_analyzed , etc.

Dba_tables:

Ower,table_name,tablespace_name,last_analyzed , etc.

All_tables:

Ower,table_name,tablespace_name,last_analyzed , etc.

All_objects:

Ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status , etc.

Get table field:

SELECT * from User_tab_columns where table_name= ' user table ';

SELECT * from All_tab_columns where table_name= ' user table ';

SELECT * from Dba_tab_columns where table_name= ' user table ';

User_tab_columns:

Table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,

column_id , etc.

All_tab_columns :

Ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,

column_id , etc.

Dba_tab_columns:

Ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,

column_id , etc.

Get Table Comments:

SELECT * FROM User_tab_comments

User_tab_comments:table_name,table_type,comments

--and the corresponding dba_tab_comments,all_tab_comments, these two than user_tab_comments

More ower columns.  

Get field Comments:

SELECT * FROM User_col_comments

User_col_comments:table_name,column_name,comments

Querying table space capacity, adding dbf files to table spaces

Select

FILE_ID,

file_name,

Tablespace_name,

Autoextensible,increment_by,

bytes/1024/1024 "bytes MB",

maxbytes/1024/1024 "MaxBytes MB",

Bytes/maxbytes

From Dba_data_files

ORDER BY file_id Desc;

Alter tablespace table Space

Add datafile ' dbf file physical path ' size 20m reuse

Autoextend on

Next 5M

SELECT * from sys.props$ where name = ' Nls_characterset ';

Query Character Set

SELECT object_name, S.sid, s.serial#, P.spid

From V$locked_object L, dba_objects O, v$session S, v$process p

WHERE l.object_id = o.object_id

and l.session_id = S.sid

and s.paddr = p.addr;

Query deadlock

Alter system kill session ' sid,serial# ';

Kill the process

SELECT * FROM V$version

Finding Database Information

Oracle Small Knowledge