| FAQ for Junior DBA's |
| How to create structure (no data) of a
table from another table? |
| If you need to duplicate a table you can
do a "create table newtablename as select * from
tablename;" This will create the new table will all
the data. If you need to create only the structure, add
a where condition "1=2" or some condition
which is always false.
CREATE TABLE copy_table AS
SELECT * FROM orignal_table WHERE 1=2;
|
|
| What is the SGA size? |
| There are two simple ways to find this.
The first is to invoke server manager (svrmgrl) and
connect ineternal (or as any user). Issue the command
"SHOW SGA". Or you can run the query
"SELECT * FROM V$SGA;" from svrmgrl or sqlplus. |
|
| Where are my alert log and dump files
written to? |
| The alert file is written to your
BACKGROUND_DUMP_DEST. This variable is set in the
config.ora (init.ora) file. You can find the current
values of the dump directories from the database. Invoke
svrmgrl and connect. Issue command "SHOW PARAMETER
DUMP". The SHOW PARAMETER command can be used to
find the value of any database parameter value. For
example if you want to find the block size and block
buffers, issue command "SHOW PARAMETER BLOCK". |
|
| How to increase the size of a tablespace? |
| The size of the tablespace is increased
by changing the size of the size of the underlying
physical files. You can either add more space to the
existing file by
ALTER DATABASE DATAFILE 'filename' RESIZE nn M;
OR you can add more physical datafiles to the
tablespace by
ALTER TABLESPACE tablespacename
ADD DATAFILE 'filename' SIZE nn M;
|
|
| Is my database running in Archivelog
mode? Where are the archived files written? |
| This can be found by invoking server
manager (svrmgrl) and issuing the command "ARCHIVE
LOG LIST".
|
|
| Which database am I connected to? As
which user? |
| The database name can be found out from
different views. The view which everyone has access is
GLOBAL_NAME. The query is
SELECT GLOBAL_NAME FROM GLOBAL_NAME;
To find the user, from sqlplus you can do "SHOW
USER".
|
|
| How to performed compressed
Exports and Imports |
| To export to a compressed file:
/etc/mknod pipename p
compress < pipename > newfilename.Z &
exp <username>/<password> ...... file=pipename
rm -f pipename
To import from a compressed file:
/etc/mknod pipename p
uncompress < newfilename.Z > pipename &
imp <username>/<password> ....... file=pipename
rm -f pipename
|
|
| How do show Tablespace usage (8i ) |
|
COL tablespace_name FORMAT A15 HEADING 'Tablespace|Name'
JUSTIFY L
COL initial_extent FORMAT 999,999 HEADING 'Initial|Extent
Mb' JUSTIFY L
COL next_extent FORMAT 999,999 HEADING 'Next|Extent Mb'
JUSTIFY L
COL max_extents FORMAT 99999999999 HEADING 'Max|Extent'
JUSTIFY L
COL pct_increase FORMAT 999,999 HEADING 'Pct|Increase'
JUSTIFY L
COL status FORMAT A10 HEADING 'Status' JUSTIFY L
COL extent_management FORMAT A10 HEADING 'Extent|Management'
JUSTIFY L
COL allocation_type FORMAT A10 HEADING 'Allocation|Type'
JUSTIFY L
SELECT tablespace_name,
initial_extent/1024/1024 initial_extent,
next_extent/1024/1024 next_extent,
max_extents,
pct_increase,
status,
extent_management,
allocation_type
FROM dba_tablespaces
ORDER by tablespace_name;
|
|
| What is the total size of my Database |
| This can be achieved using the following
statement
SELECT SUM(bytes/1024/1024) Total_MB_Allocated
FROM dba_data_files;
|
|
| Display Installed Products & Version |
This will show you everything installed
and its version for your defined $ORACLE_HOME.
Make sure you're $ORACLE_HOME variable is set correctly.
cd $ORACLE_HOME/orainst
./inspdver
Alternatively On Unix-> strings $ORACLE_HOME/orainst/unix.rgs.
|
|