News for October 2007

Useful Oracle Statements

# 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

# Change 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;</span>

# 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
Posted: October 5th, 2007
Categories: database, oracle
Tags:
Comments: No Comments.