Common Static DBA View:
====================
DBA_TABLES;
DBA_TAB_COLUMNS;
DBA_CONSTRAINTS;
DBA_CONS_COLUMNS;
DBA_INDEXES;
DBA_TABLESPACES;
DBA_DATA_FILES;
DBA_OBJECTS;
DBA_SEGMENTS;
DBA_EXTENTS;
DBA_FREE_SPACE;
DBA_VIEW;
DBA_SYNONYMS;
DBA_SYS_PRIVS;
DBA_TABS_PRIVS;
DBA_TRIGGERS;
select tablespace_name,sum(bytes)/(1024*1024) from sm$ts_used
group by tablespace_name;
V$sort_segment,
dba_temp_files,
database version:
select * from v$version;
database character set information:
select * from nls_database_parameters;
select * from session_roles;
select * from session_privs
datafile resize:
alter database datafile 'E:\ORADATA\ORADATA\user_data3.dbf' resize 2048m;
database size:(must connect in sys)
select sum(bytes)/1024/1024/1024 as gb from dba_data_files;
size occupied by data in database:
select sum(bytes)/1024/1024/1024 as gb from dba_segments;
SIZE OF SCHEMA/USER:
select sum(bytes)/1024/1024 as gb from dba_segments where owner='GBDML';
v$sqltext_with_newlines
v$session
v$process
v$sesstatse
v$statname
v$session_longops
show parameter db_name;
SELECT LOG_MODE FROM V$DATABASE;
ARCHIVE LOG LIST
blog: http://oracledbaconsolidation.blogspot.com
1} V$CONTROLFILE > Lists the names and status of the control files.
2} V$DATABASE > Contains database information from the control files.
3} V$DATAFILE >Contains data file information from control file.
4} V$INSTANCE >Displays the state of the current instance.
5} V$PARAMETER > Lists parameters and values currently in effect for the Session also status and location of all parameters.
6} V$SESSION >Lists session information for each current session.
7} V$SGA >Contains summary information on SGA.
8} V$SPPARAMETER > Lists the contents of SPFILE.
9} V$TABLESPACE > Displays tablespace information from the control file.
10} V$THREAD >Contains thread information from control file.
11} V$VERSION > Version numbers of core library components in oracle server.
12} V$FIXED_TABLE >To find list of data dictionary views.
13} V$CONTROLFILE_RECORD_SECTION > Provides information about the Control file record section.
14}SHOW PARAMETER CONTROL_FILES > Lists the name, status and location of Control files.
15}V$THREAD >To display the current redo log group, the no. of online redo log groups and current sequence number.
16} V$LOGFILE >Displays each redo log group, member and status of each Member.
17} V$LOG > Same as above.
18} V$DATABASE_PROPERTIES >Name of default tablespaces.
19}DBA_TABLESPACES >Complete information about tablespaces.
20}DBA_DATA_FILES >Complete information of the files present in the Tablespaces.
21} DBA_TEMP_FILES >File’s information of temporary tablespace.
22} DBA_EXTENTS >To check the extents for a given segment.
23} DBA_SEGMENTS >View to get number of extents and blocks allocated to a Segment.
24} DBA_FREE_SPACE >Displays free extents in tablespace.
25} DBA_ROLLBACK_SEGS >To obtain information about all the undo segments In the database.
{IMP*:-Information about undo segments that are offline can be seen only in this view.}
26}V$ROLLSTAT & V$ROLLNAME >Views to obtain the statistics of the undo Segments currently used by the instance.
27} V$TRANSACTION & V$SESSION >To check the use of a undo segment by Currently active transactions.
28} DBA_TABLES à All the information about tables can be obtained here.
29} DBA_OBJECTS >All the information about objects in table can be Obtained here.
30} DBA_UNUSED_COL_TABS >To identify tables with unused columns.
31} DBA_PARTIAL_DROP_TABS >To identify tables that have partially Completed DROP columns operations.
32}DBA_INDEXES >Provides information on the indexes.
33}DBA_IND_COLUMNS >Provides information on the columns indexed.
34}V$OBJECT _USAGE >Provides information on the usage of an index.
35}DBA_CONSTRAINTS >To obtain name, type and status of all constraints.
36}DBA_CONS_COLUMNS >To obtain the columns in the constraints on table.
37}DBA_USERS >To obtain information about account status, default Tablespace for users.
38}DBA_PROPERTIES >View to display passwd profile information.
39}DBA_TS_QUOTAS >Amount of space a user can use in tablespaces.
40}DBA_SYS_PRIVS > Lists system privileges granted to users and roles.
41} V$SESSION_PRIVS > Lists the privileges that are currently available to user.
42} DBA_TAB_PRIVS > Lists all grants on all objects in the database.
43}DBA_COL_PRIVS >Describes all object grants in the database.
44}DBA_ROLES >All roles that exist in the database.
45}DBA_ROLES_PRIVS >Roles granted to users and roles.
46}V$ROLE_ROL_PRIVS > Roles that are granted to roles.
47}DBA_SYS_PRIVS >System privileges granted to users and roles.
48}V$ROLE_SYS_PRIVS > System privileges granted to roles.
49}V$ROLE_TAB_PRIVS >Object privileges granted to roles.
50}V$SESSION_ROLES >Roles that the user currently has enabled.
There is a difference between:
Taking the tablespace offline and taking the datafiles offline
ALTER TABLESPACE (tablespace_name ) OFFLINE; does a checkpoint on all datafiles and then takes the datafiles offline.
ALTER DATABASE DATAFILE (datafile_path) OFFLINE;does not perform a checkpoint, so that if the database is open, you may need to perform media recovery when bringing it online.
That is the reason why:
You cannot do ‘alter database datafile (datafile_path) offline’ if you are in noarchivelog (but tablespace offline works)
You cannot do ‘alter tablespace (tablespace_name) offline’ if database is read-only (but datafile offline works)
Note that in both cases, you can check the STATUS column from v$datafile to see if the file is online, offline or needs recovery.
====================
DBA_TABLES;
DBA_TAB_COLUMNS;
DBA_CONSTRAINTS;
DBA_CONS_COLUMNS;
DBA_INDEXES;
DBA_TABLESPACES;
DBA_DATA_FILES;
DBA_OBJECTS;
DBA_SEGMENTS;
DBA_EXTENTS;
DBA_FREE_SPACE;
DBA_VIEW;
DBA_SYNONYMS;
DBA_SYS_PRIVS;
DBA_TABS_PRIVS;
DBA_TRIGGERS;
select tablespace_name,sum(bytes)/(1024*1024) from sm$ts_used
group by tablespace_name;
V$sort_segment,
dba_temp_files,
database version:
select * from v$version;
database character set information:
select * from nls_database_parameters;
select * from session_roles;
select * from session_privs
datafile resize:
alter database datafile 'E:\ORADATA\ORADATA\user_data3.dbf' resize 2048m;
database size:(must connect in sys)
select sum(bytes)/1024/1024/1024 as gb from dba_data_files;
size occupied by data in database:
select sum(bytes)/1024/1024/1024 as gb from dba_segments;
SIZE OF SCHEMA/USER:
select sum(bytes)/1024/1024 as gb from dba_segments where owner='GBDML';
v$sqltext_with_newlines
v$session
v$process
v$sesstatse
v$statname
v$session_longops
show parameter db_name;
SELECT LOG_MODE FROM V$DATABASE;
ARCHIVE LOG LIST
blog: http://oracledbaconsolidation.blogspot.com
Data File Add:
===========
BEGIN
forms_ddl('alter tablespace user_data add datafile ''E:\ORADATA\ORADATA\USER_DATA5.DBF'' SIZE 1024M');
END;
forms_ddl('alter tablespace user_data add datafile ''E:\ORADATA\ORADATA\USER_DATA5.DBF'' SIZE 1024M');
END;
Free Tablespace :
=============SELECT Total.name "Tablespace Name", nvl(Free_space, 0) Free_space, nvl(total_space-Free_space, 0) Used_space, total_space FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space from sys.dba_free_space group by tablespace_name ) Free, (select b.name, sum(bytes/1024/1024) TOTAL_SPACE from sys.v_$datafile a, sys.v_$tablespace B where a.ts# = b.ts# group by b.name ) Total WHERE Free.Tablespace_name(+) = Total.name ORDER BY Total.name
VIEWS WE QUERY IN DATABASE
1} V$CONTROLFILE > Lists the names and status of the control files.
2} V$DATABASE > Contains database information from the control files.
3} V$DATAFILE >Contains data file information from control file.
4} V$INSTANCE >Displays the state of the current instance.
5} V$PARAMETER > Lists parameters and values currently in effect for the Session also status and location of all parameters.
6} V$SESSION >Lists session information for each current session.
7} V$SGA >Contains summary information on SGA.
8} V$SPPARAMETER > Lists the contents of SPFILE.
9} V$TABLESPACE > Displays tablespace information from the control file.
10} V$THREAD >Contains thread information from control file.
11} V$VERSION > Version numbers of core library components in oracle server.
12} V$FIXED_TABLE >To find list of data dictionary views.
13} V$CONTROLFILE_RECORD_SECTION > Provides information about the Control file record section.
14}SHOW PARAMETER CONTROL_FILES > Lists the name, status and location of Control files.
15}V$THREAD >To display the current redo log group, the no. of online redo log groups and current sequence number.
16} V$LOGFILE >Displays each redo log group, member and status of each Member.
17} V$LOG > Same as above.
18} V$DATABASE_PROPERTIES >Name of default tablespaces.
19}DBA_TABLESPACES >Complete information about tablespaces.
20}DBA_DATA_FILES >Complete information of the files present in the Tablespaces.
21} DBA_TEMP_FILES >File’s information of temporary tablespace.
22} DBA_EXTENTS >To check the extents for a given segment.
23} DBA_SEGMENTS >View to get number of extents and blocks allocated to a Segment.
24} DBA_FREE_SPACE >Displays free extents in tablespace.
25} DBA_ROLLBACK_SEGS >To obtain information about all the undo segments In the database.
{IMP*:-Information about undo segments that are offline can be seen only in this view.}
26}V$ROLLSTAT & V$ROLLNAME >Views to obtain the statistics of the undo Segments currently used by the instance.
27} V$TRANSACTION & V$SESSION >To check the use of a undo segment by Currently active transactions.
28} DBA_TABLES à All the information about tables can be obtained here.
29} DBA_OBJECTS >All the information about objects in table can be Obtained here.
30} DBA_UNUSED_COL_TABS >To identify tables with unused columns.
31} DBA_PARTIAL_DROP_TABS >To identify tables that have partially Completed DROP columns operations.
32}DBA_INDEXES >Provides information on the indexes.
33}DBA_IND_COLUMNS >Provides information on the columns indexed.
34}V$OBJECT _USAGE >Provides information on the usage of an index.
35}DBA_CONSTRAINTS >To obtain name, type and status of all constraints.
36}DBA_CONS_COLUMNS >To obtain the columns in the constraints on table.
37}DBA_USERS >To obtain information about account status, default Tablespace for users.
38}DBA_PROPERTIES >View to display passwd profile information.
39}DBA_TS_QUOTAS >Amount of space a user can use in tablespaces.
40}DBA_SYS_PRIVS > Lists system privileges granted to users and roles.
41} V$SESSION_PRIVS > Lists the privileges that are currently available to user.
42} DBA_TAB_PRIVS > Lists all grants on all objects in the database.
43}DBA_COL_PRIVS >Describes all object grants in the database.
44}DBA_ROLES >All roles that exist in the database.
45}DBA_ROLES_PRIVS >Roles granted to users and roles.
46}V$ROLE_ROL_PRIVS > Roles that are granted to roles.
47}DBA_SYS_PRIVS >System privileges granted to users and roles.
48}V$ROLE_SYS_PRIVS > System privileges granted to roles.
49}V$ROLE_TAB_PRIVS >Object privileges granted to roles.
50}V$SESSION_ROLES >Roles that the user currently has enabled.
ALTER TABLESPACE OFFLINE vs. ALTER DATABASE DATAFILE OFFLINE
Taking the tablespace offline and taking the datafiles offline
ALTER TABLESPACE (tablespace_name ) OFFLINE; does a checkpoint on all datafiles and then takes the datafiles offline.
ALTER DATABASE DATAFILE (datafile_path) OFFLINE;does not perform a checkpoint, so that if the database is open, you may need to perform media recovery when bringing it online.
That is the reason why:
You cannot do ‘alter database datafile (datafile_path) offline’ if you are in noarchivelog (but tablespace offline works)
You cannot do ‘alter tablespace (tablespace_name) offline’ if database is read-only (but datafile offline works)
Note that in both cases, you can check the STATUS column from v$datafile to see if the file is online, offline or needs recovery.
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন