How do I find the overall database size?

The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:

select sum(bytes)/1024/1024 "Meg" from dba_data_files;


To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

To get the size of the on-line redo-logs:

select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

Putting it all together into a single query:

select a.data_size+b.temp_size+c.redo_size "total_size"

from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c;

Can one resize tablespaces and data files?

Add more files to tablespaces
To add more space to a tablespace, one can simply add another file to it. Example:
ALTER TABLESPACE USERS ADD DATAFILE '/oradata/orcl/users1.dbf' SIZE 100M;
Resize datafiles
One can manually increase or decrease the size of a datafile from Oracle 7.2 using the following command:
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M;
Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.
Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimations of space requirements.
Extend datafiles
Also, datafiles can be allowed to automatically extend if more space is required. Look at the following commands:
CREATE TABLESPACE pcs_data_ts
DATAFILE 'c:ora_appspcspcsdata1.dbf' SIZE 3M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
DEFAULT STORAGE ( INITIAL 10240
NEXT 10240
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0)
ONLINE
PERMANENT;
ALTER DATABASE DATAFILE 1 AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

How does one see the uptime for a database?

Look at the following SQL query:
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance;
Marco Bergman provided the following solution:
SELECT to_char(logon_time,'Dy dd Mon HH24:MI:SS') "DB Startup Time"
FROM sys.v_$session
WHERE sid=1 /* this is pmon */
/
Tarun Dua provided the following solution:
Check on operating system level when the PMON process was stated (UNIX/ LINUX only):
ps -ef | grep pmon
Users still running on Oracle 7 can try one of the following queries:
column STARTED format a18 head 'STARTUP TIME'

select C.INSTANCE,
to_date(JUL.VALUE, 'J')
|| to_char(floor(SEC.VALUE/3600), '09' )
|| ':'
-- || substr (to_char(mod(SEC.VALUE/60, 60), '09'), 2, 2)
|| substr (to_char(floor(mod(SEC.VALUE/60, 60)), '09'), 2, 2)
|| '.'
|| substr (to_char(mod(SEC.VALUE, 60), '09'), 2, 2) STARTED
from SYS.V_$INSTANCE JUL,
SYS.V_$INSTANCE SEC,
SYS.V_$THREAD C
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
select to_date(JUL.VALUE, 'J')
|| to_char(to_date(SEC.VALUE, 'SSSSS'), ' HH24:MI:SS') STARTED
from SYS.V_$INSTANCE JUL,
SYS.V_$INSTANCE SEC
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';
select to_char(to_date(JUL.VALUE, 'J') + (SEC.VALUE/86400), -- Return a DATE
'DD-MON-YY HH24:MI:SS') STARTED
from V$INSTANCE JUL,
V$INSTANCE SEC
where JUL.KEY like '%JULIAN%'
and SEC.KEY like '%SECOND%';

Can one rename a tablespace?

From Oracle 10g Release 1, users can rename tablespaces. Example:
ALTER TABLESPACE ts1 RENAME TO ts2;
However, you must adhere to the following restrictions:
• COMPATIBILITY must be set to at least 10.0.1
• Cannot rename SYSTEM or SYSAUX
• Cannot rename an offline tablespace
• Cannot rename a tablespace that contains offline datafiles
For older releases, use the following workaround:
• Export all of the objects from the tablespace
• Drop the tablespace including contents
• Recreate the tablespace
• Import the objects

Can one rename a database user (schema)?

No, this is listed as Enhancement Request 158508. Workaround:

-->Do a user-level export of user A
-->create new user B
-->import system/manager fromuser=A touser=B
-->drop user A

How does one rename a database?

How does one rename a database?
Follow these steps to rename a database:

==>Start by making a full database backup of your database (in case you need to restore if this procedure is not working).
==>Execute this command from sqlplus while connected to 'SYS AS SYSDBA':

ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

==>Locate the latest dump file in your USER_DUMP_DEST directory (show parameter USER_DUMP_DEST) - rename it to something like dbrename.sql.
==>Edit dbrename.sql, remove all headers and comments, and change the database's name. Also change "CREATE CONTROLFILE REUSE ..." to "CREATE CONTROLFILE SET ...".
Shutdown the database (use SHUTDOWN NORMAL or IMMEDIATE, don't ABORT!) and run dbrename.sql.
==>Rename the database's global name:


ALTER DATABASE RENAME GLOBAL_NAME TO new_db_name;

What database aspects should be monitored?

One should implement a monitoring system to constantly monitor the following aspects of a database. This can be achieved by writing custom scripts, implementing Oracle's Enterprise Manager, or buying a third-party monitoring product. If an alarm is triggered, the system should automatically notify the DBA (e-mail, text, etc.) to take appropriate action.
Infrastructure availability:
Is the database up and responding to requests
Are the listeners up and responding to requests
Are the Oracle Names and LDAP Servers up and responding to requests
Are the Application Servers up and responding to requests
Etc.
Things that can cause service outages:
Is the archive log destination filling up?
Objects getting close to their max extents
Tablespaces running low on free space/ Objects what would not be able to extend
User and process limits reached
Etc.

What database block size should I use?

Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application, use a larger block size.

If you are using a volume manager, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).

How does one create a new database?

One can create and modify Oracle databases using the Oracle DBCA (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software to create the starter database.

One can also create databases manually using scripts. This option, however, is falling out of fashion as it is quite involved and error prone. Look at this example for creating an Oracle 9i or higher database:

CONNECT SYS AS SYSDBA
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_1='/u02/oradata/';
ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/';
CREATE DATABASE;