I wrote up this fairly basic cheat sheet for myself several years ago. At the time, I was not very familiar with the Oracle database, and was working with a DBA whom we suspected was not setting things up correctly or even consistently. So, I put together a set of queries to verify basic parameters and compare them to our project documentation. As a result, I learned a bit about database configuration, and we found a very good DBA.
I’m putting these queries on the blog in case someone else finds them useful. The queries were originally for 10gR2.
Data file locations:
SELECT
name
FROM
v$datafile; |
SELECT
name
FROM
v$datafile;
Temp file locations:
SELECT
name
FROM
v$tempfile; |
SELECT
name
FROM
v$tempfile;
Undo file locations:
SELECT
name
FROM
v$datafile
WHERE
ts# = (
SELECT
ts#
FROM
v$tablespace
WHERE
name = (SELECT
tablespace_name
FROM
dba_tablespaces
WHERE
contents = 'UNDO')
); |
SELECT
name
FROM
v$datafile
WHERE
ts# = (
SELECT
ts#
FROM
v$tablespace
WHERE
name = (SELECT
tablespace_name
FROM
dba_tablespaces
WHERE
contents = 'UNDO')
);
Archive log destinations:
– or –
SELECT destination FROM v$archive_dest; |
SELECT destination FROM v$archive_dest;
Control file locations:
SELECT name FROM v$controlfile; |
SELECT name FROM v$controlfile;
Redo log locations:
SELECT member FROM v$logfile; |
SELECT member FROM v$logfile;
Checking ARCHIVELOG mode:
SELECT log_mode FROM v$database; |
SELECT log_mode FROM v$database;
Enabling ARCHIVELOG mode:
SQL> startup mount
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1300928 bytes
Variable Size 157820480 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
Determine which datafiles have AUTOEXTEND enabled:
SELECT file_name FROM dba_data_files WHERE autoextensible = 'YES'; |
SELECT file_name FROM dba_data_files WHERE autoextensible = 'YES';
Disable AUTOEXTEND for a datafile:
ALTER DATABASE DATAFILE '/path/to/datafile.dbf' AUTOEXTEND OFF |
ALTER DATABASE DATAFILE '/path/to/datafile.dbf' AUTOEXTEND OFF
Enable AUTOEXTEND for a datafile, but with a maximum size:
ALTER DATABASE DATAFILE '/path/to/datafile.dbf' AUTOEXTEND ON MAXSIZE 500M |
ALTER DATABASE DATAFILE '/path/to/datafile.dbf' AUTOEXTEND ON MAXSIZE 500M
Estimate size of SYSAUX tablespace (after system has been running for >1 days):
@$ORACLE_HOME/rdbms/admin/utlsyxsz.sql; |
@$ORACLE_HOME/rdbms/admin/utlsyxsz.sql;
and answer the questions that the script asks
Change archive log destination:
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/path/to/archivelogs'; |
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/path/to/archivelogs';
Getting ORA-19815 errors
“ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.”
What’s happening:
* Oracle is trying to write to the flashback area, but is bumping up against
a hard limit defined by the db_recovery_file_dest_size parameter
Possible causes:
* In ARCHIVELOG mode, but no destination is set (defaults to flashback)
Possible fixes:
* Set one or more archive log destinations
* Increase the db_recovery_file_dest_size (if not full)
– BUT: You’ll probably end up having this error again in the future
if you try to use this as a permanent fix
Open Cursors:
SELECT
a.value,
b.name
FROM
v$mystat a,
v$statname b
WHERE
a.statistic# = b.statistic# AND
a.statistic# = 3; |
SELECT
a.value,
b.name
FROM
v$mystat a,
v$statname b
WHERE
a.statistic# = b.statistic# AND
a.statistic# = 3;
Who opened cursors:
SELECT user_name, STATUS, osuser, machine, a.sql_text
FROM v$session b, v$open_cursor a
WHERE a.sid = b.sid; |
SELECT user_name, status, osuser, machine, a.sql_text
FROM v$session b, v$open_cursor a
WHERE a.sid = b.sid;
Max number of cursors per user:
SELECT *
FROM v$parameter
WHERE name = 'open_cursors'; |
SELECT *
FROM v$parameter
WHERE name = 'open_cursors';
Recent Comments