• View
• Edit
• History
• Print

Oracle

To Start Oracle

> su - oracle
> sqlplus /nolog
sqlplus> connect / as sysdba;
sqlplus> startup;
sqlplus> exit;
> lsnrctl start

To Stop Oracle

> su - oracle
> sqlplus /nolog
sqlplus> connect / as sysdba;
sqlplus> shutdown;
or
sqlplus> shutdown immediate;
sqlplus> exit;

em console

# su - oracle
> cd $ORACLE_HOME/bin
> ./emctl start dbconsole
> ./emctl stop dbconsole

To get all columns for a table

• select * from all_tab_columns where table_name = upper(:String__table);

to get all tables

• select * from all_all_tables;
• select table_name from all_all_tables where owner='SIMON';
• select * from tabs;
• SELECT table_name FROM user_tables; (tables in users schema only)

Change oracle memory

• alter system set sga_target=600m

Make read only partitioned tabled writable again

select 'alter tablespace ' || tablespace_name  || ' read write;'
from dba_tablespaces
where status = 'READ ONLY';

CLOBS

dbms_lob.substr(debug.ERROR, 6, 1) = '<ERROR'

to see what sql was just run:

select * from v$sql order by first_load_time desc;

Stop Archive logs (no backups after this)

1. stop db
2. ALTER DATABASE NOARCHIVELOG
3. start db

NOTE: do full backup after switching back on.

See how much archive log space left:

1. select (space_used/space_limit)*100 from v$recovery_file_dest
2. select name, round(space_limit/1048576) As space_limit,round(space_used/1048576) As space_used 2 from v$RECOVERY_FILE_DEST;
3. show parameter db_recovery_file_dest;

Increase your archive space (make sure you have enough free space no that partition first)

1. ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;

Kickoff our rman backup (you can do this anytime to clear out archive logs)

1. /home/oracle/scripts/backup.sh