oracleÖбȽÏʵÓÃsqlÓï¾ä

-- ²é¿´ORACLE Êý¾Ý¿âÖб¾Óû§ÏµÄËùÓбí
SELECT table_name from user_tables;
-- ²é¿´ORACLE Êý¾Ý¿âÖÐËùÓÐÓû§ÏµÄËùÓбí
select user,table_name from all_tables;
-- ²é¿´ORACLE Êý¾Ý¿âÖб¾Óû§ÏµÄËùÓÐÁÐ
select table_name,column_name from user_tab_columns;
-- ²é¿´ORACLE Êý¾Ý¿âÖб¾Óû§ÏµÄËùÓÐÁÐ
select user,table_name,column_name from all_tab_columns;
-- ²é¿´ORACLE Êý¾Ý¿âÖеÄÐòÁкÅ
select * from user_sequences;
-- ÉÏÃæµÄËùÓжÔÏ󣬶¼¿ÉÒÔͨ¹ýÏÂÃæµÄSQLÓï¾ä²éѯµÃµ½
-- ²éѯËùÓеÄÓû§Éú³ÉµÄORACLE¶ÔÏó
SELECT * from user_objects;
-- ²é¿´ORACLE Êý¾Ý¿âÖÐËùÓбíµÄ×¢ÊÍ
select table_name,comments from user_tab_comments;
-- ²é¿´ORACLE Êý¾Ý¿âÖÐËùÓÐÁеÄ×¢ÊÍ
select table_name,column_name,comments from user_col_comments;
-- ¸ø±í¼ÓORACLEµÄ×¢ÊÍ
COMMENT ON TABLE aa10 IS 'ϵͳ²ÎÊý±í';
-- ¸øÁмÓORACLEµÄ×¢ÊÍ
COMMENT ON COLUMN aa10.aaa100 IS '²ÎÊýÀà±ð';
-- ²é¿´±íÖÐÁеÄÊôÐÔ£¬°üÀ¨ Êý¾ÝÀàÐÍ£¬ÊÇ·ñ·Ç¿ÕµÈ
DESC aa10;
-- ͨ¹ýϵͳ±í£¬²é¿´±íÖÐÁеÄÊôÐÔ£¬°üÀ¨ Êý¾ÝÀàÐÍ£¬ÊÇ·ñ·Ç¿ÕµÈ
SELECT table_name,COLUMN_ID,column_name,data_type,data_length, DATA_PRECISION,NULLABLE
from user_tab_columns
ORDER BY table_name,COLUMN_ID;
-- ²é¿´Êý¾Ý¿âÖÐ±í¡¢Ë÷ÒýÕ¼ÓõÄÊý¾Ý¿â¿Õ¼ä´óС
SELECT * from user_segments;
-- ²é¿´ËùÓбíµÄ¼Ç¼Êý
CREATE TABLE table_count(table_name VARCHAR2(50),columns NUMBER(20));
-- ͨ¹ýPBÔËÐÐÏÂÃæµÄÓï¾ä£¬µÃµ½½á¹û¼¯£¬½«½á¹û¼¯ÔÚPBÏÂÖ´ÐУ¬×îºóÌá½»
select 'insert into table_count values('''||table_name||''', (select count(1) from '||table_name||'));//'||comments from user_tab_comments;
-- ËùÓбíµÄ¼Ç¼¶¼ÔÚtable_countÁË
SELECT * from table_count;
// ½«ORACLEÊý¾Ý¿âµÄ×¢ÊÍͬ²½µ½PBÖÐ ´úÂ뿪ʼ
DELETE from PBCATCOL WHERE PBC_TNAM LIKE '%';
DELETE from PBCATTBL WHERE PBT_TNAM LIKE '%';
INSERT INTO PBCATTBL
( PBT_TNAM,
PBT_OWNR ,
PBT_CMNT)
SELECT ALL_TAB_COMMENTS.TABLE_NAME,
ALL_TAB_COMMENTS.OWNER,
ALL_TAB_COMMENTS.COMMENTS
from ALL_TAB_COMMENTS
WHERE ALL_TAB_COMMENTS.OWNER = 'LH'
AND TABLE_NAME LIKE '%';
// ͬ²½×Ö¶ÎÃû
INSERT INTO PBCATCOL
( PBC