Oracle Tuning Table Spaces, Data Files, Redo Logfiles Related

-- 0.10 QUICK INFO WITH REGARDS TO TABLESPACES, DATAFILES, REDO LOGFILES etc..: -- ------------------------------------------------------------------------------ online redo log informatie: V$LOG, V$LOGFILE: SELECT l.group#, l.members, l.status, l.bytes, substr(lf.member, 1, 50) FROM V$LOG l, V$LOGFILE lf WHERE l.group#=lf.group#; SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, FIRST_TIME, to_char(FIRST_TIME, 'DD-MM-YYYY:HH24:MI') FROM V$LOG_HISTORY; -- WHERE SEQUENCE# SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG; -- tablespace free-used: SELECT Total.name "Tablespace Name", Free_space, (total_space-Free_space) Used_space, total_space FROM (SELECT tablespace_name, sum(bytes/1024/1024) Free_Space FROM sys.dba_free_space GROUP BY tablespace_name ) Free, (SELECT b.name, sum(bytes/1024/1024) TOTAL_SPACE FROM sys.v_$datafile a, sys.v_$tablespace B WHERE a.ts# = b.ts# GROUP BY b.name) Total WHERE Free.Tablespace_name = Total.name; SELECT substr(file_name, 1, 70), tablespace_name FROM dba_data_files; ----------------------------------------------- 0.11 AUDIT Statements: ---------------------------------------------select v.sql_text, v.FIRST_LOAD_TIME, v.PARSING_SCHEMA_ID, v.DISK_READS, v.ROWS_PROCESSED, v.CPU_TIME, b.username from v$sqlarea v, dba_users b where v.FIRST_LOAD_TIME > '2008-05-12' and v.PARSING_SCHEMA_ID=b.user_id order by v.FIRST_LOAD_TIME ; ------------------------------------------------ 0.12 EXAMPLE OF DYNAMIC SQL: ----------------------------------------------select 'UPDATE '||t.table_name||' SET '||c.column_name||'=REPLACE('|| c.column_name||','''',CHR(7));' from user_tab_columns c, user_tables t where c.table_name=t.table_name and t.num_rows>0 and c.DATA_LENGTH>10 and data_type like '%CHAR%' ORDER BY t.table_name desc;

create public synonym EMPLOYEE for HARRY.EMPLOYEE; select 'create public synonym '||table_name||' for CISADM.'||table_name||';' from dba_tables where owner='CISADM'; select 'GRANT SELECT, INSERT, UPDATE, DELETE ON '||table_name||' TO CISUSER;' from dba_tables where owner='CISADM'; select 'GRANT SELECT ON '||table_name||' TO CISREAD;' from dba_tables where owner='CISADM';