Thursday, January 1, 2015
Friday, December 26, 2014
Wednesday, December 24, 2014
how to check last analyzed table in oracle
SELECT table_name,num_rows,last_analyzed FROM user_TABLES
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------
MTHPASSKBS 611 15-SEP-14
SH_NO 6319 19-SEP-14
D_R_D_SEP 280 11-OCT-14
REQ_TRAINEE_T 3152 20-DEC-14
REQUISITION_T 1251 20-DEC-14
EVALUATION_T 1349 20-DEC-14
EVALUATION_WEEK_T 296 20-DEC-14
REGISTRATION_T 1634 20-DEC-14
OFFICEORDER_T 2069 20-DEC-14
MASTER 710 20-DEC-14
10 rows selected
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------
MTHPASSKBS 611 15-SEP-14
SH_NO 6319 19-SEP-14
D_R_D_SEP 280 11-OCT-14
REQ_TRAINEE_T 3152 20-DEC-14
REQUISITION_T 1251 20-DEC-14
EVALUATION_T 1349 20-DEC-14
EVALUATION_WEEK_T 296 20-DEC-14
REGISTRATION_T 1634 20-DEC-14
OFFICEORDER_T 2069 20-DEC-14
MASTER 710 20-DEC-14
10 rows selected
ANALYZE TABLE command
ANALYZE TABLE <TABLE_NAME> COMPUTE STATISTICS;
Tuesday, December 23, 2014
How to check whether Oracle instance is started using pfile or spfile?
SQL> show parameter spfile;
NAME TYPE VALUE
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
spfile string F:\APP\PRABHAKAR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA
It gives path of spfile file which is getting used by database for starting up.
It means my database is using spfile
No value is displayed because database is not using spfile it is using pfile.
It means database is using pfile for startup.
Monday, May 12, 2014
MySQL: SHOW FUNCTION STATUS WHERE Db = ‘name’: Cannot load from mysql.proc. The table is probably corrupted
mysqldump: Couldn't execute
'SHOW FUNCTION STATUS WHERE Db ='something'':
Cannot load from mysql.proc.
The table is probably corrupted (1548)
The reason why is because MySQL 5.5 provided an update to the schemas, which are not automatically applied to already existing databases. To provide that schema update, it's important to run the MySQL upgrade command, as it also helps with missing tables or prefixed tables.
The command is run as follows.
$ mysql_upgrade
If you're getting an access denied error, pass along the username + password.
$ mysql_upgrade mysqlcheck:
Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to
connect FATAL ERROR: Upgrade failed
$ mysql_upgrade -u root -p
That should solve your problem. If it does not, have a look at your MySQL logfiles for more indications.
Wednesday, April 23, 2014
Linux Command
File Commands
ls -list directoryls -al -formatted listing with hidden filesls -ltr -formatted listing ordered by timecd dir -change to directorycd - -go to directory in previouslycd -go to home directorypwd -show current directory pathmkdir dir -make directory dirrm file -delete filerm -r dir -delete directory dirrm -f file -force remove filerm -rf dir -force remove directory dircp file1 file2 -copy file1 to file2cp -r dir1 dir2 -copy dir1 to dir2; create dir2 if it doesn’t existmv file1 file2 -rename/move file1 to file2 if file2 is an existing dir, moves file1ln -s file link -create symbolic link link to filetouch file -create or update filecat > file -places standard input into filemore file -output the contents of filehead file -output the first 10 lines of filetail file -output the last 10 lines of filetail -f file -output the contents of file as it grows, starting with the last 10ssh user@host -connect to host as userssh -p port user@host -connect to host on port port as userssh-copy-id user@host -add your key to host for user to enable a keyed or passwordless loginping host – ping host and output resultswhois domain – get whois information for domaindig domain – get DNS information for domaindig -x host – reverse lookup hostwget file – download filewget -c file – continue a stopped downloadSave File in Vi / Vim Text Editor
Command Description
q :Quit
q! :Quit without saving changes i.e. discard changes
r : :fileName Read data from file called fileName
wq :Write and quit (save and exit)
w :fileName Write to file called fileName (save as)
w! :fileName Overwrite to file called fileName (save as forcefully)
Monday, April 21, 2014
Table Fragmentation in oracle
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size",
round((num_rows*avg_row_len/1024),2)||'kb' "Actual size",
round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100
-10 "reclaimable space % " from dba_tables where table_name =’
table_Name’ AND OWNER LIKE 'schema_name';
PAYSOUTH_REG PAY_EARNINGS 18944kb 15808.48kb 3135.52kb 6.55152027027027027027027027027027027027
Gather table states:
exec dbms_stats.gather_table_stats('schema_name','table_name');
Rebuild all indexes:
select index_name from user_indexes where table_name like 'table_name';
Subscribe to:
Posts (Atom)
