RAM&KRISHNA BLOG

prerequisite step: 1. Need to install and configure the GRID on the standalone server. 2. First check the locations of Parameter file, Control file, Data file, Temp file and Redo Log files. This is important piece of information as we move database files to ASM. show parameter pfile; select name from v$controlfile; select name from v$datafile; select name from v$tempfile; select member from v$logfile; 3.Take backup of spfile using RMAN and restore it to ASM disk group. rman target / backup as backupset spfile; restore spfile to '+DATA/spfileoradb.ora'; 4.Rename old spfile as it no longer required mv $ORACLE_HOME/dbs/spfileoradb.ora $ORACLE_HOME/dbs/spfileoradb.ora_old 5.Create new pfile which points to the spfile on ASM vi $ORACLE_HOME/dbs/initoradb.ora spfile='+DATA/spfileoradb.ora' 6.Bounce the database and check if it’s showing new location for spfile shut immediate startup nomount; show Parameter pfile; 7. As our database is already in no-mount mode, we shall connec

 -- Ensure you're logged in as the appropriate user -- 1. Define variables for schema and table names DEFINE schema_name = 'YOUR_SCHEMA_NAME'; DEFINE table_name = 'YOUR_TABLE_NAME'; -- 2. Check the status and size of the table before reorganization SELECT table_name, num_rows, blocks, empty_blocks, avg_row_len FROM dba_tables WHERE owner = '&schema_name' AND table_name = '&table_name'; -- 3. Move the table within the same tablespace to reorganize it -- No tablespace specified, it will remain in the same tablespace ALTER TABLE &schema_name..&table_name MOVE; -- 4. Rebuild indexes associated with the table -- Get all indexes associated with the table SELECT 'ALTER INDEX ' || index_name || ' REBUILD;'  FROM dba_indexes  WHERE owner = '&schema_name' AND table_name = '&table_name'; -- 5. Gather statistics for the table after reorganization EXEC DBMS_STATS.GATHER_TABLE_STATS('&schema_name'