update : 29-06-2017

DBA Script : example_create_tablespace.sql

-- | FILE : example_create_tablespace.sql |
-- +----------------------------------------------------------------------------+
-- | |
-- | |
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : example_create_tablespace.sql |
-- | CLASS : Examples |
-- | PURPOSE : Example SQL script that demonstrates how to create several |
-- | types of tablespaces in Oracle7 - 11g. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
-- +-----------------------------------------------------------------+
-- | (Oracle9i and higher) |
-- | ORACLE MANAGED FILES (OMF) |
-- +-----------------------------------------------------------------+
CREATE TABLESPACE users
LOGGING DATAFILE SIZE 500M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE 500M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
/
-- +-----------------------------------------------------------------+
-- | (Oracle9i and higher) |
-- | SEGMENT SPACE MANAGEMENT AUTO |
-- +-----------------------------------------------------------------+
CREATE TABLESPACE users
LOGGING DATAFILE '/u10/app/oradata/ORA920/users01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE 500M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
/
-- +---------------------------------------------------------------------------+
-- | (Oracle8i and higher) - In version Oracle9i and higher, you |
-- | create the temporary tablespace as part |
-- | of the CREATE TABLESPACE statement: |
-- | |
-- | ... |
-- | DEFAULT TEMPORARY TABLESPACE temp |
-- | TEMPFILE '/u07/app/oradata/ORA920/temp01.dbf' |
-- | SIZE 500M REUSE |
-- | AUTOEXTEND ON NEXT 500M MAXSIZE 1500M |
-- | ... |
-- | TEMPORARY TABLESPACES |
-- +---------------------------------------------------------------------------+
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u07/app/oradata/ORA920/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND on NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/
-- +-----------------------------------------------------------------+
-- | (Oracle8i and higher) |
-- | LOCALLY MANAGED TABLESPACES - with AUTOALLOCATE clause |
-- | |
-- | Use AUTOALLOACTE clause, if the tablespace is expected to |
-- | contain objects of varying sizes requiring different extent |
-- | sizes and having many extents. AUTOALLOCATE is default. |
-- +-----------------------------------------------------------------+
CREATE TABLESPACE users
LOGGING DATAFILE '/u10/app/oradata/ORA817/users01.dbf' SIZE 100M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE 500M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
/
-- +-----------------------------------------------------------------+
-- | (Oracle8i and higher) |
-- | LOCALLY MANAGED TABLESPACES - with UNIFORM SIZE clause |
-- | |
-- | Use UNIFORM SIZE clause if you want exact control over |
-- | unused space, and you can predict exactly the space to be |
-- | allocated for an object or objects and the number and size |
-- | of extents. Default extent size is 1M, if you do not specify |
-- | size parameter. |
-- +-----------------------------------------------------------------+
CREATE TABLESPACE users
LOGGING DATAFILE '/u10/app/oradata/ORA817/users01.dbf' SIZE 10M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
/
-- +-----------------------------------------------------------------+
-- | (All versions of Oracle) |
-- | DICTIONARY MANAGED TABLESPACE |
-- +-----------------------------------------------------------------+
CREATE TABLESPACE users
DATAFILE '/u10/app/oradata/ORA734/users01.dbf' SIZE 10M
DEFAULT STORAGE (initial 64k next 64k maxextents 121 pctincrease 0)
/

End Script

Oracle Database Error Messages



Oracle Database High Availability Any organization evaluating a database solution for enterprise data must also evaluate the High Availability (HA) capabilities of the database. Data is one of the most critical business assets of an organization. If this data is not available and/or not protected, companies may stand to lose millions of dollars in business downtime as well as negative publicity. Building a highly available data infrastructure is critical to the success of all organizations in today's fast moving economy.

Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.