SQL Issues & Resources - joyocaowei/joyocaowei.github.io GitHub Wiki

10 Easy Steps to a Complete Understanding of SQL

中文翻译: 十步完全理解SQL

to_date的使用: select to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') from dual;, 这是从灵活性和可移植性考虑的，如果to_date中的第一个参数本身就是string的话，那就直接select to_date('2015-02-12','yyyy-mm-dd') from dual;

Using Optimizer Hints

1. How To View Data Files in the Current Database?
   SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM DBA_DATA_FILES;

2. Displays Space Usage for Each Datafile

   --
   -- Displays Space Usage for Each Datafile.
   --
   
   SET PAUSE ON
   SET PAUSE 'Press Return to Continue'
   SET PAGESIZE 60
   SET LINESIZE 300
   COLUMN "Tablespace Name" FORMAT A20
   COLUMN "File Name" FORMAT A80
    
   SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
           Substr(df.file_name,1,80) "File Name",
           Round(df.bytes/1024/1024,0) "Size (M)",
           decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
           decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
           decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
   FROM    DBA_DATA_FILES DF,
          (SELECT file_id,
                  sum(bytes) used_bytes
           FROM dba_extents
           GROUP by file_id) E,
          (SELECT Max(bytes) free_bytes,
                  file_id
           FROM dba_free_space
           GROUP BY file_id) f
   WHERE    e.file_id (+) = df.file_id
   AND      df.file_id  = f.file_id (+)
   ORDER BY df.tablespace_name,
            df.file_name
   /

3. Increase TABLESPACE

   ALTER TABLESPACE <yourtable>
   ADD DATAFILE '/somewhere/somefile' 
   SIZE 4M
   AUTOEXTEND ON NEXT 4M
   MAXSIZE 1G;

4. Shink Table

   alter table <tablename> enable row movement;
   alter table <tablename> shrink space;
   alter table <tablename> disable row movement;
   
   analyze table <tablename> compute statistics;
   commit;

5. Resize Datafile
   ALTER DATABASE DATAFILE '/somewhere/somefile' RESIZE 200M;

Managing Data Files and Temp Files