Last active
July 18, 2017 13:21
-
-
Save jacqueminv/42f7079c422974a382cc to your computer and use it in GitHub Desktop.
Oracle SQL milliseconds to date
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select to_date('1970-01-01', 'YYYY-MM-DD') + (:milliseconds / 86400000) from dual; | |
| -- charset | |
| select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; | |
| -- FULL table sizes | |
| SELECT owner, | |
| table_name, | |
| TRUNC (SUM (bytes) / 1024 / 1024 / 1024) GB, | |
| ROUND (ratio_to_report (SUM (bytes)) OVER () * 100) Percent | |
| FROM (SELECT segment_name table_name, owner, bytes | |
| FROM dba_segments | |
| WHERE segment_type IN | |
| ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') | |
| UNION ALL | |
| SELECT i.table_name, i.owner, s.bytes | |
| FROM dba_indexes i, dba_segments s | |
| WHERE s.segment_name = i.index_name | |
| AND s.owner = i.owner | |
| AND s.segment_type IN | |
| ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION') | |
| UNION ALL | |
| SELECT l.table_name, l.owner, s.bytes | |
| FROM dba_lobs l, dba_segments s | |
| WHERE s.segment_name = l.segment_name | |
| AND s.owner = l.owner | |
| AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION') | |
| UNION ALL | |
| SELECT l.table_name, l.owner, s.bytes | |
| FROM dba_lobs l, dba_segments s | |
| WHERE s.segment_name = l.index_name | |
| AND s.owner = l.owner | |
| AND s.segment_type = 'LOBINDEX') | |
| WHERE owner IN UPPER ('&owner') | |
| GROUP BY table_name, owner | |
| HAVING SUM (bytes) / 1024 / 1024 / 1024 > 10 /* Ignore really small tables */ | |
| ORDER BY SUM (bytes) DESC; | |
| -- Table sizes | |
| SELECT owner, | |
| table_name, | |
| ROUND ( (num_rows * avg_row_len) / (1024 * 1024 * 1024)) GB | |
| FROM all_tables | |
| WHERE owner NOT LIKE 'SYS%' -- Exclude system tables. | |
| AND num_rows > 0 -- Ignore empty Tables. | |
| ORDER BY GB DESC -- Biggest first. | |
| ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment