estimate_only=y

will only give the size. It will not tell how many hours the export/import is going to take.

shrink datafiles

SQL>COLUMN SHRINK_DATAFILES FORMAT A75 WORD_WRAPPED
SQL>COLUMN VALUE NEW_VAL BLKSIZE
SQL>SELECT VALUE FROM V$PARAMETER WHERE NAME = 'DB_BLOCK_SIZE'
/
SQL>SELECT 'ALTER DATABASE DATAFILE ''' FILE_NAME ''' RESIZE ' CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) 'M;' SHRINK_DATAFILES FROM DBA_DATA_FILES DBADF,(SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) AND CEIL(BLOCKS*&&BLKSIZE/1024/1024)- CEIL((NVL(HWM,1)* &&BLKSIZE)/1024/1024 ) > 0
/

drop temp tablespace in asm

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES
/

add datafile to temp tablespace in asm

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA1_A3' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
/

you can also specify user defined location instead of system generated one.

create temp tablespace in asm

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA1_A3' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M SEGMENT SPACE MANAGEMENT MANUAL
/

dblink creation

make sure that you have the tnsentry of db2 in db1.

create public database link dblinkname connect to userindb2 identified by passwordindb2 using 'tnsentrynameindb2'
/

verify
select * from dual@dblinkname
/
you need to get x as the output.
select name from v$database@dblinkname
/
you need to get db2name as the output.

you may get the error "ORA-02082: a loopback database link must have a connection qualifier" in case if you use the database link name as that of the global name in db2. To avoid this, you can either give a different database link name (or) you can change the global name, then create the database link and after that you change the global name back to its original name.

Query dba_db_links

reset the password

alter user username identified by password
/

unable to login as ops user

User was unable to login to the database using the ops user ops$abc so I had to change the parameter remote_os_authent parameter from false to true.

sql>alter system set remote_os_authent=true scope=spfile
/

Blackout the database in OEM grid control
bounce the database as remote_os_authent parameter is a static parameter
Remove the blackout for the database
Then the user was able to login to the database

restart RAC database

The user might have modified the initialization parameter. So, the RAC database has to be restarted like this,

oracle@dbname:/home/oracle=>srvctl stop database -d dbname
oracle@dbname:/home/oracle=>srvctl start database -d dbname

SQL>create pfile from spfile
/
File Created.

create an oracle directory

CREATE OR REPLACE DIRECTORY directoryname AS 'complete path'
/
GRANT read, write ON DIRECTORY directoryname TO username
/

The username should already exist, otherwise create one.

create ops user

spool create_ops_user.log
/
CREATE USER OPS$ABC IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK
/
GRANT "CONNECT" TO OPS$ABC
/
GRANT DEVELOPMENT TO OPS$ABC
/
GRANT "RESOURCE" TO OPS$ABC
/
ALTER USER OPS$ABC DEFAULT ROLE "CONNECT", DEVELOPMENT, "RESOURCE"
/
GRANT CREATE SESSION TO OPS$ABC
/
GRANT UNLIMITED TABLESPACE TO OPS$ABC
/
spool off
/

logon trigger

The motive of this is that if the user logs in as U_SCHEMA, it should display as if the user has logged in as SCHEMA.

Go to OEM grid control(login as system)
Target
database
schema
triggers
login as system user
schema->system
go
edit the 'logon aspire' trigger
include the below lines
if(upper(USER) = 'U_SCHEMA') then execute immediate 'alter session set current_schema=SCHEMA';
end if;

To check
----------------
sql>conn U_SCHEMA/U_SCHEMA;
connected.

sqL>select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;
SCHEMA

shrink space

An user complained that querying a table in uat took lot of time compared to production. To resolve the problem, I did the following.

SQL> alter table schemaname.tablename enable row movement
/
Table altered.

SQL> alter table schemaname.tablename shrink space
/
Table altered.


Conditions
-------------------
Make sure that the tablespace has double the size of the table
Make sure that the undo tablespace and temp tablespace have enough space
Avoid using this method if the table has LOB's, etc.

oracle stop script

#!/usr/bin/ksh
stty istrip
stty erase ^?
ulimit -n 1024
PATH=/usr/bin:/usr/ccs/bin:.:/etc:/src:/usr/sbin:/usr/ucb:/usr/openwin/bin/xview:/usr/local/bin
export PATH
EDITOR=vi; export EDITOR
ENV=$HOME/.kshrc
#PS1="oracle@"'$ORACLE_SID'":"'$PWD'"=>"ORACLE_SID=db1
export ORACLE_SID
export myhost=`hostname`
PS1='$myhost: $ORACLE_SID $PWD $ '
LD_LIBRARY_PATH=/ora01/app/oracle/product/10.2.0/lib
export LD_LIBRARY_PATH
export ORATAB=/var/opt/oracle/oratab
export TNS_ADMIN=/ora01/app/oracle/product/10.2.0/network/admin
export ORACLE_BASE=/ora01/app/oracle
export ORACLE_HOME=/ora01/app/oracle/product/10.2.0
PATH=$PATH:$ORACLE_HOME/bin:.
export PATH
export ORACLE_SID=db1
sqlplus /nolog <connect / as sysdba
shutdown immediate
EOF1
export ORACLE_SID=db2
sqlplus /nolog <connect / as sysdba
shutdown immediate
EOF1
export ORACLE_SID=db3
sqlplus /nolog <connect / as sysdba
shutdown immediate
EOF1
export ORACLE_SID=db4
sqlplus /nolog <connect / as sysdba
shutdown immediate
EOF1
export ORACLE_SID=db5
sqlplus /nolog <connect / as sysdba
shutdown immediate
EOF1
lsnrctl stop
cd /ora01/app/oracle/product/agent10g/bin
./emctl stop agent
exit 0

oracle start script

#!/usr/bin/ksh
stty istrip
stty erase ^?
ulimit -n 1024
PATH=/usr/bin:/usr/ccs/bin:.:/etc:/src:/usr/sbin:/usr/ucb:/usr/openwin/bin/xview:/usr/local/bin export PATH
EDITOR=vi;
export EDITOR
ENV=$HOME/.kshrc
#PS1="oracle@"'$ORACLE_SID'":"'$PWD'"=>"
ORACLE_SID=db1
export ORACLE_SID
export myhost=`hostname`
PS1='$myhost: $ORACLE_SID $PWD $'
LD_LIBRARY_PATH=/ora01/app/oracle/product/10.2.0/lib
export LD_LIBRARY_PATH
export ORATAB=/var/opt/oracle/oratab
export TNS_ADMIN=/ora01/app/oracle/product/10.2.0/network/admin
export ORACLE_BASE=/ora01/app/oracle
export ORACLE_HOME=/ora01/app/oracle/product/10.2.0
PATH=$PATH:$ORACLE_HOME/bin:.
export PATH
export ORACLE_SID=db1
sqlplus /nolog <connect / as sysdba
startup
EOF1
export ORACLE_SID=db2
sqlplus /nolog <connect / as sysdba
startup
EOF1
export ORACLE_SID=db3
sqlplus /nolog <connect / as sysdba
startup
EOF1
export ORACLE_SID=db4
sqlplus /nolog <connect / as sysdba
startup
EOF1
export ORACLE_SID=db5
sqlplus /nolog <connect / as sysdba
startup
EOF1
lsnrctl start
cd /ora01/app/oracle/product/agent10g/bin
./emctl stop agent
sleep 30
./emctl start agent
exit 0

stop script (RAC and ASM)

export ORAENV_ASK=NO
export ORACLE_SID=+ASM
. oraenv
echo " "
echo "listener status before stop"
echo " "
srvctl status listener
echo " "
echo "db status before stop"
echo " "
srvctl status database -d db1
srvctl status database -d db2
echo " "
echo "asm/diskgroup status before stop"
echo " "
srvctl status diskgroup -g DATA1_T3
srvctl status asm
echo " "
echo "Now stopping db"
#echo "Now stopping oracle_home= /ora01/app/oracle/product/11.2.0.2 using statefile /ora01/state_file/dbhome_state"
echo " "
#srvctl stop home -o /ora01/app/oracle/product/11.2.0.2 -s /ora01/state_file/dbhome_state
srvctl stop database -d db1
srvctl stop database -d db2
echo " "
echo "Status after stop..."
echo " "
#srvctl status home -o /ora01/app/oracle/product/11.2.0.2 -s /ora01/state_file/dbhome_state
srvctl status database -d db1
srvctl status database -d db2
echo " "
echo "Now stopping grid home components"
echo " "
rm /ora01/state_file/gridhome_state
srvctl stop home -o /ora01/GRID -s /ora01/state_file/gridhome_state
echo " "
echo "asm/diskgroup/listener status after stop"
echo " "
srvctl status diskgroup -g DATA1_T3
srvctl status asm
srvctl status listener
echo " "
echo "Now disabling and stopping has"
echo " "
echo "current has status"
echo " "
crsctl check has
#crsctl disable has
#crsctl stop has

start script (RAC and ASM)

export ORAENV_ASK=NO
export ORACLE_SID=+ASM
. oraenv
echo " "
echo "enabling has and checking status"
echo " "
crsctl enable has
crsctl start has
crsctl check has
echo " "
echo "listener status before start"
echo " "
srvctl status listener
echo " "
echo "asm/diskgroup status before start"
echo " "
srvctl status diskgroup -g DATA1_T3
srvctl status asm
echo " "
echo "db status before start"
echo " "
srvctl status database -d db1
srvctl status database -d db2
echo " "
echo "Now starting grid home components"
echo " "
srvctl start home -o /ora01/GRID -s /ora01/state_file/gridhome_state
echo " "
echo "asm/diskgroup/listener status after start"
echo " "
srvctl status diskgroup -g DATA1_T3
srvctl status asm
srvctl status listener
echo " "
echo "Now starting db"
echo " "
srvctl start database -d db1
srvctl start database -d db2
echo " "
echo "Status after start..."
echo " "
srvctl status database -d db1
srvctl status database -d db2

create oracle report about the users

vi sox_list.sh
---------------------------
#!/bin/sh
ORACLE_SID=dbname
ORACLE_HOME=/oracle/app/oracle/product/10.2.0
TNS_ADMIN=/oracle/app/oracle/product/10.2.0/network/admin
export ORACLE_SID ORACLE_HOME TNS_ADMIN
PATH=/usr/bin:/usr/ccs/bin:.:/etc:/src:/usr/sbin:/usr/ucb:/usr/openwin/bin/xview:/usr/local/bin:/oracle/app/oracle/product/10.2.0/bin
export PATH
cd /oracle/SOX
EMAILDBA=`/usr/bin/cat /oracle/SOX/.emaildba`; export EMAILDBA
sqlplus -s sox_audit/sox_audit @userlist_himes.sql
/usr/bin/cat userlist_himes.lst /usr/bin/mailx -s "SOX Uerlist" myemail@gmail.com
/usr/bin/cat userlist_himes.lst /usr/bin/mailx -s "SOX Uerlist" myemail1@gmail.com
/usr/bin/cat userlist_himes.lst /usr/bin/mailx -s "SOX Uerlist" myemail2@gmail.com

vi "userlist_himes.sql"
-------------------------------
spool userlist_himes.lst
set linesize 150
set pagesize 1000
set heading off
SELECT TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
select name from v$database;
set heading on
break on username skip 1
select a.USERNAME,a.ACCOUNT_STATUS,a.PROFILE, b.granted_role from dba_users a, dba_role_privs b
where a.USERNAME=b.GRANTEE
order by username,granted_role;
spool off
exit

Refresh script with original export/import (using dblink method)

works fine in 10g not in 11g
vi schema_export.sh
----------------------------
#!/bin/ksh
. /home/oracle/.profile
SYSTEMPWORD=`cat /home/oracle/cronjobs/.systempword`
export ORACLE_SID=testdb
##echo $SYSTEMPWORD $ORACLE_HOME/bin/exp@proddb system \exp system/password@proddb \owner=schema file=/orabkup/refresh/testdb/schema_refresh.dmp compress=n statistics=none consistent=y buffer=1024000 log=/orabkup/refresh/testdb/schema_import.log
/bin/mailx -s "schema REFRESH exports complete" myemail@gmail.com < /dev/null
exit

vi drop_create_user.sql
----------------------------------
spool drop_create_user.log
drop user schema cascade;
CREATE USER schema IDENTIFIED BY VALUES 'password' DEFAULT TABLESPACE tbsname
TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON tbsname QUOTA UNLIMITED ON tbsname
PROFILE DEFAULT
ACCOUNT UNLOCK
/
GRANT "CONNECT" TO schema WITH ADMIN OPTION
/
GRANT DBA TO schema
/
GRANT "RESOURCE" TO schema
/
GRANT ROLE TO schema WITH ADMIN OPTION
/
ALTER USER schema DEFAULT ROLE "CONNECT", DBA, "RESOURCE", ROLE
/
GRANT CREATE USER TO schema WITH ADMIN OPTION
/
GRANT UNLIMITED TABLESPACE TO schema
/
GRANT CREATE ROLE TO schema WITH ADMIN OPTION
/
spool off
exit

vi coalesce_tablespace.sql
---------------------------------------
spool coalesce_tablespace.log
ALTER TABLESPACE tbsname COALESCE
/
ALTER TABLESPACE tbsname COALESCE
/
spool off
exit

vi siebel_import.sh
------------------------------
#!/bin/ksh
. /home/oracle/.profile
export ORACLE_SID=testdb
cd /home/oracle/REFRESH/testdb/
rm drop_create_user.log
rm coalesce_tablespace.log
$ORACLE_HOME/bin/sqlplus "/ as sysdba" << EOF1@drop_create_user.sql
quit
EOF1

$ORACLE_HOME/bin/sqlplus "/ as sysdba" << EOF2@coalesce_tablespace.sql
quit
EOF2

imp system/password file=/orabkup/refresh/testdb/schema_refresh.dmp log=/orabkup/refresh/testdb/schema_import.log fromuser=schema touser=schema rows=y indexes=y grants=y buffer=1024000 STATISTICS=none
/bin/mailx -s "IMPORT REFRESH schema COMPLETED" myemail@gmail.com < /dev/null
exit

crontab -e
-----------------
01 10 14 06 * /home/oracle/REFRESH/testdb/schema_export.sh &
46 11 14 06 * /home/oracle/REFRESH/testdb/schema_import.sh &

bind peek variable

alter system set "_optim_peek_user_binds"=FALSE
/

get grant script

SELECT 'GRANT 'PRIVILEGE' TO 'GRANTEE';' FROM DBA_SYS_PRIVS WHERE GRANTEE IN (); (for system privileges)
SELECT 'GRANT 'PRIVILEGE' TO 'GRANTEE';' FROM DBA_TAB_PRIVS WHERE GRANTEE IN (); (for object privileges)
SELECT 'GRANT 'GRANTED_ROLE' TO 'GRANTEE ';' FROM DBA_ROLE_PRIVS WHERE GRANTEE IN ();

tablespace I/O

target
database
snapshots
tablespace IO stats

You can compare the Av Rd(ms) from one period to another. If one is too high compared to the other, it is a cause of concern.

alert logs in oem grid control

target
database
alert log
contents
last 50 go

awr report in oem grid control

target
database
snapshots

paging

go to oem grid control
home page
target
database
paging
cpu utilization
all metrics
paging activity
pages paged in (per second)
manual refresh

kill session

SQL> drop user xxxxx cascade;
drop user xxxxx cascade
*
ERROR at line 1:ORA-01940: cannot drop a user that is currently connected
SQL> select sid, serial# from v$session where username = 'xxxxx';
SID SERIAL#
----- ------------
268 1268
315 1223

SQL> Alter user xxxxx account lock;
SQL> alter system kill session '268,1268';
System altered.

SQL> alter system kill session '315,1223';
System altered.

SQL> drop user xxxxx cascade;
User dropped.

size for asm filesystems

in OEM grid control, go to the hosts and type in the required host
click on the host name
click on targets
click on the asm instance
click on disk groups

which parameters are static and dynamic

https://netfiles.uiuc.edu/jstrode/www/oraparm/index.html

questions to developers

Before implementing the change in to the production, have you tested in the development environments?
Have you created a ticket? You have to create a ticket even for non production environments.

add database to alert

find the database names in a particular version (eg., 10g or 11g)
go to OEM grid control(10g or 11g)
preferences
rules
check on "on call instance alerts/oncall - db instance alerts" and click edit

not able to login

When user says he is unable to connect, first you try those tnsnames by keeping in your tnsnames.ora file. It that works then ask user to keep the working tns entiries
in his tnsnames.ora file.Simple checks are :
Tnsping
Ping
When above works use sqlplus

purge junk tables

sql>purge dba_recyclebin
/

history of commands in unix

history
history -100
history -400

switch from one server to another server

ssh server_name (or) ipaddress

maximum sessions active

SQL> select count (*) from v$session;

maximum sessions allowed

SQL> SELECT name, value FROM v$parameter WHERE name = 'sessions'
/

find ip address using servername

C:\Users\user1>ping servername

find servername using ip address

C:\Users\user1>ping -a 1821.12.142.31

filesize

du -g -s *

gives the size in GB and lists the files (folders) in ascending order.
Biggest one first.

subdirectory size

du -sk *

directories consuming the most space
--------------------------------------------------
du -sk * sort -rn

analyze table

ANALYZE TABLE schemaname.tablename ESTIMATE STATISTICS
/

find whether a table is analyzed or not

SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='tablename' AND OWNER='ownername'
/

recreate dblinks

http://askdba.org/weblog/2009/01/recreating-database-link/

recreate synonyms

select ‘create public synonym ‘synonym_name’ for ‘table_owner’.'table_name’;’from dba_synonyms where table_owner=’ownername’ or owner in 'ownername'
/

recreate indexes

select 'drop index ' index_name ';' from all_indexes where table_owner='ownername'
/

SELECT 'alter index ' index_name ' rebuild ; ' FROM all_indexes where table_name='tablename' or table_owner='ownername'
/

difference of objects in 2 schemas

select table_name, index_name from dba_indexes where owner='owner1' minus select table_name,index_name from dba_indexes where owner='owner2'
/

run sql commands in background (similar to nohup in os)

vi test.sql
--------------
spool test.log
select name from v$database;
select name from v$database;
select name from v$database;
spool off;
exit

vi script.sh
-----------------
#! /bin/ksh
export ORACLE_SID=dbname
export ORACLE_HOME=/ora01/app/oracle/product/10.2.0
export PATH=$ORACLE_HOME/bin:$PATH
cd /ora01
sqlplus / as sysdba @test.sql
exit 0

$ chmod 777 test.sql script.sh

crontab -e
-----------------
41 15 15 06 * /ora01/script.sh mail myemail@gmail.com

crontab

00 12 24 05 00 /home/oracle/cronjobs/test.sh > /home/oracle/cronjobs/test.log 2>&1

00 - minutes
12 - hours
24 - day
05 - month
00 - weekday

blackout in oem grid control

go to targets
click on hosts
enter the host
click on blackout
select databasename
next check 'immediately & indefinite'
next
finish
you will receive a message like this "Blackout "Blackout_May 23 2011 9:20:15 AM" created successfully.
Click on "End Blackout" if you want to end the blackout.

create user

CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE tablespacename
TEMPORARY TABLESPACE tablespacename
PROFILE DEFAULT
ACCOUNT UNLOCK
/
GRANT "CONNECT" TO username
/
GRANT "RESOURCE" TO username
/
ALTER USER username DEFAULT ROLE "CONNECT",
"RESOURCE"
/
GRANT UNLIMITED TABLESPACE TO username
/

impdp parameters

attach
content
directory
dumpfile
estimate
exclude
full
include
job_name
logfile
parallel
parfile
remap_datafile
remap_schema
remap_tablespace
reuse_datafiles
schemas
skip_unusable_indexes
sqlfile
status
table_exists_action
tables
tablespaces
transport_datafiles
transport_tablespaces
kill_job
start_job
stop_job

expdp parameters

attach
content
directory
dumpfile
logfile
filesize
exclude
estimate_only
full
include
parallel
parfile
schemas
status
tables
tablespaces
transport_tablespaces
kill_job
stop_job

imp parameters

file
log
buffer
commit
fromuser
touser
ignore
show
filesize
statistics

exp parameters

file
log
buffer
statistics
compress
owner
filesize
consistent

oracle errors

$oerr ORA 01934

which shell

echo $SHELL

scp command

run in foreground
-----------------------
$ scp foobar.txt your_username@remotehost.edu:/some/remote/directory

run in background
------------------------
$ scp foobar.txt your_username@remotehost.edu:/some/remote/directory
ctrl z
$ bg

kill session

os level
---------
ps -ef grep databasename
kill -9 processid
kill only sessions which (LOCAL=NO)

database level
-----------------
select sid, serial# from v$session;
alter system kill session 'sid,serial#';

You can get the SID from OEM grid control (performance page). Once you know the SID, you can issue the below command in order to find out the session id,

SQL> SELECT SID, SERIAL#, USERNAME FROM V$SESSION where SID='797';

SID SERIAL# USERNAME
---------- ---------- ------------------------------
797 34605 username

nohup

nohup command &
nohup ./filename &

validate objects

http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php

select object_type, count(*) from dba_objects where owner in 'ownername' group by object_type

select object_name, object_type from dba_objects where owner in 'ownername' order by object_name
/

select count(*), object_type from user_objects group by object_type
/

select owner, count(*) from dba_objects group by owner
/

Index size

select sum(bytes)/1024/1024/1024 "GB" from dba_extents where segment_name ='segmentname'
/

biggest table

select table_name, num_rows from dba_tables order by num_rows desc
/

Table size

select sum(bytes)/1024/1024/1024 from dba_segments where owner='ownername' AND segment_name='segmentname'
/

metadata

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_list.sql
SELECT DBMS_METADATA.GET_DDL('USER', username) FROM DBA_users;
select dbms_metadata.get_ddl('TABLE','table_name','owner') from dual;
select dbms_metadata.get_ddl('INDEX','index_name','owner') from dual;
SELECT DBMS_METADATA.GET_DDL('TABLESPACE','tablespace_name') FROM dual;
select dbms_metadata.get_ddl('CLUSTER','C_MLOG#','SYS') from dual;
select dbms_metadata.get_ddl('CONTEXT','LT_CTX') from dual;
select dbms_metadata.get_ddl('DB_LINK','PROD.WORLD','ADAM') from dual;
select dbms_metadata.get_ddl('FUNCTION','TO_DATE_FUNC','SCOTT') from dual;
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','BS_TAB_MV','SYS') from dual;
select dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG','MLOG$_BS_TABLE','SYS') from dual;
select dbms_metadata.get_ddl('PACKAGE','XMLPARSER','SYS') from dual;
select dbms_metadata.get_ddl('PACKAGE_BODY','STATSPACK','PERFSTAT') from dual;
select dbms_metadata.get_ddl('PROCEDURE','ORA$_SYS_REP_AUTH','SYSTEM') from dual;
select dbms_metadata.get_ddl('SEQUENCE','STATS$SNAPSHOT_ID','PERFSTAT') from dual;
select dbms_metadata.get_ddl('SYNONYM','/2fddc42_paintARGB_PRE_ONTO_S5','PUBLIC') from dual;
select dbms_metadata.get_ddl('TRIGGER','DEF$_PROPAGATOR_TRIG','SYSTEM') from dual;
select dbms_metadata.get_ddl('TYPE','XMLSEQ_IMP_T','SYS') from dual;
select dbms_metadata.get_ddl('TYPE_BODY','ORACLE_LOADER','SYS') from dual;
select dbms_metadata.get_ddl('VIEW','DBA_PROPAGATION','SYS') from dual;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','tablename','schemaname') from dual;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','indexname','schemaname') from dual;
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','tablename','schemaname') from dual;
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','schemaname') from dual;
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','schemaname') from dual;
select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','schemaname') from dual;
spool off


metadata for all the users
----------------------------------
set head off
set pages 0
set long 9999999
spool test.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) '/' DDL FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) '/' DDL FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) '/' DDL FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) '/' DDL FROM DBA_USERS;
spool off;


metadata for complete tables and indexes
----------------------------------------------------------
set pagesize 0
set long 90000
set feedback off
set echo off
spool schema.sql
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)FROM USER_INDEXES u;
spool off;


metadata for all the tables in a schema
--------------------------------------------------------
set pagesize 0
set long 90000
spool tables_ddl.sql
SELECT dbms_metadata.get_ddl('TABLE', table_name, 'SCOTT') FROM ALL_TABLES WHERE OWNER = 'SCOTT';

database size

set linesize 100
col database_size format a30
col used_space format a30
col free_space format a30
select
 sum(used.bytes) / 1024 / 1024 / 1024  || ' GB' Database_Size,
 sum(used.bytes) / 1024 / 1024 / 1024  - free.space / 1024 / 1024 / 1024 || ' GB' Used_space,
 free.space / 1024 / 1024 / 1024 || ' GB' Free_space
 from
 (select bytes
 from v$datafile
 union   all
 select bytes
 from v$tempfile
 union   all
 select bytes
 from v$log) used,
 (select sum(bytes) space
 from dba_free_space) free
 group by free.space;

schema size

All schema sizes
-----------------
select OWNER,sum(BYTES)/1024/1024/1024 GB from dba_segments
group by OWNER
order by GB
/

only one schema size
----------------------
SELECT SUM(BYTES)/1024/1024/1024 "GB" FROM DBA_SEGMENTS WHERE OWNER='schemaname'
/

convert MB to GB

http://www.convertunits.com/from/MB/to/GB

Add datafile to a tablespace

select file_name from dba_data_files where tablespace_name='tablespacename';

note down the 'datafilename with complete location'

ALTER TABLESPACE tablespacename ADD DATAFILE 'datafilename with complete location' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
/