October 2007 Archives

# Define ORACLE_HOME
export ORACLE_HOME=/sw/oracle

# Exporting
exp username/password FILE=/var/tmp/MYDATABASE.export OWNER=MYOWNER

# Importing
imp username/password FILE=/var/tmp/MYDATABASE.export LOG=/var/tmp/MYDATABASE.log FULL=Y

# Changing User Password
sqlplus `/as sysdba`
select username, password from dba_users;
alter user MYOWNER identified by MYPASSWORD; 

# Shutting down
export ORACLE_SID=SID
sqlplus '/ as sysdba'
# shutdown
# shutdown immediate
# shutdown abort

* No option means SHUTDOWN NORMAL.  The database waits for all users to disconnect, prohibits new connects, then closes and dismounts the database, then shuts down the instance.

* SHUTDOWN IMMEDIATE.  Cancels current calls like a system interrupt, and closes and dismounts the database, then shuts down the instance. PMON gracefully shuts down the user processes. No instance recovery is required on startup.

* SHUTDOWN ABORT.  This doesn't wait for anything. It shuts the database down now. Instance recovery will probably be required on startup. You should escalate to this by trying the other shutdowns first. 

# Show number of rows
select * from table where rownum <= 10;

# Show Tablespace
select * from v$tablespace;

# Drop Tablespace & User
drop tablespace MYDATABASE_TS including contents;
drop tablespace MYDATABASE_TS including contents and datafiles;
drop user MYOWNER; 

# Create Tablespace and grant DBA permissions
CREATE TABLESPACE MYDATABASE_TS
    DATAFILE '/db/MYDATABASE/MYDATABASE.dbf' SIZE 10M
    AUTOEXTEND ON NEXT 10M
    DEFAULT STORAGE (INITIAL 10M NEXT 10M);

GRANT DBA TO MYOWNER; 

CREATE USER ANOTHERUSER IDENTIFIED BY ANOTHERUSER
    DEFAULT TABLESPACE MYDATABASE_TS
    QUOTA UNLIMITED ON MYDATAVASE)TS;

GRANT DBA TO ANOTHERUSER;

# show all the existing tables and the owner of the db
select table_name, owner from all_tables;

# show all the tables for the owner
select table_name from all_tables where owner = ""
select owner,table_name from all_tables where owner = 'OWNER_NAME' order by TABLE_NAME;

# show all the tables from the entire db
select table_name from user_tables;

# show all the tables from the db
select table_name from tabs;

# show all the existing tables
select table_name from all_tables;

# show only the table specified in the table_name below
select table_name, owner from all_tables where table_name = 'TABLE_NAME;

# show all the column types of a table
describe TABLE_NAME;

# removing a column
atler table TABLE_NAME drop column COLUMN_NAME;

# adding a column
alter table TABLE_NAME add (
 COLUMN_NAME COLUMN_TYPE,
 COLUMN_NAME COLUMN_TYPE
);

# modifying a column
alter table TABLE_NAME modify (
 COLUMN_NAME COLUMN_TYPE
);