Oracle’s equivalent for MySQL SHOW CREATE TABLE
SQL> set pages 0
SQL> set long 999999
SQL> select dbms_metadata.get_ddl('TABLE', 'EMPLOYEES', 'EMP') from dual;
Administration Workshop II 12-34. Migrating your database to ASM Storage
- Obtain the file names of the current control files and online redo logs by using
V$CONTROLFILEandV$LOGFILE. - Shut down the database consistently. Modify the server parameter file of your database as follows:
- Set the necessary OMF destination parameters to the desired ASM disk group.
- Remove the
CONTROL_FILEparameter.
- Run the following RMAN script:
STARTUP NOMOUNT; # replace '/u1/c1.ctl' for the location of the control file RESTORE CONTROLFILE FROM '/u1/c1.ctl'; ALTER DATABASE MOUNT; # replace the disk group '+dgroup1' for your disk group name BACKUP AS COPY DATABASE FORMAT '+dgroup1'; SWITCH DATABASE TO COPY; # replace '/u1/log1' for your online redo log name and '+dgroup1' for your disk group name SQL "ALTER DATABASE RENAME '/u1/log1' TO '+dgroup1' "; # Repeat RENAME command for all online redo log members ... ALTER DATABASE OPEN RESETLOGS; SQL "ALTER DATABASE TEMPFILE '/u1/temp1' DROP";
- Delete the old database files.
Administration Workshop II 12-34. Obtaining file names of the current control files and online redo logs
Obtain them by querying V$CONTROLFILE and V$LOGFILE
Administration Workshop II 11-20. Shrinking Segments by Using SQL
To shrink an object
ALTER TABLE employees SHRINK SPACE [CASCADE]
To shrink a LOB (must be issued separately since SHRINK SPACE CASCADE does not cascade to LOB)
ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE)
Shrink the overflow segment of an Index Organized Table (IOT)
ALTER TABLE employees OVERFLOW SHRINK SPACE
Administration Workshop II 9-32. SQL Tuning Advisor Workload Source
- Current and recent SQL activity
- Import workload from SQL repository
- User-defined workload; import SQL from a Table or View
- Create a Hypothetical workload from the following Schemas and Tables (separated by commas)
Administration Workshop II 9-30. SQL Access Advisor Overview
The SQL Access Advisor can recommend the proper set of materialized views, materialized view logs, and indexes for a given workload. Understanding and using these structures is essential when optimizing SQL because they can result in significant performance improvements in data retrieval.
- A bitmap index offers a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques.
- B-tree indexes are most commonly used in a data warehouse to index unique or near-unique keys.
Administration Workshop II 9-14. Statistics for n-tier environments
In an n-tier environment where there is an application server that is pooling database connections, viewing sessions may not provide the information yhou need to analyze performance. Grouping sessions into service names enables you to monitor performance more accurately. These two views provide the same information that their like-named session counterparts provide, except that the information is presented at the service level rather than at the session level:
V$SERVICE_WAIT_CLASSshows wait statistics for each service, broken down by wait class.V$SERVICE_EVENTshows the same information asV$SERVICE_WAIT_CLASS, except that it is further broekn down by event ID.
Viewing pool sizes
From Administration Workshop II 8-23
SELECT name, value, isdefault FROM v$parameter WHERE name LIKE '%size';
Administration Workshop II 8-21. Manually tuning pool sizes
SELECT SUM(bytes)/1024/1024 size_mb
FROM v$sgastat WHERE pool = 'shared pool';
Determine the size for the autotuned component in the SGA
SELECT component, current_size/1024/1024 size_mb
FROM v$sga_dynamic_components;
Memory Management
Source: Administration Workshop II 8-14.
To be taken into account when manually tuning the pool sizes:
- OLTP systems require large buffer cache
- Parallel batch jobs require large value for large pool
Now, it’s typical to have a system that runs large OLTP jobs during the day (requiring a large buffer cache) and runs parallel batch jobs at night (requiring a large value for the large pool). In this system you would have to simultaneously configure both the buffer cache and the large pool to acoommodate your peak requirements, if using manual pool sizing. This means wasting memory. Using Automatic Shared Memory Management (ASMM) you would maximize memory utilization by automatically adjusting the pool sizes.
