Useful Sql

$ORACLE_HOME\rdbms\admin\catalog.sql
Create views data dictionary information

$ORACLE_HOME\rdbms\admin\catproc.sql
Build procedural package

Tablespace
Create undo tablespace
1* create undo tablespace undotbs2 datafile size 20M
SYS> /

Tablespace created.

Create temp tablespace
1* create temporary tablespace temptbs2 tempfile size 5M
SYS> /

Tablespace created.


SYS> alter system set undo_management=AUTO scope=spfile;

System altered.

Change undo tablespace
SYS> alter system set undo_tablespace=UNDOTBS1 scope=spfile;

System altered.

Change temporary tablespace
SYS> alter system set undo_tablespace=UNDOTBS1 scope=spfile;

System altered.

Create users tablespace
1* create tablespace userstbs2 datafile size 20M
SYS> /

Tablespace created.

Change users tablespace
SYS> alter database default tablespace userstbs2;

Database altered.


Create tablespace
SYS> create tablespace invoice_pk datafile '/u01/app/oracle/oradata/ORCL/datafile/invoicetbs1' size 5M autoextend ON;

Tablespace created.

SYS> ed
Wrote file afiedt.buf

1 create table invoice
2 (
3 ID number constraint invpk primary key
4 using index
5 (create unique index invpk on invoice(id) tablespace invoice_pk),
6 invdate date,
7 description varchar2(200))
8* tablespace invoice_pk
SYS> /

Table created.

Resize Tablespace
SYS> alter database datafile '/u01/app/oracle/oradata/ORCL/datafile/invoicetbs1' resize 10M;

Database altered.

Set Tablespace read only

SYS> alter tablespace invoice_pk read only;

Tablespace altered.

Set Tablespace read write

1* alter tablespace invoice_pk read write
SYS> /

Tablespace altered.

Users

Create users
SYS> create user usera identified by oracle default tablespace INVENTORY temporary tablespace temp;

User created

SYS> grant create session to usera;

Grant succeeded.

SYS> alter user usera quota unlimited on inventory;

User altered.

SYS> grant unlimited tablespace to usera;

Grant succeeded.
Create OS authenticated users
SYS> show parameter prefix;
os_authent_prefix string ops$
SYS> create user ops$oracle identified externally;

User created.

SYS> grant grant any object privilege to ops$oracle;

Grant succeeded.

SYS> grant create any table to ops$oracle;

Grant succeeded.
Allow to create table on any schema
owner stay the same as the schema owner
Lock/Unlock users
1* alter user usera account lock
SYS> /

SYS> alter user usera account unlock;

User altered.


Primary Key Deferrable

DEFERRED
indicates that the conditions specified by the deferrable constraint are checked when the transaction is committed.

INITIALLY DEFERRED DEFERRABLE tells Oracle to do deferred constraint checking

1 create table inventory(
2 partno number(4) constraint partno_pk primary key deferrable initially immediate,
3* partdesc varchar2(35) constraint partdesc_uq unique deferrable initially immediate)
SYS> /

Table created.

SYS> insert into inventory values (1,'A');

1 row created.

SYS> insert into inventory values (1,'B');
insert into inventory values (1,'B')
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.PARTNO_PK) violated

1 create table inventory(
2 partno number(4) constraint partno_pk primary key deferrable initially deferred,
3* partdesc varchar2(35) constraint partdesc_uq unique deferrable initially deferred)
SYS> /

Table created.

SYS> insert into inventory values (1,'A');

1 row created.

SYS> insert into inventory values (1,'B');

1 row created.

SYS> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SYS.PARTNO_PK) violated

Check Log Switch
SYS> select sequence#, first_time from v$log_history;
1 23-JAN-09
2 23-JAN-09
3 23-JAN-09
4 23-JAN-09
5 23-JAN-09
6 24-JAN-09
7 25-JAN-09
8 25-JAN-09
9 26-JAN-09
10 15-FEB-09
11 25-FEB-09
12 01-MAR-09
13 02-APR-09

13 rows selected.
ArchiveLog Mode
Make sure you have enough disk space
archive log list;
SYS> Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Current log sequence 13
SYS>
1* select name, log_mode,open_mode from v$database
SYS> /
ORCL NOARCHIVELOG READ WRITE


SYS> alter database archivelog;

Database altered.

SYS> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SYS>

SYS> select name, log_mode,open_mode from v$database;
ORCL ARCHIVELOG MOUNTED

DB_Recovery_Size
SYS> show parameter db_recovery_file_dest_size;
db_recovery_file_dest_size big integer 8048M

Guarantee Undo Rentention
SQL> alter tablespace undotbs1 RENTENTION GUARANTEE
SQL> alter tablespace undotbs1 RENTENTION NOGUARANTEE

Show current user
SQL> show users;

Display current database
SQL> select * from global_name;