oracle10g系统管理之表管理

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">截断和删除表</span><span lang="EN-US"><span style=""><span style="font-family: Times New Roman;"> </span></span></span><span style="">当表结构必须保留，而表数据不再需要时，可以使用</span><span lang="EN-US"><span style="font-family: Times New Roman;">TRUNCATE TABLE</span></span><span style="">命令截断表。该命令会释放表所占用的空间，但会保留表结构。</span><span lang="EN-US"><span style="font-family: Times New Roman;">DELETE</span></span><span style="">不会释放空间。</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">删除表</span><span lang="EN-US"><span style="font-family: Times New Roman;"><span style=""> </span>DROP TABLE</span></span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">3.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">显示表信息</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">显示特定用户的表</span><span lang="EN-US"><span style=""><span style="font-family: Times New Roman;"> </span></span></span><span style="">建立表时，</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">会将表的结构信息存放在数据字典中。通过查询数据字典视图</span><span lang="EN-US"><span style="font-family: Times New Roman;">DBA_TABLES,</span></span><span style="">可以显示所有数据库表的详细信息；通过查询数据字典视图</span><span lang="EN-US"><span style="font-family: Times New Roman;">ALL_TABLES,</span></span><span style="">可以显示用户可以访问的所有表信息；通过查询数据字典视图</span><span lang="EN-US"><span style="font-family: Times New Roman;">USER_TABLES,</span></span><span style="">可以显示用户可以访问的某个用户的所有表信息。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 42pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">SELECT table_name,num_rows,pct_free FROM user_tables where own=’SCOTT’</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">显示表列信息</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 42pt;"><span style="font-size: small;"><span style="">建立表时，</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">会将表列的信息存放到数据字典中。通过查询</span><span lang="EN-US"><span style="font-family: Times New Roman;">DBA_TAB_COLUMNS,</span></span><span style="">可以显示数据库任何表的列信息；通过查询</span><span lang="EN-US"><span style="font-family: Times New Roman;">ALL_TAB_COLUMNS,</span></span><span style="">可以显示用户可以访问的表的列信息；通过查询</span><span lang="EN-US"><span style="font-family: Times New Roman;">USER_TAB_COLUMNS,</span></span><span style="">可以显示用户所拥有的表的列信息；</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 42pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">SELECT column_name, date_type, date_length, nullable, FROM dba_tab_columns WHERE owner=’SCOTT’ AND table_name=’EMP’;</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">3)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">显示表段信息</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 42pt;"><span style="font-size: small;"><span style="">建立表时，</span><span lang="EN-US"><span style="font-family: Times New Roman;">ORACLE</span></span><span style="">会自动为表分配相应的表段，并且将表的所有数据存放到相应表段中，通过查询数据字典视图</span><span lang="EN-US"><span style="font-family: Times New Roman;">DBA_SEGMENTS,</span></span><span style="">可以显示数据库所有表段的详细信息；通过查询数据字典视图</span><span lang="EN-US"><span style="font-family: Times New Roman;">USER_SEGMENTS</span></span><span style="">，可以显示当前用户段的信息。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 42pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">SELECT segment_type,tablespace_name,extents,bytes FROM dba_segments WHERE owner =’SCOTT’ AND segment_name=’EMP’;</span></span></p>